数据库实验七.docx
实验工程名称：存储过程和触发器实验学时:2同组学生姓名：实验地点：实验日期：实验成绩：批改教师：批改时间：一、实验目的和要求1、通过对常用系统存储过程的使用，了解存储过程的类型；2、通过创立和执行存储过程，了解存储过程的根本概念，掌握使用存储过程的操作技巧和方法;3、通过对已创立的存储过程的改变，掌握修改、删除存储过程的技巧；4、了解触发器的根本概念，理解触发器的功能；5、掌握创立、修改和删除和使用触发器的操作方法。二、实验设备、环境设备：奔腾IV或奔腾IV以上计算机；环境：WINDOWS2000SERVER或WINDOWS2003SERVER.SQLServer2005中文版。三、实验步骤1、根据题目要求熟悉SQLServer2005的各种管理工具；2、分析题意，重点分析题目要求并给出解决方法；3、按题目要求完成实际操作任务，并将相关文档资料保存在以自己学号命名的文件夹中；4、提交完成的实验结果。四、实验内容一、存储过程的类型。1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。sphelptextbyroyalty;二、创立与执行存储过程1、在MyDB中创立存储过程Proc，要求实现如下功能：产生学分为4的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、专业、性别等。并调用此存储过程，显示执行结果。createorreplacePROCEDUREprocascnochar(4);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);sexchar(2);beginselectcourseo,courseame,course,credit,student,sno,student,sname,student,sex,classl.speciallyintocno,cname,credit,sno,sname,sex,speciallyfromcourse,student,grade,classlwherecourseo=gradeoandstudent,sno=grade,snoandclassl.clsno=student,clsnoandcourse,credit=4;endproc_l;2XWsselectedCWOCNMZCR11>IT三0SRAKESEXSPECIALLY0007柳理419920101王至再计算机应用0007牧理419940106吴妙女柳甲Zroveselected2、在UyDB中创立存储过程ProJ2,要求实现如下功能：输入专业名称，产生该专业学生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用此存储过程，显示“计算机应用”专业学生的选课情况列表。createorreplacePROCEDUREproc2(specinvarchar)ascnochar(4);cnamevarchar(16);creditint：snochar(8);snamevarchar(10);speciallyvarchar(3);scorenumeric(4,2);BEGINselectclassl.specially,student,sno,student,sname,courseo,courseame,grade,score,course,creditintospecially,sno,sname,cno,cname,score,creditfromclassl,student,course,gradewherestudent,sno=grade,snoandcourseo=gradeoandstudent.clsno=classl.clsnoandclassl.specially=spec;end;SPECIALLYSBO3HAKCSOCNAKESCOREC三D髀19920306玲00019876豺学199203060003计算机至成9332rowsselected3、在MyDB中创立存储过程PrOC.3,要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分<9,那么显示“此学生学分缺乏！"，否那么显示“此学生学分己足！”，并调用此存储过程，显示“19920102”学生的总学分情况。createprocedureproc_3(snolchar(8)asdeclare©Totaltinyint;beginselect©Total-sum(course.credit)fromcourse,student,gradewherestudent.sno=grade.snoandcourseo=gradeoandstudent.sno=snolifGTotal<9print，此学生学分缺乏，elseprint，此学生学分已足，end;execproc_319920106三、修改存储过程1、对MyDB中已创立的存储过程PrOc进行修改，要求在显示列表中增加班级字段，即产生学分为“4”的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、专业、班级、性别等.alterPROCEDUREproc_lascnochar(4)jclsnamechar(10);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);scorenumeric(4,2);BEGINselectcourseo,courseame,course,credit,student,sno,student,sname,student,sex,classl.specially,classl.cIanamefromcourse,student,grade,classlwherecourseo=gradeoandstudent,sno=grade,snoandclassl.clsno=student,clsnoandcourse,credit=4;end;execprocl;CXOCXAHECREDITSNOSNAMEsexSfeciallyClanjjie0007物悭419920101王军里计Jr机应用计JC机一班0007物理419940106吴纱攵物理枷SjM2cowsselected2、在MyDB中创立的存储过程PrOJ2进行修改，要求实现如下功能：输入专业名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用修改后的存储过程，显示“计算机应用”专业男生的选课情况列表。alterorreplacePROCEDUREproc_2(specinvarchar)ascnochar(4);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);scorenumeric(4,2);BEGINselectclassl.specially,student,sno,student,sname,courseo,courseame,grade,score,course,creditfromclassl,student,course,gradewherestudent,sno=grade.snoandcourseo=gradeoandstudent,clsno=classl.clsnoandstudent,sex='男'andclassl.specially=spec;end;execproc_2'计算机应用'；SPECIALLYSNOSHXCHOCNAKESCOSECREDIT谓机应用19920101王军0001ft?906i+算机邮19920101王军0007辘864计朝岫用199201020001孵876计算机应用19920102李东0003计算腥他7634rovsselected3、对MyDB中已创立的存储过程Proj3进行修改，要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分9,那么显示“此学生所选总学分为XXX,学分缺乏！"，否那么显示“此学生所选总学分为XXX,学分已足！”。并调用修改后的存储过程，显示“19920102”学生的总学分情况。alterorreplaceprocedureproc_3(snolinchar)isTotalint;beginselectsum(grade.credit)intoTotalfromcourse,student,gradewherestudent.sno=grade.snoandcourseo=gradeoandstudent.sno=snol;ifTotal>9thendbms_oUlPUt.put('此学生所选学分为：,);dbms_output.put(total);dbms_oUtPUt.putjine('此学生学分已足');elsePrinU此学生所选学分为：，；printtotal;print，此学生学分缺乏;endif;endproc_3;callproc_3(,1992010);callPgJ3('19920102);callproc_3(,19940106');callpocj(,19920101l)怦甥罅分为：1眦牲物道callprocj(l19920102l)幼。照甥渔弱为:9衅生翔桂callprocj(,19940106')拗。1惇甥龌分为:妣学生翎不足四、删除存储过程：删除MyDB中的存储过程PrOC。dropprocedureproc_l;llAULlUiKdropprocedurePrOJI成功。五、创立触发器1、创立触发器trigger,实现当修改学生表(StUdent)中的数据时，显示提示信息“学生情况表被修改了”。createorreplaceTRIGGERTRIGGER.1AFTERINSERTORUPDATEONSTUDENTBEGINprint'学生信息被修改了，；END;insertintostudentVaIUeSCI9920103?曹操；男；MTO4;江中路39#','22-Il月-1993：1.88,8);1行已插入学生信息被修改了2、在MyDB中创立触发器triggej2,实现如下功能：当在学生成绩表(Grade)中删除条学生选课信息后,自动实现更新该学生在学生情况表(StUdent)中的总学分信息。createorreplaceTRIGGERTRIGGER_2AFTERDELETEONGRADEBEGINupdatestudentsettotalcreidt=(selectsum(credit)fromgrade);END;deletefromgradewherescore=90;SNOIlSNAME扇SEX(IlCLSNOmSTUADDRHBIRTHDAYIaHEIGHTTOTALCREIDT119920306王女MT04中央路94#18-3月771.6526219940106吴妙女PuD8莲花小区74#08-4月-791.626319920101王军男CSOl下关40#21-12月-761.7626419920103曹操里MT04江中路39#22-11月-931.8826519920102李杰男CSOl江边路96#24-5月-741.72263、创立触发器IriggeJ3,实现当修改学生情况表(SIUdem)中的某个学生的学号时，对应学生成绩表(Grade)中的学号也作修改。createorreplaceTRIGGERTRIGGERIAFTERUPDATEOFSNOONSTUDENTdeclareold_idchar(8);new_idchar(8);BEGINupdategradesetsno=(selectsnofrominserted)wheresno=(selectsnofromdeleted);END;六、修改触发器1、对已创立的触发器trigger_l进行修改，实现当修改学生情况表(StUdent)中的数据时，显示提示信息“学生情况表中XXX号学生记录被修改了”。七、删除触发器1、删除学生情况表上的触发器trigger。五、问题解答及实验结果1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。sphelptextbyroyalty;二、创立与执行存储过程1、在MyDB中创立存储过程Proc，要求实现如下功能：产生学分为4的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、专业、性别等。并调用此存储过程，显示执行结果。createorreplacePROCEDUREprocascnochar(4);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);sexchar(2);beginselectcourseo,courseame,course,credit,student,sno,student,sname,student,sex,classl.speciallyintocno,cname,credit,sno,sname,sex,speciallyfromcourse,student,grade,classlwherecourseo=gradeoandstudent,sno=grade,snoandclassl.clsno=student,clsnoandcourse,credit=4;endproc_l;2XWsselectedCWOCNMZCR11>IT三0SRAKESEXSPECIALLY0007柳理419920101王至再计算机应用0007牧理419940106吴妙女柳甲Zroveselected2、在UyDB中创立存储过程ProJ2,要求实现如下功能：输入专业名称，产生该专业学生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用此存储过程，显示“计算机应用”专业学生的选课情况列表。createorreplacePROCEDUREproc2(specinvarchar)ascnochar(4);cnamevarchar(16);creditint：snochar(8);snamevarchar(10);speciallyvarchar(3);scorenumeric(4,2);BEGINselectclassl.specially,student,sno,student,sname,courseo,courseame,grade,score,course,creditintospecially,sno,sname,cno,cname,score,creditfromclassl,student,course,gradewherestudent,sno=grade,snoandcourseo=gradeoandstudent.clsno=classl.clsnoandclassl.specially=spec;end;SPECIALLYSBO3HAKCSOCNAKESCOREC三D髀19920306玲00019876豺学199203060003计算机至成9332rowsselected3、在MyDB中创立存储过程PrOC.3,要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分<9,那么显示“此学生学分缺乏！"，否那么显示“此学生学分己足！”，并调用此存储过程，显示“19920102”学生的总学分情况。createprocedureproc_3(snolchar(8)asdeclare©Totaltinyint;beginselect©Total-sum(course.credit)fromcourse,student,gradewherestudent.sno=grade.snoandcourseo=gradeoandstudent.sno=snolifGTotal<9print，此学生学分缺乏，elseprint，此学生学分已足，end;execproc_319920106三、修改存储过程1、对MyDB中已创立的存储过程PrOc进行修改，要求在显示列表中增加班级字段，即产生学分为“4”的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、专业、班级、性别等.alterPROCEDUREproc_lascnochar(4)jclsnamechar(10);cnamevarchar(16);creditint;snochar(8);snamevarchar(10);speciallyvarchar(3);scorenumeric(4,2);BEGINselectcourseo,courseame,course,credit,student,sno,student,sname,student,sex,classl.specially,classi,cIanamefromcourse,student,grade,classlwherecourseo=gradeoandstudent,sno=grade,snoandclassl.clsno=student,clsnoandcourse,credit=4;end;execprocl;CXOCXAHECREDITSNOSNAMEsexSfeciallyClanjjie0007物悭419920101王军里计Jr机应用计JC机一班0007物理419940106吴纱攵物理枷SjM2cowsselected2、在MyDB中创立的存储过程PrOJ2进行修改，要求实现如下功能：输入专业名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用修改后的存储过程，显示“计算机应用”专业男生的选课情况列表。alterorreplacePROCEDUREproc_2(specinvarchar)ascnochar(4);cnamevarchar(16);creditint;snochar(8);snamevarchar(10)speciallyvarchar(3);scorenumeric(4,2);BEGINselectclassl.specially,student,sno,student,sname,courseo,courseame,grade,score,course,creditfromclassl,student,course,gradewherestudent,sno=grade.snoandcourseo=gradeoandstudent,clsno=classl.clsnoandstudent,sex='男'andclassl.specially=spec;end;execproc_2'计算机应用'；SPECIALLYSNOSHXCHOCNAKESCOSECREDIT谓机应用19920101王军0001ft?906i+算机邮19920101王军0007辘864计朝岫用199201020001孵876计算机应用19920102李东0003计算腥他7634rovsselected3、对MyDB中已创立的存储过程Proj3进行修改，要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分9,那么显示“此学生所选总学分为XXX,学分缺乏！"，否那么显示“此学生所选总学分为XXX,学分已足！”。并调用修改后的存储过程，显示“19920102”学生的总学分情况。alterorreplaceprocedureproc_3(snolinchar)isTotalint;beginselectsum(grade.credit)intoTotalfromcourse,student,gradewherestudent.sno=grade.snoandcourseo=gradeoandstudent.sno=snol;ifTotal>9thendbms_oUlPUt.put('此学生所选学分为：,);dbms_output.put(total);dbms_oUtPUt.putjine('此学生学分已足');elsePrinU此学生所选学分为：，；printtotal;print，此学生学分缺乏;endif;endproc_3;callproc_3(,1992010);callproc_3('19920l02,);callproc_3(,19940106');callpocj(,19920101l)怦甥罅分为：1眦牲物道callprocj(l19920102l)幼。照甥渔弱为:9衅生翔桂callprocj(,19940106')拗。1惇甥龌分为:妣学生翎不足四、删除存储过程：删除MyDB中的存储过程PrOc。dropprocedureproc_l;llAULlUiKdropprocedurePrOjI成功。五、创立触发器1、创立触发器trigger,实现当修改学生表(StUdent)中的数据时，显示提示信息“学生情况表被修改了”。createorreplaceTRIGGERTRIGGER.1AFTERINSERTORUPDATEONSTUDENTBEGINprint'学生信息被修改了，；END;insertintostudentVaIUeSCI9920103?曹操；男；MTO4;江中路39#','22-Il月-1993：1.88,8);1行已插入学生信息被修改了2、在MyDB中创立触发器triggej2,实现如下功能：当在学生成绩表(Grade)中删除一条学生选课信息后,自动实现更新该学生在学生情况表(StUdent)中的总学分信息。createorreplaceTRIGGERTRIGGER_2AFTERDELETEONGRADEBEGINupdatestudentsettotalcreidt=(selectsum(credit)fromgrade);END;deletefromgradewherescore=90;SNOIlSNAME扇SEXiIlCLSNOmSTUADDRHBIRTHDAY&HEIGHTTOTALCREIDT119920306王女MT04中央路94#18-3月771.6526219940106吴妙女PuD8莲花小区74#08-4月-791.626319920101王军男CSOl下关40#21T2月-761.7626419920103曹操里MT04江中路39#22-11月-931.8826519920102李杰男CSOl江边路96#24-5月-741.72263、创立触发器IriggeJ3,实现当修改学生情况表(SIUdem)中的某个学生的学号时，对应学生成绩表(Grade)中的学号也作修改。createorreplaceTRIGGERTRIGGERIAFTERUPDATEOFSNOONSTUDENTdeclareold_idchar(8);new_idchar(8);BEGINupdategradesetsno=(selectsnofrominserted)wheresno=(selectsnofromdeleted);END;六、修改触发器1、对已创立的触发器trigger_l进行修改，实现当修改学生情况表(StUdent)中的数据时，显示提示信息“学生情况表中XXX号学生记录被修改了”。七、删除触发器1、删除学生情况表上的触发器trigger。droptriggertrigger_l;droptriggertrigger成功。六、实验体会和收获SQLSerVer数据库的实验学习使我对数据库的有了新的进步，以后再看到也就不至于什么也不懂，其实那么多数据库我觉得学好一门就行，只是他们的语言可能不大一样，学好一门后就可去认识其它的，这样应该有事半功倍的效果。就像我学习C语言，当时不能说是学习的棒，但不算差。所以我对以后的语言感觉都不是很困难，了解了VB、C+还有网页中用的Htnll语言、asp语言都能看懂，起码可以对别人的东西进行了一下修改。因此，我感谢数据库老师给了我有用的知识，以便我在以后学习或认识更多的内容能有新的方法和思维，也能更加有效和快速的去消化吸收新的东西。希望在今后中，SQLServer能给我更多帮助。感谢学校开设这样一门优秀使用的课程，让我对数据库有了更深的了解。
