实验6 数据库实验存储过程和触发器.docx
《实验6 数据库实验存储过程和触发器.docx》由会员分享，可在线阅读，更多相关《实验6 数据库实验存储过程和触发器.docx（21页珍藏版）》请在冰豆网上搜索。
实验6数据库实验存储过程和触发器
实验6存储过程和触发器
一、实验目的
1、加深和巩固对存储过程和触发器概念的理解。
2、掌握触发器的简单应用。
3、掌握存储过程的简单应用。
二、实验内容
一）存储过程：
1.创建一存储过程，求l+2+3+…+n，并打印结果。
CREATEPROCEDUREaddresult
AS
DECLARE@nint=10,/*最后一个数*/
@iint=0,
@resultint=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的结果。
EXECaddresult
3.修改上述存储过程为addresult1,使得@n为输入参数，其具体值由用户调用此存储过程时指定。
CREATEPROCEDUREaddresult1
@nint=10/*最后一个数*/
AS
DECLARE@iint=0,
@resultint=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的结果。
EXECaddresult1100
5．修改上述存储过程为addresult2，将@n参数设定默认值为10，并改设@sum为输出参数，让主程序能够接收计算结果。
CREATEPROCEDUREaddresult2
@nint=10,/*最后一个数*/
@sumintout/*结果*/
AS
DECLARE@iint=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@sint
set@s=0
EXECaddresult210,@sum=@sout
PRINT'1+2+3+...+n的结果是:
'
PRINT@s
7．创建一存储过程Proc_Student，用于显示学号为“0102”的学生基本信息（包括学号、姓名、性别和系）。
CREATEPROCEDUREProc_Student
AS
BEGIN
select*
fromS
where=0102
END
GO
EXECProc_Student
8．创建一存储过程Stu_grade，通过读取某门课的编号，求出不及格的学生的学号。
CREATEPROCEDUREStu_grade
@nchar（10）
AS
BEGIN
selectsno
fromSC
wherecno=@nandgrade<60
END
GO
9．调用上面的存储过程Stu_grade，求出课程编号为“0101”的不及格的学生。
EXECStu_grade0101
10．创建一存储过程avgGrade，通过读取学生的学号，以参数形式返回该学生的平均分。
CREATEPROCEDUREavgGrade
@nchar（10）
AS
BEGIN
selectAVG（grade）平均分
fromSC
wheresno=@n
groupbysno
END
GO
11．调用上面的存储过程avgGrade，求出学号为“990102014”的平均分。
EXECavgGrade4
12．删除上述存储过程avgGrade。
dropprocedureavgGrade
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
selecttitle_id,title,price,pubdate
fromtitles
wheretitle=@tandprice<=@p2andprice>=@p1
END
GO
14．调用上面的存储过程search，求出书名与computer有关，而且价格小于$20大于$10的书。
EXECsearch"computer",10,20
二）触发器：
1、在学生成绩库中创建触发器trigger1，实现如下功能：
当在学生成绩表（xscj）中插入一条学生选课信息后，自动实现更新该学生在学生情况表（xsqk）中的总学分信息。
分析：
根据题意，也即要求在学生成绩表中插入一条记录时，自动更新学生情况表中的相应记录信息。
可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息。
其实，只要在该生原总学分基础上加上新选课程的学分就可以了。
createtriggertrigger1
onxscj
afterinsert
as
declare@creditint;
select@credit=creditfrominsertedxscj;
updatexsqksetallcredit=allcredit+@credit;
go
2、创建触发器trigger2，实现当修改学生课程表（xskc）中的数据时，显示提示信息“学生课程表被修改了”。
createtriggertrigger2
onxskc
afterupdate
as
print'学生课程表被修改了';
go
3、创建触发器trigger3，实现当删除学生课程表中某门课程的记录时，对应学生成绩表中所有有关此课程的记录均删除。
createtriggertrigger3
onxskc
afterdelete
as
declare@cnamechar（10）;
select@cname=cnamefromdeletedxsks;
deletexscjwherecname=@cname;
go
4、创建触发器trigger4，实现当修改学生课程表（xskc）中的某门课的课程号时，对应学生成绩表（xscj）中的课程号也作相应修改。
createtriggertrigger4
onxskc
afterupdate
as
declare@cnochar（10）;
declare@cnamechar（10）;
select@cname=,@cno=fromupdatedxsks;
updatexscjset=@cnowhere=@cname;
go
5、创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，查看该学生的信息是否存在在学生信息表中，如果不存在，则把该学生的基本信息加入到学生信息表中。
createtriggertrigger5
onxscj
afterinsert
as
declare@sname1char（10）;
declare@sname2char（10）=NULL;
select@sname1=fromupdatedxscj;
select@sname2=fromxsqkwhere=@sname1;
if@sname2=NULL
insertintoxsqkvalues（NULL,@sname1）;
go
6、在学生成绩库中创建触发器trigger6，实现如下功能：
当在学生成绩表（xscj）中插入一条学生选课信息后，查看该学生的信息是否存在在学生信息表中，如果不存在，则给出“该记录不能被插入！
”的错误提示，并撤销插入操作；同样，如果课程信息在课程信息表中不存在，给出“该记录不能被插入！
”的错误提示，并撤销插入操作。
createtriggertrigger6
onxscj
forinsert
as
begin
ifnotexists
（selectfromxsqkwherein（selectfrominsertedxscj）
）
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
8、分别用触发器和存储过程实现对学生情况表（xsqk）和学生成绩表（xscj）表的级联删除。
createtriggertrigger8
onxsqk
afterdelete
as
deletefromxscj
wherein
（selectfromdeletedxsqk）
go
CREATEPROCEDUREdel_qk_cj
@snamechar（10）=NULL
AS
BEGIN
deletefromxscj
where=@sname;
deletefromxsqk
where=@sname;
END
GO
9、创建触发器8，要求实现：
当向xscj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25，则给出“该学生已修满，不需要再选修！
”的提示信息；否则，自动更新该学生的总学分。
createtriggertrigger88
onxscj
forinsert
as
begin
declare@credit1int;
declare@credit2int;
declare@snamechar（10）;
select@credit1=,@sname=frominsertedxscj;
select@credit2=fromxsqkwhere=@sname;
if（@credit1+@credit2）>=25
begin
raiserror（'该学生已修满，不需要再选修！
',16,1）
rollback
return
end
else
insertintoxscjvalues（@credit1,NULL,NULL,@sname）;
end
go
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卷烟库存表（卷烟品牌,库存数量,库存单价,库存金额）
values（'玉溪',0,30,0）
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
select学号,姓名,课程号,课程名,成绩=
case
when>=90then'优秀'
when>=80then'良好'
when>=70then'中等'
when>=60then'及格'
when>=0then'不及格'
else'未考'
end
fromSC,S,C
where=and=
orderbydesc
END
GO
2、利用学生_课程数据库中的SC表，编程实现：
如果所有学生所有课程的平均成绩高于80分，使用while循环就将每门成绩减5分，然后查找所有学生所有课程中的最高分，如果最高分大于或等于85分，while循环重新启动并再次将每门成绩减5分并继续查找所有学生所有课程中的最高分，一直循环到最高分低于85分循环停止，然后退出。
CREATEPROCEDURE选课成绩情况
AS
BEGIN
while（selectmax（grade）fromSC）>=85
begin
updateSC
setgrade=grade-5
selectavg（grade）fromSC
if（selectmax（grade）fromSC）<85
break;
else
continue
end
selectavg（grade）平均成绩fromSC
END
GO
EXEC选课成绩情况
升级会员 