实验6数据库实验存储过程和触发器Word格式文档下载.docx
《实验6数据库实验存储过程和触发器Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《实验6数据库实验存储过程和触发器Word格式文档下载.docx（22页珍藏版）》请在冰豆网上搜索。
SET@sum=@sum+@i
6.调用上面修改后的addresult2存储过程，设置变量@s接收计算
1+2+3+…+10的结果。
DECLARE@sint
set@s=0
EXECaddresult210,@sum=@sout
PRINT@s
7.创建一存储过程Proc_Student用于显示学号为“0102”的学生基本信息（包括学号、姓名、性别与系）。
CREATEPROCEDUREProc_Student
select*
fromS
whereS、sno=0102
EXECProcStudent
8.创建一存储过程Stu_grade通过读取某门课的编号，求出不及格的学
生的学号。
CREATEPROCEDUREStu_grade
@nchar（10）
selectsno
fromSC
whereeno=@nandgrade<
60
9•调用上面的存储过程Stu_grade求出课程编号为“0101”的不及格
的学生
EXECStu_grade0101
彳1
EXECScugrade0101
闻结杲［匕消息
sno
10.创建一存储过程avgGrade通过读取学生的学号，以参数形式返回该学生的平均分。
CREATEPROCEDUREavgGrade
selectAVG（grade）平均分
wheresno=@n
groupbysno
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
wheretitle=@tandprice<
=@p2andprice>
=@p1
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
afterdelete
declare@cnamechar（10）;
select@cname=cnamefromdeletedxsksdeletexscjwherecname=@cname;
4、创建触发器trigger4,实现当修改学生课程表（xskc）中的某门课的课程号时，对应学生成绩表（xscj）中的课程号也作相应修改。
createtriggertrigger4
declare@cnochar（10）;
declare@cnamechar（10）;
select@cname=xskc、cname,@cno=xskc、enofromupdatedxsks;
updatexscjsetxscj、eno=@cnowherexscj、cname=@cname;
5、创建触发器trigger5,实现当向学生成绩表（xscj）中插入一条选课记录时,查瞧该学生的信息就是否存在在学生信息表中，如果不存在，则
把该学生的基本信息加入到学生信息表中。
createtriggertrigger5
declare@snamelchar（10）;
declare@sname2char（10）=NULL;
select@sname1=xscj、snamefromupdatedxscj;
select@sname2=xsqk、snamefromxsqkwherexsqk、sname=@sname1;
if@sname2=NULL
insertintoxsqkvalues（NULL,@sname1）;
6、在学生成绩库中创建触发器trigger6，实现如下功能：
表（xscj）中插入一条学生选课信息后，查瞧该学生的信息就是否存在在学生信息表中，如果不存在，则给出“该记录不能被插入！
”的错误提示，并撤销插入操作；
同样，如果课程信息在课程信息表中不存在，给出
“该记录不能被插入！
”的错误提示，并撤销插入操作。
createtriggertrigger6
forinsert
begin
ifnotexists
（selectxsqk、snamefromxsqkwherexsqk、snamein（selectxscj、sname
frominsertedxscj）
raiserror（'
该记录不能被插入！
’，16,1）
rollback
return
end
7、创建触发器trigger7,强制实现业务规则：
当向学生成绩表中插入一条记录时，自动修改学生情况表中该学生的总学分，要求总学分为该学生所有已修课程的学分总与。
createtriggertrigger7
begintransaction
committransaction
&
分别用触发器与存储过程实现对学生情况表（xsqk与学生成绩表
（xscj表的级联删除。
createtriggertrigger8
onxsqk
deletefromxscj
wherexscj、snamein
（selectxsqk、snamefromdeletedxsqk）
1-耳uqK.nnuirffrcn1.xsqk：
CREATEPROCEDUREdel_qk_cj@snamechar（10）=NULLAS
delete
from
xscj
where
xscj、
sname=@sname;
xsqk
xsqk、
ECREATEPROCEDUREdel_qk_cjSsnair.echar（10=1RJLL
（iBEGIN
E]deletefronxacj
whexexscj.且namg=世且口師十：
r\deletefrorx^qk
炖b电工皀xsqk•朋克昭=临吕席址已：
Lend
-命晶成功完成。
9、创建触发器8,要求实现：
当向xscj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25,则给出“该学生已修满，不需要再选
修！
”的提示信息；
否则，自动更新该学生的总学分
createtriggertrigger88
forinsertas
begin
declare
@credit1
int;
@credit2
@sname
char（10）;
select
=xscj、credit
@sname
=xscj、
snamefrom
insertedxscj;
=xsqk、allcredit
wherexsqk
、sname=@sname;
if（@credit1+@credit2）>
=25
该学生已修满，不需要再选修！
else
insertintoxscjvalues（@credit1,NULL,NULL,@sname）;
[■
匚
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
）
--业务规则:
库存金额=库存数量*库存单价业务规则。
CREATETABLE烟库存表
卷烟品牌VARCHAR（40）PRIMARYKEYNOTNULL,
库存数量INTNULL,
库存单价MONEYNULL,
库存金额MONEYNULL
创建触发器［T_INSERT卷烟库存表］,实现每当［卷烟库存表］发生INSERT动作，则引发该触发器。
触发器功能：
强制执行业务规则，保证插入的数据中,库存金额=库存数量*库存单价。
针对［卷烟库存表］,插入测试数据：
注意,第一条数据（红塔山新势力）中的数据符合业务规则,第二条数据（红塔山人为峰）中,［库存金额］空,不符合业务规则,第三条数据（云南映像）中,［库存金额］不等于［库存数量］乘以［库存单价］,不符合业务规则。
第四条数据库存数量为0。
请注意在插入数据后,检查［卷烟库存表］中的数据就是否库存金额=库存数量*库存单价。
INSERTINTO卷烟库存表（卷烟品牌，库存数量，库存单价，库存金额）
values（红塔山新势力'
100,12,1200）
values（红塔山人为峰'
100,22,null）
values（云南映像'
100,60,500）
□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_
玉濯
乳皿
Q.W
4
云南腆嫁
S0.00
€00000
11、创建触发器［T_INSERT卷烟销售表］,实现每当卷烟库存表发生
INSERT动作，则引发该触发器。
实现业务规则：
如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。
否则则自动减少
卷烟库存表中对应品牌卷烟的库存数量与库存金额。
createtriggerT_INSERT_卷烟销售表
on卷烟销售表
begintransaction
ifnotexists（select卷烟品牌
from卷烟库存表
where卷烟品牌in（select卷烟品牌
frominserted卷烟库存表））
该卷烟不存在于库存，不能销售！
’，16,1）;
ifexists（select库存数量
frominserted卷烟库存表）
and库存数量＜=0）
该卷烟库存小于等于，不能销售！
’，16,1）;
update卷烟库存表
set库存金额=库存数量*库存单价
where卷烟品牌in（select卷烟品牌frominserted卷烟库存表）
declare@卷烟品牌VARCHAR（40）
卷烟销售表）
set@卷烟品牌=（select卷烟品牌frominserted
declare@销售数量INT
set@销售数量=（select销售数量frominserted
set库存数量=库存数量-@销售数量，
库存金额=（库存数量-@销售数量）*库存单价where卷烟品牌=@卷烟品牌
命令已咸功完成。
三）T-SQL编程
1、从学生_课程数据库中查询所有学生选课成绩情况：
姓名、课程名、成绩。
要求:
将学生的百分制转换为5级评分制，成绩大于等于90显示为“优秀”，成绩在80-89分显示为“良好”，“70-79”分显示为“中等”，成绩在60-69显示为“及格”，60以下显示为“不及格”，没成绩的显示为“未考”。
并且输出记录按下列要求排序：
先按学号升序，再按课程号升序，最后按成绩降序。
CREATEPROCEDURE选课成绩情况
selectS、sno学号，S、sname姓名，C、cno课程号，C、cname课程名，成绩=
case
when
SC、grade
>
=90
then
优秀'
=80
良好'
=70
中等'
=60
及格'
=0
不及格
1丰毛1
未考
fromSC,S,C
whereSC、eno=C、enoandSC、sno=S、snoorderbySC、gradedese
ECREATEPROCEDURE选课成绩情况丄
selectS.snorSrC.cnc谏程号fC・uumgje课程名F成绩r
余好尊fe
优良中及
Vfnenjraae>
=3Qthen
vhenSC.grade>
=S0then
whenS匚+grade>
=T0then
whenS匚.gr且日轻thenwhenSC.CTtade^-Othen"
不及格'
■1«
"
未考，
fromSCf3fC
wiiere:
SG・cno=C・匸口口已口d.SC・sno=S■日tid
-EHD
口rderbyS・snoasc
口结果;
.J消寻
学弓
姓普
课程母
课程名
95001
I
wool
3
信息系毓
95002
#lft
j
S5002
倍息购
9W
5
埶据帥
7
55W5
王敏
t
揃乍啟
a
^003
数据応
96004
弓边
10
95004
二堵臭IdiML
II
!
锦
IX霁
壬酿
aus
不爾
i
不脚a
ftt?
爭IB
J1
ftfi?
申專
»
壬萌
d
flmtt
SW
W■
廉程唱
tea
|»
0D1
|科
nw
35W1
S50D1
升级会员 