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

-实验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)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 RETURN(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调用上面修改后的addresult2存储过程，设置变量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 S.sno=0102ENDGOEXEC Proc_Student8创建一存储过程Stu_grade，通过读取某门课的编号，求出不及格的学生的学号。 CREATE PROCEDURE Stu_grade n char(10)AS BEGIN select sno from SC where cno=n and grade<60ENDGO9调用上面的存储过程Stu_grade，求出课程编号为“0101”的不及格的学生。EXEC Stu_grade 0101 10创建一存储过程avgGrade，通过读取学生的学号，以参数形式返回该学生的平均分。 CREATE PROCEDURE avgGrade n char(10)AS BEGIN select AVG(grade) 平均分 from SC where sno=n group by snoENDGO11调用上面的存储过程avgGrade，求出学号为“990102014”的平均分。EXEC avgGrade 990102014 12删除上述存储过程avgGrade。 drop procedure avgGrade13.创建存储过程search，该存储过程有三个参数，分别为t、p1，p2，根据这些参数，找出书名与t有关，价格在p1与p2(p2>=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 titles where title=t and price<=p2 and price>=p1ENDGO14调用上面的存储过程search，求出书名与computer有关，而且价格小于$20大于$10的书。 EXEC search "computer",10,20二）触发器：1、在学生成绩库中创建触发器trigger1，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，自动实现更新该学生在学生情况表（xsqk）中的总学分信息。分析：根据题意，也即要求在学生成绩表中插入一条记录时，自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息。其实，只要在该生原总学分基础上加上新选课程的学分就可以了。create trigger trigger1on xscjafter insert as declare credit int; select credit=credit from inserted xscj; update xsqk set allcredit=allcredit+credit;go 2、创建触发器trigger2，实现当修改学生课程表（xskc）中的数据时，显示提示信息“学生课程表被修改了”。 create trigger trigger2on xskcafter update 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 trigger trigger4on 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 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=NULL insert into xsqk values(NULL,sname1); go6、在学生成绩库中创建触发器trigger6，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，查看该学生的信息是否存在在学生信息表中，如果不存在，则给出“该记录不能被插入！”的错误提示，并撤销插入操作；同样，如果课程信息在课程信息表中不存在，给出“该记录不能被插入！”的错误提示，并撤销插入操作。 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，强制实现业务规则：当向学生成绩表中插入一条记录时，自动修改学生情况表中该学生的总学分，要求总学分为该学生所有已修课程的学分总和。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 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，则给出“该学生已修满，不需要再选修！”的提示信息；否则，自动更新该学生的总学分。 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)>=25 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-业务规则：库存金额 = 库存数量 * 库存单价 业务规则。CREATE TABLE 卷烟库存表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,库存数量 INT NULL,库存单价 MONEY NULL,库存金额 MONEY NULL)GO创建触发器T_INSERT_卷烟库存表，实现每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能：强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。针对卷烟库存表，插入测试数据：注意，第一条数据（红塔山新势力）中的数据符合业务规则，第二条数据（红塔山人为峰）中，库存金额空，不符合业务规则，第三条数据（云南映像）中，库存金额不等于库存数量乘以库存单价，不符合业务规则。第四条数据库存数量为0。请注意在插入数据后，检查卷烟库存表中的数据是否 库存金额 = 库存数量 * 库存单价。INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '红塔山新势力',100,12,1200)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '红塔山人为峰',100,22,null)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '云南映像',100,60,500)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '玉溪',0,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('该卷烟不存在于库存，不能销售！',16,1); rollback return end if exists(select 库存数量 from 卷烟库存表 where 卷烟品牌in(select 卷烟品牌 from inserted 卷烟库存表) and 库存数量<=0) begin raiserror('该卷烟库存小于等于，不能销售！',16,1); rollback return end update 卷烟库存表 set 库存金额= 库存数量* 库存单价 where 卷烟品牌in (select 卷烟品牌from inserted 卷烟库存表) declare 卷烟品牌VARCHAR(40) set 卷烟品牌=(select 卷烟品牌from inserted 卷烟销售表) declare 销售数量INT set 销售数量=(select 销售数量from inserted 卷烟销售表) update 卷烟库存表 set 库存数量=库存数量-销售数量, 库存金额=(库存数量-销售数量)*库存单价 where 卷烟品牌=卷烟品牌commit transactiongo 三）T-SQL编程1、从学生_课程数据库中查询所有学生选课成绩情况：姓名、课程名、成绩。要求：将学生的百分制转换为5级评分制，成绩大于等于90显示为“优秀”，成绩在80-89分显示为“良好”，“70-79”分显示为“中等”，成绩在60-69显示为“及格”，60以下显示为“不及格”，没成绩的显示为“未考”。并且输出记录按下列要求排序：先按学号升序，再按课程号升序，最后按成绩降序。 CREATE PROCEDURE 选课成绩情况AS BEGIN select S.sno 学号,S.sname 姓名,C.cno 课程号,C.cname 课程名,成绩= case when SC.grade>=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 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 页-