实验6 存储过程与触发器一、实验目的1、加深与巩固对存储过程与触发器概念的理解。

2、掌握触发器的简单应用。

3、掌握存储过程的简单应用。

二、实验内容一)存储过程:1、创建一存储过程,求l+2+3+…+n,并打印结果。

CREATE PROCEDURE addresultASDECLARE @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)ENDGO2.调用上面的addresult存储过程,打印l十2+3+…+10的结果。

EXEC addresult3、修改上述存储过程为addresult1,使得@n为输入参数,其具体值由用户调用此存储过程时指定。

CREATE PROCEDURE 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)ENDGO4、调用上面修改后的addresult1存储过程,打印l+2+3+…+100的结果。

EXEC addresult1 1005.修改上述存储过程为addresult2,将@n参数设定默认值为10,并改设@sum为输出参数,让主程序能够接收计算结果。

CREATE PROCEDURE addresult2@n int=10,/*最后一个数*/@sum int out/*结果*/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=0EXEC addresult2 10,@sum=@s outPRINT'1+2+3+、、、+n的结果就是:'PRINT @s7.创建一存储过程Proc_Student,用于显示学号为“0102”的学生基本信息(包括学号、姓名、性别与系)。

CREATE PROCEDURE Proc_StudentASBEGINselect*from Swhere S、sno=0102ENDGOEXEC Proc_Student8.创建一存储过程Stu_grade,通过读取某门课的编号,求出不及格的学生的学号。

CREATE PROCEDURE Stu_grade@n char(10)ASBEGINselect snofrom SCwhere cno=@n and grade<60ENDGO9.调用上面的存储过程Stu_grade,求出课程编号为“0101”的不及格的学生。

EXEC Stu_grade 010110.创建一存储过程avgGrade,通过读取学生的学号,以参数形式返回该学生的平均分。

CREATE PROCEDURE avgGrade@n char(10)ASBEGINselect AVG(grade)平均分from SCwhere sno=@ngroup by snoENDGO11.调用上面的存储过程avgGrade,求出学号为“990102014”的平均分。

EXEC avgGrade 99010201412.删除上述存储过程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)=NULLASBEGINselect title_id,title,price,pubdatefrom titleswhere 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 insertasdeclare @credit int;select @credit=credit from inserted xscj;update xsqk set allcredit=allcredit+@credit;go2、创建触发器trigger2,实现当修改学生课程表(xskc)中的数据时,显示提示信息“学生课程表被修改了”。

create trigger trigger2on xskcafter updateasprint'学生课程表被修改了';go3、创建触发器trigger3,实现当删除学生课程表中某门课程的记录时,对应学生成绩表中所有有关此课程的记录均删除。

create trigger trigger3on xskcafter deleteasdeclare @cname char(10);select @cname=cname from deleted xsks;delete xscj where cname=@cname;go4、创建触发器trigger4,实现当修改学生课程表(xskc)中的某门课的课程号时,对应学生成绩表(xscj)中的课程号也作相应修改。

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

create trigger trigger5on xscjafter insertasdeclare @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=NULLinsert into xsqk values(NULL,@sname1);go6、在学生成绩库中创建触发器trigger6,实现如下功能:当在学生成绩表(xscj)中插入一条学生选课信息后,查瞧该学生的信息就是否存在在学生信息表中,如果不存在,则给出“该记录不能被插入！”的错误提示,并撤销插入操作;同样,如果课程信息在课程信息表中不存在,给出“该记录不能被插入！”的错误提示,并撤销插入操作。

create trigger trigger6on xscjfor insertasbeginif not exists(select xsqk、sname from xsqk where xsqk、sname in(select xscj、sname from inserted xscj)beginraiserror('该记录不能被插入！',16,1)rollbackreturnendendgo7、创建触发器trigger7,强制实现业务规则:当向学生成绩表中插入一条记录时,自动修改学生情况表中该学生的总学分,要求总学分为该学生所有已修课程的学分总与。

create trigger trigger7on xscjfor insertasbegin transactiondeclare @credit int;select @credit=credit from inserted xscj;update xsqk set allcredit=allcredit+@credit;commit transactiongo8、分别用触发器与存储过程实现对学生情况表(xsqk)与学生成绩表(xscj)表的级联删除。

create trigger trigger8on xsqkafter deleteasdelete from xscjwhere xscj、sname in(select xsqk、sname from deleted xsqk)CREATE PROCEDURE del_qk_cj@sname char(10)=NULLASBEGINdelete from xscjwhere xscj、sname=@sname;delete from xsqkwhere xsqk、sname=@sname;ENDGO9、创建触发器8,要求实现:当向xscj表插入一条记录时,判断该学生的总学分,如果总学分大于等于25,则给出“该学生已修满,不需要再选修！”的提示信息;否则,自动更新该学生的总学分。