数据库存储过程触发器和函数实验报告.docx
《数据库存储过程触发器和函数实验报告.docx》由会员分享，可在线阅读，更多相关《数据库存储过程触发器和函数实验报告.docx（14页珍藏版）》请在冰豆网上搜索。
数据库存储过程触发器和函数实验报告
存储过程、触发器和用户自定义函数实验
兰州大学数据库实验报告
实验内容一
练习教材中存储过程、触发器和用户自定义函数的例子。
教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。
实验内容二
针对附件1中的教学活动数据库，完成下面的实验内容。
1、存储过程
（1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。
CREATEProcMATH_NUM@MATHCHAR（20）='高等数学'
AS
SELECT@MATHascanme,count（casewhenscore>=90then1end）as[90以上],
count（casewhenscore>=80andscore<90then1end）as[80-90],
count（casewhenscore>=70andscore<80then1end）as[70-80],
count（casewhenscore>=60andscore<70then1end）as[60-70],
count（casewhenscore<60then1end）as[60以下]
FROMstudy,course
WHEREo=oandame=@MATH
GROUPBYame
运行结果：
（2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。
CREATEProcAVG_SCORE@cnoCHAR（5）
AS
SELECT@cnoas课程号,ameas课程名,STR（AVG（score）,5,2）as平均成绩
FROMstudy,course
WHEREo=oando=@cno
GROUPBYame
运行结果：
（3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。
CREATEProcSCORE_CHANGE
AS
SELECTameas课程名,study.snoas学号,oas课程号,study.scoreas成绩,
case
whenscore>=90andscore<=100then'A'
whenscore>=80andscore<90then'B'
whenscore>=70andscore<80then'C'
whenscore>=60andscore<70then'D'
whenscore<60then'E'
endas'等级'
fromstudy,course
whereo=o
运行结果：
（4）创建一个存储过程，该存储过程有一个参数用来接收学生姓名，该存储过程查询该学生的学号以及选修课程的门数。
CREATEProcSTUDENT_STUDY@namechar（8）
AS
select@nameas姓名,study.snoas学号,count（cno）as选修门数
fromstudy,student
wherestudy.sno=student.snoandsname=@name
groupbystudy.sno
运行结果：
（5）创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。
CREATEProcSTU_COR_SCORE@snochar（5）,@cnochar（4）,@wordsmallintoutput
AS
select@word=score
fromstudy
wheresno=@snoandcno=@cno
运行结果：
2、触发器
（1）为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。
CREATETRIGGERUPDATE_SCOREONstudy
insteadofupdate
as
declare@sno2char（5）,@cno2char（4）,@score1smallint,@score2smallint
select@sno2=sno,@cno2=cno,@score2=score
frominserted
select@score1=score
fromdeleted
if（@score2>=@score1）
updatestudysetscore=@score2
whereo=@cno2andstudy.sno=@sno2
go
运行结果：
按要求sno=98604cno=C604score=85改成89不能改成85了
（2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。
CREATETRIGGERDEL_STUDYONstudy
insteadofDELETE
AS
begin
declare@numint,@snochar（5）,@cnochar（4）
select@num=COUNT（*）fromdeleted
if@num=1
begin
select@sno=sno,@cno=cnofromdeleted
deletefromstudywhere@sno=study.snoand@cno=o
end
elseprint'一次不能删除多条记录'
end
运行结果：
（3）为course表创建一个INSERT触发器，要求插入的课程记录中任课教师不能为空。
CREATETRIGGERINSERT_CORONcourse
insteadofinsert
AS
declare@cnochar（4）,@cnamechar（20）,@teacherchar（8）
select@cno=cno,@cname=cname,@teacher=teacherfrominserted
if（@teacherisnull）
print'注意：
任课教师不能为空！
'
else
insertcoursevalues（@cno,@cname,@teacher）
运行结果：
3、用户自定义函数
（1）创建一个返回标量值的用户定义函数RectangleArea：
输入矩形的长和宽就能计算矩形的面积。
CREATEfunctionRectangleArea（@aint,@bint）returnsint
AS
begin
return@a*@b
end
运行结果：
（2）创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。
该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。
调用这个函数，生成相应的报表并给用户浏览。
CREATEfunctionSTUDENT_TABLE（）returnstable
AS
return（
selectstudent_o课程号,ame课程名,COUNT（student_course.sno）选修人数,
max（student_course.score）最高分,min（student_course.score）最低分,AVG（student_course.score）平均分
fromstudent_course,course
wherestudent_o=o
groupbystudent_o,ame
）
运行结果：
实验数据库说明
教学活动数据库包括student、course和study三个基本表，三个基本表的结构说明和数据如下：
（1）学生表（student）
学生表的结构
列名
数据类型
长度
是否允许为空值
字段说明
sno
char
5
NO
学号
sname
char
8
NO
姓名
age
smallint
年龄
sex
nchar
1
性别
说明：
sno为主键，age的范围为15~35之间，sex只能为“男”或“女”。
学生表的记录
sno
sname
age
sex
98601
李强
20
男
98602
刘丽
21
女
98603
张兵
20
男
98604
陈志坚
22
男
98605
王颖
21
女
（2）课程表（course）
课程表的结构
列名
数据类型
长度
是否允许为空值
说明
cno
char
4
NO
课程号
cname
char
20
NO
课程名
teacher
char
8
任课教师
说明：
cno为主键。
课程表的记录
cno
cname
teacher
C601
高等数学
周振兴
C602
数据结构
刘建平
C603
操作系统
刘建平
C604
编译原理
王志伟
（3）选课表（study）
选课表的结构
列名
数据类型
长度
是否允许为空值
说明
sno
char
5
NO
学号
cno
char
4
NO
课程号
score
smallint
成绩
说明：
sno和cno为主键，sno为外键（参照student表的sno），cno为外键（参照course表的cno），score的范围为0~100之间。
选课表的记录
sno
cno
score
98601
C601
90
98601
C602
90
98601
C603
85
98601
C604
87
98602
C601
90
98603
C601
75
98603
C602
70
98603
C604
56
98604
C601
90
98604
C604
85
98605
C601
95
98605
C603
80
升级会员 