实验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
数据库作业储存过程
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)