数据库原理与应用实验9

1、实验九存储过程与触发器一、实验目的及要求本实验主要目的是让学生通过实验掌握存储过程的创建及使用方式、触发器的创建，具体要求 有：理解存储过程的概念及类型，深刻理解存储过程的优点掌握创建各种存储过程的方法掌握调用存储过程的方法掌握查看、修改、删除存储过程的方法理解触发器的概念与类型理解触发器的功能及工作原理掌握创建、更改、删除触发器的方法掌握利用触发器维护数据完整性的方法二、实验原理及背景知识（一）存储过程知识存储过程是一种数据库对象，将执行计划存储在数据库的服务器中。它的运行速度比独立运行 同样的程序要快。1、存储过程类型1）系统存储过程存储在master数据库中，以sp_为前缀。可以在其他数

2、据库中对其进行调用。2）用户自定义存储过程由用户创建并能完成某一特定功能的存储过程，或称本地存储过程。2、创建存储过程存储过程的三个组成部分：（1）所有的输入参数以及传给调用者的输出参数。（2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。（3）返回给调用者的状态值，以指明调用是成功还是失败。3、T-SQL创建存储过程的基本语法格式：CREATEPROCEDURE存储过程名称参数定义AS SQL语句（二）触发器知识触发器是一种特殊类型的存储过程，主要通过事件进行触发执行的。可以实现由主键和外键所 不能保证的参照完整性和数据的一致性；强化约束；跟踪变化；级联运行；存储过程的调用等功能

3、。1、触发器的种类1）AFTER触发器：只有对表执行某一操作这后，才能被触发。可以为表的同一操作定义多个触 发器，其触发次序可使用sp_settriggerorder来完成。2）INSTEAD OF触发器：并不执行其所定义的操作而仅是执行触发器本身，对同一操作只能定 义一个instead of触发器。2、INSERTED 和 DELETED 临时表插入一行时，INSERTED表保存了一份插入行的拷贝。删除一行时，DELETED表保存了删除行的拷贝。更新一行时，INSERTED表中保存了一份新行的拷贝，DELETED表中保存了一份旧行的拷贝。3、语法格式：CREATE TRIGGER触发器名称O

4、N表或视图 FOR | AFTER | INSTEAD OF DELETE， INSERT， UPDATEAS SQL语句三、实验内容及步骤：I :基本实验内容在SQL Server 2000中创建存储过程和触发器虽然可以通过企业管理器进行，但是企业管理器 仅仅是提供了一个利用T-SQL编写存储过程和触发器的简单模板，大部分内容都是需要自己手工编 写的。而且在SQL Server 2005中只能通过T-SQL来编写存储过程和触发器。因此，本实验只要求 通过T-SQL来创建并管理存储过程和触发器，可视化方式请参考相关资料。（一）存储过程的创建和执行1、创建下面的存储过程并执行，然后分析其功能1）

5、创建CREATE PROCEDURE List stu courseAS_SELECT s . sno s sname s ssex, c.enor c cname, sc.mark:, k:. kcreditFROM student s course c,kaike k.student_course scWHERE s sno= sc. sno AND sc. cno= k. eno AND k:. eno c. enoGO2）执行EXEC list_stu_course或者list_stu_course3）功能分析2、带参数存储过程并执行创建CREATE PROC 1 i s t_s tu

6、dent_departm.ent (department char(20)AS|BEGINSELECT sno sname ssex, snative mname FROM student,rmajor departmentWHERE studentnmo=major mno田云二二二 -i-二二二二三二三；二三定二*二 2)END执行EXEC list_student_department department=工程与信息学院功能分析3、根据功能要求完成下面的任务在stuManag数据库中创建一个名为proc_1的存储过程，产生某门课程的选课学生情况列表，其 中包括课程号、课程名

7、、学号、姓名、成绩。要求输入某门课程的课程号，得到上述信息。1）创建：CREATE PROCEDURE proclASSELECT ccnor amer s.sno s.sname, sc.markFROM student s course cfstudent_course scWHERE s.sno = sc sno AND sc = 60PRINT ，此学生综合成绩合格！，ELSEprint ，此学生综合成绩不合格!GO2）执行：EXEC proc_2 091650102,（二）管理存储过程1、查看存储过程代码EXEC sp_helptext list_student_department

8、,EXEC sp_helptext objname=list_student_department*2、修改存储过程1）修改alter proc 1ist_student_departmentdepartment char（20） as beginselect dname r mname r count （*）学生人数 from student sf major department d.where s .mno=m.mno and. m,dno =d. dnoand d.n ame = dep a r tme ntgroup by dnamer nmame end.2）执行（查询工程与信息学

9、院情况）EXEC 3）功能分析：3、删除存储过程DROP PROCEDURE list_stu_course（三）触发器操作1、触发器创建及验证新建一个Test数据库，然后在Test数据库中完成下面内容。在查询分析器中输入下面的代码， 思考其功能，运行后，查看输出结果。并描述每一部分的功能或者输出结果。1）创建触发器创建表 TestTable1CREATE TAB崩 TestTablel（Cl INT, c2 CHAR（30）创建触发器CREATE TRIGGER trigl ON TestTablelFOR INSERT.UPDATEDELETE asSELECT *FROM TestTab

10、lel-下面两个语句分开执行INSERT TestTable1 VALUES（1, 林冲）UPDATE TestTable1 SET c2=宋江 WHERE c1=1输出结果触发器功能描述2) inserted表和deleted表的使用CREATE TRIGGER trig2 ON TestTablel FOR INSERTUPDATEDELETE ASPRINT r inserted: TSELECT * FROM INSERTEDPRINT deleted；, SELECT * FROM DELETEDGO-下面三个语句分开执行INSERT TestTablel VALUES(2, 吴用)

11、UPDATE TestTable1 SET c2=关胜 WHERE c1=2DELETE TestTable1 WHERE c1=2insert 和 update 触发器CREATE TABLE TestTable2(cl INTfc2 CHAR(LO)GOCREATE TRIGGER trig3 ON TestTable2FOR INSERT,UPDATEASDECLARE cl_l intSELECT C1_1=C1 FROM INSERTEDIF C1_11O8BEGIN ROLLBACKRAISERRORf Cl值必须在工到工08之间！ J , 16, 1) ENDGO-下面三个语句分

12、开执行INSERT TestTable2 VALUES (200, 武松)INSERT TestTable2 VALUES (15, 武松)UPDATE TestTable2 SET c1=300 WHERE c2=* 武松输出结果触发器功能描述4) delete触发器INSERT Stu VALUES (1 031703561 f T 陈功 1男17r 1 广东，)INSERT study VALUES (r 03170356f x1 88)INSERT study VALUES (T 03170356f r 2,99) GOCREATE TRIGGER trig4 ON stu FOR D

13、ELETEASDELETE study WHERE study,sno=(SELECT sno FROM DELETED) go-执行下面语句DELETE FROM stu WHERE sno=03170356输出结果触发器功能描述5)instead of触发器CREATE TRIGGER trig5 ON TestTable2 instead Of INSERT, UPDATE, DELETE ASINSERT TestTablel VALUES(100, r1234 r)-执行下面语句，然后查询TestTable1表INSERT INTO TestTable2 VALUES (10, 宋江

14、)输出结果触发器功能描 2、基于stuManag数据，根据功能要求，完成T-SQL语句1）创建触发器trigger_1，实现当修改学生表（student）中的数据时，显示提示信息“学生表被修 改了”。CREATE TRIGGER trigger_l ON studentFOR ASPRINT,学生表被修改了， GO2）创建触发器trigger_2,实现当删除课程表中某门学生的记录时，对应学生成绩表中所有有关该 学生的记录均删除。CREATE TRIGGER trigger_2 ON student IFOR DELETE AS DELETEFROM stud.ent_cours eWHERE

15、student_course. sno= （） GO创建触发器trigger_3，实现当修改学生课程表(course )中的某门课的课程号时，对应学生成 绩表(student_course)中的课程号也作修改。当修改学生课程表中记录时，相当于删除一条旧记录并插入一条新记录，删除的旧记录在deleted 临时表中，插入的新记录在inserted临时表中。因此，可以通过在查询分析器的查询窗口中输入如 下语句创建触发器：CREATE TRIGGER trigger_3 ON courseFOR UPDATE一ASBEGINUPDATE student_courseSET cno= (SELECT e

16、no FROM )WHERE cno= (SELECT eno from )END二3、删除触发器trigger_1 trigger_1II ：高级实验内容基于stuManag数据库完成下面的任务1、创建一个存储过程proc_name：当任意输入一个学生的姓名时，将返回该学生的学号、选修 的课程名称和课程成绩。并执行该存储过程，查询“蔡大炮”的学号、选修课程和课程成绩。2、创建一个存储过程proc_totalcredit，要求实现如下功能：输入学生学号，根据该学生所 选课程的总学分显示提示信息，如果总学分9,则显示“此学生所选总学分为XXX，学分不足！ ”， 否则显示“此学生所选总学分为XXX，学分已足！ ”。并调用修改后的存储过程，显示091650102” 学生的总学分情况。3、创建一个带输入参数和输出参数的存储过程，要求实现如下功能：输入学生学号，然后输出 学生的选课门数、平均分以及所选学分。4、首先创建一个表stu_credit，用来存放学生的学号及选修总学分。然后创建一个触发器 trigger_scredit,实现如下功能：当在学生选课表(student_course)中删除