数据库实验11存储过程触发器及答案

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

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

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

4、 | AFTER | INSTEAD OF DELETE，INSERT，UPDATEAS SQL语句 三、实验内容及步骤（一）存储过程的创建和执行1、创建下列存储过程并执行，然后分析其功能。（1）创建（2）执行（3）功能分析_答:选取系别为计算机系的所有学生的学号,姓名,性别,籍贯,以及主修的名字;2、根据功能要求完成下列任务。在stuManag数据库中创建一个名为show_course_Mark的存储过程，产生某门课程的选课学生的成绩情况，其中包括选课人数、平均成绩、及格率。要求输入某门课程的课程号，得到上述信息。（1）创建答: stuCount int output stuCount（2）

5、执行答: stucount output, avgmark output,pationpass output执行结果:课程号 人数 平均分 及格率 65 70 0.7692（二）管理存储过程1、查看存储过程代码2、修改存储过程3、删除存储过程（三）触发器验证1、Inserted表和Deleted表的使用（1）创建下面的触发器，用于显示Inserted表和Deleted表中的内容。（2）分别执行下面三个语句，查看每个语句的执行结果。2、AFTER触发器（1）创建一个学生选课统计表（选课数量，总学分）。（2）创建触发器，当学生选课后自动跟新stu_credit表内容，将所选课程数加1，并在选课程总

6、学分中增加新选的课程学分。（3）分别执行下列三条语句并进行测试。答：(1).sno sname courseCOUNT sumCredit 吴明 3 171 (2). 消息2627，级别14，状态1，第1 行违反了PRIMARY KEY 约束PK_student_course。不能在对象dbo.student_course 中插入重复键。语句已终止。3、INSTEAD OF触发器（1）为开课表增加一个字段，标识课程是否有效，值1为有效，值0为无效。（2）创建一个触发器，当删除课程时并不真正删除数据，而是将要删除的开课课程标志为无效，同时删除已选课学生。（3）分别执行下列语句并进行验证。（四）触

7、发器编程1、创建触发器实现当修改学生表的平时成绩或期末考试成绩时自动计算总评成绩，按照2：8进行计算。答student_course for insert2、依次运行下列语句并进行验证。答：sNO ccNO NormalMark ExamMark Mark NULL 90 88sNO ccNO NormalMark ExamMark Mark 80 90 88四、实验测验1、创建一个带输入参数和输出参数的存储过程，要求实现如下功能：输入学生学号，输出学生的选课门数、平均分以及所选总学分。答：create procedure c( sno char(10),cnocount int output

8、,avgmark float output,sumcredit int output)Asbegin select cnocount=count(*),avgmark=avg(mark),sumcredit=sum(credit) From student_course sc,course_class cc ,student s where sc.sno=s.sno and cc.ccno=sc.ccno group by s.sNO,snameend执行过程：Declare ccount intDeclare smark floatDeclare scredit intExec c , cc

9、ount output, smark output, scredit outputSelect as 学生号, ccount as 选课门数, smark as 平均分,scredit as 所选学分2、创建一个触发器，当在student_course中添加一个学生选课时，如果已选学分超过30学分或者选课人数超过65人，则拒绝学生本次选修申请。答：create trigger trig1 on student_coursefor insert ,update asdeclare a intdeclare b intselect a=(select SUM(credit) from course_class ) , b=(select COUNT(*) from course_class cc ) from insertedif a30 or b