实验11 存储过程

实验11 存储过程

〖仪器设备〗

每位学生一台PC机，装有SQL Server 2005（或SQL Server 2000）；

配备一台教师机为上课教师专用。

〖试验学时〗

2学时

〖目的要求〗

1.了解几个常用的系统存储过程。

2.掌握如何使用T-SQL语句创建、执行和重编译存储过程。

3.了解如何使用SSMS管理存储过程。

〖实验内容〗

1.创建存储过程。

(1)创建一个存储过程，查看“2”号课程的选修情况，包括选修该

课程的学号、姓名和成绩。

use stu

go

create procedure ssc_3

as

select student.sno,sname,grade

from student join sc on student.sno=sc.sno

where o='2'

go

(2)执行存储过程

execute ssc_3

2.使用输入参数

(1)上面所建立的存储过程只能对“2”号课程的选修情况进行查看，

要想对所有课程进行随机查看，需要进行参数的传递。

use stu

go

create procedure ssc_4

@cnumber char(5)

as

select student.sno,sname,grade

from student join sc on student.sno=sc.sno

where o=@cnumber

go

(2)按位置传递参数

execute ssc_4 '3'

(3)通过参数名传递参数

execute ssc_4

@cnumber='3'

3.使用默认参数值

(1)执行存储过程ssc_4时，如果没有给出参数，系统会报错。如果

希望不给参数时，能查询所有课程的进修情况，则可以使用默认

参数值来实现。

use stu

go

create procedure ssc_5

@cnumber char(5)=null

as

if @cnumber is null

begin

select student.sno,sname,grade

from student join sc on student.sno=sc.sno

end

else

begin

select student.sno,sname,grade

from student join sc on student.sno=sc.sno

where o=@cnumber

end

go

(2)执行下面两条语句，比较执行结果。

execute ssc_5

execute ssc_5 '2'

4.使用输出参数。

(1)创建一个存储过程ssc_6，获得选修某门课程的总人数。

use stu

go

create procedure ssc_6

@cnumber char(5),@ccount int output

as

select@ccount=count(*)from sc where o=@cnumber go

(2)执行存储过程ssc_6。

declare @ccount int

execute ssc_6 '2',@ccount output

select'结果是：',@ccount

5.使用返回值。

(1)创建一个返回执行状态码的存储过程ssc_7，它接受课程号为输

入参数，如果执行成功，返回0；如果没有给出课程号，返回错

误码1；如果给出的课程号不存在，返回错误码2；如果出现其

他错误，返回错误码3。

use stu

go

create procedure ssc_7

@cnumber char(5)=null

as

if @cnumber is null

begin

print'错误：你必须输入课程号。'

return(1)

end

else

begin

if(select count(*)from sc where

cno=@cnumber)=0

begin

print'错误：你给出的课程号不存在。'

return(2)

end

end

select @cnumber as cno from sc where cno=@cnumber

if@@error<>0

begin

return(3)

end

else

return(0)

go

(2)执行存储过程ssc_7。

declare @result int

exec @result=ssc_7

select'其结果是：',@result

6.修改存储过程-使用对象资源管理器修改存储过程。

7.查看存储过程。

exec sp_help

8.删除存储过程。

drop procedure ssc_3

〖问题解答〗

1.创建存储过程之前应考虑什么？

〖思考、练习题〗

1.使用对象资源管理器创建存储过程ssc_3。

2.使用对象资源管理器查看存储过程ssc_3的定义。

3.使用对象资源管理器删除存储过程ssc_3。

4.请创建学员成绩数据库stu1，其中学员信息表stuinfo和学生成绩

表stumarks如下：

(1)请创建存储过程，查看本次考试平均分以及未通过考试的学员名

单

(2)修改上题：由于每次考试的难易程度不一样，每次笔试和机试的

及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变

化。（提示：使用带输入参数的存储过程）