Transact-SQL: scorrere la lista di cursori utilizzando sp_cursor_list
Ecco un esempio della store procedure sp_cursor_list, che restituisce tutte le informazioni relative ai cursori allocati:
DECLARE C_PIPPO CURSOR KEYSET FOR SELECT * FROM VENDITE
OPEN C_PIPPO
DECLARE C_TOPOLINO CURSOR STATIC FOR SELECT * FROM VENDITE
OPEN C_TOPOLINO
DECLARE C_PAPERINO CURSOR DYNAMIC FOR SELECT * FROM VENDITE
OPEN C_PAPERINO
DECLARE C_PLUTO CURSOR FAST_FORWARD FOR SELECT * FROM VENDITE
OPEN C_PLUTO
DECLARE @CURSORI CURSOR
EXEC master.dbo.sp_cursor_list @cursor_return = @CURSORI OUTPUT, @cursor_scope = 2
FETCH NEXT from @CURSORI
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT from @CURSORI
END
CLOSE @CURSORI
DEALLOCATE @CURSORI
CLOSE C_PAPERINO
DEALLOCATE C_PAPERINO
CLOSE C_TOPOLINO
DEALLOCATE C_TOPOLINO
CLOSE C_PLUTO
DEALLOCATE C_PLUTO
CLOSE C_PIPPO
DEALLOCATE C_PIPPO
Il risultato:
reference_name cursor_name cursor_scope status model concurrency scrollable open_status cursor_row fetch_status column_count row_count last_operation cursor_handle C_PIPPO C_PIPPO 2 1 1 1 1 1 16 -9 4 0 1 180150003 C_TOPOLINO C_TOPOLINO 2 1 1 1 1 1 16 -9 4 0 1 180150005 C_PAPERINO C_PAPERINO 2 1 3 3 1 1 -1 -9 4 0 1 180150007 C_PLUTO C_PLUTO 2 1 4 1 0 1 -1 -9 4 0 1 180150009
Leave a Reply