存储过程触发器和用户自定义函数实验概要文档格式.docx
《存储过程触发器和用户自定义函数实验概要文档格式.docx》由会员分享，可在线阅读，更多相关《存储过程触发器和用户自定义函数实验概要文档格式.docx（9页珍藏版）》请在冰豆网上搜索。
（2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。
CREATEProcAVG_SCORE@cnoCHAR（20）
SELECT@cnoas课程号,ameas课程名,STR（AVG（score）,5,2）as平均成绩
FROMstudy,course
WHEREo=oando=@cno
（3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。
CREATEProcSCORE_CHANGE
SELECTameas课程名,study.snoas学号,oas课程号,study.scoreas成绩,
case
whenscore>
=90andscore<
=100then'
A'
90then'
B'
80then'
C'
70then'
D'
whenscore<
60then'
E'
endas'
等级'
fromstudy,course
whereo=o
（4）创建一个存储过程，该存储过程有一个参数用来接收学生姓名，该存储过程查询该学生的学号以及选修课程的门数。
CREATEProcSTUDENT_STUDY@namevarchar（20）
select@nameas姓名,study.snoas学号,count（cno）as选修门数
fromstudy,student
wherestudy.sno=student.snoandsname=@name
groupbystudy.sno
（5）创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。
CREATEProcSTU_COR_SCORE@snovarchar（20）,@cnochar（20）,@wordsmallintoutput
select@word=score
fromstudy
wheresno=@snoandcno=@cno
（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
（2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。
CREATETRIGGERDEL_STUDYONstudy
insteadofDELETE
begin
declare@numint,@snochar（5）,@cnochar（4）
select@num=COUNT（*）fromdeleted
if@num=1
select@sno=sno,@cno=cnofromdeleted
deletefromstudywhere@sno=study.snoand@cno=o
end
elseprint'
一次不能删除多条记录'
（3）为course表创建一个INSERT触发器，要求插入的课程记录中任课教师不能为空。
CREATETRIGGERINSERT_CORONcourse
insteadofinsert
declare@cnochar（4）,@cnamechar（20）,@teacherchar（8）
select@cno=cno,@cname=cname,@teacher=teacherfrominsertedif（@teacherisnull）
print'
注意：
任课教师不能为空！
'
else
insertcoursevalues（@cno,@cname,@teacher）
（1）创建一个返回标量值的用户定义函数RectangleArea：
输入矩形的长和宽就能计算矩形的面积。
CREATEfunctionRectangleArea（@aint,@bint）
returnsint
return@a*@b
（2）创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。
该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。
调用这个函数，生成相应的报表并给用户浏览。
CREATEfunctionSTUDENT_TABLE（）returnstable
return（
selecto课程号,
ame课程名,
COUNT（study.sno）选修人数,
max（study.score）最高分,min（study.score）最低分,AVG（study.score）平均分
groupbyo,ame
）
实验数据库说明
教学活动数据库包括student、course和study三个基本表，三个基本表的结构说明和数据如下：
（1）学生表（student）
学生表的结构
列名
数据类型
长度
是否允许为空值
字段说明
sno
char
5
NO
学号
sname
8
姓名
age
smallint
年龄
sex
nchar
1
性别
说明：
sno为主键，age的范围为15~35之间，sex只能为“男”或“女”。
CREATETABLEstudent（
agesmallintcheck（age>
=15andage<
=35）,
sexvarchar（20）check（sexin（'
男'
'
女'
））--性别
snovarchar（20）PRIMARYKEYnotnull,
snamevarchar（20）notnull,--姓名
insertintostudentvalues（'
98601'
'
李强'
20,'
98602'
'
刘丽'
21,'
）;
98603'
张兵'
20,'
98604'
陈志坚'
22,'
98605'
王颖'
21,'
学生表的记录
98601
李强
20
男
98602
刘丽
21
女
98603
张兵
98604
陈志坚
22
98605
王颖
（2）课程表（course）
课程表的结构
说明
cno
4
课程号
cname
课程名
teacher
任课教师
cno为主键。
createtablecourse
（
cnochar（20）notnullprimarykey,--课程编号
cnamechar（20）notnull,--课程名称
teacherchar（20）
insertintocourse
values（
C601'
周振兴'
C602'
数据结构'
刘建平'
C603'
操作系统'
C604'
编译原理'
王志伟'
课程表的记录
C601
高等数学
周振兴
C602
数据结构
刘建平
C603
操作系统
C604
编译原理
王志伟
（3）选课表（study）
createtablestudy
snovarchar（20）notnull,--学生学号
cnochar（20）,--上课编号
scoresmallint,
primarykey（sno,cno）,
foreignkey（sno）referencesstudent（sno）,
foreignkey（cno）referencescourse（cno）
insertintostudy
values
（'
90）
90）
85）
87）
75）
70）
56）
insertintostudyvalues（
insertintostudy
95）
80）
选课表的结构
score
成绩
sno和cno为主键，sno为外键（参照student表的sno），cno为外键（参照course表的cno），score的范围为0~100之间。
选课表的记录
90
85
87
75
70
56
95
80
升级会员 