《实验六-存储过程和触发器(共5页).doc》由会员分享，可在线阅读，更多相关《实验六-存储过程和触发器(共5页).doc（5页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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. 修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数，并利用该存储过程
4、以“法学院”为输入参数验证执行的结果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_type=实践goexec getPctStu 法学院4. 再修改
5、存储过程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_info.st_id=s_c_info.st_id and d_info.d_id=left(st_inf
6、o.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，旧成绩oldscore，新成绩newscore，操作员uname，操作时间udate，其中操作员设定默认值为user，操作时间默认值为系统时间。create
7、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(score)begin if(select count(*) from deleted)0 insert into sc_log(type,s
8、t_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,st_id,c_no,score from insertedend2. 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新，要求更新后的成绩不能比更新前低，如果新成
9、绩低则取消操作，给出提示信息，否则允许更新。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 (newscoreoldscore) begin print(新成绩不能比旧成绩低!) rollback transaction end(三)查看存储过程和触发器的信息1.用sp_help查看以上建立的存储
10、过程和触发器的基本信息sp_help getPctStusp_help tr_updasc2. 用sp_helptext查看存储过程和触发器的代码sp_helptext getPctStusp_helptext tr_updasc四、思考与练习1存储过程如何加密？在创建存储过程的语句as前添加语句with encryption。2 触发器有什么好处和坏处？好处：触发器可以帮助系统监测数据的正确处理，同时，触发器可以作为完整性约束，辅助用户建立正确的数据表，保证数据的正确性。坏处：触发器要用的恰到好处，一个大型应用里，触发器越少越好，触发器会使编程时源码的结构被迫打乱，为将来的程序修改、源码阅读带来很大不便。专心-专注-专业