数据库课程设计实验

实验5存储过程与触发器1.实验目的与要求：1.1掌握存储过程使用1.2掌握触发器使用1.3掌握游标使用2.实验环境与实验器材：计算机，网络环境，投影设备。实验相关软件：Windowxp、PowerDesigner、SQLServer2008。3.实验内容与步骤使用已经建立的数据库students及studentcoursesc表实现下面操作。3.1创建满足下述要求的存储过程，并查看执行结果。1）查询每个学生的修课总学分，要求列出学生学号及总分数。CREATEprocedurep1asselectstudent.sno,sum(credit)总学分fromstudent,course,SCwherestudent.sno=sc.snoandsc.cno=course.cnogroupbystudent.sno;2）查询学生的学号、姓名、选修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值“计算机”。执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。ALTERPROCEDUREp2@sdeptchar(20)='计算机系'ASSELECTstudent.Sno,Sname,Sdept,course.Cno,Cname,CreditFROMStudent,course,scWHEREstudent.sno=sc.snoandcourse.cno=sc.cnoandSdept=@sdept;execp2;execp2'信息管理系';execp2'通信工程系';3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。CREATEPROCEDUREp3@sdeptchar(20),@countintoutputASSELECT@count=count(*)fromstudentwheressex='男'andsdept=@sdept;DECLARE@resintEXECp3'计算机系',@resOUTPUTPRINT@res;4）删除指定学生的修课记录，其中学号为输入参数。CREATEPROCEDUREp4@snochar(20)ASdeletefromscwheresno=@sno;EXECp4'0611102';5）修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。CREATEPROCEDUREp5@cnochar(5),@semintASupdatecoursesetsemster=@semwherecno=@cno;execp5'c004',3;3.2创建满足下述要求的触发器（前后均可），并查看执行结果。1）限制学生的年龄在15-45之间。createtriggert1onstudentafterinsert,updateasifexists(select*frominsertedwheresagenotbetween15and45)beginprint'学生年龄不在15-45之间，请重新输入!';rollback;Endinsertintostudentvalues('0631106','林道','男',10,'建筑设计系');2）限制学生所在系的取值范围为{计算机，信息管理系、数学系、通信工程系}。createtriggert2onstudentafterinsert,updateasifexists(select*frominsertedwheresdeptnotin('计算机系','信息管理系','数学系','通信工程系'))beginprint'学生所在系输入错误，请重新输入!';rollback;endinsertintostudentvalues('0631106','林道','男',20,'建筑设计系');3）限制每个学期开设的课程总学分在20-30范围内。createtriggert3oncourseafterinsert,updateasif((selectsum(Credit)fromcoursewheresemsterin(selectsemsterfrominserted))notbetween20and30)beginprint'课程总学分超出范围!';rollback;endinsertintocoursevalues('c010','算法分析',30,1);4）限制每个学生每学期选修课门数不能超过6门。createtriggert4onscafterinsert,updateasif(selectcount(*)fromsc,coursewheresc.cno=course.cnoandsemsterin(selectsemsterfromcoursewherecourse.cnoin(selectcnofrominserted))andsc.snoin(selectsnofrominserted))6beginprint'该学生选课门数超过6门!';rollback;endinsertintoscvalues('0611101','c009',null),('0611101','c010',null),('0611101','c011',null),('0611101','c012',null),('0611101','c013',null);3.3创建满足下述要求的游标，并查看执行结果。！）列出VB考试成绩最高的前2名和最后1名学生的学号、姓名、所在系和VB成绩。显示结果。DECLAREcs1scrollCURSORFORSELECTstudent.Sno,Sname,Sdept,GradeFROMStudent,sc,courseWHEREstudent.sno=sc.snoandcourse.cno=sc.cnoandcname='VB'ORDERBYGradeDESCopencs1fetchnextfromcs1;fetchnextfromcs1;fetchlastfromcs1;closecs1;deallocatecs1;2）列出每个系年龄最大2名学生的姓名和年龄，结果按年龄降序排序。显示结果。declare@Sdeptchar(20),@Snamechar(7),@sageintdeclarecs2cursorforselectdistinctsdeptfromstudentopencs2fetchnextfromcs2into@sdeptwhile@@FETCH_STATUS=0beginprint@Sdept;print'姓名年龄';declarecs3cursorforselectSname,Sagefromstudentwheresdept=@sdeptorderbysagedesc;opencs3fetchnextfromcs3into@sname,@sage;print@Sname+cast(@sageaschar(4))fetchnextfromcs3into@sname,@sage;print@Sname+cast(@sageaschar(4))closecs3;deallocatecs3;print''fetchnextfromcs2into@sdeptendclosecs2;deallocatecs2;4．思考与总结