实验六-存储过程和触发器

实验六存储过程和触发器１．实验目的(1)掌握存储过程和触发器的基本概念和功能(2)掌握创建,管理存储过程的方法(3)掌握创建,管理触发器的方法２．实验内容及步骤(1)利用SQLServerManagementStudio创建一个存储过程ProcNum,查询每个班级中学生的人数,按班级号升序排序.在查询编辑器的存储过程模板中输入如下创建存储过程的代码并执行.USEteachingGOCREATEPROCEDUREProcNumASSELECTclassno,COUNT(*)ASnumberFROMstudentGROUPBYclassnoORDERBYclassnoASCGOEXECProcNum(2)利用Transact-SQL语句创建一个带有参数的存储过程ProcInsert,向score表插入一条选课记录,并查询该学生的姓名,选课的所有课程名称,平时成绩和期末成绩.1在查询编辑器输入如下创建存储过程的代码并执行.USEteachingGOCREATEPROCEDUREProcInsert(@snoNCHAR(10),@cnoNCHAR(6),@usuallyNUMERIC(6,2),@finalNUMERIC(6,2))ASINSERTINTOscoreVALUES(@sno,@cno,@usually,@final)SELECTsname,cname,usually,finalFROMstudents,coursec,scorescWHEREs.studentno=sc.studentnoandc.courseno=sc.coursenoands.studentno=@sno2调用存储过程ProcInsert,向score表插入一条选课记录.DECLARE@AVERAGENUMERIC(6,2)EXECProcInsert'16135222201','c05103',88,90(3)利用Transact-SQL语句创建一个存储过程ProcAvg，查询指定班级指定课程的平均分。班级号和课程名称由输入参数给定，计算出的平均分通过输出参数返回。若该存储过程已存在，则删除后重建。1在查询编辑器中输入如下创建存储过程的代码并执行：USEteachingGOIFEXISTS(SELECT*FROMsysobjectsWHEREname='ProAvg'andtype='P')DROPPROCEDUREProcAvgGOCREATEPROCEDUREProcAvg(@classnoNCHAR(10),@cnameNCHAR(20),@avgNUMERIC(6,2)OUTPUT)ASSELECT@avg=AVG(final)FROMstudents,coursec,scorescWHEREs.studentno=sc.studentnoandc.courseno=sc.coursenoandclassno=@classnoandcname=@cname2调用存储过程ProcAvg,查询160502班中C语言课程的平均分，并通过PRINT函数输出查询结果。DECLARE@averageNUMERIC(6,2)EXECProcAvg'160502','C语言',@averageOUTPUTPRINT'160502班中C语言课程的平均分为'+CAST(@averageASVARCHAR(10))(4)利用SQLServerManagementStudio创建一个AFTER触发器trigsex,当插入或修改student表中性别字段sex时，检查数据是否为“男”或“女”在查询编辑器的触发器的触发器模板中输入如下创建触发器的代码并执行：USEteachingGOCREATETRIGGERtrigsexONstudentAFTERINSERT,UPDATEASBEGINDECLARE@sexchar(2)SELECT@sex=sexFROMinsertedIF@sex'男'and@sex'女'BEGINRAISERROR('性别只能为男或女',16,1)ROLLBACKENDEND执行如下插入语句：INSERTINTOstudent(studentno,sname,sex,birthday,classno)VALUES('16138211039','李琳琳','F','1998-6-3','160802')(5)利用Transact-SQL语句创建一个AFTER触发器trigforeign,当向score表中插入或修改记录时，如果插入或修改的数据与student表中数据不匹配，即没有对应的学号存在，则将此记录删除。1删除student和score表中之间的外键约束FK_student_score,程序代码如下：ALTERTABLEscoreDROPCONSTRAINTFK_score_student2在编辑器中输入如下创建触发器的代码并执行USEteachingGOcreateTRIGGERtrigforeignONscoreAFTERINSERT,UPDATEASBEGINDECLARE@snochar(5)SELECT@sno=studentnoFROMinsertedIFNOTEXISTS(SELECT*fromstudentWHEREstudentno=@sno)BEGINRAISERROR('该学生信息不存在，不允许插入选课信息',16,1)ROLLBACKENDEND3执行如下插入语句INSERTINTOscore(studentno,courseno,usually,final)values('16138211039','c05109',88,90)提示信息如下：INSERT语句与FOREIGNKEY约束FK_score_student冲突。该冲突发生于数据库teaching，表dbo.student,column'studentno'。语句已终止。（6）利用Transact-SQL语句创建一个AFTER触发器trigclassname，当向class表中插入或修改数据时，如果出现班级名称重复则回滚事务。若该触发器已存在，则删除后重建。1在查询编辑器中输入如下创建触发器的代码并执行：USEteachingGoIFEXISTS(SELECT*FROMsysobjectsWHEREname='trigclassname'ANDtype='TR')DROPTRIGGERtrigclassnameGOCREATETRIGGERtrigclassnameONclassAFTERINSERT,UPDATEASBEGINDECLARE@nameCHAR(10)SELECT@name=inserted.classnameFROMinsertedIFEXISTS(SELECTclassnameFROMclassWHEREclassname=@nameGROUPBYclassnameHAVINGCOUNT(*)=2)BEGINRAISERROR('班级名称重复,不能插入',16,1)ROLLBACKENDEND对于AFTER触发器而言,判断是否出现姓名重复的Transact-SQL语句仅在插入和修改操作成功执行时才被触发。当向student表中插入或修改数据时，新的纪录将同时被添加到数据表student和临时表inserted中,因此仅当student表中至少存在两条纪录的姓名字段与inserted表相同，方可确认插入或修改操作出现姓名重复，事物需回滚。2执行如下插入语句：INSERTINTOclass(classno,classname,department,monitor)VALUES('160602','计算机','计算机学院','马文斐')3．实验报告总结（1）存储过程中输出参数与输入参数在定义、调用的区别：输入参数是传递给存储过程的,就是原料.输出参数是存储过程的产出,就是产品.在调用存储过程前,相关的输入参数必须已经有确定的值;存储过程根据输入参数的值以及内部算法,将计算的结果保存到输出参数中.输出参数在调用存储过程前无须有确定的值,只需定义这样一个变量,将他传给存储过程,存储过程执行之后,该输出参数就有了确定的值.可以进一步使用该值.（2）触发器INSERT、DELETE和UPDATE操作与临时表insert和deleted的关系：当由INSERT或UPDATE语句激活相应触发器之后，所有被添加或被更新的记录都被存储到inserted表。当由DELETE或UPDATE语句激活相应触发器之后，所有被删除的记录都被送到deleted表。在触发器的执行过程中，可以读取这两个表中的内容，但不能修改它们。当触发器的工作完成之后，这两个表也将从内存中删除。（3）存储过程与触发器的联系和区别：触发器（Trigger）是一种响应数据操作语言（DML）事件或数据定义语言（DDL）事件而执行的特殊类型的存储过程，是在用户对某一表中的数据进行UPDATE、INSERT和DELETE操作时被触发执行的一段程序。触发器有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。