存储过程实现基本的增删查改（二）

表结构在http://blog.csdn.net/u014180504/article/details/50589645

use Text

--4、带返回值的存储过程
--（）print语句可以将用户定义的消息返回给客户端
--编写一个存储过程up_insertstudent2,在插入学生数据前，先判断一下学号是否存在，
--如果存在，输出“要插入的学生的学号已经存在”；否则，插入学生数据，并返回”恭喜，数据插入成功“


if object_id('up_insertstudent2','p') is not null
drop procedure up_insertstudent2
go
create procedure up_insertstudent2
@sid int, 
@sname varchar(30),
@sphone bigint
as
begin
if exists(select * from T_Stuinfo where stu_id = @sid)
print('要插入的学生的学号已经存在')   --也可写成raiserror('要插入的学生的学号已经存在', 16, 1)
else
begin
insert into T_Stuinfo               
(stu_id,stu_name,stu_phone)
values
(@sid, @sname,@sphone)
print('恭喜，数据插入成功')
--raiserror('恭喜，数据插入成功', 16, 10)
end
end


--调用过程代码如下：
select * from T_Stuinfo


exec up_insertstudent2 @sid =120901017, @sname ='张小飞', @sphone=111111111


select*from T_Stuinfo


exec up_insertstudent2 @sid =120901117, @sname ='张小龙', @sphone=2222222222


select*from T_Stuinfo


--（）return 语句，return语句可以从过程、批处理或语句块中退出，不执行其后继语句

if object_id('up_delstudentbyname2','p') is not null
drop procedure up_delstudentbyname2
go
create procedure up_delstudentbyname2
@sname varchar(30)
as
begin
delete from T_Stuinfo
where stu_name = @sname
return @@rowcount  --行计数
end
--drop procedure up_delstudentbyname2
--调用存储过程的代码如下：
declare @ret_val int
exec @ret_val = up_delstudentbyname2 'ww'--ok
select @ret_val as "删除的行数"
--也可写成print @ret_val
select * from T_Stuinfo


exec up_insertstudent2 @sid =120901217, @sname ='张小龙', @sphone=5555
exec up_insertstudent2 @sid =120901317, @sname ='张小龙', @sphone=6666


--5、带变量的存储过程
/*在存储过程可以定义变量，包括全局变量（@@变量名）和局部变量（@变量名）。
用于保存存储过程中的临时结果

编写存储过程up_getavgscorebyname2，根据输入的学生姓名，计算该学生的平均成绩。
根据该生平局成绩与全体学生平均成绩的关系，返回相应信息*/

if object_id('up_getavgscorebyname2','p') is not null
drop procedure up_getavgscorebyname2
go
create procedure up_getavgscorebyname2
@sname varchar(30), 
@resStr varchar(30) output
as
begin
declare @curAvg decimal(18,2)
declare @totalAvg decimal(18,2)
select @totalAvg =avg(cou_grade) from T_stu_cou
select @curAvg =avg(cou_grade) from T_Stuinfo "S", T_stu_cou "CS"
where "S".stu_id = "CS".stu_id and "S".stu_name = @sname
if (@curAvg > @totalAvg)
set @resStr ='高于平均分'
else
set @resStr ='低于平均分'
print('总平均分为'+convert(varchar(18), @totalAvg))
print('该生平均分'+convert(varchar(18), @curAvg))
--print @resStr 
end

--drop procedure up_getavgscorebyname2
--调用存储过程的代码
declare @resstring varchar(30)
exec up_getavgscorebyname2 'ili', @resstring output
print @resstring

-------------并不理解----------------

--6、使用output游标参数
--output游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器


/*编写一个带有output型游标参数的存储过程up_getstudent_cursor, 再编写一个过程
up_printstudentbycursor, 对游标中的数据进行显示*/


--存储过程up_getstudent_cursor的代码如下
if object_id('up_getstudent_cursor','p') is not null
drop procedure up_getstudent_cursor
go
create procedure up_getstudent_cursor
@student_cursor cursor varying output
as
begin
set @student_cursor =cursor
forward_only static for
select stu_id, stu_name,stu_phone
from T_Stuinfo;
open @student_cursor;
end


--存储过程printstudentbycursor代码如下：
if object_id('printstudentbycursor','p') is not null
drop procedure printstudentbycursor
go
create procedure printstudentbycursor
as
declare @Mycursor cursor
declare @axh varchar(30)
declare @asname varchar(30)
declare @asbir varchar(30)
begin
exec up_getstudent_cursor @student_cursor = @Mycursor output
fetch next from @Mycursor into @axh, @asname, @asbir
while(@@fetch_status= 0)
begin
print('学号:'+ @axh+      +'--------姓名:'+ @asname +'-------电话'+ @asbir)
fetch next from @Mycursor into @axh, @asname, @asbir
end
close @Mycursor;
deallocate @Mycursor;
end


--调用存储过程的代码如下
exec printstudentbycursor


--查询所有存储过程

select * from sys.objects where type = 'P';