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

1、实验6 数据库实验存储过程和触发器实验6 存储过程和触发器一、实验目的1、加深和巩固对存储过程和触发器概念的理解。2、掌握触发器的简单应用。3、掌握存储过程的简单应用。二、实验内容一）存储过程：1. 创建一存储过程，求l+2+3+n，并打印结果。CREATE PROCEDURE addresult AS DECLARE n int=10, /*最后一个数*/ i int=0, result int=0 /*结果*/BEGIN WHILE(i=n) BEGIN SET result=result+i SET i=i+1 END PRINT 1+2+3+.+n的结果是: PRINT result

2、RETURN(result)ENDGO 2调用上面的addresult存储过程，打印l十2+3+10的结果。 EXEC addresult3. 修改上述存储过程为addresult1,使得n为输入参数，其具体值由用户调用此存储过程时指定。 CREATE PROCEDURE addresult1 n int=10 /*最后一个数*/ AS DECLARE i int=0, result int=0 /*结果*/BEGIN WHILE(i=n) BEGIN SET result=result+i SET i=i+1 END PRINT 1+2+3+.+n的结果是: PRINT result RET

3、URN(result)ENDGO4. 调用上面修改后的addresult1存储过程，打印l+2+3+100的结果。 EXEC addresult1 1005修改上述存储过程为addresult2，将n参数设定默认值为10，并改设sum为输出参数，让主程序能够接收计算结果。 CREATE PROCEDURE addresult2 n int=10, /*最后一个数*/ sum int out /*结果*/AS DECLARE i int=0 BEGIN set sum=0 WHILE(i=n) BEGIN SET sum=sum+i SET i=i+1 ENDENDGO6调用上面修改后的addr

4、esult2存储过程，设置变量s接收计算l+2+3+10的结果。 DECLARE s intset s=0EXEC addresult2 10,sum=s outPRINT 1+2+3+.+n的结果是:PRINT s7创建一存储过程Proc_Student，用于显示学号为“0102”的学生基本信息（包括学号、姓名、性别和系）。 CREATE PROCEDURE Proc_Student AS BEGIN select * from S where =0102ENDGOEXEC Proc_Student8创建一存储过程Stu_grade，通过读取某门课的编号，求出不及格的学生的学号。 CREAT

5、E PROCEDURE Stu_grade n char(10)AS BEGIN select sno from SC where cno=n and grade=p1)之间的书的编号，书名，价格，出舨日期。如果用户调用时没有指定t参数的值则表示可为任意值，如用户没有指定p2，则书本价格没有上限。用到的关系为：titles (title_id,title,price,pubdate)。 CREATE PROCEDURE search t char(10)=%,p1 char(10),p2 char(10)=NULLAS BEGIN select title_id,title,price,pub

6、date from titles where title=t and price=p1ENDGO14调用上面的存储过程search，求出书名与computer有关，而且价格小于$20大于$10的书。EXEC search computer,10,20二）触发器：1、在学生成绩库中创建触发器trigger1，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，自动实现更新该学生在学生情况表（xsqk）中的总学分信息。分析：根据题意，也即要求在学生成绩表中插入一条记录时，自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能是

7、更新学生情况表中的相应学生的总学分信息。其实，只要在该生原总学分基础上加上新选课程的学分就可以了。create trigger trigger1on xscjafter insert as declare credit int; select credit=credit from inserted xscj; update xsqk set allcredit=allcredit+credit;go2、创建触发器trigger2，实现当修改学生课程表（xskc）中的数据时，显示提示信息“学生课程表被修改了”。create trigger trigger2on xskcafter update

8、as print 学生课程表被修改了;go3、创建触发器trigger3，实现当删除学生课程表中某门课程的记录时，对应学生成绩表中所有有关此课程的记录均删除。create trigger trigger3on xskcafter delete as declare cname char(10); select cname=cname from deleted xsks; delete xscj where cname=cname;go4、创建触发器trigger4，实现当修改学生课程表（xskc）中的某门课的课程号时，对应学生成绩表（xscj）中的课程号也作相应修改。create trigge

9、r trigger4on xskcafter update as declare cno char(10); declare cname char(10); select cname=,cno= from updated xsks; update xscj set =cno where =cname;go5、创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，查看该学生的信息是否存在在学生信息表中，如果不存在，则把该学生的基本信息加入到学生信息表中。create trigger trigger5on xscjafter insert as declare sname

10、1 char(10); declare sname2 char(10)=NULL; select sname1= from updated xscj; select sname2= from xsqk where =sname1; if sname2=NULL insert into xsqk values(NULL,sname1); go6、在学生成绩库中创建触发器trigger6，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，查看该学生的信息是否存在在学生信息表中，如果不存在，则给出“该记录不能被插入！”的错误提示，并撤销插入操作；同样，如果课程信息在课程信息表中不存在

11、，给出“该记录不能被插入！”的错误提示，并撤销插入操作。create trigger trigger6on xscjfor insert as begin if not exists (select from xsqk where in(select from inserted xscj) ) begin raiserror(该记录不能被插入！,16,1) rollback return end end go7、创建触发器trigger7，强制实现业务规则：当向学生成绩表中插入一条记录时，自动修改学生情况表中该学生的总学分，要求总学分为该学生所有已修课程的学分总和。create trigger

12、 trigger7on xscjfor insert asbegin transaction declare credit int; select credit=credit from inserted xscj; update xsqk set allcredit=allcredit+credit;commit transactiongo8、分别用触发器和存储过程实现对学生情况表（xsqk）和学生成绩表（xscj）表的级联删除。create trigger trigger8 on xsqk after delete as delete from xscj where in (select f

13、rom deleted xsqk)goCREATE PROCEDURE del_qk_cj sname char(10)=NULLAS BEGIN delete from xscj where =sname; delete from xsqk where =sname; ENDGO9、创建触发器8，要求实现：当向xscj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25，则给出“该学生已修满，不需要再选修！”的提示信息；否则，自动更新该学生的总学分。create trigger trigger88on xscjfor insert as begin declare credit1 i

14、nt; declare credit2 int; declare sname char(10); select credit1=,sname= from inserted xscj; select credit2= from xsqk where =sname; if(credit1+credit2)=25 begin raiserror(该学生已修满，不需要再选修！,16,1) rollback return end else insert into xscj values(credit1,NULL,NULL,sname); end go10、在数据库中用以下语句创建两张表：CREATE T

15、ABLE 卷烟销售表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,购货商 VARCHAR(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 卷烟库存表(卷烟品牌,库

17、存数量,库存单价,库存金额)values( 红塔山人为峰,100,22,null)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( 云南映像,100,60,500)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( 玉溪,0,30,0) 11、创建触发器T_INSERT_卷烟销售表，实现每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能： 实现业务规则：如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。create trigger T

18、_INSERT_卷烟销售表on 卷烟销售表for insertas begin transaction if not exists(select 卷烟品牌 from 卷烟库存表 where 卷烟品牌in(select 卷烟品牌 from inserted 卷烟库存表) begin raiserror(该卷烟不存在于库存，不能销售！,16,1); rollback return end if exists(select 库存数量 from 卷烟库存表 where 卷烟品牌in(select 卷烟品牌 from inserted 卷烟库存表) and 库存数量=90 then 优秀 when =8

19、0 then 良好 when =70 then 中等 when =60 then 及格 when =0 then 不及格 else 未考 end from SC,S,C where = and = order by descENDGO2、利用学生_课程数据库中的SC表，编程实现：如果所有学生所有课程的平均成绩高于80分，使用while循环就将每门成绩减5分，然后查找所有学生所有课程中的最高分，如果最高分大于或等于85分，while循环重新启动并再次将每门成绩减5分并继续查找所有学生所有课程中的最高分，一直循环到最高分低于85分循环停止，然后退出。CREATE PROCEDURE 选课成绩情况AS BEGIN while(select max(grade) from SC)=85 begin update SC set grade=grade-5 select avg(grade) from SC if(select max(grade) from SC)85 break; else continue end select avg(grade) 平均成绩from SCENDGOEXEC 选课成绩情况