2016-数据库原理实验实验指导书(SQL Server 2008) 下载本文

数据库原理实验指导书

实验8 存储过程和触发器

1.实验目的

(1)掌握通过SQL Server管理平台和Transact-SQL语句CREATE PROCEDURE创建存储过程的方法和步骤。

(2)掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。 (3)掌握通过SQL Server管理平台和Transact-SQL语句ALTER PROCEDURE修改存储过程的方法。

(4)掌握通过SQL Server管理平台和Transact-SQL语句DROP PROCEDURE删除存储过程的方法。

(5)掌握通过SQL Server管理平台和Transact-SQL语句CREATE TRIGGER创建触发器的方法和步骤。

(6)掌握引发触发器的方法。

(7)掌握使用SQL Server管理平台或Transact-SQL语句修改和删除触发器。 (8)掌握事务、命名事务的创建方法,了解不同类型的事务的处理情况。

2.实验内容及步骤

(1)在查询设计器中输入以下代码,创建一个利用流控制语句的存储过程letters_print,该存储过程能够显示26个小写字母。

CREATE PROCEDURE letters_print AS

DECLARE @count int SET @count=0 WHILE @count<26 BEGIN

PRINT CHAR(ASCII('a')+ @count) SET @count=@count +1 END

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

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

(2)输入以下代码,创建存储过程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

使用EXECUTE命令执行存储过程stu_info,其参数值为“马东”。 如果存储过程stu_info执行时没有提供参数,要求能按默认值查询(设姓名为“刘卫平”),如何修改该过程的定义?

21

数据库原理实验指导书

(3)使用studentsdb数据库中的student_info表、curriculum表、grade表。

① 创建一个存储过程stu_grade,查询学号为0001的学生的姓名、课程名称、分数。 ② 执行存储过程stu_grade,查询0001学生的姓名、课程名称、分数。 ③ 使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。 (4)使用student_info表、curriculum表、grade表。

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

② 执行存储过程stu_g_p,查询“刘卫平”的学号、选修课程和课程成绩。 ③ 使用系统存储过程sp_helptext,查看存储过程stu_g_p的文本信息。 (5)使用student_info表。

① 创建一个加密的存储过程stu_en,查询所有男学生的信息。 ② 执行存储过程stu_en,查看返回学生的情况。

③ 使用Transact-SQL语句DROP PROCEDURE删除存储过程stu_en。 (6)使用grade表。

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

② 执行存储过程stu_g_r,输入学号0002。 ③ 显示0002号学生的平均成绩。

(7)输入以下代码,复制student_info表命名为stu2,为stu2表创建一个触发器stu_tr,当stu2表插入一条记录时,为该记录生成一个学号,该学号为学号列数据的最大值加1。

--复制student_info表命名为stu2

SELECT * INTO stu2 FROM student_info GO

--为stu2表创建一个INSERT型触发器stu_tr CREATE TRIGGER stu_tr ON stu2 FOR INSERT AS

DECLARE @max char(4)

SET @max=(SELECT MAX(学号) FROM stu2)

SET @max=@max+1

UPDATE stu2 SET 学号=REPLICATE('0',4-len(@max))+@max

执行以上代码,查看studentsdb数据库中是否有stu2表,展开stu2,查看其触发器项中是否有stu_str触发器。

在查询设计器的编辑窗口输入以下代码:

运行以上代码,查看stu2表的变化情况,为什么插入记录的学号值发生了改变?

(8)为grade表建立一个名为insert_g_tr 的INSERT触发器,当用户向grade表中插入记录时,如果插入的是在curriculum表中没有的课程编号,则提示用户不能插入记录,否则提示记录插入成功。在进行插入测试时,分别输入以下数据:

学号 课程编号 分数

0004 0003 76 0005 0007 69

观察插入数据时的运行情况,说明为什么?

22

INSERT INTO stu2(学号,姓名,性别) VALUES('0001','张主','女')

FROM stu2 INNER JOIN inserted on stu2.学号=inserted.学号

数据库原理实验指导书

(9)为curriculum表创建一个名为del_c_tr的DELETE触发器,该触发器的作用是禁止删除curriculum表中的记录。

(10)为student_info表创建一个名为update_s_tr的UPDATE触发器,该触发器的作用是禁止更新student_info表中的“姓名”字段的内容。

(11)使用Transact-SQL语句DROP TRIGGER删除update_s_tr触发器。 (12)为student_info表建立删除触发器del_s_tr,要求当student_info表的记录被删除后,grade表中相应的记录也能自动删除。

(13)在studentsdb数据库中,执行以下事务处理过程,说明这些事务属于哪一种事务类型(隐性事务、显性事务或自动式事务)。

BEGIN TRANSACTION

INSERT INTO student_info(学号,姓名) VALUES('0009','李青')

COMMIT TRANSACTION

SET IMPLICIT_TRANSACTIONS ON

GO

INSERT INTO grade(学号,课程编号) VALUES('0005','0007')

GO

IF ((SELECT count(*) FROM curriculum WHERE 课程编号='0007')=0)

23

数据库原理实验指导书

附录1:SQL server 2008 R2 安装图解

24