实验6 数据库实验——存储过程和触发器(17页).doc

《实验6 数据库实验——存储过程和触发器(17页).doc》由会员分享，可在线阅读，更多相关《实验6 数据库实验——存储过程和触发器(17页).doc（17页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-实验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 RETURN(result)EN

2、DGO 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 RETURN(result)ENDGO

3、4. 调用上面修改后的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调用上面修改后的addresult2存储过程，设置变量s

4、接收计算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 S.sno=0102ENDGOEXEC Proc_Student8创建一存储过程Stu_grade，通过读取某门课的编号，求出不及格的学生的学号。 CREATE PROCEDURE

5、 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,pubdate from t

6、itles 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 as print 学生

8、课程表被修改了;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 trigger trigger4o

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

10、har(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=NULL insert into xsqk values(NULL,sname1); go6、在学生成绩库中创建触发器trigger6，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，查看该学生的信息是否存在在学生信息表中，如果不存在，则给出“该记录不能被插入！”的错误提示，

11、并撤销插入操作；同样，如果课程信息在课程信息表中不存在，给出“该记录不能被插入！”的错误提示，并撤销插入操作。create trigger trigger6on xscjfor insert as begin if not exists (select xsqk.sname from xsqk where xsqk.sname in(select xscj.sname from inserted xscj) ) begin raiserror(该记录不能被插入！,16,1) rollback return end end go7、创建触发器trigger7，强制实现业务规则：当向学生成绩表中插

12、入一条记录时，自动修改学生情况表中该学生的总学分，要求总学分为该学生所有已修课程的学分总和。create trigger 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

13、 on xsqk after delete as delete from xscj where xscj.sname in (select xsqk.sname from deleted xsqk)goCREATE PROCEDURE del_qk_cj sname char(10)=NULLAS BEGIN delete from xscj where xscj.sname=sname; delete from xsqk where xsqk.sname=sname; ENDGO9、创建触发器8，要求实现：当向xscj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25，则给出“该学

14、生已修满，不需要再选修！”的提示信息；否则，自动更新该学生的总学分。create trigger trigger88on xscjfor insert as begin declare credit1 int; declare credit2 int; declare sname char(10); select credit1=xscj.credit,sname=xscj.sname from inserted xscj; select credit2=xsqk.allcredit from xsqk where xsqk.sname=sname; if(credit1+credit2)=2

15、5 begin raiserror(该学生已修满，不需要再选修！,16,1) rollback return end else insert into xscj values(credit1,NULL,NULL,sname); end go10、在数据库中用以下语句创建两张表：CREATE TABLE 卷烟销售表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,购货商 VARCHAR(40) NULL,销售数量 INT NULL,销售单价 MONEY NULL,销售金额 MONEY NULL)GO-业务规则：库存金额 = 库存数量 * 库存单价 业务规则。CREAT

16、E TABLE 卷烟库存表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,库存数量 INT NULL,库存单价 MONEY NULL,库存金额 MONEY NULL)GO创建触发器T_INSERT_卷烟库存表，实现每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能：强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。针对卷烟库存表，插入测试数据：注意，第一条数据（红塔山新势力）中的数据符合业务规则，第二条数据（红塔山人为峰）中，库存金额空，不符合业务规则，第三条数据（云南映像）中，库存金额不等于库存数量乘以库存单价，不符合业务

17、规则。第四条数据库存数量为0。请注意在插入数据后，检查卷烟库存表中的数据是否 库存金额 = 库存数量 * 库存单价。INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( 红塔山新势力,100,12,1200)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( 红塔山人为峰,100,22,null)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( 云南映像,100,60,500)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( 玉溪,0

18、,30,0) 11、创建触发器T_INSERT_卷烟销售表，实现每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能： 实现业务规则：如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。create trigger T_INSERT_卷烟销售表on 卷烟销售表for insertas begin transaction if not exists(select 卷烟品牌 from 卷烟库存表 where 卷烟品牌in(select 卷烟品牌 from inserted 卷烟库存表) begin raiserror(该卷

19、烟不存在于库存，不能销售！,16,1); rollback return end if exists(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 未考 end from SC,S,C where SC.cno=C.cno and SC.sno=S.sno

20、order by SC.grade 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 选课成绩情况-第 17 页-