SQLServer实验三分解.docx
《SQLServer实验三分解.docx》由会员分享，可在线阅读，更多相关《SQLServer实验三分解.docx（14页珍藏版）》请在冰豆网上搜索。
SQLServer实验三分解
实验七
（1）创建并运行存储过程student_grade，要求实现如下功能：
查询studb数据库中每个学生各门课的成绩，其中包括每个学生的sno、sname、cname和score。
createprocedurestudent_grade
as
selectstudent.sno,student.sname,ame,student_course.score
fromstudentjoinstudent_courseonstudent.sno=student_course.sno
joincourseono=student_o
运行结果代码：
useStudb
go
executestudent_grade
go
（2）创建并运行名为proc_exp的存储过程，要求实现如下功能：
从student_course表中查询某一学生考试的平均成绩。
createprocedureproc_exp
@snamevarchar（8）
as
begin
selectsname,AVG（score）
fromstudentjoinstudent_courseonstudent.sno=student_course.sno
wheresname=@sname
groupbysname
end
运行结果代码：
useStudb
go
executeproc_exp@sname='刘招香'
go
（3）修改存储过程proc_exp，要求实现如下功能：
输入学生学号，根据该学生所选课程的平均成绩给出提示信息，即如果平均成绩在60分以上，显示“成绩合格，成绩为XX分”，否则显示“成绩不合格，成绩为XX分”；然后调用存储过程proc_exp，输入学号0705010131，显示成绩是否合格。
alterprocedureproc_exp
@student_snovarchar（20）
as
declare@avgvarchar（20）
set@avg=（selectAVG（score）
fromstudent_course
wheresno=@student_sno）
if@avg>=60print'成绩合格，成绩为'+@avg+'分'
elseprint'成绩不合格，成绩为'+@avg+'分'
运行结果代码：
useStudb
go
declare@student_snovarchar（20）
select@student_sno='0705010131'
execproc_exp@student_sno
（4）创建名为proc_add的存储过程，要求实现以下功能：
向student_course表中添加学生记录；然后调用存储过程proc_add，向student_course表中添加学生成绩记录。
createprocedureproc_add
@snochar（10）,
@cnochar（10）,
@scoretinyint
as
begin
setnocounton
ifnotexists
（select*fromstudent_coursewheresno=@sno
andcno=@cnoandscore=@score）
insertintostudent_course（sno,cno,score）
values（@sno,@cno,@score）
end
运行结果代码：
usestudb
go
execproc_add'0705010102','0208','80'
go
执行前：
执行后：
（5）删除存储过程proc_exp和proc_add
IFOBJECT_ID（'proc_exp'）ISNOTNULL
DROPPROCEDUREproc_exp
IFOBJECT_ID（'proc_add'）ISNOTNULL
DROPPROCEDUREproc_add
实验八
（1）创建触发器student_trg，当删除student表中的数据时，同时删除student_course表中相同的数据；然后通过删除student表中的某个学生记录来验证该触发器。
createtriggerstudent_trg
onstudent
afterdelete
as
begin
delete
fromstudent_course
wheresnoIN（
selectsno
fromdeleted
）
end
运行结果代码：
DELETEFROMstudent
WHEREsno='0705010303'
（2）修改触发器student_trg，当更新student表中sno的值时，同时更新student_course表中相同的sno的值；然后通过修改student表中的某个学生的学号（sno）来验证该触发器
ALTERTRIGGERstudent_trg
ONstudentAFTERUPDATE
AS
BEGIN
IFUPDATE（sno）
UPDATEstudent_course
SETsno=（SELECTsnoFROMinserted）
WHEREsnoIN（SELECTsnoFROMDELETED）
END
运行结果代码：
UPDATEstudent
SETsno='0705010217'
WHEREsno='0705010215'
（3）删除触发器student_trg
DROPTRIGGERstudent_trg
（4）创建一个新的触发器，要求实现“计算机系的学生选课不能超过三门”这一完整性约束，并验证该触发器。
CREATETRIGGERstudent_choONstudent_courseAFTERINSERT
AS
BEGIN
IFEXISTS（SELECTsnoFROMinsertedWHEREsnoin（SELECTsnoFROM
studentWHEREdept='计算机系'）AND（SELECTCOUNT（*）FROMstudent_courseWHERE
student_course.sno=inserted.sno）>3）
BEGIN
PRINT'计算机系的学生选课不能超过三门'
ROLLBACKTRANSACTION
END
END
实验九
（1）利用游标逐行显示所查询的数据块的内容：
在student表中定义一个包含sno、sname、sex和dept的只读游标，游标名为c_cursor，并将游标中的数据逐条显示出来。
1．在数据库引擎上查询文档中输入如下代码：
declarec_cursorscrollcursor
for
selectsno,sname,sex,dept
fromstudent
forreadonly
openc_cursor
fetchfromc_cursor
2．单击执行
3．接着读取游标中的第二行，在查询编辑器重输入如下语句：
fetchfromc_cursor
4．连续单击“执行”按钮，就可以逐条显示记录
5．最后关闭游标、释放游标
closec_cursor
（2）利用游标显示指定行的数据的内容：
在student表中定义一个所在系为“计算机系”，包含sno、sname、sex、和dept的游标，游标名为c_cursor，完成如下操作：
declarec_cursorscrollcursor
for
selectsno,sname,sex,dept
fromstudentwheredept='计算机系'
forreadonly
openc_cursor
1．读取第一行数据，并输出；
fetchfirstfromc_cursor
2．读取最后一行数据，并输出；
fetchlastfromc_cursor
3．读取当前行的前一行数据，并输出；
fetchpriorfromc_cursor
4．读取从游标开始的第三行数据，并输出。
fetchabsolute3fromc_cursor
（3）利用游标修改指定的数据元组：
在student表中定义一个所在系为“计算机系”，一个包含sno、sname、sex、和dept的游标，游标名为c_cursor，将游标中绝对位置为3的学生姓名改为“胡平”，性别改为“男”。
declarec_cursorscrollcursor
for
selectsno,sname,sex,dept
fromstudentwheredept='计算机系'
forupdateofsname,sex
openc_cursor
fetchabsolute3fromc_cursor
updatestudent
setsname='胡平',sex='男'
wherecurrentofc_cursor
fetchabsolute3fromc_cursor
（4）编写一个使用游标的存储过程并查看运行结果，要求该存储过程以课程名（cname）和系（dept）作为输入参数，计算指定系的学生指定课程的成绩分布情况，要求分别输出大于90，80~89，70~79，60~69和60分以下的学生人数。
createprocedureproc_dis
@cnamevarchar（20）,
@deptvarchar（50）
as
begin
declarec_cursorcursor
selectcount（*）less60
fromstudent_course
joinstudentonstudent.sno=student_course.sno
joincourseono=student_o
wheredept=@deptandcname=@cnameandscore<60
selectcount（*）b60a70
fromstudent_course
joinstudentonstudent.sno=student_course.sno
joincourseono=student_o
wheredept=@deptandcname=@cnameandscore>=60andscore<70
selectcount（*）b70a80
fromstudent_course
joinstudentonstudent.sno=student_course.sno
joincourseono=student_o
wheredept=@deptandcname=@cnameandscore>=70andscore<80
selectcount（*）b80a90
fromstudent_course
joinstudentonstudent.sno=student_course.sno
joincourseono=student_o
wheredept=@deptandcname=@cnameandscore>=80andscore<90
selectcount（*）more90
fromstudent_course
joinstudentonstudent.sno=student_course.sno
joincourseono=student_o
wheredept=@deptandcname=@cnameandscore>=90
openc_cursor
while@@FETCH_STATUS=0
begin
fetchnextFROMc_cursor
end
closec_cursor
end
go
executeproc_dis@cname='计算机基础',@dept='计算机系'
go
升级会员 