计科09数据库技术实践第三部分.docx
《计科09数据库技术实践第三部分.docx》由会员分享，可在线阅读，更多相关《计科09数据库技术实践第三部分.docx（25页珍藏版）》请在冰豆网上搜索。
计科09数据库技术实践第三部分
实验报告
课程名称数据库技术实践
实验项目存储过程、触发器、用户自定义函数与游标
实验仪器SQLServer2008
系别____计算机科学与技术系_
专业____计算机科学与技术____
班级/学号_______________________
学生姓名_______________________
实验日期__________
成绩_______________________
指导教师_________________
[在内容说明部分请总体说明在本部分实践过程中，具体都完成了哪些内容]
一．内容说明
[请按照下面练习题的要求，完成各项内容，并说明每个题目完成的情况，是否存在问题，如何解决等]
二.实验步骤与内容
如无特别说明，以下各题均利用之前建立的Students数据库以及Student、Course和SC表实现。
1．创建满足下述要求的存储过程，并查看存储过程的执行结果。
（1）查询每个学生的修课总学分，要求列出学生学号及总学分。
createprocSumCredit
as
selectsno学号,sum（credit）总学分fromscjoincoursecono=o
groupbysno
go
execSumCredit
（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系”。
执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。
createprocStudentInformation
@deptvarchar（50）='计算机系',@snamechar（50）
as
selectsc.sno学号,sname姓名,o课程号,cname课程名,credit学分
fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cno
wheredept=@deptandSname=@sname
go
execStudentInformation'信息管理系','吴宾'
execStudentInformation@sname='李勇'
（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。
createprocMan_Num
@deptvarchar（50）,@rsintoutput
as
select@rs=COUNT（*）fromStudent
whereDept=@deptandSex='男'
go
declare@rsint
execMan_Num'信息管理系',@rsoutput
select@rsas人数
（4）查询考试平均成绩超过指定分值的学生学号和平均成绩。
createprocAvgGrade
@gradeint
as
selectsno,AVG（grade）as'平均成绩'fromSC
groupbySno
havingAVG（grade）>@grade
go
execAvgGrade60
（5）查询查询指定系的学生中，选课门数最多的学生的选课门数和平均成绩，要求系为输入参数，选课门数和平均成绩用输出参数返回。
createprocChoose_Course
@deptvarchar（50）,@rsintoutput,@avgintoutput
as
selecttop1@rs=COUNT（*）,@avg=avg（grade）fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cno
wheredept=@dept
groupbysc.Sno
go
declare@rsint,@avgint
execChoose_Course'信息管理系',@rsoutput,@avgoutput
select@rsas选课门数,@avgas平均成绩
（6）删除指定学生的修课记录，其中学号为输入参数。
createprocDel_Course
@snochar（50）
as
deletefromSC
whereSno=@sno
go
execDel_Course'0831102'
（7）修改指定课程的开课学期。
输入参数为：
课程号和修改后的开课学期,开课学期的默认值为2。
如果指定的开课学期不在1~8范围内，则不进行修改。
2．利用SSMS工具查看在students数据库中创建的全部存储过程。
Students=>可编程性=>存储过程
3．修改第1题
（1）的存储过程，使之能够查询指定系中，每个学生选课总门数、总学分和考试平均成绩。
alterprocSumCredit
@deptnvarchar（20）
as
selectCOUNT（SC.Cno）总门数,sum（credit）总学分,AVG（Grade）平均成绩fromstudentsleftjoinscons.sno=sc.snoleftjoincoursecono=o
wheredept=@dept
groupbysc.Sno
go
execSumCredit'信息管理系'
4．创建满足下述要求的触发器（前触发器、后触发器均可），并验证触发器执行情况。
（1）限制每个学期开设的课程总学分在20~30范围内。
altertriggerTR_SumGrade
oncourseafterinsert
as
declare@sint,@xint,@yint
set@s=（selectsum（Credit）fromcoursewheresemesterin（selectsemesterfrominserted））
if（20<@sand@s<30）
begin
print'课程总学分没有超出范围!
!
!
'
print@s
end
else
begin
print'课程总学分超出范围!
!
!
'
print's='
print@s
rollback
end
insertintocoursevalues（'C010','汇编语言',200,1）
（2）限制每个学生每学期选课门数不能超过5门（设只针对插入操作）。
ALTERtriggerTR_MEN
onscafterinsert
as
declare@xint
set@x=（selectcount（*）fromscjoincoursecono=o
wheresemesterin（selectsemesterfromcoursewherecnoin（selectcnofrominserted））andsc.snoin（selectsnofrominserted））
if（@x>5）
begin
select*fromscjoincoursecono=o
select*frominserted
print@x
print'选课门数超过门'
rollback
end
5．在Students数据库建立如下所示的工作表和职工表
CREATETABLE工作表（
工作号CHAR（8）PRIMARYKEY,
最低工资SMALLINT,
最高工资SMALLINT）
CREATETABLE职工表（
职工号CHAR（7）PRIMARYKEY,
职工名CHAR（10）NOTNULL,
工作号CHAR（8）REFERENCES工作表（工作号）,
基本工资SMALLINT,
浮动工资SMALLINT）
利用这两张表建立满足如下要求的触发器。
（1）限制职工的基本工资和浮动工资之和必须大于等于2000。
createtriggerTR_Salary
on职工表afterinsert,update
as
declare@xSMALLINT,@ySMALLINT,@zSMALLINT
set@x=（select基本工资from职工表where职工号in（select职工号frominserted））
set@y=（select浮动工资from职工表where职工号in（select职工号frominserted））
set@z=@x+@y
if（@z>=2000）
begin
print'操作符合要求'
end
else
begin
print@x
print@y
print@z
print'请注意，职工的基本工资和浮动工资之和小于！
！
！
！
'
select*from职工表
select*frominserted
rollback
end
insertinto工作表values（'G001',10000,1000）
insertinto职工表values（'Z001','张三','G001',1000,100）
（2）限制工作表中最高工资不能低于最低工资的1.5倍。
createtriggerTR_Salary1
on工作表afterinsert,update
as
declare@xSMALLINT,@ySMALLINT,@zfloat
set@x=（select最低工资from工作表where工作号in（select工作号frominserted））
set@y=（select最高工资from工作表where工作号in（select工作号frominserted））
set@z=@y/@x
if（@z>=1.5）
begin
print'操作符合要求'
end
else
begin
print@x
print@y
print@z
select*from工作表
select*frominserted
print'请注意，最高工资低于最低工资的.5倍'
rollback
end
insertinto工作表values（'G002',1000,1000）
（3）限制不能删除基本工资低于1500的职工。
altertriggerTR_Salary2
on职工表afterdelete
as
ifexists（select*from职工表where基本工资<1500）
begin
print'操作符合要求'
end
else
begin
print'不能删除基本工资低于的职工'
select*from职工表
select*fromdeleted
rollback
end
6．创建满足下述要求的用户自定义标量函数。
（1）查询指定学生已经得到的修课总学分（考试及格的课程才能拿到学分），学号为输入参数，总学分为函数返回结果。
并写出利用此函数查询9512101学生的姓名、所修的课程名、课程学分、考试成绩以及拿到的总学分的SQL语句。
createfunctiondbo.Sum_Credit（@snochar（7））
returnsint
as
begin
declare@sumint
set@sum=（selectsum（credit）fromscjoincoursecono=o
wheresc.sno=@snoandgrade>=60）
return@sum
end
selectsname姓名,o课程名,credit课程学分,grade考试成绩,dbo.Sum_Credit（sc.sno）as总学分fromscjoincoursecono=o
joinstudentsons.sno=sc.sno
wheresc.sno='0811101'
（2）查询指定系在指定课程（课程号）的考试平均成绩。
createfunctiondbo.Avg_Grade（@deptnvarchar（20）,@cnochar（6））
returnsint
as
begin
declare@Avgint
select@Avg=avg（grade）fromscjoinstudentsonsc.sno=s.sno
wheredept=@deptando=@cno
return@Avg
end
selectdistinctdbo.Avg_Grade（dept,cno）as平均成绩fromscjoinstudentsonsc.sno=s.sno
wheredept='计算机系'ando='C001'
（3）查询指定系的男生中选课门数超过指定门数的学生人数。
createfunctiondbo.Man（@deptnvarchar（20）,@menshuint）
returnsint
as
begin
declare@Numint
select@Num=count（*）from（selectsc.sno,count（o）asbfromstudentsleftjoinscons.sno=sc.sno
wheredept=@deptandsex='男'
groupbysc.sno
havingcount（o）>@menshu）ast
return@Num
end
selectdistinctdbo.Man（dept,0）学生人数fromstudentsleftjoinscons.sno=sc.sno
wheredept='计算机系'
7．创建满足下述要求的用户自定义内联表值函数。
（1）查询选课门数在指定范围内的学生的姓名、所在系和所选的课程。
createfunctiondbo.F_7_1（@menshuint）
returnstable
as
return（
selectsname,dept,o,cnamefromStudentsjoinSCons.Sno=sc.Sno
joinCourseconc.Cno=SC.Cno
wheres.snoin（
selectsnofromsc
groupbysno
havingcount（*）=@menshu））
select*fromdbo.F_7_1（3）
（2）查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。
并写出利用此函数查询计算机系学生考试情况的SQL语句，只列出学生姓名、课程名和考试成绩。
createfunctiondbo.F_7_2（@deptchar（20））
returnstable
as
return（selectsname,dept,cname,gradefromStudentsjoinSCons.Sno=sc.Sno
joinCourseconc.Cno=SC.Cno
wheredept=@deptandgrade>=90）
selectsname,cname,gradefromdbo.F_7_2（'计算机系'）
8．创建满足下述要求的用户自定义多语句表值函数。
（1）查询指定系年龄最大的前2名学生的姓名和年龄，包括并列的情况。
alterfunctiondbo.F_8_1（@deptchar（20））
returns@ret_F_8_1table（
snamechar（10）,
ageint）
as
begin
insertinto@ret_F_8_1
selecttop2WITHTIESsname,year（GETDATE（））-year（Birthday）agefromstudent
wheredept=@dept
orderbyageDESC
return
end
selectsname,agefromdbo.F_8_1（'计算机系'）
（2）查询指定学生（姓名）的考试情况，列出姓名、所在系、修的课程名和考试情况，其中考试情况列的取值为：
如果成绩大于等于90，则为“优”；如果成绩在80~89，则为“良好”；如果成绩在70~79，则为“一般”；如果成绩在60~69，则为“不太好”；如果成绩小于60，则为“很糟糕”。
并写出利用此函数查询李勇的考试情况的SQL语句。
alterfunctiondbo.F_8_2（@snamechar（10））
returns@ret_F_8_2table（
snamechar（10）,
deptchar（20）,
cnamechar（20）,
GStyechar（6））
as
begin
insertinto@ret_F_8_2
selectsname,dept,cname,case
whengrade>=90then'优'
whengradebetween80and89then'良好'
whengradebetween70and79then'一般'
whengradebetween60and69then'不太好'
whengrade<60then'很糟糕'
end
fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cno
wheresname=@sname
return
end
selectsname,dept,cname,gstyefromdbo.F_8_2（'刘晨'）
selectsname,dept,cname,gstyefromdbo.F_8_2（'李勇'）
9．创建满足下述要求的游标。
（1）查询VB课程的考试情况，并按如下形式显示结果数据:
选了VB课程的学生情况：
姓名所在系成绩
李勇计算机系86
刘晨计算机系78
吴宾信息系75
张海信息系68
print'选了VB课程的学生情况：
'
print''
print'姓名所在系成绩'
declare@schar（10）,@dchar（20）,@gint
declareC_9_1cursorfor
selectsname,dept,gradefromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cno
wherecname='VB'
openC_9_1
fetchnextfromC_9_1into@s,@d,@g
while@@fetch_status=0
begin
print''
print@s+@d+cast（@gaschar（4））
fetchnextfromC_9_1into@s,@d,@g
end
closeC_9_1
deallocateC_9_1
（2）统计每个系的男生人数和女生人数，并按如下形式显示结果数据。
系名性别人数
====================
计算机系男2
计算机系女1
数学系男1
数学系女1
信息系男2
信息系女1
print'系名性别人数'
print'===================='
declare@dchar（10）,@schar
（2）,@cint
declareC_9_2cursorfor
selectdept,sex,count（*）人数fromStudent
groupbydept,sex
orderbydept
openC_9_2
fetchnextfromC_9_2into@d,@s,@c
while@@fetch_status=0
begin
print''
print@d+''+@s+''+cast（@caschar（4））
fetchnextfromC_9_2into@d,@s,@c
end
closeC_9_2
deallocateC_9_2
（3）列出每个系的学生信息，要求首先列出一个系的系名，然后在该系名下列出本系学生的姓名和性别；再列出下一个系名，然后在此系名下再列出该系的学生姓名和性别；以此类推，直至列出全部系。
要求按如下形式显示结果数据:
计算机系学生：
李勇计算机系
刘晨计算机系
王敏计算机系
=====================
数学系学生：
钱小平数学系
王大力数学系
=====================
信息系学生：
张立信息系
吴宾信息系
张海信息系
=====================
declare@deptvarchar（20）,@snamechar（10）
declareC_9_3cursorfor
selectdistinctdeptfromstudent
openC_9_3
fetchnextfromC_9_3into@dept
while@@fetch_status=0
begin
print@dept+':
'
declareC_3cursorfor
selectsname,deptfromstudent
wheredept=@dept
openC_3
fetchnextfromC_3into@sn
升级会员 