数据库应用程序

上机实验六

【实验目的】

(1) 学会在图形用户界面下使用Management Studio工具和Transact-SQL语句创建存储过程。

(2) 掌握存储过程的管理方法。

(3) 学会在图形用户界面下使用Management Studio工具和Transact-SQL语句创建触发器。

(4) 掌握触发器的管理方法。

(5) 通过实验理解存储过程和触发器的作用。

【实验准备】

(1) 已经接入局域网的网络实验室。

(2) 机器上已安装SQL Server 2008 R2。

【实验内容及步骤1】

（1）创建一个名为xs_bk_proc的存储过程，完成不及格学生的学号、姓名、课程名，成绩和班号信息的查询。

CREATE PROC xs_bk_proc AS

SELECT学号,姓名,课程名,成绩,班号信息

FROM学生基本信息JOIN课程ON学生基本信息.学号=课程.学号JOIN班级ON学生基本信息.学号=班级.学号

where成绩<60

（2）在STUMS数据库中，基于班级表创建一个名为BJ_INFO_PROC的存储过程，根据班号查询班主任、班长和教室位置信息。

CREATE PROC BJ_INFO_PROC

@BH CHAR(2)

AS

SELECT班主任,班长,教室位置

FROM班级

WHERE班号=@BH

（3）创建一个名为xs_tj_proc的存储过程，实现按性别统计学生数。

CREATE PROC xs_tj_proc

@count int OUTPUT

AS

SELECT@count=COUNT(*)

FROM班级

GROUP BY性别

（4）调用上述xs_tj_proc存储过程，统计女生人数。

DECLARE@num int

EXEC xs_tj_proc@num OUTPUT

PRINT@num

（5）创建一个名为xk_ins_proc的存储过程，用于向选课表插入记录。

CREATE PROCEDURE xk_ins_proc

@KCH char(4),

@KCM varchar(20),

@KCXZ char(1),

@XF tinyint

AS

INSERT课程V ALUES(@KCH,@KCM,@KCXZ,@XF)

（6）创建一个名为xk_cj_proc的存储过程，根据课程号更新选课表中的对应成绩，令成绩等于0。

ALTER PROC xk_cj_proc

@KCH char(6)

AS

SELECT成绩AS'0'

FROM选课

where课程号=@KCH

（7）使用系统存储过程查看xk_cj_proc的定义信息、一般信息和相关性信息。

EXEC sp_helptext xk_cj_proc

EXEC sp_help xk_cj_proc

EXEC sp_depends xk_cj_proc

（8）使用ALTER PROCEDURE 命令修改xs_tj_proc存储过程，实现按系部统计学生数。

ALTER PROCEDURE xs_tj_proc

@count int OUTPUT

AS

SELECT@count=COUNT(*)

FROM学生

GROUP BY系部

（9）将存储过程xs_tj_proc重命名为xs_xibu_proc。

sp_rename'xs_tj_proc','xs_xibu_proc'

（10）删除xk_ins_proc、xk_cj_proc存储过程。

DROP PROCEDURE xk_ins_proc,xk_cj_proc

【实验内容及步骤2】

写出T-SQL语句，对STUMS数据库进行如下操作：

（1）在专业表上创建一个名为zy_all_trigger触发器，使得用户执行删除、插入、修改操作时，该触发器被触发，自动给出报警信息“不能更改此表数据！”，并撤销此次操作。

CREATE TRIGGER zy_all_trigger ON专业

FOR DELETE,UPDATE,INSERT

AS

BEGIN

PRINT('不能更改此表数据！')

ROLLBACK TRANSACTION

END

（2）在系部表上创建一个名称为xbjs_delete_trigger触发器，当删除系部表中的记录时，如果教师表中引用了此记录的系部代码，则提示“用户不能删除！”，否则提示“记录已删除！”。

CREATE TRIGGER xbjs_delete_trigger ON系部

FOR DELETE

AS

DECLARE@XBDM CHAR(9)

SELECT@XBDM=教师.系部代码

FROM教师,deleted

WHERE教师.系部代码=deleted.系部代码

IF@XBDM<>' '

PRINT('用户不能删除!')

ELSE

PRINT('记录已删除!')

ROLLBACK TRANSACTION

（3）在选课表上创建一个名称为xkkc_insert_trigger触发器，当向选课表中插入记录时，检查该记录的课程号在课程表中是否存在，如果不存在，则不允许插入。

CREATE TRIGGER xkkc_insert_trigge ON选课

FOR INSERT

AS

DECLARE@KCH CHAR(9)

SELECT@KCH=课程表.课程号

FROM课程,inserted

WHERE课程表.课程号=inserted.课程号

IF@KCH<>' '

PRINT('记录插入成功')

ELSE

BEGIN

PRINT('课程号不存在，不能插入记录，插入将终止！')

ROLLBACK TRANSACTION

END

（4）在学生基本信息表上创建一个名称为xsxk_updare_trigger触发器，当修改学生基本信息表中的学号时，如果选课表中引用了该学号，则作同样的修改，并提示“记录已修改！”。

CREATE TRIGGER xsxk_updare_trigger ON学生基本信息

FOR UPDATE

AS

IF UPDATE(学号)

BEGIN