第13次实验2012-答案

存储过程的创建和使用

( 1) 创建简单存储过程

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

IF EXISTS ( SELECTname FROMsysobjects WHEREname = 'stu_pr' AND type = 'P' )

BEGIN

DROPPROCEDURsEtu_pr

PRINT ' 已删除！'

END

ELSE

PRINT ' 不存在，可创建！'

GO

CREATEPROCEDURsEtu_pr

AS

SELECT*

FROMstudent left join sc on student .id =sc.id

WHERELEFT ( student . id , 8)= '20154103'

GO

STU_PR

(2)创建带参数的存储过程

a) 创建一个名为stu_proc1 的存储过程：查询某姓名的学生的学号、姓名、年龄，选修课程名、成绩。姓名在调用该存储过程时输入，其默认值为“王%”。执行该存储过程，用多种

参数加以测试。

create procedure stu_proc1

@namenvarchar (8)=' 王%'

as

select 学号=s. id ,姓名=name 年龄=age,选修课程名=coursename,成绩=score

from student s,sc,course

where name like @nameand s. id =sc. id and sc. courseid =course .courseid

go

stu_proc1

go

stu_proc1 ' 张%'

go

stu_proc1 ' 郭贺'

b)创建一个名为t_student_t_score的存储过程：可查询出某学号段的同学的学号、姓名、

总成绩。( 学号起始号与终止号在调用时输入，可设默认值，如：

‘201541030103' -‘201541030108')。执行该存储过程create procedure t_student_t_score @lowx char ( 12)= '201541030103' , @upx char ( 12)= '201541030108' as

select 学号=s. id ,姓名=n ame总成绩=sum( score ) from student s,sc

where s.id =sc. id and s. id >=@lowx and s. id <=@upx group by s. id , name

go t_student_t_score go

t_student_t_score '201541030101' , '201541030103'

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

a)创建一个名为t_course_average的存储过程，可查询某门课程(输入课号)考试的平均成绩。平均成绩可以输出，以便进一步调用。

create procedure t_course_average

@cnchar (5),

@avreal output

as

select @av=avg ( score )

from sc

where courseid =@cn

go

declare @screal

execute t_course_average '20001' , @sc output

select @sc

b) 创建执行该存储过程的批处理，要求当平均成绩小于60 时，显示信息为：“XX 课程的平均成绩为：XX ，其平均分未达60 分”。超过60时，显示信息为：“XX 课程的平均成绩为：XX ”。

declare @screal , @sr char (5)

set @sr = '20001'

execute t_course_average @sr, @sc output

if @sc<60

print @sr+' 课程的平均成绩为：' +str (@sc, 5, 2)+' ，其平均分未达60分' else

print @sr+' 课程的平均成绩为：' +str (@sc, 5, 2)

(4)使用T-SQL语句管理和维护存储过程

a)使用sp_helptext查看存储过程t_student_t_score的定义脚本。

exec sp_helptext t_student_t_score

b)使用select语句查看t_student_t_score存储过程的sysobjects信息。select * from sysobjects

where name = 't_student_t_score'

c)将存储过程stu_pr改为查询学号为201541030103的学生的详细资料alter procedure stu_pr

as

select *

from student s left join sc on s. id =sc.id

where s.id ='201541030103'

go

execute stu_pr

d）删除存储过程stu_pr。

drop procedure stu_pr