实验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
(注:专业文档是经验性极强的领域,无法思考和涵盖全面,素材和资料部分来自网络,供参考。可复制、编制,期待你的好评与关注)