数据库原理和应用实验12(二版).docVIP

实验十二 存储过程与触发器 一、实验目的及要求 本实验主要目的是让学生通过实验掌握存储过程的创建及使用方式、触发器的创建，具体要求有： 理解存储过程的概念及类型，深刻理解存储过程的优点 掌握创建各种存储过程的方法 掌握调用存储过程的方法 掌握查看、修改、删除存储过程的方法 理解触发器的概念与类型 理解触发器的功能及工作原理 掌握创建、更改、删除触发器的方法 掌握利用触发器维护数据完整性的方法 二、实验原理及背景知识 （一）存储过程知识 存储过程是一种数据库对象，将执行计划存储在数据库的服务器中。它的运行速度比独立运行同样的程序要快。 1、存储过程类型 1）系统存储过程 存储在master数据库中，以sp_为前缀。可以在其他数据库中对其进行调用。 2）用户自定义存储过程 由用户创建并能完成某一特定功能的存储过程，或称本地存储过程。 2、创建存储过程 存储过程的三个组成部分： （1）所有的输入参数以及传给调用者的输出参数。 （2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。 （3）返回给调用者的状态值，以指明调用是成功还是失败。 3、T-SQL创建存储过程的基本语法格式： CREATE PROC[EDURE] 存储过程名称 参数定义 AS SQL语句 （二）触发器知识 触发器是一种特殊类型的存储过程，主要通过事件进行触发执行的。可以实现由主键和外键所不能保证的参照完整性和数据的一致性；强化约束；跟踪变化；级联运行；存储过程的调用等功能。 1、触发器的种类 1）AFTER触发器：只有对表执行某一操作这后，才能被触发。可以为表的同一操作定义多个触发器，其触发次序可使用sp_settriggerorder来完成。 2）INSTEAD OF触发器：并不执行其所定义的操作而仅是执行触发器本身，对同一操作只能定义一个instead of 触发器。 2、INSERTED和DELETED临时表 插入一行时，INSERTED表保存了一份插入行的拷贝。 删除一行时，DELETED表保存了删除行的拷贝。 更新一行时，INSERTED表中保存了一份新行的拷贝，DELETED表中保存了一份旧行的拷贝。 3、语法格式： CREATE TRIGGER 触发器名称 ON 表或视图 { FOR | AFTER | INSTEAD OF } {[DELETE]，[INSERT]，[UPDATE]} AS SQL语句 三、实验内容及步骤: Ⅰ：基本实验内容 在SQL Server 2000中创建存储过程和触发器虽然可以通过企业管理器进行，但是企业管理器仅仅是提供了一个利用T-SQL编写存储过程和触发器的简单模板，大部分内容都是需要自己手工编写的。而且在SQL Server 2005中只能通过T-SQL来编写存储过程和触发器。因此，本实验只要求通过T-SQL来创建并管理存储过程和触发器，可视化方式请参考相关资料。 （一）存储过程的创建和执行 1、创建下面的存储过程并执行，然后分析其功能 1）创建 CREATE PROCEDURE list_stu_course AS SELECT s.sno, s.sname, s.ssex, c.cno, c.cname, skc.grade , k.kcredit FROM student s,course c, kaike k,skc WHERE s.sno=skc.sno AND skc.kno=k.kno AND k.cno=c.cno GO 2)执行 EXEC list_stu_course 或者 list_stu_course 3）功能分析 __________________________________________________________ 2、带参数存储过程并执行 1）创建 CREATE PROC list_student_department (@department char(20)) AS BEGIN SELECT sno,sname,ssex,snative,mname,dname FROM student,major,department WHERE student.mno=major.mno AND major.dno=department.dno AND dname=@department END 2)执行 EXEC list_student_department @department=‘计算机’ 3）功能分析 __________________________________________________________ 3、根据功能要求完成下面的任务 在stuManag数据库中创建一个名为proc_1的存储过程，产生某门课程的选课学