数据库原理及应用 实验七

一、实验目的

（1）掌握T-SQL流控制语句。 （2）掌握创建存储过程的方法。 （3）掌握存储过程的执行方法。 （4）掌握存储过程的管理和维护。

二、实验内容

1、创建简单存储过程

（1）创建一个名为stu_pr的存储过程，该存储过程能查询出051班学生的所有资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息）。要求在创建存储过程前请判断该存储过程是否已创建，若已创建则先删除，并给出“已删除！”信息，否则就给出“不存在，可创建！”的信息。

ifexists(selectnamefromsysobjectswherename='stu_pr'andtype='p') begin

print'已删除！' dropprocedurestu_pr end else

print'不存在，可创建！' go

createprocedurestu_proc as select*

fromSleftouterjoinSC

on(S.Sno=SC.Sno)leftouterjoinC on(C.Cno=SC.Cno) whereSdept='CS'

1

execstu_proc

2、创建带参数的存储过程 （1）创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄，选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“%”与“林%”。执行该存储过程，用多种参数加以测试。

ifexists(selectnamefromsysobjectswherename='stu_proc1'andtype='p') begin

print'已删除!'

2

dropprocedurestu_proc1 end else

print'不存在,可创建!' go

createprocedurestu_proc1

@Sdeptchar(8)='%',@Snamevarchar(8)='李%' as

selectSdept,S.Sno,Sname,Sage,Cname,Grade fromS,SC,C whereS.Sno=SC.Sno andC.Cno=SC.Cno andSdeptlike@Sdept andSnamelike@Sname

执行所创建存储过程：

usestudent

executestu_proc1'CS','李勇'

3

executestu_proc1'信息安全','胡光璟'

（2）创建一个名为Student_sc的存储过程，可查询出某段学号的同学的学号、姓名、总成绩。（学号起始号与终止号在调用时输入，可设默认值）。执行该存储过程。

ifexists(selectnamefromsysobjectswherename='Student_sc'andtype='p') begin

print'已删除！'

dropprocedurestudent_sc end else

print'不存在，可创建！' go

createprocedureStudent_sc @Sno1char(8),@Sno2char(8) as

selectS.Sno,Sname,SUM(Grade)总成绩 fromS,SC,C whereS.Sno=SC.Sno andC.Cno=SC.Cno andS.Sno>=@Sno1 andS.Sno<=@Sno2 groupbyS.Sno,Sname

4

执行所创建存储过程：

executeStudent_sc'95000','95009'

3、创建带输出参数的存储过程

（1）创建一个名为Course_sum的存储过程，可查询某门课程考试的总成绩。 总成绩可以输出，以便进一步调用。

ifexists(selectnamefromsysobjectswherename='Course_sum'andtype='p') begin

print'已删除！'

dropprocedureCourse_sum end else

print'不存在，可创建！' go

createprocedureCourse_sum @Cnamevarchar(20),@sumintoutput as

5

select@sum=sum(Grade) fromSC,C

whereC.Cno=SC.Cno andCname=@Cname groupbySC.Cno,Cname

declare@pingint

execCourse_sum'数学',@pingoutput

print'高数的考试总成绩为：'+cast(@pingasvarchar(20))

（2）创建一执行该存储过程的批处理，要求当总成绩小于100时，显示信息为：“XX课程的总成绩为：XX,其总分未达100分”。超过100时，显示信息为：“XX课程的总成绩为：XX”。

declare@sumint

declare@Cnamevarchar(20) ExecCourse_sum@cname,@sumout begin if@sum<100

printcast(@cnameasvarchar)+'课程的总成绩为:'+cast(@sumasvarchar)+',其总分未达分' else

printcast(@cnameasvarchar)+'课程的总成绩为:'+cast(@sumasvarchar) end

6

declare@sumint

declare@Cnamevarchar(20) set@Cname='数学'

ExecCourse_sum@cname,@sumout begin if@sum<100

printcast(@cnameasvarchar)+'课程的总成绩为:'+cast(@sumasvarchar)+',其总分未达分' else

printcast(@cnameasvarchar)+'课程的总成绩为:'+cast(@sumasvarchar) end

4、创建带重编译及加密选项的存储过程

创建一个名为update_sc、并带重编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩。（学号、课程号由调用时输入）

ifexists(selectnamefromsysobjectswherename='update_sc'andtype='p') begin

print'已删除！'

dropprocedureupdate_sc end else

7

print'不存在，可创建！' go

createprocedureupdate_sc

@snochar(8),@cnochar(3),@gradetinyint withRECOMPILE,ENCRYPTION as updateSC

setGrade=@grade

whereSno=@snoandCno=@cno

declare@snochar(8),@cnochar(3),@gradetinyint set@sno='95001' set@cno='3' set@grade='100'

execupdate_sc@sno,@cno,@grade begin

printcast(@snoasvarchar)+'的'+cast(@cnoasvarchar)+'课程成绩为：'+cast(@gradeasvarchar) end

8

5、使用T-SQL语句管理和维护存储过程

（1）使用sp_helptext查看存储过程Student_sc的定义脚本 execsp_helptextstudent_sc

（2）使用select语句查看Student_sc存储过程的定义脚本（提示：通过查询表sysobjects和表syscomments）

select*

fromsysobjects,syscomments wherename='Student_sc'

（3）将存储过程stu_proc改为查询学号为95001的学生的详细资料。

alterprocedurestu_proc as select*

fromSleftouterjoinSC

on(S.Sno=SC.Sno)leftouterjoinC on(C.Cno=SC.Cno) whereS.Sno='95001'

9

（4）删除存储过程stu_proc。 dropprocedurestu_proc

6、使用SQL Server Management Studio管理存储过程

（1）在SQL Server Management Studio中重新创建刚删除的存储过程stu_pr

createprocedurestu_proc as begin select*

fromSleftouterjoinSC

on (S.Sno=SC.Sno)leftouterjoinC on (C.Cno=SC.Cno) whereS.Sno='95001' end

10

（2）查看存储过程stu_proc，并将该过程修改为查询051班女生的所有资料。

ALTERprocedure[dbo].[stu_proc] as begin select*

fromSleftouterjoinSC

on(S.Sno=SC.Sno)leftouterjoinC on(C.Cno=SC.Cno) whereS.Sno='95001' andSsex='男' end

（3）删除存储过程stu_pr

11

12