实验10 存储过程

实验10 存储过程

1．实验目的

（1）掌握使用SQL Server管理平台和Transact-SQL语句创建存储过程、执行存储过程、修改存储过程、删除存储过程的用法。

（2）理解使用SQL Server管理平台和Transact-SQL语句查看存储过程定义、重命名存储过程的用法。

2．实验内容及步骤

请先附加studentsdb数据库，然后完成以下实验。

（1）CREATE PROCEDURE st_g

AS

BEGIN

SELECT x.学号,x.姓名,y.分数

FROM student_info x ,grade y

Where x.学号=y.学号

END

该程序完成的功能是创建一个存储过程，存储过程的功能是对student_info和grade表进行查询，然后输出学号，姓名和分数信息。

（2）设计一个存储过程proc_grade完成这样的功能：查询grade表中课程编号为’k002’的学号、分数信息，并使存储过程不能使用sp_helptext

查看（即加密），请编写程序实现。

答：create procedure proc_grade

with encryption

as

select学号,分数

from grade

where课程编号='k002'

--执行存储过程，进行验证

exec proc_grade

（3）修改存储过程proc_grade，查询grade表中课程编号为’k001’的学号、

分数信息，去掉proc_grade加密性，使其在运行时重新编译。

答：alter procedure proc_grade

with recompile

as

select学号,分数

from grade

where课程编号='k001'

--执行存储过程，进行验证

exec proc_grade

（4）在student_info 表中增加一列名为“手机号”，数据类型char(11)。

设计一个存储过程proc_tel，查询student_info表中末尾数字为5的手

机号码。

答：

craeate table student_info

add手机号char(11)

go

create procedure proc_tel

as

select手机号from student_info

where手机号like'%5'

--执行存储过程，进行验证

exec proc_tel

（5）设计一个存储过程proc_avg,输出grade表中每个学生的平均成绩。答：create procedure proc_avg

as

select学号,avg(分数)as平均成绩

from grade

group by学号

--执行存储过程，进行验证

exec proc_avg

（6）设计一个存储过程，用于修改指定学生(参数@sid char(4))指定课程(参数为@cid char(4))的分数(@score decimal(3,1))，编写并调用该存储过程，修改学号’0002’的学生的修读课程‘K003’的成绩改为96。编写并调用存储过程。

答：create procedure proc_modifyscore

@sid char(4),

@cid char(4),

@score decimal(3,1)

as

update grade set分数=@score

where学号=@sid and课程编号=@cid

--执行存储过程，进行验证

exec proc_modifyscore'0002','k003',96

（7）设计一个存储过程proc_list，查询grade表中指定课程(参数@cid

char(4))的成绩排名前3的学生成绩信息。编写程序并调用该存储过程。答：

alter procedure proc_list

@cid char(4)

as

select top 3 学号,分数

from grade

where课程编号=@cid

order by分数desc

--执行存储过程，进行验证

exec proc_list'k004'

（8）设计一个存储过程proc_credit完成这样的功能：输入学号@sid、课程名称@cname参数值，将查询curriculum、grade表，并从输出参数

@score、@credit获取该学生该课程的成绩和学分，如果分数大于等于60，

则返回对应课程的学分，否则返回学分值0，请编写程序并调用该存储过程。答：

方案一：利用set进行赋值（SET是SQL Server 中对已经定义的变量赋值的

方式）

create procedure proc_credit

@sid char(4),

@cname nchar(10),

@score decimal(3,1)output,

@credit int output

as

SELECT @score = grade.分数, @credit = curriculum.学分

FROM curriculum

JOIN grade ON curriculum.课程编号= grade.课程编号

WHERE grade.学号= @sid

AND curriculum.课程名称= @cname;

if (@score<60)

begin

--通过set进行赋值

set @credit=0

end

--执行查询

declare@score int,@credit int

exec proc_credit'0001','c语言程序设计',@score output,@credit output

select@score,@credit

方案二：利用case when 语法

create procedure proc_credit

@sid char(4),

@cname nchar(10),

@score decimal(3,1)output,

@credit int output

as

select@score=grade.分数,@credit=case

when grade.分数<60 then 0

when grade.分数>=60 then curriculum.学分

end

from curriculum join grade on curriculum.课程编号=grade.课程编号

where grade.学号=@sid and curriculum.课程名称=@cname

--执行查询

declare@score int,@credit int

exec proc_credit'0001','c语言程序设计',@score output,@credit output

select@score,@credit

（9）创建一个自定义函数maxscore，用于计算指定课程号的最高分。

答：create function maxscore(@cid char(4))

returns int

as

begin

declare@score int

select@score=max(分数)from grade where课程编号=@cid

return@score

end

print dbo.maxscore('k001')

（10）创建一个自定义函数tscore，用于计算指定学生（姓名）所读的课程编

号、课程名、分数。

答：CREATE FUNCTION tscore(@sname char(8))

RETURNS Table

AS

RETURN (SELECT b.课程编号,c.课程名称,b.分数FROM student_info a join grade b on a.

学号=b.学号

join curriculum c on b.课程编号=c.课程编号

WHERE a.姓名=@sname)

select*from dbo.tscore('刘东')