Question

请问，在一个SQL Server 查询中能同时使用多个游标么？ 像下面这样： declare YX_cursor cursor scroll fo

declare YX_cursor cursor scroll
for select * from dbo.GetFrom_YX611 order by dbo.GetFrom_YX611.#DEPTH

declare CJ_cursor cursor scroll
for select * from dbo.GetFrom_CJ611 order by dbo.GetFrom_CJ611.#DEPTH

open CJ_cursor
open YX_cursor

fetch next from CJ_cursor
into @CJ_depth,@sp,@gr,@cal,@ac,@den,@lld,@lls,@cnl

fetch next from YX_cursor
into @num,@YX_depth,@yanxing

close CJ_cursor
close YX_cursor

deallocate CJ_cursor
deallocate YX_cursor

go

Answer 1

可以的，SQl中游标也是对记录集进行逐行获取，相当于循环语句

Answer 2

感谢灵感来源，在游标里定义两个游标变量，可以实现对deleted表和inserted表的同时同步调用。
代码如下：
begin
declare @class_noidA char(4), @class_noidB char(4),@spnoidA char(8),@spnoidB char(8)
declare class_cursorA cursor local forward_only
for select class_no,spno from deleted
declare class_cursorB cursor local forward_only
for select class_no,spno from inserted
open class_cursorA
open class_cursorB
fetch next from class_cursorA into @class_noidA,@spnoidA
fetch next from class_cursorB into @class_noidB,@spnoidB
while(@@FETCH_STATUS=0)
begin
使用inserted表里的数据和deleted表里的数据实现操作。
fetch next from class_cursorA into @class_noidA,@spnoidA
fetch next from class_cursorB into @class_noidB,@spnoidB
end
close class_cursorA
close class_cursorB
deallocate class_cursorA
deallocate class_cursorB
end

Answer 3

可以。

追问

不好意思，我刚开始用百度知道，没有金币能给，能再问个问题么，
@@fetch_status 的值在多个游标的状况下是怎么确定的呢？

追答

@@fetch_status返回上一条fetch语句的状态。
例如：
fetch next from CJ_cursor
into @CJ_depth,@sp,@gr,@cal,@ac,@den,@lld,@lls,@cnl
-- 这时 @@fetch_status 是 fetch next from CJ_cursor的状态
fetch next from YX_cursor
into @num,@YX_depth,@yanxing
-- 这时 @@fetch_status 是 fetch next from YX_cursor的状态