Escopo de nomes de cursor Transact-SQL
O Microsoft SQL Server dá suporte a palavras-chave GLOBAL e LOCAL na instrução DECLARE CURSOR para definir o escopo do nome de cursor. GLOBAL especifica que o nome de cursor é global à conexão. LOCAL especifica que o nome de cursor é LOCAL ao procedimento armazenado, gatilho ou lote que contém a instrução DECLARE CURSOR.
Antes da versão 7.0 do Microsoft SQL Server, os nomes de cursores Transact-SQL eram globais à conexão. Você podia executar um procedimento armazenado que criava um cursor e, depois, podia chamar outro procedimento armazenado que buscava as linhas daquele cursor:
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE OpenCrsr AS
DECLARE SampleCrsr CURSOR FOR
SELECT TOP (20)LastName
FROM Person.Person
WHERE LastName LIKE 'S%';
OPEN SampleCrsr;
GO
CREATE PROCEDURE ReadCrsr AS
FETCH NEXT FROM SampleCrsr
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM SampleCrsr
END
GO
EXEC OpenCrsr; /* DECLARES and OPENS SampleCrsr. */
GO
EXEC ReadCrsr; /* Fetches the rows from SampleCrsr. */
GO
CLOSE SampleCrsr;
GO
DEALLOCATE SampleCrsr;
GO
Cursores locais oferecem proteção importante para cursores implementados em procedimentos armazenados e gatilhos. Cursores globais podem ser mencionados fora do procedimento armazenado ou do gatilho no qual eles são declarados. Consequentemente, eles podem ser alterados inadvertidamente por instruções externas ao procedimento armazenado ou gatilho. Cursores locais são mais seguros do que cursores globais porque não podem ser mencionados fora de um procedimento armazenado, a menos que fosse passado deliberadamente ao chamador por um parâmetro de saída de cursor.
Como cursores globais podem ser mencionados fora de um procedimento armazenado ou gatilho, eles podem ter efeitos colaterais não intencionais que influenciam outras instruções. Um exemplo é um procedimento armazenado que cria um cursor global com nome xyz e permite a abertura do cursor quando é finalizado. Uma tentativa para declarar outro cursor global com nome xyz depois da conclusão do procedimento armazenado falha com um erro de nome duplicado.
Cursores globais e locais têm espaços de nome separados, assim é possível ter um cursor global e um cursor local ao mesmo tempo com o mesmo nome. As instruções Transact-SQL que aceitam um parâmetro de nome de cursor também dão suporte à palavra-chave GLOBAL para identificar o escopo do nome. Se GLOBAL não for especificado e houver um cursor local e global com o nome especificado no parâmetro de nome de cursor, o cursor local será consultado.
A opção do banco de dados CURSOR_DEFAULT, definida com a instrução ALTER DATABASE, controla o padrão assumido pela instrução DECLARE CURSOR se nem LOCAL nem GLOBAL forem especificados. O valor atual para essa opção de banco de dados é armazenado na exibição do catálogo sys.databases. Se o valor na coluna for local_cursor_default na exibição do catálogo sys.databases será true, o padrão dos cursores Transact-SQL será local. Se a opção for false, os cursores Transact-SQL definirão global como padrão. No SQL Server, a própria opção de banco de dados define o padrão como FALSE (GLOBAL) para corresponder ao comportamento de versões anteriores do SQL Server.
Procedimentos armazenados que cursores locais DECLARE e OPEN podem distribuir aos cursores para uso pelo procedimento armazenado de chamada, gatilho ou lote. Isso é feito usando um parâmetro OUTPUT definido com o novo tipo de dados CURSOR VARYING. Só podem ser usadas variáveis de cursor como parâmetros OUTPUT. Elas não podem ser usadas em parâmetros de entrada. O cursor deve estar aberto quando o procedimento armazenado for concluído para ser devolvido em um parâmetro OUTPUT. Também podem ser declaradas variáveis locais com o novo tipo de dados CURSOR para manter uma referência a um cursor local.
USE AdventureWorks2008R2;
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS
SET @OutCrsr = CURSOR FOR
SELECT TOP (20) LastName
FROM Person.Person
WHERE LastName LIKE 'S%';
OPEN @OutCrsr;
GO
/* Allocate a cursor variable. */
DECLARE @CrsrVar CURSOR;
/* Execute the procedure created earlier to fill
the variable. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT;
/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrVar
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @CrsrVar
END;
CLOSE @CrsrVar;
DEALLOCATE @CrsrVar;
GO
As APIs do banco de dados não dão suporte a parâmetros de saída de cursor em procedimentos armazenados. Um procedimento armazenado que contém um parâmetro de saída de cursor não pode ser executado diretamente de uma função API de banco de dados. Esses procedimentos armazenados só podem ser executados de outro procedimento armazenado, um gatilho ou um lote de Transact-SQL ou script.
Um cursor GLOBAL fica disponível até que seja explicitamente desalocado ou quando a conexão é encerrada. Cursores LOCAL são implicitamente desalocados quando o procedimento armazenado, gatilho ou lote nos quais foram criados é encerrado, a menos que o cursor tenha sido passado como um parâmetro. Em seguida, o cursor LOCAL será implicitamente desalocado quando o parâmetro ou a variável que faz referência ao cursor no código que chamou o procedimento sai do escopo.