实验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
sql存储过程实例--执行查询
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('刘东')
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论