儲存過程中使用cursor

在sqlserver的儲存過程中使用cursor物件的例子

[@more@]create proc p_test
@cu1 cursor varying output,
@cu2 cursor varying output
as
set @cu1=cursor for select top 5 name from sysobjects where xtype='U'
set @cu2=cursor for select top 5 name from sysobjects where xtype='V'
open @cu1
open @cu2
go

--呼叫示例
declare @cu1 cursor,@cu2 cursor,@name sysname
exec p_test @cu1=@cu1 out,@cu2=@cu2 out

print '--------遊標1----------------'
fetch next from @cu1 into @name
while @@fetch_status=0
begin
print @name
fetch next from @cu1 into @name
end
close @cu1
deallocate @cu1

print '--------遊標2----------------'
fetch next from @cu2 into @name
while @@fetch_status=0
begin
print @name
fetch next from @cu2 into @name
end
close @cu2
deallocate @cu2
go

--刪除測試
drop proc p_test

/*--測試結果
--------遊標1----------------
t
by_huikao
by_huikaobukao
B
cj_banji
--------遊標2----------------
syssegments
sysconstraints

--*/