实验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

RETURN(@result)

END

GO

2.调用上面的addresult存储过程,打印l十2+3+…+10的结果。EXEC addresult

3、修改上述存储过程为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)

END

GO

4、调用上面修改后的addresult1存储过程,打印l+2+3+…+100的结果。

EXEC addresult1 100

5.修改上述存储过程为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

END

END

GO

6.调用上面修改后的addresult2存储过程,设置变量@s接收计算l+2+3+…+10的结果。

DECLARE @s int

set @s=0

EXEC addresult2 10,@sum=@s out

PRINT'1+2+3+、、、+n的结果就是:'

PRINT @s

7.创建一存储过程Proc_Student,用于显示学号为“0102”的学生基本信息(包括学号、姓名、性别与系)。

CREATE PROCEDURE Proc_Student

AS

BEGIN

select*

from S

where S、sno=0102

END

GO

EXEC Proc_Student

8.创建一存储过程Stu_grade,通过读取某门课的编号,求出不及格的学生的学号。

CREATE PROCEDURE Stu_grade

@n char(10)

AS

BEGIN

select sno

from SC

where cno=@n and grade<60

END

GO

9.调用上面的存储过程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 sno

END

GO

11.调用上面的存储过程avgGrade,求出学号为“990102014”的平均分。

EXEC avgGrade 990102014

12.删除上述存储过程avgGrade。

drop procedure avgGrade

13、创建存储过程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)=NULL

AS

BEGIN

select title_id,title,price,pubdate

from titles

where title=@t and price<=@p2 and price>=@p1

END

GO

14.调用上面的存储过程search,求出书名与computer有关,而且价格小于$20大于$10的书。

EXEC search "computer",10,20

二)触发器:

1、在学生成绩库中创建触发器trigger1,实现如下功能:当在学生成绩表(xscj)中插入一条学生选课信息后,自动实现更新该学生在学生情况表(xsqk)中的总学分信息。

分析:根据题意,也即要求在学生成绩表中插入一条记录时,自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT 类型的触发器,触发器中语句要完成的功能就是更新学生情况表中的相应学生的总学分信息。其实,只要在该生原总学分基础上加上新选课程的学分就可以了。

create trigger trigger1

on xscj

after insert

as

declare @credit int;

select @credit=credit from inserted xscj;

update xsqk set allcredit=allcredit+@credit;

go

2、创建触发器trigger2,实现当修改学生课程表(xskc)中的数据时,显示

提示信息“学生课程表被修改了”。

create trigger trigger2

on xskc

after update

as

print'学生课程表被修改了';

go

3、创建触发器trigger3,实现当删除学生课程表中某门课程的记录时,对应学生成绩表中所有有关此课程的记录均删除。

create trigger trigger3

on xskc

after delete

as

declare @cname char(10);

select @cname=cname from deleted xsks;

delete xscj where cname=@cname;

go

4、创建触发器trigger4,实现当修改学生课程表(xskc)中的某门课的课程号时,对应学生成绩表(xscj)中的课程号也作相应修改。

create trigger trigger4

on xskc

after update

as

declare @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;

go

5、创建触发器trigger5,实现当向学生成绩表(xscj)中插入一条选课记录时,查瞧该学生的信息就是否存在在学生信息表中,如果不存在,则把该学生的基本信息加入到学生信息表中。

create trigger trigger5

on xscj

after 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);

go

6、在学生成绩库中创建触发器trigger6,实现如下功能:当在学生成绩表(xscj)中插入一条学生选课信息后,查瞧该学生的信息就是否存在在学生信息表中,如果不存在,则给出“该记录不能被插入！”的错误提示,并撤销插入操作;同样,如果课程信息在课程信息表中不存在,给出“该记录不能被插入！”的错误提示,并撤销插入操作。

create trigger trigger6

on xscj

for 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

go

7、创建触发器trigger7,强制实现业务规则:当向学生成绩表中插入一条记录时,自动修改学生情况表中该学生的总学分,要求总学分为该学生所有已修课程的学分总与。

create trigger trigger7

on xscj

for insert

as

begin transaction

declare @credit int;

select @credit=credit from inserted xscj;

update xsqk set allcredit=allcredit+@credit;

commit transaction

go

8、分别用触发器与存储过程实现对学生情况表(xsqk)与学生成绩表(xscj)表的级联删除。

create trigger trigger8

on xsqk

after delete

as

delete from xscj

where xscj、sname in

(select xsqk、sname from deleted xsqk)

CREATE PROCEDURE del_qk_cj

@sname char(10)=NULL

AS

BEGIN

delete from xscj

where xscj、sname=@sname;

delete from xsqk

where xsqk、sname=@sname;

END

GO

9、创建触发器8,要求实现:当向xscj表插入一条记录时,判断该学生的总学分,如果总学分大于等于25,则给出“该学生已修满,不需要再选修！”的提示信息;否则,自动更新该学生的总学分。

create trigger trigger88

on xscj

for 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

go

10、在数据库中用以下语句创建两张表:

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 insert

as

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 transaction

go

三)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 desc

END

GO

2、利用学生_课程数据库中的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 SC

END

GO

EXEC选课成绩情况