数据库实验七

实验项目名称：存储过程和触发器实验学时：2同组学生姓名：实验地点：实验日期：实验成绩：批改教师：批改时间：一、实验目的和要求1、通过对常用系统存储过程的使用，了解存储过程的类型；2、通过创建和执行存储过程，了解存储过程的基本概念，掌握使用存储过程的操作技巧和方法；3、通过对已创建的存储过程的改变，掌握修改、删除存储过程的技巧；4、了解触发器的基本概念，理解触发器的功能；5、掌握创建、修改和删除和使用触发器的操作方法。二、实验设备、环境设备：奔腾Ⅳ或奔腾Ⅳ以上计算机；环境：WINDOWS2000SERVER或WINDOWS2003SERVER、SQLServer2005中文版。三、实验步骤1、根据题目要求熟悉SQLServer2005的各种管理工具；2、分析题意，重点分析题目要求并给出解决方法；3、按题目要求完成实际操作任务，并将相关文档资料保存在以自己学号命名的文件夹中；4、提交完成的实验结果。四、实验内容一、存储过程的类型。1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。sp_helptextbyroyalty;二、创建与执行存储过程1、在MyDB中创建存储过程proc_1，要求实现如下功能：产生学分为4的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、专业、性别等。并调用此存储过程，显示执行结果。createorreplacePROCEDUREproc_1ascnochar(4);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);sexchar(2);beginselectcourse.cno,course.cname,course.credit,student.sno,student.sname,student.sex,class1.speciallyintocno,cname,credit,sno,sname,sex,speciallyfromcourse,student,grade,class1wherecourse.cno=grade.cnoandstudent.sno=grade.snoandclass1.clsno=student.clsnoandcourse.credit=4;endproc_1;2、在MyDB中创建存储过程proc_2，要求实现如下功能：输入专业名称，产生该专业学生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用此存储过程，显示“计算机应用”专业学生的选课情况列表。createorreplacePROCEDUREproc_2(specinvarchar)ascnochar(4);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);scorenumeric(4,2);BEGINselectclass1.specially,student.sno,student.sname,course.cno,course.cname,grade.score,course.creditintospecially,sno,sname,cno,cname,score,creditfromclass1,student,course,gradewherestudent.sno=grade.snoandcourse.cno=grade.cnoandstudent.clsno=class1.clsnoandclass1.specially=spec;end;3、在MyDB中创建存储过程proc_3，要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分9,则显示“此学生学分不足！”，否则显示“此学生学分已足！”，并调用此存储过程，显示“19920102”学生的总学分情况。createprocedureproc_3(@sno1char(8))asdeclare@Totaltinyint;beginselect@Total=sum(course.credit)fromcourse,student,gradewherestudent.sno=grade.snoandcourse.cno=grade.cnoandstudent.sno=@sno1if@Total9print'此学生学分不足'elseprint'此学生学分已足'end;execproc_319920106三、修改存储过程1、对MyDB中已创建的存储过程proc_1进行修改，要求在显示列表中增加班级字段，即产生学分为“4”的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、专业、班级、性别等.alterPROCEDUREproc_1ascnochar(4);clsnamechar(10);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);scorenumeric(4,2);BEGINselectcourse.cno,course.cname,course.credit,student.sno,student.sname,student.sex,class1.specially,class1.clanamefromcourse,student,grade,class1wherecourse.cno=grade.cnoandstudent.sno=grade.snoandclass1.clsno=student.clsnoandcourse.credit=4;end;execproc_1;2、在MyDB中创建的存储过程proc_2进行修改，要求实现如下功能：输入专业名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用修改后的存储过程，显示“计算机应用”专业男生的选课情况列表。alterorreplacePROCEDUREproc_2(specinvarchar)ascnochar(4);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);scorenumeric(4,2);BEGINselectclass1.specially,student.sno,student.sname,course.cno,course.cname,grade.score,course.creditfromclass1,student,course,gradewherestudent.sno=grade.snoandcourse.cno=grade.cnoandstudent.clsno=class1.clsnoandstudent.sex='男'andclass1.specially=spec;end;execproc_2‘计算机应用’;3、对MyDB中已创建的存储过程proc_3进行修改，要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分9，则显示“此学生所选总学分为XXX，学分不足！”，否则显示“此学生所选总学分为XXX，学分已足！”。并调用修改后的存储过程，显示“19920102”学生的总学分情况。alterorreplaceprocedureproc_3(sno1inchar)isTotalint;beginselectsum(grade.credit)intoTotalfromcourse,student,gradewherestudent.sno=grade.snoandcourse.cno=grade.cnoandstudent.sno=sno1;ifTotal9thendbms_output.put('此学生所选学分为：');dbms_output.put(total);dbms_output.put_line('此学生学分已足');elseprint'此学生所选学分为：';printtotal;print'此学生学分不足';endif;endproc_3;callproc_3('19920101');callproc_3('19920102');callproc_3('19940106');四、删除存储过程：删除MyDB中的存储过程proc_1。dropprocedureproc_1;五、创建触发器1、创建触发器trigger_1,实现当修改学生表(Student)中的数据时，显示提示信息“学生情况表被修改了”。createorreplaceTRIGGERTRIGGER_1AFTERINSERTORUPDATEONSTUDENTBEGINprint'学生信息被修改了';END;insertintostudentvalues('19920103','曹操','男','MT04','江中路39#','22-11月-1993',1.88,8);2、在MyDB中创建触发器trigger_2,实现如下功能：当在学生成绩表(Grade)中删除一条学生选课信息后，自动实现更新该学生在学生情况表(Student)中的总学分信息。createorreplaceTRIGGERTRIGGER_2AFTERDELETEONGRADEBEGINupdatestudentsettotalcreidt=(selectsum(credit)fromgrade);END;deletefromgradewherescore=90;3、创建触发器trigger_3,实现当修改学生情况表(Student)中的某个学生的学号时，对应学生成绩表(Grade)中的学号也作修改。createorreplaceTRIGGERTRIGGER1AFTERUPDATEOFSNOONSTUDENTdeclareold_idchar(8);new_idchar(8);BEGINupdategradesetsno=(selectsnofrominserted)wheresno=(selectsnofromdeleted);END;六、修改触发器1、对已创建的触发器trigger_1进行修改，实现当修改学生情况表(Student)中的数据时，显示提示信息“学生情况表中XXX号学生记录被修改了”。七、删除触发器1、删除学生情况表上的触发器trigger_1。五、问题解答及实验结果1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。sp_helptextbyroyalty;二、创建与执行存储过程1、在MyDB中创建存储过程proc_1，要求实现如下功能：产生学分为4的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、专业、性别等。并调用此存储过程，显示执行结果。createorreplacePROCEDUREproc_1ascnochar(4);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);sexchar(2);beginselectcourse.cno,course.cname,course.credit,student.sno,student.sname,student.sex,class1.speciallyintocno,cname,credit,sno,sname,sex,speciallyfromcourse,student,grade,class1wherecourse.cno=grade.cnoandstudent.sno=grade.snoandclass1.clsno=student.clsnoandcourse.credit=4