DECLARAR CURSOR (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Define os atributos de um cursor do servidor Transact-SQL, como seu comportamento de rolagem e a consulta usada para criar o conjunto de resultados no qual o cursor opera. DECLARE CURSOR aceita uma sintaxe baseada no padrão ISO e uma sintaxe usando um conjunto de extensões Transact-SQL.

Transact-SQL convenções de sintaxe

Syntax

Sintaxe ISO:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ_ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]

Transact-SQL sintaxe estendida:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
    FOR select_statement
    [ FOR UPDATE [ OF column_name [ , ...n ] ] ]
[ ; ]

Arguments

cursor_name

O nome do cursor do servidor Transact-SQL definido. cursor_name devem estar em conformidade com as regras relativas aos identificadores.

INSENSÍVEL

Define um cursor que faz uma cópia temporária dos dados a serem usados pelo cursor. Todas as solicitações para o cursor são respondidas a partir desta tabela temporária em tempdb. Portanto, as modificações da tabela base não são refletidas nos dados retornados pelas buscas feitas nesse cursor, e esse cursor não permite modificações. Quando a sintaxe ISO é usada, se INSENSITIVE for omitida, exclusões confirmadas e atualizações feitas nas tabelas subjacentes (por qualquer usuário) são refletidas em buscas subsequentes.

ROLO

Especifica que todas as opções de busca (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) estão disponíveis. Se SCROLL não estiver especificado em um ISO DECLARE CURSOR, NEXT é a única opção de busca suportada. SCROLL não pode ser especificado se FAST_FORWARD também for especificado. Se SCROLL não for especificado, apenas a opção NEXT de busca estará disponível e o cursor se tornará FORWARD_ONLY.

select_statement

Uma instrução padrão SELECT que define o conjunto de resultados do cursor. As palavras-chave FOR BROWSE, e INTO não são permitidas dentro de select_statement de uma declaração de cursor.

O SQL Server converte implicitamente o cursor em outro tipo se as cláusulas em select_statement entrarem em conflito com a funcionalidade do tipo de cursor solicitado.

READ_ONLY

Impede que as atualizações feitas através deste cursor. O cursor não pode ser referenciado em uma WHERE CURRENT OF cláusula em uma UPDATE instrução or DELETE . Esta opção substitui a capacidade padrão de um cursor a ser atualizado.

ATUALIZAÇÃO [ DE column_name [ ,... n ] ]

Define colunas atualizáveis dentro do cursor. Se OF <column_name> [, <... n> ] for especificado, apenas as colunas listadas permitem modificações. Se UPDATE for especificado sem uma lista de colunas, todas as colunas podem ser atualizadas.

cursor_name

O nome do cursor do servidor Transact-SQL definido. cursor_name devem estar em conformidade com as regras relativas aos identificadores.

LOCAL

Especifica que o escopo do cursor é local para o lote, procedimento armazenado ou gatilho no qual o cursor foi criado. O nome do cursor só é válido dentro deste escopo. O cursor pode ser referenciado por variáveis de cursor locais no lote, procedimento armazenado ou gatilho, ou um parâmetro de procedimento OUTPUT armazenado. Um OUTPUT parâmetro é usado para passar o cursor local de volta para o lote de chamada, procedimento armazenado ou gatilho, que pode atribuir o parâmetro a uma variável de cursor para fazer referência ao cursor após o término do procedimento armazenado. O cursor é implicitamente desalocado quando o lote, procedimento armazenado ou gatilho termina, a menos que o cursor tenha sido passado de volta em um OUTPUT parâmetro. Se ele passar de volta em um OUTPUT parâmetro, o cursor será desalocado quando a última variável que faz referência a ele for desalocada ou sair do escopo.

GLOBAL

Especifica que o escopo do cursor é global para a conexão. O nome do cursor pode ser referenciado em qualquer procedimento armazenado ou lote executado pela conexão. O cursor só é implicitamente desalocado ao desconectar.

Note

Se nenhum dos dois GLOBAL for LOCAL especificado, o padrão será controlado pela configuração da opção padrão para banco de dados de cursor local .

FORWARD_ONLY

Especifica que o cursor só pode avançar e ser rolado da primeira para a última linha. FETCH NEXT é a única opção de busca suportada. Todas as instruções de inserção, atualização e exclusão feitas pelo usuário atual (ou confirmadas por outros usuários) que afetam as linhas no conjunto de resultados ficam visíveis à medida que as linhas são buscadas. Como o cursor não pode ser rolado para trás, no entanto, as alterações feitas nas linhas do banco de dados depois que a linha foi buscada não são visíveis através do cursor. Os cursores somente para encaminhamento são dinâmicos por padrão, o que significa que todas as alterações são detetadas à medida que a linha atual é processada. Isso proporciona uma abertura mais rápida do cursor e permite que o conjunto de resultados exiba as atualizações feitas nas tabelas subjacentes. Embora os cursores somente para frente não ofereçam suporte à rolagem para trás, os aplicativos podem retornar ao início do conjunto de resultados fechando e reabrindo o cursor.

Se FORWARD_ONLY for especificado sem as palavras-chave STATIC, KEYSETou , o DYNAMIC cursor funciona como um cursor dinâmico. Quando FORWARD_ONLY ou SCROLL não são especificados, FORWARD_ONLY é o padrão, a menos que as palavras-chave STATIC, KEYSETou DYNAMIC são especificadas. STATIC, KEYSETe DYNAMIC os cursores padrão para SCROLL. Ao contrário das APIs de banco de dados, como ODBC e ADO, FORWARD_ONLY há suporte com STATICcursores , KEYSETe DYNAMIC Transact-SQL.

ESTÁTICA

Especifica que o cursor sempre exibe o conjunto de resultados como estava quando o cursor foi aberto pela primeira vez e faz uma cópia temporária dos dados a serem usados pelo cursor. Todas as solicitações para o cursor são respondidas a partir desta tabela temporária em tempdb. Portanto, inserções, atualizações e exclusões feitas em tabelas base não são refletidas nos dados retornados por buscas feitas a esse cursor, e esse cursor não deteta alterações feitas na associação, ordem ou valores do conjunto de resultados depois que o cursor é aberto. Os cursores estáticos podem detetar suas próprias atualizações, exclusões e inserções, embora não sejam obrigados a fazê-lo.

Por exemplo, suponha que um cursor estático busca uma linha e outro aplicativo atualiza essa linha. Se o aplicativo buscar novamente a linha do cursor estático, os valores que ele vê permanecerão inalterados, apesar das alterações feitas pelo outro aplicativo. Todos os tipos de rolagem são suportados.

CONJUNTO DE CHAVES

Especifica que a associação e a ordem das linhas no cursor são corrigidas quando o cursor é aberto. O conjunto de chaves que identificam exclusivamente as linhas é incorporado em uma tabela conhecida tempdb como conjunto de chaves. Este cursor fornece funcionalidade entre um cursor estático e um cursor dinâmico na sua capacidade de detetar alterações. Como um cursor estático, ele nem sempre deteta alterações na associação e na ordem do conjunto de resultados. Como um cursor dinâmico, ele deteta alterações nos valores das linhas no conjunto de resultados.

Os cursores controlados por conjunto de chaves são controlados por um conjunto de identificadores exclusivos (chaves) conhecidos como conjunto de chaves. As chaves são construídas a partir de um conjunto de colunas que identificam exclusivamente as linhas no conjunto de resultados. O conjunto de chaves é o conjunto de valores de chave de todas as linhas retornadas pela instrução query. Com cursores controlados por conjunto de teclas, uma chave é criada e salva para cada linha no cursor e armazenada na estação de trabalho cliente ou no servidor. Quando você acessa cada linha, a chave armazenada é usada para buscar os valores de dados atuais da fonte de dados. Em um cursor controlado por conjunto de chaves, a associação ao conjunto de resultados é congelada quando o conjunto de chaves é totalmente preenchido. Depois disso, adições ou atualizações que afetam a associação não fazem parte do conjunto de resultados até que ele seja reaberto.

As alterações nos valores de dados (feitas pelo proprietário do conjunto de chaves ou por outros processos) são visíveis à medida que o usuário percorre o conjunto de resultados:

• Se uma linha for excluída, uma tentativa de buscar a linha retornará um @@FETCH_STATUS de porque a linha excluída aparece como uma lacuna no conjunto de -2 resultados. A chave para a linha existe no conjunto de chaves, mas a linha não existe mais no conjunto de resultados.

• As inserções feitas fora do cursor (por outros processos) só são visíveis se o cursor for fechado e reaberto. Inserções feitas de dentro do cursor são visíveis no final do conjunto de resultados.

• As atualizações de valores de chave de fora do cursor assemelham-se a uma exclusão da linha antiga seguida por uma inserção da nova linha. A linha com os novos valores não está visível e as tentativas de buscar a linha com os valores antigos retornam um @@FETCH_STATUS de -2. Os novos valores são visíveis se a atualização for feita através do cursor especificando a WHERE CURRENT OF cláusula.

Note

Se a consulta fizer referência a pelo menos uma tabela sem um índice exclusivo, o cursor do conjunto de teclas será convertido em um cursor estático.

DINÂMICA

Define um cursor que reflete todas as alterações de dados feitas nas linhas em seu conjunto de resultados à medida que você rola ao redor do cursor e busca um novo registro, independentemente de as alterações ocorrerem de dentro do cursor ou por outros usuários fora do cursor. Portanto, todas as instruções de inserção, atualização e exclusão feitas por todos os usuários são visíveis através do cursor. Os valores de dados, a ordem e a associação das linhas podem ser alterados em cada busca. A ABSOLUTE opção de busca não é suportada com cursores dinâmicos. As atualizações feitas fora do cursor não ficam visíveis até serem confirmadas (a menos que o nível de isolamento da transação do cursor esteja definido como UNCOMMITTED).

Por exemplo, suponha que um cursor dinâmico busca duas linhas e outro aplicativo atualiza uma dessas linhas e exclui a outra. Se o cursor dinâmico buscar essas linhas, ele não encontrará a linha excluída, mas exibirá os novos valores para a linha atualizada.

FAST_FORWARD

Especifica um FORWARD_ONLYcursor , READ_ONLY com otimizações de desempenho habilitadas. FAST_FORWARD não pode ser especificado se SCROLL ou FOR_UPDATE também é especificado. Este tipo de cursor não permite modificações de dados a partir do interior.

Note

Ambos FAST_FORWARD e FORWARD_ONLY podem ser usados na mesma DECLARE CURSOR declaração.

READ_ONLY

Impede que as atualizações feitas através deste cursor. O cursor não pode ser referenciado em uma WHERE CURRENT OF cláusula em uma UPDATE instrução or DELETE . Esta opção substitui a capacidade padrão de um cursor a ser atualizado.

SCROLL_LOCKS

Especifica que as atualizações posicionadas ou exclusões feitas através do cursor têm garantia de êxito. O SQL Server bloqueia as linhas à medida que são lidas no cursor para garantir sua disponibilidade para modificações posteriores. SCROLL_LOCKS não pode ser especificado se FAST_FORWARD ou STATIC também é especificado.

OTIMISTA

Especifica que as atualizações posicionadas ou exclusões feitas através do cursor não terão êxito, se a linha tiver sido atualizada desde que foi lida no cursor. O SQL Server não bloqueia linhas à medida que são lidas no cursor. Em vez disso, ele usa comparações de valores de coluna de carimbo de data/ hora, ou um valor de soma de verificação se a tabela não tiver coluna de carimbo de data/hora , para determinar se a linha foi modificada depois de ser lida no cursor.

Se a linha foi modificada, a tentativa de atualização ou exclusão posicionada falhará. OPTIMISTIC não pode ser especificado se FAST_FORWARD também for especificado.

Se STATIC for especificado junto com o OPTIMISTIC argumento do cursor, a combinação dos dois será implicitamente convertida para o equivalente da combinação de usar STATIC e READ_ONLY argumentos, ou os STATIC e FORWARD_ONLY argumentos.

TYPE_WARNING

Especifica que uma mensagem de aviso é enviada ao cliente quando o cursor é implicitamente convertido do tipo solicitado para outro.

Nenhum aviso é enviado ao cliente quando a combinação de argumentos e OPTIMISTICSTATIC cursor são usados, e o cursor é implicitamente convertido para o equivalente a um STATIC READ_ONLY ou STATIC FORWARD_ONLY cursor. A conversão para READ_ONLY se transforma em um FAST_FORWARD e READ_ONLY cursor a partir da perspetiva de um cliente.

select_statement

Uma instrução padrão SELECT que define o conjunto de resultados do cursor. As palavras-chave COMPUTE, COMPUTE BY, FOR BROWSE, e INTO não são permitidas dentro de select_statement de uma declaração de cursor.

Note

Você pode usar uma dica de consulta em uma declaração de cursor. No entanto, se você também usar a FOR UPDATE OF cláusula, especifique OPTION (<query_hint>) após FOR UPDATE OF.

O SQL Server converte implicitamente o cursor em outro tipo se as cláusulas em select_statement entrarem em conflito com a funcionalidade do tipo de cursor solicitado.

PARA ATUALIZAÇÃO [ DE column_name [ ,... n ] ]

Define colunas atualizáveis dentro do cursor. Se OF <column_name> [, <... n>] for fornecido, apenas as colunas listadas permitem modificações. Se UPDATE for especificado sem uma lista de colunas, todas as colunas poderão ser atualizadas, a menos que a opção de READ_ONLY simultaneidade tenha sido especificada.

Remarks

DECLARE CURSOR Define os atributos de um cursor do Transact-SQL Server, como seu comportamento de rolagem e a consulta usada para criar o conjunto de resultados no qual o cursor opera. A OPEN instrução preenche o conjunto de resultados e FETCH retorna uma linha do conjunto de resultados. A CLOSE instrução libera o conjunto de resultados atual associado ao cursor. A DEALLOCATE instrução libera os recursos usados pelo cursor.

A primeira forma da instrução usa a sintaxe ISO para declarar comportamentos do DECLARE CURSOR cursor. A segunda forma de usos Transact-SQL extensões que permitem definir cursores usando os mesmos tipos de DECLARE CURSOR cursor usados nas funções de cursor da API de banco de dados de ODBC ou ADO.

Não é possível misturar as duas formas. Se você especificar as SCROLL palavras-chave ou INSENSITIVE antes da CURSOR palavra-chave, não poderá usar nenhuma palavra-chave entre as CURSOR palavras-chave e FOR <select_statement> . Se você especificar qualquer palavra-chave entre as CURSOR palavras-chave e FOR <select_statement> , não poderá especificar SCROLL ou INSENSITIVE antes da CURSOR palavra-chave.

Se uma DECLARE CURSOR sintaxe usando Transact-SQL não especificar READ_ONLY, OPTIMISTICou SCROLL_LOCKS, o padrão será o seguinte:

• Se a SELECT instrução não oferecer suporte a atualizações (permissões insuficientes, acesso a tabelas remotas que não oferecem suporte a atualizações e assim por diante), o cursor será READ_ONLY.

• STATIC e FAST_FORWARD cursores padrão para READ_ONLY.

• DYNAMIC e KEYSET cursores padrão para OPTIMISTIC.

Os nomes dos cursores só podem ser referenciados por outras instruções Transact-SQL. Eles não podem ser referenciados por funções de API de banco de dados. Por exemplo, depois de declarar um cursor, o nome do cursor não pode ser referenciado a partir de funções ou métodos OLE DB, ODBC ou ADO. As linhas do cursor não podem ser buscadas usando as funções ou métodos de busca das APIs; as linhas só podem ser obtidas por Transact-SQL FETCH instruções.

Depois que um cursor é declarado, esses procedimentos armazenados do sistema podem ser usados para determinar as características do cursor.

Procedimentos armazenados do sistema	Description
sp_cursor_list	Retorna uma lista de cursores atualmente visíveis na conexão e seus atributos.
sp_describe_cursor	Descreve os atributos de um cursor, como se é um cursor somente para frente ou de rolagem.
sp_describe_cursor_columns	Descreve os atributos das colunas no conjunto de resultados do cursor.
sp_describe_cursor_tables	Descreve as tabelas base acessadas pelo cursor.

As variáveis podem ser usadas como parte do select_statement que declara um cursor. Os valores das variáveis do cursor não são alterados depois que um cursor é declarado.

Permissions

Permissões padrão DECLARE CURSOR para qualquer usuário que tenha SELECT permissões nos modos de exibição, tabelas e colunas usados no cursor.

Limitations

Não é possível usar cursores ou gatilhos em uma tabela com um índice columnstore clusterizado. Essa restrição não se aplica a índices columnstore não clusterizados. Você pode usar cursores e gatilhos em uma tabela com um índice columnstore não clusterizado.

Examples

A. Usar cursor e sintaxe básicos

O conjunto de resultados gerado na abertura deste cursor inclui todas as linhas e todas as colunas da tabela. Esse cursor pode ser atualizado e todas as atualizações e exclusões são representadas em buscas feitas contra esse cursor. FETCH NEXT é a única busca disponível porque a SCROLL opção não é especificada.

DECLARE vend_cursor CURSOR
    FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;

B. Usar cursores aninhados para produzir saída de relatório

O exemplo a seguir mostra como os cursores podem ser aninhados para produzir relatórios complexos. O cursor interno é declarado para cada fornecedor.

SET NOCOUNT ON;

DECLARE @vendor_id INT, @vendor_name NVARCHAR(50),
    @message VARCHAR(80), @product NVARCHAR(50);

PRINT '-------- Vendor Products Report --------';

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    SELECT @message = '----- Products From Vendor: ' +
        @vendor_name

    PRINT @message

    -- Declare an inner cursor based
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR
    SELECT v.Name
    FROM Purchasing.ProductVendor pv, Production.Product v
    WHERE pv.ProductID = v.ProductID AND
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor

    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @product

    IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>'

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product
        END

    CLOSE product_cursor
    DEALLOCATE product_cursor
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor
    INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Conteúdo relacionado

• @@FETCH_STATUS (Transact-SQL)
• FECHAR (Transact-SQL)
• Cursores (Transact-SQL)
• DESALOCAR (Transact-SQL)
• BUSCAR (Transact-SQL)
• SELECIONAR (Transact-SQL)
• sp_configure (Transact-SQL)