Cursores (SQL Server)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

As operações em um ato de banco de dados relacional em um conjunto completo de linhas. Por exemplo, o conjunto de linhas retornado por uma instrução SELECT consiste de todas as linhas que satisfazem as condições na cláusula WHERE da instrução. Esse conjunto completo de linhas retornado pela instrução é conhecido como conjunto de resultados. Aplicativos, especialmente aplicativos online interativos, não podem sempre trabalhar efetivamente com todo o conjunto de resultados como uma unidade. Esses aplicativos precisam de um mecanismo para trabalhar com uma linha ou um bloco pequeno de linhas de cada vez. Os cursores são uma extensão dos conjuntos de resultados que proveem esse mecanismo.

Os cursores estendem o resultado de processamento por:

• Permitirem o posicionamento em linhas específicas do conjunto de resultados.

• Recuperarem uma linha ou bloco de linhas de posições atuais em um conjunto de resultados.

• Oferecerem suporte às modificações de dados nas linhas da posição atual no conjunto de resultados.

• Oferecerem suporte a diferentes níveis de visibilidade às mudanças feitas por outros usuários ao banco de dados que são apresentados no conjunto de resultados.

• Fornecerem instruções Transact-SQL em scripts, procedimentos armazenados, e acionarem o acesso de gatilho aos dados em um conjunto de resultados.

Comentários

Em alguns cenários, se houver uma chave primária em uma tabela, um loop WHILE poderá ser usado em vez de um cursor, sem implicar a sobrecarga de um cursor.

No entanto, há cenários em que os cursores não são apenas inevitáveis, eles são realmente necessários. Quando esse for o caso, se não houver nenhum requisito para atualizar as tabelas baseadas no cursor, use cursores firehose, o que significa cursores de avanço e somente leitura.

Implementações de cursor

O SQL Server dá suporte a três implementações de cursor.

Tipo de cursores

O SQL Server é compatível com quatro tipos de cursor.

Os cursores podem usar tabelas de trabalho tempdb. Assim como as operações de agregação ou classificação que causam despejo, elas implicam custos de E/S e são um possível gargalo de desempenho. Os cursores STATIC usam tabelas de trabalho desde seu início. Para obter mais informações, confira a seção de tabelas de trabalho no Guia da arquitetura de processamento de consultas.

Somente avanço

Um cursor de somente avanço é especificado como FORWARD_ONLY e READ_ONLY e não é compatível com a rolagem. Eles também são chamados cursores firehose e são compatíveis apenas com a busca das linhas em série do início ao fim do cursor. As linhas não são recuperadas do banco de dados até que sejam buscadas. Os efeitos de todas as instruções INSERT, UPDATE e DELETE feitas pelo usuário atual ou confirmadas por outros usuários que afetam as linhas no conjunto de resultados são visíveis como as linhas buscadas pelo cursor.

Como o cursor não pode rolar para trás, a maioria das alterações feitas nas linhas no banco de dados depois que a linha foi buscada não ficam visíveis pelo cursor. Nos casos em que um valor usado para determinar o local da linha dentro do conjunto de resultados é modificado, como a atualização de uma coluna coberta por um índice clusterizado, o valor modificado é visível pelo cursor.

Embora os modelos de cursor de API do banco de dados considerem um cursor de somente avanço um tipo distinto de cursor, o SQL Server não faz isso. O SQL Server considera tanto somente de avanço quanto rolagem como opções que podem ser aplicadas a cursores estáticos, controlados por conjuntos de chaves e dinâmicos. Os cursores Transact-SQL dão suporte a cursores estáticos somente de avanço, controlados por conjuntos de chaves e dinâmicos. Os modelos de cursor de API de banco de dados assumem que os cursores estáticos, controlados por conjunto de chaves e dinâmicos são sempre roláveis. Quando um atributo ou propriedade de cursor de API de banco de dados é definido como somente de avanço, o SQL Server o implementa como um cursor dinâmico somente de avanço.

Estático

O conjunto completo de resultados de um cursor estático é criado em tempdb quando o cursor está aberto. Um cursor estático sempre exibe o conjunto de resultados como ele estava quando o cursor estava aberto. Os cursores estáticos detectam poucas ou nenhuma alteração, porém consomem relativamente poucos recursos durante a rolagem.

O cursor não reflete qualquer alteração feita no banco de dados que afete a associação do conjunto de resultados ou as alterações feitas nos valores nas colunas das linhas que compõem o conjunto de resultados. Um cursor estático não exibe novas linhas inseridas no banco de dados após o cursor ter sido aberto, mesmo se elas corresponderem aos critérios de pesquisa da instrução SELECT do cursor. Se as filas que constituem o conjunto de resultados forem atualizadas por outros usuários, os novos valores de dados não serão exibidos no cursor estático. O cursor estático exibe linhas excluídas do banco de dados após o cursor ter sido aberto. Nenhuma operação UPDATE, INSERT ou DELETE são refletidas em um cursor estático (a menos que o cursor esteja fechado e reaberto), nem mesmo alterações feitas usando a mesma conexão que abriu o cursor.

Como o conjunto de resultados de um cursor estático é armazenado em uma tabela de trabalho em tempdb, o tamanho das linhas do conjunto de resultados não pode exceder o tamanho máximo de linhas para uma tabela do SQL Server.

Para obter mais informações, confira a seção de tabelas de trabalho no Guia da arquitetura de processamento de consultas. Para obter mais informações sobre o tamanho máximo da linha, confira Especificações de capacidade máxima para o SQL Server.

O Transact-SQL usa o termo insensitivo para cursores estáticos. Algumas APIs de banco de dados os identificam como cursores de instantâneo.

Keyset

A associação e a ordem de linhas em um cursor controlado por conjunto de chaves são fixadas quando o cursor é aberto. Os cursores controlados por conjuntos de chaves são controlados por um conjunto exclusivo de identificadores, as chaves, conhecido como o conjunto de chaves. As chaves são criadas a partir de um conjunto de colunas, que identificam exclusivamente as linhas no conjunto de resultados. O conjunto de chaves é o conjunto dos valores de chaves de todas as linhas qualificadas para a instrução SELECT no momento em que o cursor foi aberto. O conjunto de chaves para um cursor controlado por conjunto de chaves é criado em tempdb quando o cursor está aberto.

Dinâmico

Os cursores dinâmicos são o oposto dos cursores estáticos. Cursores dinâmicos refletem todas as alterações feitas nas linhas de seu conjunto de resultados ao rolar pelo cursor. Os valores de dados, a ordem e a associação das linhas do conjunto de resultados podem ser alterados em cada busca. Todas as instruções UPDATE, INSERT e DELETE feitas por todos os usuários são visíveis pelo cursor. Atualizações estarão visíveis imediatamente se forem feitas por meio do cursor usando uma função de API como SQLSetPos ou a cláusula WHERE CURRENT OF do Transact-SQL. As atualizações feitas fora do cursor não são visíveis até serem confirmadas, a menos que o nível de isolamento da transação do cursor esteja definido para ler não confirmadas. Para obter mais informações sobre os níveis de isolamento, confira SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Solicitar um cursor

O SQL Server dá suporte a dois métodos de solicitação de cursor:

• Transact-SQL

  A linguagem Transact-SQL oferece suporte a uma sintaxe para utilizar cursores modelados após a sintaxe de cursor ISO.

• Funções de cursor de interface de programação de aplicativo (API) de banco de dados

  O SQL Server dá suporte à funcionalidade do cursor dessas APIs de banco de dados:

  • ADO (Microsoft ActiveX Data Object)

  • OLE DB

  • ODBC (Open Database Connectivity)

Um aplicativo não deve nunca misturar estes dois métodos de solicitar um cursor. Um aplicativo que use uma API para especificar comportamentos de cursor não deve executar uma instrução Transact-SQL DECLARE CURSOR para também solicitar um cursor Transact-SQL. Um aplicativo deverá apenas executar DECLARE CURSOR se ele tiver definido todos os atributos da API do cursor de volta ao seu padrão.

Se nem um cursor Transact-SQL nem um cursor de API tiverem sido solicitados, o SQL Server usará como padrão o retorno de um conjunto de resultados completo, conhecido como um conjunto de resultados padrão, para o aplicativo.

Processo do cursor

Cursores Transact-SQL e cursores de API têm sintaxe diferente, mas o seguinte processo geral é usado com todos os cursores do SQL Server:

1. Associe um cursor ao conjunto de resultados de uma instrução Transact-SQL e defina as características do cursor, tais como se as filas no cursor podem ser atualizadas ou não.

2. Execute a instrução Transact-SQL para preencher o cursor.

3. Recupere as linhas no cursor que você quer visualizar. A operação para recuperar uma linha ou um bloco de linhas de um cursor é denominada busca. Executar uma série de buscas para recuperar linhas em direção para frente ou para trás é chamado rolagem.

4. Opcionalmente, execute operações de modificação (atualização ou exclusão) na fila da posição atual no cursor.

5. Feche o cursor.

Conteúdo relacionado

• Comportamentos de cursor
• Como os cursores são implementados
• DECLARE CURSOR (Transact-SQL)
• cursores (Transact-SQL)
• funções de cursor (Transact-SQL)
• Procedimentos armazenados de cursor (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)