SQL Server实验三

实验七
（1）创建并运行存储过程student_grade，要求实现如下功能：查询studb数据库中每个学生各门课的成绩，其中包括每个学生的sno、sname、cname和score。

createprocedurestudent_grade
as
selectstudent.sno,student.sname,course.cname,student_course.scorefromstudentjoinstudent_courseonstudent.sno=student_course.sno joincourseoncourse.cno=student_course.cno
运行结果代码：
useStudb
go
executestudent_grade
go

（2）创建并运行名为proc_exp的存储过程，要求实现如下功能：从student_course表中查询某一学生考试的平均成绩。

createprocedureproc_exp
@snamevarchar(8)
as
begin
selectsname,AVG(score)
fromstudentjoinstudent_courseonstudent.sno=student_course.snowheresname=@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

运行结果代码： 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

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_addIFOBJECT_ID('proc_exp')ISNOTNULL
实验八
(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

(3)删除触发器student_trg
DROPTRIGGERstudent_trg
(4)创建一个新的触发器，要求实现“计算机系的学生选课不能超过三门”这一完整性约束，并验证该触发器。

CREATETRIGGERstudent_choONstudent_courseAFTERINSERT
AS
BEGIN
IFEXISTS(SELECTsnoFROMinsertedWHEREsnoin(SELECTsnoFROM

WHERE
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．单击执行

fetchfromc_cursor
4．连续单击“执行”按钮，就可以逐条显示记录

5．最后关闭游标、释放游标
closec_cursor

（2）

selectsno,sname,sex,dept

fromstudentwheredept='计算机系'
forreadonly
openc_cursor
1．读取第一行数据，并输出；
fetchfirstfromc_cursor

2．读取最后一行数据，并输出；
fetchlastfromc_cursor

3．读取当前行的前一行数据，并输出；
fetchpriorfromc_cursor

4．读取从游标开始的第三行数据，并输出。
fetchabsolute3 fromc_cursor

（3） 利用游标修改指定的数据元组：在student表中定义一个所在系为“计算机系”，一个 包含sno、sname、sex、和dept的游标，游标名为c_cursor，将游标中绝对位置为3的 学生姓名改为“胡平”，性别改为“男”。

declarec_cursorscrollcursor
for
selectsno,sname,sex,dept
fromstudentwheredept='计算机系'
forupdateofsname,sex
openc_cursor fetchabsolute 3fromc_cursor
fetchabsolute 3 fromc_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
joincourseoncourse.cno=student_course.cno wheredept=@deptandcname=@cnameandscore<60 selectcount(*)b60a70
selectcount(*)b70a80
fromstudent_course
joinstudentonstudent.sno=student_course.sno
joincourseoncourse.cno=student_course.cno
wheredept=@deptandcname=@cnameandscore>=70 andscore<80
selectcount(*)b80a90
fromstudent_course
joinstudentonstudent.sno=student_course.sno
joincourseoncourse.cno=student_course.cno
wheredept=@deptandcname=@cnameandscore>=80 andscore<90
selectcount(*)more90
fromstudent_course
joinstudentonstudent.sno=student_course.sno
joincourseoncourse.cno=student_course.cno

openc_cursor wheredept=@deptandcname=@cnameandscore>=90

while@@FETCH_STATUS=0

begin
fetchnextFROMc_cursor
end
closec_cursor
end

go
executeproc_dis@cname='计算机基础',@dept='计算机系'go