实验6 数据库实验存储过程和触发器.docx
- 文档编号:30334748
- 上传时间:2023-08-13
- 格式:DOCX
- 页数:20
- 大小:176.14KB
实验6 数据库实验存储过程和触发器.docx
《实验6 数据库实验存储过程和触发器.docx》由会员分享,可在线阅读,更多相关《实验6 数据库实验存储过程和触发器.docx(20页珍藏版)》请在冰豆网上搜索。
实验6数据库实验存储过程和触发器
实验6存储过程与触发器
一、实验目得
1、加深与巩固对存储过程与触发器概念得理解。
2、掌握触发器得简单应用。
3、掌握存储过程得简单应用。
二、实验内容
一)存储过程:
1、创建一存储过程,求l+2+3+…+n,并打印结果。
CREATE PROCEDURE addresult
AS
DECLAREnint=10, /*最后一个数*/
iint=0,
resultint=0 /*结果*/
BEGIN
WHILE(i<=n)
BEGIN
SETresult=result+i
SETi=i+1
END
PRINT'1+2+3+、、、+n得结果就是:
’
PRINTresult
RETURN(result)
END
GO
2.调用上面得addresult存储过程,打印l十2+3+…+10得结果。
EXECaddresult
3、修改上述存储过程为addresult1,使得n为输入参数,其具体值由用户调用此存储过程时指定.
CREATE PROCEDUREaddresult1
nint=10/*最后一个数*/
AS
DECLAREiint=0,
resultint=0/*结果*/
BEGIN
WHILE(i<=n)
BEGIN
SET result=result+i
SET i=i+1
END
PRINT’1+2+3+、、、+n得结果就是:
’
PRINTresult
RETURN(result)
END
GO
4、调用上面修改后得addresult1存储过程,打印l+2+3+…+100得结果。
EXECaddresult1100
5.修改上述存储过程为addresult2,将n参数设定默认值为10,并改设sum为输出参数,让主程序能够接收计算结果。
CREATEPROCEDUREaddresult2
nint=10,/*最后一个数*/
sumintout /*结果*/
AS
DECLAREiint=0
BEGIN
set sum=0
WHILE(i<=n)
BEGIN
SETsum=sum+i
SETi=i+1
END
END
GO
6。
调用上面修改后得addresult2存储过程,设置变量s接收计算l+2+3+…+10得结果。
DECLAREs int
sets=0
EXECaddresult210,sum=sout
PRINT'1+2+3+、、、+n得结果就是:
'
PRINT s
7.创建一存储过程Proc_Student,用于显示学号为“0102"得学生基本信息(包括学号、姓名、性别与系)。
CREATEPROCEDURE Proc_Student
AS
BEGIN
select *
from S
where S、sno=0102
END
GO
EXECProc_Student
8。
创建一存储过程Stu_grade,通过读取某门课得编号,求出不及格得学生得学号。
CREATEPROCEDUREStu_grade
nchar(10)
AS
BEGIN
select sno
fromSC
wherecno=n andgrade<60
END
GO
9。
调用上面得存储过程Stu_grade,求出课程编号为“0101”得不及格得学生.
EXECStu_grade0101
10.创建一存储过程avgGrade,通过读取学生得学号,以参数形式返回该学生得平均分。
CREATEPROCEDUREavgGrade
nchar(10)
AS
BEGIN
selectAVG(grade) 平均分
fromSC
wheresno=n
group bysno
END
GO
11.调用上面得存储过程avgGrade,求出学号为“990102014"得平均分.
EXEC avgGrade990102014
12.删除上述存储过程avgGrade。
drop procedureavgGrade
13、创建存储过程search,该存储过程有三个参数,分别为t、p1,p2,根据这些参数,找出书名与t有关,价格在p1与p2(p2〉=p1)之间得书得编号,书名,价格,出舨日期。
如果用户调用时没有指定t参数得值。
则表示可为任意值,如用户没有指定p2,则书本价格没有上限。
用到得关系为:
titles(title_id,title,price,pubdate)。
CREATE PROCEDURE search
t char(10)=”%",
p1char(10),
p2char(10)=NULL
AS
BEGIN
selecttitle_id,title,price,pubdate
fromtitles
wheretitle=tandprice〈=p2 andprice〉=p1
END
GO
14。
调用上面得存储过程search,求出书名与puter有关,而且价格小于$20大于$10得书。
EXECsearch"puter”,10,20
二)触发器:
1、在学生成绩库中创建触发器trigger1,实现如下功能:
当在学生成绩表(xscj)中插入一条学生选课信息后,自动实现更新该学生在学生情况表(xsqk)中得总学分信息.
分析:
根据题意,也即要求在学生成绩表中插入一条记录时,自动更新学生情况表中得相应记录信息。
可以通过在学生成绩表中定义INSERT类型得触发器,触发器中语句要完成得功能就是更新学生情况表中得相应学生得总学分信息.其实,只要在该生原总学分基础上加上新选课程得学分就可以了.
createtriggertrigger1
onxscj
after insert
as
declarecreditint;
select credit=credit frominsertedxscj;
updatexsqksetallcredit=allcredit+credit;
go
2、创建触发器trigger2,实现当修改学生课程表(xskc)中得数据时,显示提示信息“学生课程表被修改了”。
createtrigger trigger2
on xskc
after update
as
print'学生课程表被修改了’;
go
3、创建触发器trigger3,实现当删除学生课程表中某门课程得记录时,对应学生成绩表中所有有关此课程得记录均删除。
createtriggertrigger3
on xskc
afterdelete
as
declarecname char(10);
select cname=cnamefrom deletedxsks;
deletexscjwhere cname=cname;
go
4、创建触发器trigger4,实现当修改学生课程表(xskc)中得某门课得课程号时,对应学生成绩表(xscj)中得课程号也作相应修改。
create triggertrigger4
on xskc
afterupdate
as
declarecnochar(10);
declare cnamechar(10);
selectcname=xskcame,cno=xskcofrom updated xsks;
updatexscjset xscjo=cnowherexscjame=cname;
go
5、创建触发器trigger5,实现当向学生成绩表(xscj)中插入一条选课记录时,查瞧该学生得信息就是否存在在学生信息表中,如果不存在,则把该学生得基本信息加入到学生信息表中.
createtriggertrigger5
onxscj
afterinsert
as
declare sname1 char(10);
declaresname2char(10)=NULL;
selectsname1=xscj、sname from updatedxscj;
selectsname2=xsqk、snamefromxsqk where xsqk、sname=sname1;
if sname2=NULL
insertinto xsqkvalues(NULL,sname1);
go
6、在学生成绩库中创建触发器trigger6,实现如下功能:
当在学生成绩表(xscj)中插入一条学生选课信息后,查瞧该学生得信息就是否存在在学生信息表中,如果不存在,则给出“该记录不能被插入!
”得错误提示,并撤销插入操作;同样,如果课程信息在课程信息表中不存在,给出“该记录不能被插入!
”得错误提示,并撤销插入操作。
create triggertrigger6
onxscj
forinsert
as
begin
ifnotexists
(selectxsqk、snamefromxsqkwherexsqk、snamein(select xscj、snamefrominsertedxscj)
)
begin
raiserror('该记录不能被插入!
’,16,1)
rollback
return
end
end
go
7、创建触发器trigger7,强制实现业务规则:
当向学生成绩表中插入一条记录时,自动修改学生情况表中该学生得总学分,要求总学分为该学生所有已修课程得学分总与。
createtriggertrigger7
onxscj
for insert
as
begintransaction
declarecreditint;
selectcredit=creditfrominsertedxscj;
updatexsqk setallcredit=allcredit+credit;
mit transaction
go
8、分别用触发器与存储过程实现对学生情况表(xsqk)与学生成绩表(xscj)表得级联删除.
createtriggertrigger8
onxsqk
after delete
as
deletefromxscj
wherexscj、snamein
(selectxsqk、snamefromdeleted xsqk)
go
CREATEPROCEDURE del_qk_cj
sname char(10)=NULL
AS
BEGIN
deletefromxscj
wherexscj、sname=sname;
delete from xsqk
wherexsqk、sname=sname;
END
GO
9、创建触发器8,要求实现:
当向xscj表插入一条记录时,判断该学生得总学分,如果总学分大于等于25,则给出“该学生已修满,不需要再选修!
”得提示信息;否则,自动更新该学生得总学分。
createtrigger trigger88
onxscj
for insert
as
begin
declarecredit1 int;
declarecredit2 int;
declaresnamechar(10);
selectcredit1=xscj、credit,sname=xscj、sname frominsertedxscj;
selectcredit2=xsqk、allcreditfromxsqk wherexsqk、sname=sname;
if(credit1+credit2)>=25
begin
raiserror('该学生已修满,不需要再选修!
',16,1)
rollback
return
end
else
insertintoxscjvalues(credit1,NULL,NULL,sname);
end
go
10、在数据库中用以下语句创建两张表:
CREATETABLE 卷烟销售表
(
卷烟品牌VARCHAR(40)PRIMARY KEYNOTNULL,
购货商VARCHAR(40)NULL,
销售数量INTNULL,
销售单价 MONEYNULL,
销售金额MONEYNULL
)
GO
--业务规则:
库存金额=库存数量 *库存单价业务规则。
CREATE TABLE卷烟库存表
(
卷烟品牌VARCHAR(40)PRIMARYKEY NOTNULL,
库存数量 INTNULL,
库存单价 MONEYNULL,
库存金额MONEYNULL
)
GO
创建触发器[T_INSERT_卷烟库存表],实现每当[卷烟库存表]发生INSERT 动作,则引发该触发器。
触发器功能:
强制执行业务规则,保证插入得数据中,库存金额 = 库存数量 *库存单价。
针对[卷烟库存表],插入测试数据:
注意,第一条数据(红塔山新势力)中得数据符合业务规则,第二条数据(红塔山人为峰)中,[库存金额]空,不符合业务规则,第三条数据(云南映像)中,[库存金额]不等于[库存数量]乘以[库存单价],不符合业务规则.第四条数据库存数量为0。
请注意在插入数据后,检查[卷烟库存表]中得数据就是否库存金额=库存数量*库存单价.
INSERTINTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
values('红塔山新势力’,100,12,1200)
INSERT INTO卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
values(’红塔山人为峰',100,22,null)
INSERTINTO卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
values('云南映像',100,60,500)
INSERTINTO卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
values( '玉溪’,0,30,0)
11、创建触发器[T_INSERT_卷烟销售表],实现每当卷烟库存表发生INSERT 动作,则引发该触发器。
触发器功能:
实现业务规则:
如果销售得卷烟品牌不存在库存或者库存为零,则返回错误。
否则则自动减少卷烟库存表中对应品牌卷烟得库存数量与库存金额。
createtriggerT_INSERT_卷烟销售表
on 卷烟销售表
for insert
as
begintransaction
ifnot exists(select卷烟品牌
from卷烟库存表
where 卷烟品牌in(select卷烟品牌
from inserted卷烟库存表))
begin
raiserror(’该卷烟不存在于库存,不能销售!
',16,1);
rollback
return
end
ifexists(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卷烟品牌frominserted卷烟销售表)
declare 销售数量INT
set销售数量=(select 销售数量frominserted卷烟销售表)
update卷烟库存表
set库存数量=库存数量—销售数量,
库存金额=(库存数量-销售数量)*库存单价
where 卷烟品牌=卷烟品牌
mittransaction
go
三)T-SQL编程
1、从学生_课程数据库中查询所有学生选课成绩情况:
姓名、课程名、成绩。
要求:
将学生得百分制转换为5级评分制,成绩大于等于90显示为“优秀",成绩在80-89分显示为“良好”,“70—79”分显示为“中等”,成绩在60-69显示为“及格",60以下显示为“不及格”,没成绩得显示为“未考”。
并且输出记录按下列要求排序:
先按学号升序,再按课程号升序,最后按成绩降序。
CREATEPROCEDURE 选课成绩情况
AS
BEGIN
select S、sno学号,S、sname姓名,Co课程号,Came课程名,成绩=
case
whenSC、grade〉=90 then’优秀'
whenSC、grade>=80 then'良好’
when SC、grade>=70then ’中等'
whenSC、grade>=60then'及格'
whenSC、grade>=0then'不及格'
else ’未考'
end
from SC,S,C
where SCo=Co andSC、sno=S、sno
order bySC、gradedesc
END
GO
2、利用学生_课程数据库中得SC表,编程实现:
如果所有学生所有课程得平均成绩高于80分,使用while循环就将每门成绩减5分,然后查找所有学生所有课程中得最高分,如果最高分大于或等于85分,while循环重新启动并再次将每门成绩减5分并继续查找所有学生所有课程中得最高分,一直循环到最高分低于85分循环停止,然后退出。
CREATEPROCEDURE选课成绩情况
AS
BEGIN
while(selectmax(grade)fromSC)〉=85
begin
updateSC
set grade=grade-5
selectavg(grade) from SC
if(selectmax(grade)fromSC)<85
break;
else
continue
end
selectavg(grade)平均成绩from SC
END
GO
EXEC选课成绩情况
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验6 数据库实验存储过程和触发器 实验 数据库 存储 过程 触发器