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

1、-. z.实验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+1ENDPRINT1+2+3+.+n的结果是:PRINT resultRETURN(result)ENDGO2调用上面的

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

3、，打印l+2+3+100的结果。E*EC 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+10的结果。DECLARE s intset s=0E*EC

4、 addresult2 10,sum=s outPRINT1+2+3+.+n的结果是:PRINT s7创立一存储过程Proc_Student，用于显示*为0102”的学生根本信息包括*、性别和系。CREATEPROCEDURE Proc_Student ASBEGINselect*from Swhere S.sno=0102ENDGOE*EC Proc_Student8创立一存储过程Stu_grade，通过读取*门课的编号，求出不及格的学生的*。CREATEPROCEDURE Stu_grade n char(10)ASBEGINselect snofrom SCwhereo=n and gr

5、ade=p1)之间的书的编号，书名，价格，出舨日期。如果用户调用时没有指定t参数的值则表示可为任意值，如用户没有指定p2，则书本价格没有上限。用到的关系为：titles (title_id,title,price,pubdate)。CREATEPROCEDURE search t char(10)=%,p1 char(10),p2 char(10)=NULLASBEGINselect title_id,title,price,pubdatefrom titleswhere title=tand price=p1ENDGO14调用上面的存储过程search，求出书名与puter有关，而且价格小于

6、$20大于$10的书。E*EC search puter,10,20二触发器：1、在学生成绩库中创立触发器trigger1，实现如下功能：当在学生成绩表*scj中插入一条学生选课信息后，自动实现更新该学生在学生情况表*sqk中的总学分信息。分析：根据题意，也即要求在学生成绩表中插入一条记录时，自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息。其实，只要在该生原总学分根底上加上新选课程的学分就可以了。createtrigger trigger1on *scjafterinsertasdecla

7、re credit int;select credit=credit from inserted *scj;update *sqk set allcredit=allcredit+credit;go2、创立触发器trigger2，实现当修改学生课程表*skc中的数据时，显示提示信息学生课程表被修改了。createtrigger trigger2on *skcafterupdateasprint学生课程表被修改了;go3、创立触发器trigger3，实现当删除学生课程表中*门课程的记录时，对应学生成绩表中所有有关此课程的记录均删除。createtrigger trigger3on *skcaft

8、erdeleteasdeclare ame char(10);select ame=ame from deleted *sks;delete *scj whereame=ame;go4、创立触发器trigger4，实现当修改学生课程表*skc中的*门课的课程号时，对应学生成绩表*scj中的课程号也作相应修改。createtrigger trigger4on *skcafterupdateasdeclare o char(10);declare ame char(10);select ame=*skc.ame,o=*skc.o from updated *sks;update *scj set

9、*scj.o=o where *scj.ame=ame;go5、创立触发器trigger5，实现当向学生成绩表*scj中插入一条选课记录时，查看该学生的信息是否存在在学生信息表中，如果不存在，则把该学生的根本信息参加到学生信息表中。createtrigger trigger5on *scjafterinsertasdeclare sname1 char(10);declare sname2 char(10)=NULL;select sname1=*scj.sname from updated *scj;select sname2=*sqk.sname from *sqk where *sqk.

10、sname=sname1;if sname2=NULLinsertinto *sqk values(NULL,sname1);go6、在学生成绩库中创立触发器trigger6，实现如下功能：当在学生成绩表*scj中插入一条学生选课信息后，查看该学生的信息是否存在在学生信息表中，如果不存在，则给出该记录不能被插入！的错误提示，并撤销插入操作；同样，如果课程信息在课程信息表中不存在，给出该记录不能被插入！的错误提示，并撤销插入操作。createtrigger trigger6on *scjforinsertasbeginifnote*ists(select *sqk.snamefrom *sqk

11、where *sqk.sname in(select *scj.sname from inserted *scj)beginraiserror(该记录不能被插入！,16,1)rollbackreturnendendgo7、创立触发器trigger7，强制实现业务规则：当向学生成绩表中插入一条记录时，自动修改学生情况表中该学生的总学分，要求总学分为该学生所有已修课程的学分总和。createtrigger trigger7on *scjforinsertasbegintransactiondeclare credit int;select credit=credit from inserted *

12、scj;update *sqk set allcredit=allcredit+credit;mittransactiongo8、分别用触发器和存储过程实现对学生情况表*sqk和学生成绩表*scj表的级联删除。createtrigger trigger8 on *sqk afterdeleteasdeletefrom *scj where *scj.sname in(select *sqk.sname from deleted *sqk)goCREATEPROCEDURE del_qk_cj sname char(10)=NULLASBEGINdeletefrom *scj where *sc

13、j.sname=sname;deletefrom *sqk where *sqk.sname=sname;ENDGO9、创立触发器8，要*现：当向*scj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25，则给出该学生已修满，不需要再选修！的提示信息；否则，自动更新该学生的总学分。createtrigger trigger88on *scjforinsertasbegindeclare credit1 int;declare credit2 int;declare sname char(10);select credit1=*scj.credit,sname=*scj.sname f

14、rom inserted *scj;select credit2=*sqk.allcredit from *sqk where *sqk.sname=sname;if(credit1+credit2)=25beginraiserror(该学生已修满，不需要再选修！,16,1)rollbackreturnendelseinsertinto *scj values(credit1,NULL,NULL,sname);endgo10、在数据库中用以下语句创立两*表：CREATE TABLE 卷烟销售表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,购货商 VARCHAR(

15、40) NULL,销售数量 INT NULL,销售单价 MONEY NULL,销售金额 MONEY NULL)GO-业务规则：库存金额 = 库存数量 * 库存单价业务规则。CREATE TABLE 卷烟库存表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,库存数量 INT NULL,库存单价 MONEY NULL,库存金额 MONEY NULL)GO创立触发器T_INSERT_卷烟库存表，实现每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能：强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。针对卷烟库存表，插入测试数据：注

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

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

18、卷烟库存表where 卷烟品牌in(select 卷烟品牌from inserted 卷烟库存表)beginraiserror(该卷烟不存在于库存，不能销售！,16,1);rollbackreturnendife*ists(select 库存数量from 卷烟库存表where 卷烟品牌in(select 卷烟品牌from inserted 卷烟库存表)and 库存数量=90 then优秀when SC.grade=80 then良好when SC.grade=70 then中等when SC.grade=60 then及格when SC.grade=0 then不及格else未考endfrom SC,S,Cwhere SC.o=C.o and SC.sno=S.snoorderby SC.grade descENDGO2、利用学生_课程数据库中的SC表，编程实现：如果所有学生所有课程的平均成绩高于80分，使用while循环就将每门成绩减5分，然后查找所有学生所有课程中的最