实验11 存储过程
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
该程序完成的功能是 创建一个存储过程，储存分数 。
（2） 设计一个存储过程proc_grade完成这样的功能：查询grade表中课程编号
为’k002’的学号、分数信息，并使存储过程不能使用sp_helptext查看（即加密），请编写程序实现。 答：create procedure proc_grade
with encryption as begin
select grade.学号,grade.分数 from grade
where grade.课程编号='k002' end
（3） 修改存储过程proc_grade，查询grade表中课程编号为’k001’的学号、分数信息，去掉proc_grade加密性，使其在运行时重新编译。 答：alter procedure proc_grade
as
begin
select grade.学号,grade.分数 from grade
where grade.课程编号='k001' end
（4） 在student_info 表中增加一列名为“手机号”，数据类型char(11)。设计一个存储过程proc_tel，查询student_info表中末尾数字为5的手机号码。 答：alter table student_info
add 手机号 char(11)
create procedure proc_tel as begin
insert into student_info(学号,手机号) values （’ ‘,’ ‘） select 手机号 from student_info where 手机号 like '%5' end
exec proc_tel
（5） 设计一个存储过程proc_avg，输出grade表中每个学生的平均成绩。 答： create procedure proc_avg
as begin
select AVG(grade.分数) as 平均成绩,grade.学号 from grade
group by grade.学号 end
exec proc_avg
（6） 设计一个存储过程proc_list，查询grade表中指定课程(参数@cid char(4))的成绩排名前3的学生成绩信息。编写程序并调用该存储过程。 答：create procedure proc_list
@cid char(4) as begin
select top 3 * from grade
where grade.课程编号=@cid order by grade.分数 desc end
exec proc_list ' '
（7） 设计一个存储过程proc_stu，查询某门课程某个分数段的学生信息，显示
学号、姓名、分数，要求课程名称和分数段均为输入参数，请编写程序并调用该存储过程。 答：
create procedure proc_stu @sclass nchar(10), @score decimal(4,1), @score2 decimal(4,1) as begin
select a.学号,a.姓名,b.分数
from student_info a inner join grade b on a.学号=b.学号 inner join curriculum c on b.课程编号=c.课程编号
where c.课程名称=@sclass and b.分数>@score and b.分数<@score2 end
exec proc_stu ' ', ,(自己输入参数)
（8） 设计一个存储过程proc_course，查询某门课程的平均成绩并将其输出给
用户。课程名称为输入参数，平均成绩为输出参数，编写程序并调用该存储过程。
答：create procedure proc_course
@cclass nchar(10),
@avgscore decimal(4,1) output as begin
select @avgscore=avg(grade.分数)
from grade inner join curriculum on grade.课程编号=curriculum.课程编号 where curriculum.课程名称=@cclass end
declare @cclass nchar(10) exec proc_course ' ', @cclass output select @cclass 平均成绩
（9） 设计一个存储过程，用于修改指定学生(参数@sid char(4))指定课程(参数
为@cid char(4))的分数(@score decimal(3,1))，并输出该生的平均成绩（输出参数@avgs），要求输出格式为：“该生的最新平均成绩为：”。编写并调用该存储过程(修改学号’0002’的学生的修读课程‘K003’的成绩改为96)。
答：
create procedure proc_alter @sid char(4), @cid char(4), @score decimal(3,1), @avgs decimal(3,1) output as begin
update grade
set grade.分数=@score
where grade.学号=@sid and grade.课程编号=@cid select @avgs=avg(grade.分数) from grade
where grade.学号=@sid end
declare @avgs decimal(3,1) exec proc_alter '0002','K003',96, @avgs output
select @avgs 平均成绩
（10） 设计一个存储过程proc_credit完成这样的功能：输入学号@sid、课程名
称@cname参数值，将查询curriculum、grade表，并从输出参数@score、@credit获取该学生该课程的成绩和学分，如果分数大于等于60，则返回对应课程的学分，否则返回学分值0，请编写程序并调用该存储过程。 答：
create procedure proc_credit @sid char(4), @cname nchar(10), @credit int output, @score decimal(4,1) output as begin
select @score=grade.分数,@credit=curriculum.学分
from grade inner join curriculum on grade.课程编号=curriculum.课程编号 where grade.学号=@sid and curriculum.课程名称=@cname end
declare @credit int,@score decimal(4,1) exec proc_credit ' ',' ', @credit output,@score output
select case when @score>=60 then @credit
when @score<60 then '0'
else ' 'end
数据库作业储存过程
