实验6数据库实验存储过程和触发器.docx
《实验6数据库实验存储过程和触发器.docx》由会员分享，可在线阅读，更多相关《实验6数据库实验存储过程和触发器.docx（22页珍藏版）》请在冰豆网上搜索。
实验6数据库实验存储过程和触发器
实验6存储过程与触发器
、实验目的
1、加深与巩固对存储过程与触发器概念的理解。
2、掌握触发器的简单应用。
3、掌握存储过程的简单应用。
二、实验内容
1）存储过程：
1、创建一存储过程，求1+2+3+…+n并打印结果。
CREATEPROCEDUREaddresult
AS
DECLARE@nint=10,/*最后一个数*/
@iint=0,
@resultint=0/*结果*/
BEGIN
WHILE（@i<=@r）
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的结果
EXECaddresult
3、修改上述存储过程为addresultl,使得@n为输入参数，其具体值由用户调用此存储过程时指定。
CREATEPROCEDUREaddresultl
@nint=10/*最后一个数*/
AS
DECLARE@iint=0,
@resultint=0/*结果*/
BEGIN
WHILE（@i<=@r）
BEGIN
SET@result=@result+@i
SET@i=@i+1
END
PRINT'1+2+3+、、、+n的结果就是：
’
PRINT@result
RETURN（@result）
END
GO
*
命令已成9閘克
4、调用上面修改后的addresultl存储过程，打印1+2+3十…+100的结果。
EXECaddresultl100
心消息
1+2+3+..-如瞬果是:
他0
5•修改上述存储过程为addresult2,将@n参数设定默认值为10,并改设
@sum为输出参数，让主程序能够接收计算结果。
CREATEPROCEDUREaddresult2
@nint=10,/*最后一个数*/
@sumintout/*结果*/
AS
DECLARE@iint=0
BEGIN
set@sum=0
WHILE（@i<=@r）
BEGIN
SET@sum=@sum+@i
SET@i=@i+1
END
END
GO
6.调用上面修改后的addresult2存储过程，设置变量@s接收计算
1+2+3+…+10的结果。
DECLARE@sint
set@s=0
EXECaddresult210,@sum=@sout
PRINT'1+2+3+、、、+n的结果就是：
'
PRINT@s
7.创建一存储过程Proc_Student用于显示学号为“0102”的学生基本信息（包括学号、姓名、性别与系）。
CREATEPROCEDUREProc_Student
AS
BEGIN
select*
fromS
whereS、sno=0102
END
GO
EXECProcStudent
8.创建一存储过程Stu_grade通过读取某门课的编号，求出不及格的学
生的学号。
CREATEPROCEDUREStu_grade
@nchar（10）
AS
BEGIN
selectsno
fromSC
whereeno=@nandgrade<60
END
GO
9•调用上面的存储过程Stu_grade求出课程编号为“0101”的不及格
的学生
EXECStu_grade0101
彳1
EXECScugrade0101
闻结杲［匕消息
sno
10.创建一存储过程avgGrade通过读取学生的学号，以参数形式返回该学生的平均分。
CREATEPROCEDUREavgGrade
@nchar（10）
AS
BEGIN
selectAVG（grade）平均分
fromSC
wheresno=@n
groupbysno
END
GO
11.调用上面的存储过程avgGrade求出学号为“990102014”的平均
分。
EXECavgGrade990102014
*1
EXEC
3结果蔭消息」
平均分
12.删除上述存储过程avgGrade,
dropprocedureavgGrade
—Lnr*■■甘、—-rtt"■—-ib
-dropp.oced口工飪aT-cGrade|
鬲消息
13、创建存储过程search该存储过程有三个参数，分别为@t、@p1,@p2,根据这些参数，找出书名与@t有关,价格在@p1与@p2（@p2>=@p1之间的书的编号，书名，价格，出舨日期。
如果用户调用时没有指定@t参
数的值.则表示可为任意值，如用户没有指定@p2,则书本价格没有上限。
用到的关系为:
titles（title_id,title,price,pubdate）。
CREATEPROCEDUREsearch
@tchar（10）="%",
@p1char（10）,
@p2char（10）=NULL
AS
BEGIN
wheretitle=@tandprice<=@p2andprice>=@p1
END
GO
14.调用上面的存储过程search求出书名与computer有关,而且价格小于$20大于$10的书。
EXECsearch"computer",10,20
2）触发器：
1、在学生成绩库中创建触发器triggers实现如下功能：
当在学生成绩
表（xscj）中插入一条学生选课信息后，自动实现更新该学生在学生情况表（xsqk）中的总学分信息。
分析:
根据题意,也即要求在学生成绩表中插入一条记录时，自动更新
学生情况表中的相应记录信息。
可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能就是更新学生情况表中的相应学生的总学分信息。
其实，只要在该生原总学分基础上加上新选课程的学分就可以了。
createtriggertrigger1
onxscj
afterinsert
as
declare@creditint;
select@credit=creditfrominsertedxscj;
updatexsqksetallcredit=allcredit+@credit;
go
2、创建触发器trigger2,实现当修改学生课程表（xskc）中的数据时,显示
createtriggertrigger2
onxskc
afterupdate
print'学生课程表被修改了
asgo
3、创建触发器trigger,实现当删除学生课程表中某门课程的记录时对应学生成绩表中所有有关此课程的记录均删除。
createtriggertrigger3
onxskc
afterdelete
as
declare@cnamechar（10）;
select@cname=cnamefromdeletedxsksdeletexscjwherecname=@cname;
go
4、创建触发器trigger4,实现当修改学生课程表（xskc）中的某门课的课程号时，对应学生成绩表（xscj）中的课程号也作相应修改。
createtriggertrigger4
onxskc
afterupdate
as
declare@cnochar（10）;
declare@cnamechar（10）;
select@cname=xskc、cname,@cno=xskc、enofromupdatedxsks;
updatexscjsetxscj、eno=@cnowherexscj、cname=@cname;
go
5、创建触发器trigger5,实现当向学生成绩表（xscj）中插入一条选课记录时,查瞧该学生的信息就是否存在在学生信息表中，如果不存在，则
把该学生的基本信息加入到学生信息表中。
createtriggertrigger5
onxscj
afterinsert
as
declare@snamelchar（10）;
declare@sname2char（10）=NULL;
select@sname1=xscj、snamefromupdatedxscj;
select@sname2=xsqk、snamefromxsqkwherexsqk、sname=@sname1;
if@sname2=NULL
insertintoxsqkvalues（NULL,@sname1）;
go
6、在学生成绩库中创建触发器trigger6，实现如下功能：
当在学生成绩
表（xscj）中插入一条学生选课信息后，查瞧该学生的信息就是否存在在学生信息表中，如果不存在，则给出“该记录不能被插入！
”的错误提示，并撤销插入操作；同样，如果课程信息在课程信息表中不存在，给出
“该记录不能被插入！
”的错误提示，并撤销插入操作。
createtriggertrigger6
onxscj
forinsert
as
begin
ifnotexists
（selectxsqk、snamefromxsqkwherexsqk、snamein（selectxscj、sname
frominsertedxscj）
begin
raiserror（'该记录不能被插入！
’，16,1）
rollback
return
end
end
go
7、创建触发器trigger7,强制实现业务规则：
当向学生成绩表中插入一条记录时，自动修改学生情况表中该学生的总学分，要求总学分为该学生所有已修课程的学分总与。
createtriggertrigger7
onxscj
forinsert
as
begintransaction
declare@creditint;
select@credit=creditfrominsertedxscj;
updatexsqksetallcredit=allcredit+@credit;
committransaction
go
&分别用触发器与存储过程实现对学生情况表（xsqk与学生成绩表
（xscj表的级联删除。
createtriggertrigger8
onxsqk
afterdelete
as
deletefromxscj
wherexscj、snamein
（selectxsqk、snamefromdeletedxsqk）
go
1-耳uqK.nnuirffrcn1.xsqk：
CREATEPROCEDUREdel_qk_cj@snamechar（10）=NULLAS
BEGIN
delete
from
xscj
where
xscj、
sname=@sname;
delete
from
xsqk
where
xsqk、
sname=@sname;
END
GO
ECREATEPROCEDUREdel_qk_cjSsnair.echar（10=1RJLL
AS
（iBEGIN
E]deletefronxacj
whexexscj.且namg=世且口師十：
r\deletefrorx^qk
炖b电工皀xsqk•朋克昭=临吕席址已：
Lend
-命晶成功完成。
9、创建触发器8,要求实现：
当向xscj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25,则给出“该学生已修满，不需要再选
修！
”的提示信息；否则，自动更新该学生的总学分
createtriggertrigger88
onxscj
forinsertas
begin
declare
@credit1
int;
declare
@credit2
int;
declare
@sname
char（10）;
select
@credit1
=xscj、credit
@sname
=xscj、
snamefrom
insertedxscj;
select
@credit2
=xsqk、allcredit
from
xsqk
wherexsqk
、sname=@sname;
if（@credit1+@credit2）>=25
begin
raiserror（'该学生已修满，不需要再选修！
’，16,1）
rollback
return
end
else
insertintoxscjvalues（@credit1,NULL,NULL,@sname）;
end
go
[■
匚
3exeateLzigyet22
okxaejfcrinsexta雷
]be-gm
icreditlint;
ID匸匚
dsslai!
«0seat趙eh&r（10）?
sslsct-credit-,囂gcj”羽凰贱fssm.acscj:
selectScreait-L-xs口k・aiicreoixrronixsaiwneiexsaic■超厲圧=越毁ehik:
]if（3czedxcl+€eredic2）>-2&
]Legla
raia^rrori'该学生.已倚;苗"不需要再选瞭！
*"”!
.）
rcllbaLCk
xeturn
-end
xntQ搖values（ScicdjLtlrtlULLp'4UXLP@;
*nd.
L
Tr
命令已威功壳成:
■
10、在数据库中用以下语句创建两张表：
CREATETABLE烟销售表
（
卷烟品牌VARCHAR（40）PRIMARYKEYNOTNULL,购货商VARCHAR（40）NULL,
销售数量INTNULL,
销售单价MONEYNULL,
销售金额MONEYNULL
）
GO
--业务规则:
库存金额=库存数量*库存单价业务规则。
CREATETABLE烟库存表
（
卷烟品牌VARCHAR（40）PRIMARYKEYNOTNULL,
库存数量INTNULL,
库存单价MONEYNULL,
库存金额MONEYNULL
）
GO
创建触发器［T_INSERT卷烟库存表］,实现每当［卷烟库存表］发生INSERT动作，则引发该触发器。
触发器功能：
强制执行业务规则，保证插入的数据中,库存金额=库存数量*库存单价。
针对［卷烟库存表］,插入测试数据：
注意,第一条数据（红塔山新势力）中的数据符合业务规则,第二条数据（红塔山人为峰）中,［库存金额］空,不符合业务规则,第三条数据（云南映像）中,［库存金额］不等于［库存数量］乘以［库存单价］,不符合业务规则。
第四条数据库存数量为0。
请注意在插入数据后,检查［卷烟库存表］中的数据就是否库存金额=库存数量*库存单价。
INSERTINTO卷烟库存表（卷烟品牌，库存数量，库存单价，库存金额）
values（红塔山新势力',100,12,1200）
INSERTINTO卷烟库存表（卷烟品牌，库存数量，库存单价，库存金额）
values（红塔山人为峰',100,22,null）
INSERTINTO卷烟库存表（卷烟品牌，库存数量，库存单价，库存金额）
values（云南映像',100,60,500）
INSERTINTO卷烟库存表（卷烟品牌，库存数量，库存单价，库存金额）
□insertinto誉烟库存表•卷姻品牌r库存魏量『库仔单价，库存金额;
values（玉溪',0,30,0）
-p且Lue3（，红塔山新势力
IITSERTINTO卷烟库存表I卷烟品牌土库存数量f库存单价》库存金额Ivaluefl（'红塔山人为峰\100,22,7.^11）
valueB■云南映像'100,SO,5DC
工砧皿工1HTO＜烟库存表（卷烟品牌「库存数量『库存单价，库存金頼」values（'玉，奚'”0rdCL0
AINSERTINTO卷烟厘存表（卷烟品牌卢库存数量*库存单价”库存金訓；
rrr
由消息
丄行受覺响）
L彳亍受影响）
行受覺响）
1行受影响］
行登影响）
ri行受影响］
彳亍受覺响）
u行受影响）
J消息
卷咽品牌
库存数重
库存单价
库存
1
iSL埸山人拘幡
T100
22.00
2200図
2
红増山新牺力
100
1ZM
1200.M
_3_
玉濯
0
乳皿
Q.W
4
云南腆嫁
100
S0.00
€00000
11、创建触发器［T_INSERT卷烟销售表］,实现每当卷烟库存表发生
INSERT动作，则引发该触发器。
触发器功能：
实现业务规则：
如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。
否则则自动减少
卷烟库存表中对应品牌卷烟的库存数量与库存金额。
createtriggerT_INSERT_卷烟销售表
on卷烟销售表
forinsert
as
begintransaction
ifnotexists（select卷烟品牌
from卷烟库存表
where卷烟品牌in（select卷烟品牌
frominserted卷烟库存表））
begin
raiserror（'该卷烟不存在于库存，不能销售！
’，16,1）;
rollback
return
end
ifexists（select库存数量
from卷烟库存表
where卷烟品牌in（select卷烟品牌
frominserted卷烟库存表）
and库存数量＜=0）
begin
raiserror（'该卷烟库存小于等于，不能销售！
’，16,1）;
rollback
return
end
update卷烟库存表
set库存金额=库存数量*库存单价
where卷烟品牌in（select卷烟品牌frominserted卷烟库存表）
declare@卷烟品牌VARCHAR（40）
卷烟销售表）
卷烟销售表）
set@卷烟品牌=（select卷烟品牌frominserted
declare@销售数量INT
set@销售数量=（select销售数量frominserted
update卷烟库存表
set库存数量=库存数量-@销售数量，
库存金额=（库存数量-@销售数量）*库存单价where卷烟品牌=@卷烟品牌
committransaction
go
命令已咸功完成。
三）T-SQL编程
1、从学生_课程数据库中查询所有学生选课成绩情况：
姓名、课程名、成绩。
要求:
将学生的百分制转换为5级评分制，成绩大于等于90显示为“优秀”，成绩在80-89分显示为“良好”，“70-79”分显示为“中等”，成绩在60-69显示为“及格”，60以下显示为“不及格”，没成绩的显示为“未考”。
并且输出记录按下列要求排序：
先按学号升序，再按课程号升序，最后按成绩降序。
CREATEPROCEDURE选课成绩情况
AS
BEGIN
selectS、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
1丰毛1
未考
end
fromSC,S,C
whereSC、eno=C、enoandSC、sno=S、snoorderbySC、gradedese
END
GO
ECREATEPROCEDURE选课成绩情况丄
AS
BEGIN
selectS.snorSrC.cnc谏程号fC・uumgje课程名F成绩r
余好尊fe
优良中及
case
Vfnenjraae>=3Qthen
vhenSC.grade>=S0then
whenS匚+grade>=T0then
whenS匚.gr且日轻thenwhenSC.CTtade^-Othen"不及格'
■1«"未考，
end
fromSCf3fC
wiiere:
SG・cno=C・匸口口已口d.SC・sno=S■日tid
-EHD
GO
口rderbyS・snoasc
rrr
口结果;.J消寻
学弓
姓普
课程母
课程名
1
95001
1
2
95001
2
I
wool
3
信息系毓
4
95002
#lft
2
j
S5002
3
倍息购
&
9W
5
埶据帥
7
55W5
王敏
t
揃乍啟
a
^003
王敏
1
数据応
a
96004
弓边
2
10
95004
4
二堵臭IdiML
II
!
锦
1
IX霁
t
壬酿
1
aus
不爾
i
2
不脚a
4
2
2
&
3
ftt?
1
爭IB
J1
ftfi?
4
申專
»
壬萌
d
10
5
flmtt
SW
W■
廉程唱
tea
1
|»0D1
|科
1
nw
2
35W1
1
3
S50D1
升级会员 