SQL Server存储过程

--存储过程

/*
存储过程(procedure)类似于Java语言的方法
用来执行管理任务或复杂的业务逻辑,方便调用
存储过程也分有参无参,有返回值无返回值
*/

--常用的系统存储过程

---列出当前系统中的数据库
exec sp_databases
---修改数据库的名称(单用户访问)
exec sp_renamedb 'school','university'
---返回某个表列的信息
use student
go
exec sp_tables
exec sp_columns stuInfo
---查看表stuInfo的信息
exec sp_help stuInfo
---查看表stuInfo的约束
exec sp_helpconstraint stuInfo
---查看表stuInfo的索引
exec sp_helpindex stuInfo
---查看视图的语句文本
exec sp_helptext '视图名'
---查看当前数据库中的存储过程
exec sp_stored_procedures
go


--例题:请创建存储过程，查看本次考试平均分以及未通过考试的学员名单
--创建不带输入参数的存储过程
create procedure pro_stu
as
	declare @avgwritten float,@avglab float--声明变量,笔试平均成绩和机试平均成绩
	select @avgwritten=avg(writtenExam),@avglab=avg(labExam) from stuScore--赋值
	print '笔试平均分:'+convert(nvarchar(4),@avgwritten)
	print '机试平均分:'+convert(nvarchar(4),@avglab)
	if(@avgwritten>80 and @avglab>80)
		begin
			print '本班考试成绩:优秀'
		end
	else
		begin
			print '本班考试成绩:较差'
		end
	print '---参加本次考试没有通过的学员---'
	select * from stuInfo where stuNo in(select stuNo from stuScore where writtenExam<80 or labExam<80)
go
--执行存储过程
exec pro_stu

--创建带输入参数的存储过程
create procedure pro_stu1
@writtenpass int,@labpass int
as
	declare @avgwritten float,@avglab float--声明变量,笔试平均成绩和机试平均成绩
	select @avgwritten=avg(writtenExam),@avglab=avg(labExam) from stuScore--赋值
	print '笔试平均分:'+convert(nvarchar(4),@avgwritten)
	print '机试平均分:'+convert(nvarchar(4),@avglab)
	if(@avgwritten>80 and @avglab>80)
		begin
			print '本班考试成绩:优秀'
		end
	else
		begin
			print '本班考试成绩:较差'
		end
	print '---参加本次考试没有通过的学员---'
	select * from stuInfo where stuNo in(select stuNo from stuScore where writtenExam<@writtenpass or labExam<@labpass)
go
exec pro_stu1 90,90
exec Pro_stu1 80,85

--创建为输入参数设置默认值的存储过程
create procedure pro_stu2
@writtenpass int=90,@labpass int=90
as
	declare @avgwritten float,@avglab float--声明变量,笔试平均成绩和机试平均成绩
	select @avgwritten=avg(writtenExam),@avglab=avg(labExam) from stuScore--赋值
	print '笔试平均分:'+convert(nvarchar(4),@avgwritten)
	print '机试平均分:'+convert(nvarchar(4),@avglab)
	if(@avgwritten>80 and @avglab>80)
		begin
			print '本班考试成绩:优秀'
		end
	else
		begin
			print '本班考试成绩:较差'
		end
	print '---参加本次考试没有通过的学员---'
	select * from stuInfo where stuNo in(select stuNo from stuScore where writtenExam<@writtenpass or labExam<@labpass)
go
--输入参数为默认值
exec pro_stu2
--为输入参数设值
exec pro_stu2 80,80
exec pro_stu2 80--为第一个参数赋值
exec Pro_stu2 @labpass=85--指定参数赋值

--带输出参数的存储过程
create procedure pro_stu3
@notpasssum int output,--输出参数
@writtenpass int=90,@labpass int=90--输入参数(推荐将输入参数放在输出参数后面)
as
	declare @avgwritten float,@avglab float--声明变量,笔试平均成绩和机试平均成绩
	select @avgwritten=avg(writtenExam),@avglab=avg(labExam) from stuScore--赋值
	print '笔试平均分:'+convert(nvarchar(4),@avgwritten)
	print '机试平均分:'+convert(nvarchar(4),@avglab)
	if(@avgwritten>80 and @avglab>80)
		begin
			print '本班考试成绩:优秀'
		end
	else
		begin
			print '本班考试成绩:较差'
		end
	print '---参加本次考试没有通过的学员人数---'
	select @notpasssum=count(stuNo) from stuInfo 
	where stuNo in(select stuNo from stuScore where writtenExam<@writtenpass or labExam<@labpass)
	
go
--声明变量@sum,并将输出参数赋给@sum,类似Java中声明变量接收返回值
declare @sum int
exec pro_stu3 @sum output
IF @sum>=3
  print '未通过人数：'+convert(varchar(5),@sum)+ '人, 
        超过60%,及格分数线还应下调'
ELSE
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,
        已控制在60%以下，及格分数线适中'
GO 



--使用raiserror语句
/*
RAISERROR (msg_id | msg_str,severity,
   state WITH option[,...n]]) 
msg_id：在sysmessages系统表中指定用户定义错误信息
msg_str：用户定义的特定信息，最长255个字符
severity：定义严重性级别。用户可使用的级别为0–18级
state：表示错误的状态，1至127之间的值
option：指示是否将错误记录到服务器错误日志中 

*/
--例题:完善上例，当用户调用存储过程时，传入的及格线参数不
--在0～100之间时，将弹出错误警告，终止存储过程的执行。
CREATE PROCEDURE pro_stu4 
  @notpassSum int OUTPUT, --输出参数
  @writtenPass int=85,  --默认参数放后
  @labPass int=85       --默认参数放后
  AS
    IF (NOT @writtenPass BETWEEN 0 AND 100) 
             OR (NOT @labPass BETWEEN 0 AND 100)
       BEGIN
         RAISERROR ('及格线错误，请指定0－100之间的分 
                     数，统计中断退出',16,1)
         RETURN  ---立即返回，退出存储过程
       END
    Else
       BEGIN
		 print '---参加本次考试没有通过的学员人数---'
	     select @notpasssum=count(stuNo) from stuInfo 
	     where stuNo in(select stuNo from stuScore where writtenExam<@writtenpass or labExam<@labpass)
       END
       
GO 

/*---调用存储过程，测试RAISERROR语句----*/
DECLARE @sum int,  @t int
EXEC pro_stu4 @sum OUTPUT ,604   
SET @t=@@ERROR 
print  '错误号：'+convert(varchar(5),@t )
IF @t<>0  
   RETURN  --退出批处理，后续语句不再执行
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
GO