实验8 存储过程和触发器

实验8 存储过程和触发器

一、实验目的

1．掌握通过企业管理器和Transact —SQL语句和CREATE PRODCEDURE创建存储过程的方法和步骤。

2．掌握使用企业管理器和Transact —SQL语句和EXECUTE执行存储过程的方法。

3．掌握通过企业管理器和Transact —SQL语句CREATE TRIGGER创建触发器的方法和步骤。

4．掌握引发存储器的方法。

5．掌握使用系统存储过程管理存储过程和触发器的方法。

6．掌握事物，命名事物的创建方法，不同类型的事务的处理情况。

二、实验准备

1．了解存储过程的基本概念和类型。

2．了解创建存储过程的Transact —SQL语句的基本语法。

3．了解查看、执行、修改和删除存储过程的基本语法。

4．了解触发器的基本概念和类型。

5．了解创建触发器的Transact —SQL语句基本用法。

6．了解查看、修改和删除触发器的Transact —SQL语句的用法。

7．了解创建事务，处理事务的方法和过程。

8．了解锁机制。

三、实验内容和步骤

1．在查询分析器中输入以下代码，创建一个利用流控制语句的存储过程lletters_print,该存储器能显示26个小写字母。

单击查询分析器的“执行查询”按钮，查看studentsdb数据库的存储过程是否有letters_print.

使用EXECUTE命令执行letters_print存储过程。

CREATE PROCEDURE letter_print

AS

DECLARE @count int

SET @count=0

WHILE @count<26

BEGIN

PRINT CHAR(ASCII(‘a’)+@count)

SET @count =@count +1

END

EXEC letter_print

输入以下代码，创建存储过程stu_info,执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。

使用EXECUTE命令执行存储过程stu_info，其参数值为“马东”。

如果存储过程stu_info执行时没有提供参数，能按默认值查询（设姓名为“刘卫平”），如何修改该过程的定义？

CREATE PROCEDURE stu_info @name varchar(40)

AS

SELECT a.学号，姓名，课程编号，分数

FROM student_info a INNER JOIN grade ta

ON a.学号=ta.学号

WHERE 姓名=@name

GO

EXECUTE stu_info '马东'

在创建系统存储过程后,插入默认值约束DEFAULT,语法:DEFAULT '刘卫平'

3.使用studentsdb数据库中的student_info表，curriculm表、grade表。

（1）创建一个存储过程stu_grade，查询学号为0001的学生的姓名，课程名称，分数。

（2）执行存储过程stu_grade,查询0001学生的姓名，课程名称，分数。

（3）使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g.

(1)

USE studentsdb

CREATE PROCEDURE stu_grade AS

SELECT student_info.姓名,curriculum.课程名称,grade.分数

FROM student_info,curriculum,grade

WHERE student_info.学号=0001

(2)

EXECUTE stu_grade

(3)

EXEC sp_rename 'stu_grade','stu_g'

4.使用student_info表，curriculm表、grade表。

（1）创建一个带参数的存储过程stu_p_g,当任意输入一个学生的姓名时，将从3个表中返回该学生的学号，选修的课程名称和课程成绩。

（2）执行存储过程stu_g_p，查询“刘卫平”的学号，选修课程和课程成绩。

（3）使用系统存储过程sp_helptext，查看存储过程stu_p_g的文本信息。

(1)

CREATE PROCEDURE stu_p_g @stu_name varchar(40) AS

SELECT student_info.学号,curriculum.课程名称,grade.分数

FROM student_info,curriculum,grade

WHERE student_info.姓名=@stu_name

(2)

EXECUTE stu_p_g '刘卫平'

(3)

EXEC sp_help_text 'stu_p_g'

5．使用student_info表。

（1）创建一个加密的存储过程stu_en，查询所有男学生的信息。

（2）执行存储过程stu_en,查看返回学生情况。

（3）使用Transact —SQL语句DROP PROCEDURE删除存储过程stu_en。

(1)

CREATE PROCEDURE stu_en WITH ENCRYPTION AS

SELECT * FROM student_info

WHERE 性别='男'

(2)

EXECUTE stu_en

(3)

DROP PROC stu_en

6．使用grade表。

（1）创建一个存储过程stu_g_r,当输入一个学生学号，通过返回输出参数获取该学生各门课程的平均成绩。

（2）执行存储过程stu_g_r,输入学号0002。

（3）使用Transact—SQL语句DROP PROCDURE删除存储过程stu_en.

(1)

CREATE PROCEDURE stu_g_r

@stu_id char(4)

@avg_grade int OUTPUT

AS

SELECT @avg_grade=A VG(分数)

FROM grade

WHERE 学号=@stu_id

GO

(2)