实验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('刘东')。