《2版数据库实验11 存储过程触发器及答案.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语句 三、实验内容及步骤 （一）存储过程的创建和执行 1、创建下列存储过程并执行，然后分析其功能。 （1）创建 （2）执行 （3）功能分析 _答:选取系别为计算机系的所有学生的学号,姓名,性别,籍贯,以及主修的名字; 2、根据功能要求完成下列任务。 在stuManag数据库中创建一个名为show_course_Mark的存储过程，产生某门课程的选课学生的成绩情况，其中包括选课人数、平均成绩、及格率。要求输入某门课程的课程号，得到上述信息。 （1）创建 答: @stuCount int output @stuCount （2）执行 答: @stucount output, @avgmark output,@pationpass output 执行结果: 课程号 人数 平均分 及格率 010101 65 70 0.692307692307692 （二）管理存储过程 1、查看存储过程代码 2、修改存储过程 3、删除存储过程 ?（三）触发器验证 1、Inserted表和Deleted表的使用 （1）创建下面的触发器，用于显示Inserted表和Deleted表中的内容。 （2）分别执行下面三个语句，查看每个语句的执行结果。 2、AFTER触发器 （1）创建一个学生选课统计表（选课数量，总学分）。 （2）创建触发器，当学生选课后自动跟新stu_credit表内容，将所选课程数加1，并在选课程总学分中增加新选的课程学分。 （3）分别执行下列三条语句并进行测试。 答：(1).sno sname courseCOUNT sumCredit 081220101 吴明 3 171 (2). 消息2627，级别14，状态1，第1 行 违反了PRIMARY KEY 约束PK_student_course。不能在对象dbo.student_course 中插入重复键。 语句已终止。 3、INSTEAD OF触发器 （1）为开课表增加一个字段，标识课程是否有效，值1为有效，值0为