嵌套游标实现时的@@FETCH_STATUS 值为内层的使得外层无法循环下去,该怎么解决?
当我内层没有数据的时候@@FETCH_STATUS 就等于-1了,再在外层判断@@FETCH_STATUS 的时候就不能再次循环了
------解决方案--------------------/*
这要看游标的嵌套怎么写,下面的写法,就不会出现这样的问题:
*/
declare cursor_001 cursor for ...
open cursor_001
fetch next from cursor_001 into ...
-- 当 @@fetch_status = 0
while @@fetch_status = 0
begin
declare cursor_002 cursor for ...
open cursor_002
fetch next from cursor_002 into ...
while @@fetch_status = 0
begin
...
fetch next from cursor_002 into ...
end
close cursor_002
deallocate cursor_002
-- 内层游标结束,@@fetch_status <> 0
fetch next from cursor_001 into ...
-- 外层游标的 @@fetch_status,并不受内层的影响。
end
close cursor_001
deallocate cursor_001