游标变量用法经典

-- -生成测试表T
if exists ( select 1 from sysobjects where Name = N ' T ' and objectProperty (ID,N ' IsUserTable ' ) = 1 )
drop table T
go
select top 5 ID,Name into T from sysobjects
go
方法1:

-- 创建输出游标变量的存储过程:

create procedure P_cursor(
@Roy_Test cursor varying output
)
as

set @Roy_Test = cursor global for
select
ID,Name
from
T
open @Roy_Test
if @@error <> 0
return 1
go

-- 调用的存储过程:

create procedure P_cursor2
as
declare @Roy_Test cursor ,
@ID int , @Name sysname

exec P_cursor @Roy_Test = @Roy_Test output

IF Cursor_Status ( ' variable ' , ' @Roy_Test ' ) <= 0
return 1

fetch next from @Roy_Test into @ID , @Name
while @@fetch_status = 0
begin
print ' ID= ' + rtrim ( @ID ) + ' ,Name= ' + @Name
fetch next from @Roy_Test into @ID , @Name
end
close @Roy_Test
deallocate @Roy_Test
go


exec P_cursor2 -- 查看结果

/**/ /*
ID=1,Name=sysobjects
ID=2,Name=sysindexes
ID=3,Name=syscolumns
ID=4,Name=systypes
ID=6,Name=syscomments

*/


go
create procedure P2_cursor( @Flag bit )
as
declare @roy_cursor cursor ,
@ID int , @Name sysname
if @Flag = 0
set @roy_cursor = Roy_cursor
else
return 1 -- 可以定义其它游标
open @roy_cursor
fetch @roy_cursor into @ID , @Name
while @@fetch_status = 0
begin
print ' ID= ' + rtrim ( @ID ) + ' ,Name= ' + @Name
fetch @roy_cursor into @ID , @Name
end
close @roy_cursor
if @Flag = 0
deallocate Roy_cursor
else
return 1 -- --可以定义其它游标

go
create procedure P2_cursor2
as
exec ( ' declareRoy_cursorcursorglobalfor
selectID,NamefromT
' )
declare @error int
set @error = @@error
if @error = 0
exec P2_cursor @Flag = 0
else
return @error
go

exec P2_cursor2 -- 查看结果

/**/ /*
ID=1,Name=sysobjects
ID=2,Name=sysindexes
ID=3,Name=syscolumns
ID=4,Name=systypes
ID=6,Name=syscomments

*/



删除测试:
-- droptableT
--
dropprocP_cursor,P_cursor2,P2_cursor,P2_cursor2
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值