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

数据库原理与应用实验-1-实验十一存储过程与触发器一、实验目的及要求本实验主要目的是掌握存储过程的创建及使用方式、触发器的创建，具体要求有：理解存储过程的概念及类型，深刻理解存储过程的优点。掌握创建各种存储过程的方法，掌握调用存储过程的方法。理解触发器的概念与类型。理解触发器的功能及工作原理。掌握创建、更改、删除触发器的方法。理解利用触发器维护数据完整性的方法。二、实验原理及背景知识（一）存储过程知识存储过程是一种数据库对象，将执行计划存储在数据库服务器中。它的运行速度比独立运行同样的程序要快。1、存储过程类型（1）系统存储过程存储在master数据库中，其名称以sp_为前缀。可以在其他数据库中调用。（2）用户自定义存储过程由用户创建并能完成某一特定功能的存储过程，也称本地存储过程。2、创建存储过程存储过程的三个组成部分：（1）所有的输入参数以及传给调用者的输出参数。（2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。（3）返回给调用者的状态值，以指明调用是成功还是失败。3、T-SQL创建存储过程的基本语法格式CREATEPROC[EDURE]存储过程名称参数定义ASSQL语句（二）触发器知识触发器是一种特殊类型的存储过程，主要通过事件进行触发执行的。可以实现由主键和外键所不能保证的参照完整性和数据一致性；强化约束；跟踪变化；级联运行；存储过程的调用等功能。1、触发器的种类（1）AFTER触发器：只有对表执行某一操作后，才能被触发。可以为表的同一操作定义多个触发器，其触发次序可使用sp_settriggerorder来完成。（2）INSTEADOF触发器：不执行其触发操作，仅执行触发器本身，对同一触发操作只能定义一个INSTEADOF触发器。2、Inserted和Deleted临时表插入一行时，Inserted表保存了一份插入行的拷贝。删除一行时，Deleted表保存了删除行的拷贝。更新一行时，Inserted表中保存了一份新行的拷贝，Deleted表中保存了一份旧行的拷贝。3、语法格式CREATETRIGGER触发器名称数据库原理与应用实验教材-2-ON表或视图{FOR|AFTER|INSTEADOF}{[DELETE]，[INSERT]，[UPDATE]}ASSQL语句三、实验内容及步骤（一）存储过程的创建和执行1、创建下列存储过程并执行，然后分析其功能。（1）创建（2）执行（3）功能分析_答:选取系别为计算机系的所有学生的学号,姓名,性别,籍贯,以及主修的名字;2、根据功能要求完成下列任务。在stuManag数据库中创建一个名为show_course_Mark的存储过程，产生某门课程的选课学生的成绩情况，其中包括选课人数、平均成绩、及格率。要求输入某门课程的课程号，得到上述信息。（1）创建答:@stuCountintoutput@stuCount（2）执行数据库原理与应用实验-3-答:@stucountoutput,@avgmarkoutput,@pationpassoutput执行结果:课程号人数平均分及格率01010165700.692307692307692（二）管理存储过程1、查看存储过程代码2、修改存储过程3、删除存储过程（三）触发器验证1、Inserted表和Deleted表的使用（1）创建下面的触发器，用于显示Inserted表和Deleted表中的内容。（2）分别执行下面三个语句，查看每个语句的执行结果。2、AFTER触发器（1）创建一个学生选课统计表（选课数量，总学分）。数据库原理与应用实验教材-4-（2）创建触发器，当学生选课后自动跟新stu_credit表内容，将所选课程数加1，并在选课程总学分中增加新选的课程学分。（3）分别执行下列三条语句并进行测试。答：(1).snosnamecourseCOUNTsumCredit081220101吴明3171(2).消息2627，级别14，状态1，第1行违反了PRIMARYKEY约束'PK_student_course'。不能在对象'dbo.student_course'中插入重复键。语句已终止。3、INSTEADOF触发器（1）为开课表增加一个字段，标识课程是否有效，值1为有效，值0为无效。（2）创建一个触发器，当删除课程时并不真正删除数据，而是将要删除的开课课程标志为无效，同时删除已选课学生。数据库原理与应用实验-5-（3）分别执行下列语句并进行验证。（四）触发器编程1、创建触发器实现当修改学生表的平时成绩或期末考试成绩时自动计算总评成绩，按照2：8进行计算。答student_courseforinsert2、依次运行下列语句并进行验证。第二篇实验内容及安排-6-答：sNOccNONormalMarkExamMarkMark0812201010312091006NULL9088sNOccNONormalMarkExamMarkMark0812201010312091006809088四、实验测验1、创建一个带输入参数和输出参数的存储过程，要求实现如下功能：输入学生学号，输出学生的选课门数、平均分以及所选总学分。答：createprocedurec(@snochar(10),@cnocountintoutput,@avgmarkfloatoutput,@sumcreditintoutput)Asbeginselect@cnocount=count(*),@avgmark=avg(mark),@sumcredit=sum(credit)Fromstudent_coursesc,course_classcc,studentswheresc.sno=s.snoandcc.ccno=sc.ccnogroupbys.sNO,snameend执行过程：Declare@ccountintDeclare@smarkfloatDeclare@screditintExecc'081220101',@ccountoutput,@smarkoutput,@screditoutputSelect'081220101'as'学生号',@ccountas'选课门数',@smarkas'平均分',@screditas'所选学分'2、创建一个触发器，当在student_course中添加一个学生选课时，如果已选学分超过30学分或者选课人数超过65人，则拒绝学生本次选修申请。答：createtriggertrig1onstudent_courseforinsert,updateasdeclare@aint数据库原理与应用实验-7-declare@bintselect@a=(selectSUM(credit)fromcourse_class),@b=(selectCOUNT(*)fromcourse_classcc)frominsertedif@a30or@b65beginrollbackraiserror('该学生无法再选课！',16,1)end结果：insertstudent_coursevalues('081710106',0312091006,0,90,90)五、实验思考题1、触发器的种类和用途？2、存储过程的类型有哪些？分别有什么特征？3、触发器在维护数据完整性方面有什么功能？第二篇实验内容及安排-8-