DECLARE CURSOR (Transact-SQL) (Português)

• 14/03/2017
• 13 minutos para ler
• • c
  • r
  • i
  • M
  • m
  • +6

Aplica-se a: SQL Server (todas as versões suportadas ) Banco de dados SQL do Azure

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

Convenções da sintaxe Transact-SQL

Sintaxe

Argumentos

cursor_name
É o nome do cursor do servidor Transact-SQL definido. cursor_name deve estar de acordo com as regras para identificadores.

INSENSITIVE
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 feitas nas tabelas base não são refletidas nos dados retornados pelas buscas feitas neste cursor, e este cursor não permite modificações. Quando a sintaxe ISO é usada, se INSENSITIVE for omitido, as exclusões e atualizações confirmadas feitas nas tabelas subjacentes (por qualquer usuário) são refletidas nas buscas subsequentes.

SCROLL
Especifica que todas as opções de busca (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) estão disponíveis. Se SCROLL não for 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 de busca NEXT estará disponível e o cursor se tornará FORWARD_ONLY.

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

O SQL Server converte implicitamente o cursor para outro tipo se cláusulas em instrução_selecionar entrarem em conflito com a funcionalidade do tipo de cursor solicitado.

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

cursor_name
É o nome do cursor do servidor Transact-SQL definido. cursor_name deve estar de acordo com as regras para 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 procedimento armazenado OUTPUT parâmetro. Um parâmetro OUTPUT é 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 procedimento armazenado termina. O cursor é desalocado implicitamente quando o lote, procedimento armazenado ou gatilho termina, a menos que o cursor tenha sido passado de volta em um parâmetro OUTPUT. Se for passado de volta em um parâmetro OUTPUT, 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 é apenas desalocado implicitamente na desconexão.

FORWARD_ONLY
Especifica que o cursor só pode se mover para frente e ser rolado de da primeira à última linha. FETCH NEXT é a única opção de busca compatível. 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 são visíveis conforme 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 frente são dinâmicos por padrão, o que significa que todas as alterações são detectadas conforme a linha atual é processada. Isso fornece 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 suportem a 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 o STATIC, KEYSET ou DYNAMIC palavras-chave, o cursor opera como um cursor dinâmico. Quando nem FORWARD_ONLY nem SCROLL é especificado, FORWARD_ONLY é o padrão, a menos que as palavras-chave STATIC, KEYSET ou DYNAMIC são especificados. STATIC, KEYSET e DYNAMIC cursores padrão para SCROLL. Ao contrário de APIs de banco de dados, como ODBC e ADO, FORWARD_ONLY é compatível com STATIC, KEYSET, e DYNAMIC cursores Transact-SQL.

ESTÁTICO
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, as inserções, atualizações e exclusões feitas nas tabelas de base não são refletidas nos dados retornados pelas buscas feitas para este cursor, e este cursor não detecta alterações feitas na associação, ordem ou valores do conjunto de resultados após o cursor ser aberto . Os cursores estáticos podem detectar 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 busque uma linha e outro aplicativo atualize essa linha. Se o aplicativo buscar novamente a linha do cursor estático, os valores que ele vê permanecem inalterados, apesar das alterações feitas pelo outro aplicativo. Todos os tipos de rolagem são suportados.

KEYSET
Especifica que a associação e a ordem das linhas no cursor são fixas quando o cursor é aberto. O conjunto de chaves que identificam exclusivamente as linhas é integrado a uma tabela em tempdb conhecida como conjunto de chaves. Este cursor fornece funcionalidade entre um cursor estático e um cursor dinâmico em sua capacidade de detectar alterações. Como um cursor estático, ele nem sempre detecta alterações na associação e na ordem do conjunto de resultados. Como um cursor dinâmico, ele detecta alterações nos valores das linhas no conjunto de resultados. Os cursores orientados por conjunto de chaves são controlados por um conjunto de identificadores exclusivos (chaves) conhecido 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-chave de todas as linhas retornadas pela instrução de consulta. Com os cursores orientados por conjunto de chaves, uma chave é criada e salva para cada linha do cursor e armazenada na estação de trabalho do 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 do conjunto de resultados é congelada quando o conjunto de chaves está totalmente preenchido. Depois disso, as adições ou atualizações que afetam a associação não fazem parte do conjunto de resultados até que seja reaberto. As alterações nos valores dos dados (feitas pelo proprietário do conjunto de chaves ou outros processos) são visíveis conforme o usuário rola pelo conjunto de resultados:

• Se uma linha for excluída, uma tentativa de buscar a linha retorna um @@FETCH_STATUS de -2 porque a linha excluída aparece como uma lacuna no conjunto de 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ão visíveis apenas se o cursor for fechado e reaberto. As inserções feitas de dentro do cursor são visíveis no final do conjunto de resultados.
• Atualizações de valores-chave de fora do cursor se parecem com a 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 por meio do cursor, especificando a cláusula WHERE CURRENT OF.

DYNAMIC
Define um cursor que reflete todas as alterações de dados feitas em as linhas em seu conjunto de resultados conforme 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, ordem e associação das linhas podem ser alterados em cada busca. A opção de busca ABSOLUTE não é compatível com cursores dinâmicos. As atualizações feitas fora do cursor não são visíveis até que sejam confirmadas (a menos que o nível de isolamento da transação do cursor seja definido como UNCOMMITTED). Por exemplo, suponha que um cursor dinâmico obtenha 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_ONLY, READ_ONLY cursor com otimizações de desempenho habilitadas. FAST_FORWARD não pode ser especificado se SCROLL ou FOR_UPDATE também for especificado. Este tipo de cursor não permite modificações de dados de dentro do cursor.

READ_ONLY
Impede atualizações feitas por meio deste cursor. O cursor não pode ser referenciado em uma cláusula WHERE CURRENT OF em uma instrução UPDATE ou 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 por meio do cursor terão sucesso garantido. 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 for especificado.

OPTIMISTIC
Especifica que as atualizações ou exclusões posicionadas feitas por meio 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 as linhas à medida que são lidas no cursor. Em vez disso, usa comparações de valores de coluna de carimbo de data / hora ou um valor de checksum se a tabela não tiver coluna de carimbo de data / hora, para determinar se a linha foi modificada depois de 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.

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

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

O SQL Server converte implicitamente o cursor em outro tipo se as cláusulas na instrução select entrarem em conflito com a funcionalidade do tipo de cursor solicitado. Para obter mais informações, consulte Conversões de cursor implícitas.

Observações

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

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

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

Se um DECLARE CURSOR usando a sintaxe Transact-SQL não especificar READ_ONLY, OPTIMISTIC ou SCROLL_LOCKS, o padrão é o seguinte:

• Se o SELECT não oferece 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 é READ_ONLY.

• STATIC e FAST_FORWARD cursores padrão para READ_ONLY.

• DYNAMIC e KEYSET cursores padrão para OPTIMISTIC .

Os nomes dos cursores podem ser referenciados apenas 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 podem ser buscadas apenas por instruções Transact-SQL FETCH.

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

Procedimentos armazenados do sistema	Descrição
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 de avanço 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.

Variáveis podem ser usadas como parte da instrução select que declara um cursor. Os valores das variáveis do cursor não mudam depois que um cursor é declarado.

Permissões

As permissões de DECLARE CURSOR são padrão para qualquer usuário que tenha SELECT permissões nas visualizações, tabelas e colunas usadas no cursor.

Limitações e restrições

Você não pode 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.

Exemplos

A. Usando um cursor simples e sintaxe

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

B. Usando 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.

Consulte também