Artigo
04/01/2012

Cursores Transact-SQL

Os cursores Transact-SQL são usados principalmente em procedimentos armazenados, gatilhos e scripts Transact-SQL nos quais eles tornam o conteúdo de um conjunto de resultados disponível para outras instruções Transact-SQL.

O processo normal para usar um cursor Transact-SQL em um procedimento armazenado ou gatilho é:

Declare variáveis Transact-SQL para conter os dados retornados pelo cursor. Declare uma variável para cada coluna do conjunto de resultados. Declare que as variáveis sejam suficientemente grandes para manter os valores retornados pela coluna e com um tipo de dados que pode ser convertido implicitamente do tipo de dados da coluna.
Associe um cursor Transact-SQL a uma instrução SELECT que usa a instrução DECLARE CURSOR. Uma instrução DECLARE CURSOR também define as características do cursor, como nome do cursor e se o cursor é somente leitura ou de somente avanço.
Use a instrução OPEN para executar a instrução SELECT e popular o cursor.
Use a instrução FETCH INTO para buscar linhas individuais e mover os dados de cada coluna para uma variável especificada. Outras instruções Transact-SQL podem fazer referência a essas variáveis para acessar os valores de dados buscados. Os cursores Transact-SQL não oferecem suporte à busca de blocos de linhas.
Quando você terminar com o cursor, use a instrução CLOSE. Fechar um cursor libera alguns recursos, como o conjunto de resultados de cursor e seus bloqueios na linha atual, mas a estrutura de cursor ainda estará disponível para processamento caso uma instrução OPEN seja editada novamente. Como o cursor ainda está presente, não é possível utilizar outra vez o nome do cursor neste momento. A instrução DEALLOCATE libera completamente todos os recursos alocados no cursor, inclusive o nome de cursor. Depois que um cursor é desalocado, deve-se emitir uma instrução DECLARE para recriar o cursor.

Monitorando a atividade de cursor Transact-SQL

Você pode usar o procedimento armazenado do sistema sp_cursor_list para obter uma lista de cursores visíveis à conexão atual e sp_describe_cursor, sp_describe_cursor_columnse sp_describe_cursor_tables para determinar as características de um cursor.

Após a abertura do cursor, a função @@CURSOR_ROWS ou a coluna cursor_rows retornada por sp_cursor_list ou sp_describe_cursor indica o número de linhas no cursor.

Após cada instrução FETCH, @@FETCH_STATUS é atualizada para refletir o status da última busca. Você também pode obter essas informações de status da coluna fetch_status retornada por sp_describe_cursor. @@FETCH_STATUS relata condições, como busca além da primeira ou última linha do cursor. @@FETCH_STATUS é global para a conexão e é redefinida por todas as buscas em qualquer cursor aberto para a conexão. Se você precisar conhecer posteriormente o status, salve @@FETCH_STATUS em uma variável de usuário antes de executar outra instrução na conexão. Embora a próxima instrução talvez não seja FETCH, poderá ser INSERT, UPDATE ou DELETE com gatilho que dispara instruções FETCH que redefinem @@FETCH_STATUS. A coluna fetch_status, retornada por sp_describe_cursor, é pertinente ao cursor especificado e não é afetada por instruções FETCH que fazem referência a outros cursores. sp_describe_cursor, porém, é afetada por instruções FETCH que fazem referência ao mesmo cursor. Portanto, ainda é necessário ter cuidado com esse uso.

Depois que FETCH é concluída, o cursor será posicionado na linha de busca. A linha de busca é conhecida como linha atual. Se o cursor não tiver sido declarado como cursor somente leitura, você poderá executar uma instrução UPDATE ou DELETE com uma cláusula WHERE CURRENT OF cursor_name para modificar a linha atual.

O nome dado ao cursor pela instrução Transact-SQL DECLARE CURSOR pode ser global ou local. Nomes de cursores globais são referenciados por qualquer lote, procedimento armazenado ou gatilho que executem na mesma conexão. Os nomes de cursores locais não podem ser referenciados fora de lote, procedimento armazenado ou gatilho nos quais o cursor for declarado. Os cursores locais em gatilhos e procedimentos armazenados são, portanto, protegidos contra referências não intencionais fora do procedimento armazenado ou gatilho.

Usando variável de cursor

O Microsoft SQL Server também oferece suporte a variáveis com um tipo de dados cursor. O cursor pode ser associado a uma variável de cursor por qualquer um de dois métodos:

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR;

DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks2008R2.Person.Person;

SET @MyVariable = MyCursor;
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR;

SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks2008R2.Person.Person;
DEALLOCATE MyCursor;

Depois que um cursor for associado a uma variável de cursor, a variável de cursor poderá ser usada em lugar do nome de cursor em instruções Transact-SQL de cursor. Parâmetros de saída de procedimento armazenado também podem ser atribuídos a um tipo de dados cursor e associados ao cursor. Isso permite que os procedimentos armazenados exponham os cursores locais de forma controlada.

Como referenciar cursores Transact-SQL

Os nomes de cursor e variáveis Transact-SQL são referenciados apenas por instruções Transact-SQL; não podem ser referenciados por funções API OLE DB, ODBC e ADO. Por exemplo, se você usar DECLARE CURSOR e OPEN um cursor Transact-SQL, não haverá como usar as funções ODBC SQLFetch ou SQLFetchScroll para buscar a linha do cursor Transact-SQL. Os aplicativos que necessitam de processamento de cursor e que estão usando essas APIs, deveriam usar o suporte do cursor criado na API do banco de dados, em vez de cursores Transact-SQL.

Você pode usar cursores Transact-SQL em aplicativos por meio de FETCH e associando cada coluna retornada por FETCH a uma variável de programa. A Transact-SQL FETCH não oferece suporte a lotes; portanto, essa é a forma menos eficaz de retornar dados para um aplicativo. A busca de cada linha requer viagens de ida e volta ao servidor. É mais eficaz usar a funcionalidade de cursor criada nas APIs do banco de dados que oferecer suporte à busca de lotes de linhas.

Os cursores Transact-SQL são extremamente eficazes quando contidos em procedimentos armazenados e gatilhos. Isso ocorre porque tudo é compilado em um plano de execução no servidor e não há nenhum tráfego de rede associado às linhas de busca.

Cursores Transact-SQL e opções SET

No SQL Server, surge um erro quando uma instrução FETCH é emitida, na qual há uma alteração em valores relativos ao momento em que o cursor foi aberto. Esse erro ocorre em todas as seguintes opções que afetam o plano, ou nas opções exigidas para exibições indexadas e colunas computadas. Para evitar o erro, não altere as opções SET enquanto o cursor estiver aberto.

Opções que afetam plano

ARITHABORT

NUMERIC_ROUNDABORT

FORCEPLAN

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF

ANSI_WARNINGS

ANSI_PADDING

ANSI_NULLS

CONCAT_NULL_YIELDS_NULL

DATEFIRST