实验6数据库实验——存储过程和触发器

1、 实验6 存储过程和触发器一、实验目的1、加深和巩固对存储过程和触发器概念的理解。2、掌握触发器的简单应用。3、掌握存储过程的简单应用。二、实验容一）存储过程：1. 创建一存储过程，求l+2+3+n，并打印结果。CREATEPROCEDURE addresult ASDECLARE n int=10,/*最后一个数*/ i int=0, result int=0 /*结果*/BEGINWHILE(i<=n)BEGINSET result=result+iSET i=i+1ENDPRINT'1+2+3+.+n的结果是:'PRINT resultRETURN(result)E

2、NDGO2调用上面的addresult存储过程，打印l十2+3+10的结果。EXEC addresult3. 修改上述存储过程为addresult1,使得n为输入参数，其具体值由用户调用此存储过程时指定。CREATEPROCEDURE addresult1 n int=10 /*最后一个数*/ASDECLARE i int=0, result int=0 /*结果*/BEGINWHILE(i<=n)BEGINSET result=result+iSET i=i+1ENDPRINT'1+2+3+.+n的结果是:'PRINT resultRETURN(result)ENDGO

3、4. 调用上面修改后的addresult1存储过程，打印l+2+3+100的结果。EXEC addresult1 1005修改上述存储过程为addresult2，将n参数设定默认值为10，并改设sum为输出参数，让主程序能够接收计算结果。CREATEPROCEDURE addresult2 n int=10,/*最后一个数*/sum intout/*结果*/ASDECLARE i int=0BEGINset sum=0WHILE(i<=n)BEGINSET sum=sum+iSET i=i+1ENDENDGO6调用上面修改后的addresult2存储过程，设置变量s接收计算l+2+3+1

4、0的结果。DECLARE s intset s=0EXEC addresult2 10,sum=s outPRINT'1+2+3+.+n的结果是:'PRINT s7创建一存储过程Proc_Student，用于显示学号为“0102”的学生基本信息（包括学号、性别和系）。CREATEPROCEDURE Proc_Student ASBEGINselect*from Swhere S.sno=0102ENDGOEXEC Proc_Student8创建一存储过程Stu_grade，通过读取某门课的编号，求出不及格的学生的学号。CREATEPROCEDURE Stu_grade n ch

5、ar(10)ASBEGINselect snofrom SCwhereo=n and grade<60ENDGO9调用上面的存储过程Stu_grade，求出课程编号为“0101”的不及格的学生。EXEC Stu_grade 010110创建一存储过程avgGrade，通过读取学生的学号，以参数形式返回该学生的平均分。CREATEPROCEDURE avgGrade n char(10)ASBEGINselectAVG(grade) 平均分from SCwhere sno=ngroupby snoENDGO11调用上面的存储过程avgGrade，求出学号为“990102014”的平均分。E

6、XEC avgGrade 99010201412删除上述存储过程avgGrade。dropprocedure avgGrade13.创建存储过程search，该存储过程有三个参数，分别为t、p1，p2，根据这些参数，找出书名与t有关，价格在p1与p2(p2>=p1)之间的书的编号，书名，价格，出舨日期。如果用户调用时没有指定t参数的值则表示可为任意值，如用户没有指定p2，则书本价格没有上限。用到的关系为：titles (title_id,title,price,pubdate)。CREATEPROCEDURE search t char(10)="%",p1 char

7、(10),p2 char(10)=NULLASBEGINselect title_id,title,price,pubdatefrom titleswhere title=t and price<=p2 and price>=p1ENDGO14调用上面的存储过程search，求出书名与puter有关，而且价格小于$20大于$10的书。 EXEC search "puter",10,20二）触发器：1、在学生成绩库中创建触发器trigger1，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，自动实现更新该学生在学生情况表（xsqk）中的

8、总学分信息。分析：根据题意，也即要求在学生成绩表中插入一条记录时，自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息。其实，只要在该生原总学分基础上加上新选课程的学分就可以了。createtrigger trigger1on xscjafterinsertasdeclare credit int;select credit=credit from inserted xscj;update xsqk set allcredit=allcredit+credit;go 2、创建触发器tr

9、igger2，实现当修改学生课程表（xskc）中的数据时，显示提示信息“学生课程表被修改了”。 createtrigger trigger2on xskcafterupdateasprint'学生课程表被修改了'go3、创建触发器trigger3，实现当删除学生课程表中某门课程的记录时，对应学生成绩表中所有有关此课程的记录均删除。 createtrigger trigger3on xskcafterdeleteasdeclare ame char(10);select ame=ame from deleted xsks;delete xscj wheream

10、e=ame;go4、创建触发器trigger4，实现当修改学生课程表（xskc）中的某门课的课程号时，对应学生成绩表（xscj）中的课程号也作相应修改。createtrigger trigger4on xskcafterupdateasdeclare o char(10);declare ame char(10);select ame=xskc.ame,o=xskc.o from updated xsks;update xscj set xscj.o=o where xscj.ame=ame;go5、创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，查看该学生的信

11、息是否存在在学生信息表中，如果不存在，则把该学生的基本信息加入到学生信息表中。createtrigger trigger5on xscjafterinsertasdeclare sname1 char(10);declare sname2 char(10)=NULL;select sname1=xscj.sname from updated xscj;select sname2=xsqk.sname from xsqk where xsqk.sname=sname1;if sname2=NULLinsertinto xsqk values(NULL,sname1);go6、在学生成绩库中创建触

12、发器trigger6，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，查看该学生的信息是否存在在学生信息表中，如果不存在，则给出“该记录不能被插入！”的错误提示，并撤销插入操作；同样，如果课程信息在课程信息表中不存在，给出“该记录不能被插入！”的错误提示，并撤销插入操作。 createtrigger trigger6on xscjforinsertasbeginifnotexists(select xsqk.snamefrom xsqk where xsqk.sname in(select xscj.sname from inserted xscj)beginrai

13、serror('该记录不能被插入！',16,1)rollbackreturnendendgo7、创建触发器trigger7，强制实现业务规则：当向学生成绩表中插入一条记录时，自动修改学生情况表中该学生的总学分，要求总学分为该学生所有已修课程的学分总和。createtrigger trigger7on xscjforinsertasbegintransactiondeclare credit int;select credit=credit from inserted xscj;update xsqk set allcredit=allcredit+credit;mittrans

14、actiongo8、分别用触发器和存储过程实现对学生情况表（xsqk）和学生成绩表（xscj）表的级联删除。createtrigger trigger8 on xsqk afterdeleteasdeletefrom xscj where xscj.sname in(select xsqk.sname from deleted xsqk)goCREATEPROCEDURE del_qk_cj sname char(10)=NULLASBEGINdeletefrom xscj where xscj.sname=sname;deletefrom xsqk where xsqk.sname=snam

15、e;ENDGO9、创建触发器8，要现：当向xscj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25，则给出“该学生已修满，不需要再选修！”的提示信息；否则，自动更新该学生的总学分。 createtrigger trigger88on xscjforinsertasbegindeclare credit1 int;declare credit2 int;declare sname char(10);select credit1=xscj.credit,sname=xscj.sname from inserted xscj;select credit2=xsqk.allcre

16、dit from xsqk where xsqk.sname=sname;if(credit1+credit2)>=25beginraiserror('该学生已修满，不需要再选修！',16,1)rollbackreturnendelseinsertinto xscj values(credit1,NULL,NULL,sname);endgo10、在数据库中用以下语句创建两表：CREATE TABLE 卷烟销售表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,购货商 VARCHAR(40) NULL,销售数量 INT NULL,销售单价 MON

17、EY NULL,销售金额 MONEY NULL)GO-业务规则：库存金额 = 库存数量 * 库存单价业务规则。CREATE TABLE 卷烟库存表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,库存数量 INT NULL,库存单价 MONEY NULL,库存金额 MONEY NULL)GO创建触发器T_INSERT_卷烟库存表，实现每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能：强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。针对卷烟库存表，插入测试数据：注意，第一条数据（红塔山新势力）中的数据符合业务规则，第二条数据

18、（红塔山人为峰）中，库存金额空，不符合业务规则，第三条数据（映像）中，库存金额不等于库存数量乘以库存单价，不符合业务规则。第四条数据库存数量为0。请注意在插入数据后，检查卷烟库存表中的数据是否库存金额 = 库存数量 * 库存单价。INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '红塔山新势力',100,12,1200)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '红塔山人为峰',100,22,null)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存

19、金额)values( '映像',100,60,500)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '',0,30,0)11、创建触发器T_INSERT_卷烟销售表，实现每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能：实现业务规则：如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。createtrigger T_INSERT_卷烟销售表on 卷烟销售表forinsertasbegintransactionifnotexists(sel

20、ect 卷烟品牌from 卷烟库存表where 卷烟品牌in(select 卷烟品牌from inserted 卷烟库存表)beginraiserror('该卷烟不存在于库存，不能销售！',16,1);rollbackreturnendifexists(select 库存数量from 卷烟库存表where 卷烟品牌in(select 卷烟品牌from inserted 卷烟库存表)and 库存数量<=0)beginraiserror('该卷烟库存小于等于，不能销售！',16,1);rollbackreturnendupdate 卷烟库存表set 库存金额=

21、 库存数量* 库存单价where 卷烟品牌in(select 卷烟品牌from inserted 卷烟库存表)declare 卷烟品牌VARCHAR(40)set 卷烟品牌=(select 卷烟品牌from inserted 卷烟销售表)declare 销售数量INTset 销售数量=(select 销售数量from inserted 卷烟销售表)update 卷烟库存表set 库存数量=库存数量-销售数量, 库存金额=(库存数量-销售数量)*库存单价where 卷烟品牌=卷烟品牌mittransactiongo 三）T-SQL编程1、从学生_课程数据库中查询所有学生选课成绩情况：、课程名、成绩。要求：将学生的百分制转换为5级评分制，成绩大于等于90显示为“优秀”，成绩在80-89分显示为“良好”，“70-79”分显示为“中等”，成绩在60-69显示为“及格”，60以下显示为“不及格”，没成绩的显示为“未考”。并且输出记录按下列要求排序：先按学号升序，再按课程号升序，最后按成绩降序。 CREATEPROCEDURE