数据库存储过程触发器和函数实验报告.docx

数据库存储过程触发器和函数实验报告.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