实验六-存储过程和触发器

1、精选优质文档-倾情为你奉上实验六 存储过程和触发器一、目的与要求1. 掌握编写数据库存储过程的方法。2. 掌握建立数据库触发器的方法，通过实验观察触发器的作用和触发条件设置等相关操作。二、实验准备1. 了解编写存储过程和调用的T-SQL语法； 2. 了解触发器的作用；3. 了解编写触发器的T-SQL语法。三、实验内容(一) 存储过程在studentdb数据库中建立存储过程getPractice，查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。提示：D_Info表中存储了院系代码D_ID，而St_Inf

2、o表中学号字段St_ID的前两位与之对应，则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。1. 分别执行存储过程getPractice，查询“法学院”和“材料科学与工程学院”的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程名称。create procedure getPracticed_name varchar(30)asselect st_info.st_id,st_info.st_name,s_c_info.c_no,c_info.c_namefrom st_info,d_info,s_c_info,c_infowhere d_info.d_n

3、ame=d_name and st_info.st_id=s_c_info.st_id andd_info.d_id=left(st_info.st_id,2)and s_c_info.c_no=c_info.c_no and c_info.c_type='实践'Goexec getPractice '法学院'exec getPractice '材料科学与工程学院'2. 利用系统存储过程sp_rename将getPractice更名为getPctStusp_rename getpractice,getPctStu3. 修改存储过程getPctSt

4、u，返回指定院系中参与实践课程的学生人次数，并利用该存储过程以“法学院”为输入参数验证执行的结果alter procedure getPctStud_name varchar(30)asselect count(s_c_info.st_id)from s_c_info,c_info,d_info,st_infowhere d_info.d_name=d_name and st_info.st_id=s_c_info.st_id and d_info.d_id=left(st_info.st_id,2) and s_c_info.c_no=c_info.c_no and c_info.c_typ

5、e='实践'goexec getPctStu '法学院'4. 再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数。注：“人数”和“人次数”是不同的，对某一学生而言，如果参与了多门实践课程，则“人次数”是指其参与的课程门数，而“人数”仍为1。alter procedure getPctStud_name varchar(30)asselect count(distinct s_c_info.st_id)from s_c_info,c_info,d_info,st_infowhere d_info.d_name=d_name and st_inf

6、o.st_id=s_c_info.st_id and d_info.d_id=left(st_info.st_id,2)and s_c_info.c_no=c_info.c_no and c_info.c_type='实践' goexec getPctStu '法学院'(二) 触发器1.在studentdb数据库中建立一个具有审计功能的触发器：触发器名为tr_sc，功能要求：审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中，sc_log表中有如下字段：操作类型type，学号st_id，课程号c_no，旧成绩oldsco

7、re，新成绩newscore，操作员uname，操作时间udate，其中操作员设定默认值为user，操作时间默认值为系统时间。create table sc_log( type char(6), st_id char(8), c_no char(8), oldscore numeric(5,1), newscore numeric(5,1), uname varchar(20) default user, udate datetime default getdate()create trigger tr_sc on s_c_info for insert,updateasif update(s

8、core)begin if(select count(*) from deleted)<>0 insert into sc_log(type,st_id,c_no,oldscore,newscore) select 'update',i.st_id,i.c_no,d.score,i.score from inserted i ,deleted d where i.st_id=d.st_id and i.c_no=d.c_no else insert into sc_log(type,st_id,c_no,newscore) select 'insert

9、9;,st_id,c_no,score from insertedend2. 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新，要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。create trigger tr_updasc on s_c_infofor updateas declare newscore numeric(5,1),oldscore numeric(5,1) select newscore=score from inserted select oldscore=score from deleted if (newscore<oldscore) begin print('新成绩不能比旧成绩低!') rollback transaction end(三)查看存储过程和触发器的信息1.用sp_help查看以上建立的存储过程和触发器的基本信息sp_help getPctStusp_help tr_updasc2. 用sp_helptext查看存储过程和触发器的代码sp_helptext getPctStusp_helptext tr_updasc四、思考与练习1存储过程如何加密？在创建存储过程的语句as前添加语句with encry