2018年11月30日存储过程实验（二）

一、实验目的

熟练掌握存储过程的定义和使用，理解存储过程在数据库的使用中能发挥出什么样的作用。

二、实验环境 

硬件环境：PC

软件环境：Windows 10 、SQL Server 2008 

--1、创建一个存储过程p1，要求查询每位学生所选课程的平均成绩、最高成绩。
--调用该存储过程，测试执行结果。
create procedure p1
as
select s_id 学号,AVG(grade) 平均成绩,MAX(grade) 最高成绩
from s_c group by s_id 
go

exec p1 
--2、将Course表上增加一个字段seleNUM，并编写存储过程p2：
--传入学号s_id和课程号course_id后，
--完成在S_C表中插入相应的选课记录，并在Course表中对应课程的seleNUM加1。
--调用该存储过程，测试执行结果。

--将Course表上增加一个字段seleNUM
alter table course add seleNUM int
--存储过程p2
create procedure p2
@sid char(10),
@coid char(10)
as
begin
	insert into s_c values(@sid,@coid,null)
	if(select seleNUM from course where course_id=@coid and @sid=@sid)is not null
	begin
	update course set seleNUM+=1 where course_id=@coid
	end
	else
	update course set seleNUM=1 where course_id=@coid
	print'成功插入选课记录!'
end
go

select *from course
exec p2 '2016010102','0002'
--3、创建一个存储过程p3，参数是学号s_id，
--输出信息是指定学号所对应的姓名、课程名称、成绩。
--运行时若没有指定学号，则提示“请输入学号”，
--若学号不存在，则提示“学号不存在”。
--调用该存储过程，测试执行结果。
create procedure p3
@sid char(10),
@sname varchar(20) output,
@cname varchar(20) output,
@grade decimal output
as
begin
	if (@sid is null)
		print'请输入学号'
	else if not exists(select *from student where s_id=@sid) 
		print'学号不存在'
	else
		begin
		select @sname=s_name,@cname=c.course_name,@grade=grade from student s,s_c,course c
		where s_c.s_id=s.s_id and s_c.course_id=c.course_id
		and s.s_id=@sid 
		select s_name 姓名,c.course_name 课程名,grade 成绩 from student s,s_c,course c
		where s_c.s_id=s.s_id and s_c.course_id=c.course_id
		and s.s_id=@sid 
		end
end
go
--①没有指定学号
declare @sname varchar(20),@cname varchar(20),@grade decimal
exec p3 null,@sname output,@cname output,@grade output
go
--②指定的学号不存在
declare @sname varchar(20),@cname varchar(20),@grade decimal
exec p3 '2016010101',@sname output,@cname output,@grade output
go
--③学号所对应的姓名、课程名称、成绩。
declare @sname varchar(20),@cname varchar(20),@grade decimal
exec p3 '2016010102',@sname output,@cname output,@grade output
go
--4、创建一个存储过程Pro_Accum，
--利用while循环实现计算从1到输入参数n间各整数的累加计算功能，计算结果存入输出参数sum。
--并通过T-SQL调用定义的存储过程，计算1+2+…+100的累加和，并输出。
--要求:提交创建存储过程的SQL脚本,并标注必要的注释。
--保证程序能够正确编译和运行,并有相应的测试代码。
create proc Pro_Accum
@n int,
@sum int output
as
begin
	select @sum=0
	while(@n>=0)select @sum+=@n,@n-=1
	select '总和'=@sum
end
go

declare @sum int
exec Pro_Accum 5,@sum output