Cursores (SQL Server)

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

As operações numa base de dados relacional atuam sobre um conjunto completo de linhas. Por exemplo, o conjunto de linhas devolvidas por uma SELECT afirmação consiste em todas as linhas que satisfazem as condições da WHERE cláusula da afirmação. Este conjunto completo de linhas devolvidas pela instrução é conhecido como conjunto de resultados. As aplicações, especialmente as online interativas, nem sempre conseguem funcionar eficazmente com todo o conjunto de resultados como uma unidade. Estas aplicações precisam de um mecanismo para funcionar com uma linha ou um pequeno bloco de linhas de cada vez. Os cursores são uma extensão dos conjuntos de resultados que fornecem esse mecanismo.

Os cursores prolongam o processamento de resultados através de:

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

• Recuperar uma linha ou bloco de linhas da posição atual no conjunto de resultados.

• Suportando modificações de dados nas linhas na posição atual no conjunto de resultados.

• Suportando diferentes níveis de visibilidade às alterações feitas por outros utilizadores aos dados da base de dados apresentados no conjunto de resultados.

• Fornecer instruções Transact-SQL em scripts, procedimentos armazenados e gatilhos para aceder aos dados num conjunto de resultados.

Observações

Em alguns cenários, se houver uma tecla primária numa tabela, pode ser usado um WHILE loop em vez de um cursor, sem incorrer na sobrecarga do cursor.

No entanto, há cenários em que os cursores não só são inevitáveis, como são realmente necessários. Quando isso acontece, se não houver necessidade de atualizar tabelas com base no cursor, então use cursores firehose, ou seja, cursores de avanço rápido e de leitura apenas.

Implementações de cursor

O SQL Server suporta três implementações de cursor.

Tipo de cursores

O SQL Server suporta quatro tipos de cursor.

Os cursores podem usar tempdb mesas de trabalho. Tal como as operações de agregação ou ordenação que transbordam, estas geram custos de entrada/saída (I/O) e constituem um potencial gargalo de desempenho. STATIC Os cursores usam tabelas de trabalho desde o início. Para mais informações, consulte a secção de tabelas de trabalho no guia de arquitetura de processamento de consultas.

Apenas avanço

Um cursor apenas para avançar é especificado como FORWARD_ONLY e READ_ONLY e não suporta scrolling. Estes também são chamados de cursores firehose e suportam apenas a obtenção sequencial das linhas, do início ao fim do cursor. As linhas não são selecionadas da base de dados até serem recuperadas. Os efeitos de todas as INSERT, UPDATE e DELETE instruções feitas pelo utilizador atual ou cometidas por outros utilizadores que afetam linhas no conjunto de resultados são visíveis à medida que as linhas são recolhidas pelo cursor.

Como o cursor não pode ser deslocado para trás, a maioria das alterações feitas às linhas da base de dados depois de a linha ter sido obtida não são visíveis através do cursor. Nos casos em que um valor usado para determinar a localização da linha dentro do conjunto de resultados é modificado, como ao atualizar uma coluna coberta por um índice agrupado, o valor modificado é visível através do cursor.

Embora os modelos de cursor da API da base de dados considerem um cursor apenas para frente como um tipo distinto de cursor, o SQL Server não o faz. O SQL Server considera tanto o modo apenas avançado como o scroll como opções que podem ser aplicadas a cursores estáticos, orientados por keysets e dinâmicos. Transact-SQL cursores suportam cursores estáticos de avanço único, orientados por conjunto de chaves e dinâmicos. Os modelos de cursores da API da base de dados assumem que os cursores estáticos, orientados por keysets e dinâmicos são sempre deslocáveis. Quando um atributo ou propriedade do cursor da API da base de dados é definido apenas para avançar, o SQL Server implementa isto como um cursor dinâmico apenas para frente.

Estático

O conjunto completo de resultados de um cursor estático está incorporado tempdb quando o cursor é aberto. Um cursor estático mostra sempre o conjunto de resultados tal como estava quando o cursor foi aberto. Os cursores estáticos detetam poucas ou nenhumas alterações, mas consomem relativamente poucos recursos enquanto se faz scroll.

O cursor não reflete quaisquer alterações feitas na base de dados que afetem a pertença ao conjunto de resultados ou alterações nos valores nas colunas das linhas que compõem o conjunto de resultados. Um cursor estático não exibe novas linhas inseridas na base de dados depois de o cursor ter sido aberto, mesmo que correspondam às condições de pesquisa da instrução do SELECT cursor. Se as linhas que compõem o conjunto de resultados forem atualizadas por outros utilizadores, os novos valores de dados não são apresentados no cursor estático. O cursor estático mostra as linhas eliminadas da base de dados após a abertura do cursor. Nenhuma operação UPDATE, INSERT, ou DELETE é refletida num cursor estático (a menos que o cursor seja fechado e reaberto), nem mesmo modificações feitas usando a mesma ligação que abriu o cursor.

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

Para mais informações, consulte a secção de tabelas de trabalho no guia de arquitetura de processamento de consultas. Para mais informações sobre o tamanho máximo das linhas, consulte Especificações de capacidade máxima para SQL Server.

Transact-SQL usa o termo insensível para cursores estáticos. Algumas APIs de bases de dados identificam-nos como cursores snapshot .

Conjunto de teclas

A pertença e a ordem das linhas num cursor controlado por conjunto de chaves são fixas quando o cursor é aberto. Os cursores controlados por conjuntos de teclas são controlados por um conjunto de identificadores únicos, ou chaves, conhecidos como conjunto de chaves. As chaves são construídas a partir de um conjunto de colunas que identificam de forma única as linhas do conjunto de resultados. O conjunto de chaves é o conjunto dos valores-chave de todas as linhas que se qualificaram para a declaração SELECT no momento em que o cursor foi aberto. O conjunto de chaves para um cursor controlado por conjuntos de chaves está incorporado tempdb quando o cursor é aberto.

Dynamic

Os cursores dinâmicos são o oposto dos cursores estáticos. Os cursores dinâmicos refletem todas as alterações feitas às linhas do seu conjunto de resultados ao percorrer o cursor. Os valores dos dados, a ordem e a pertença das linhas ao conjunto de resultados podem mudar em cada busca. Todos os enunciados UPDATE, INSERT, e DELETE feitos por todos os utilizadores são visíveis pelo cursor. As atualizações são imediatamente visíveis se forem feitas através do cursor usando uma função API como SQLSetPos ou a cláusula Transact-SQL WHERE CURRENT OF . 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 como read uncommitted. Para mais informações sobre níveis de isolamento, veja DEFINIR O NÍVEL DE ISOLAMENTO DA TRANSAÇÃO (Transact-SQL).

Solicite um cursor

O SQL Server suporta dois métodos para solicitar um cursor:

• Transact-SQL

  A linguagem Transact-SQL suporta uma sintaxe para usar cursores modelada a partir da sintaxe ISO do cursor.

• Funções de cursor da interface de programação de aplicações (API) de base de dados

  O SQL Server suporta a funcionalidade do cursor destas APIs de base de dados:

  • ADO (Objeto de Dados Microsoft ActiveX)

  • OLE DB

  • ODBC (Conectividade de Bases de Dados Abertas)

Uma aplicação nunca deve misturar estes dois métodos de pedido de cursor. Uma aplicação que usa a API para especificar comportamentos do cursor não deve então executar uma instrução Transact-SQL DECLARE CURSOR para também pedir um cursor Transact-SQL. DECLARE CURSOR deve ser executado apenas se todos os atributos do cursor da API forem redefinidos para os seus valores predefinidos.

Se nem um cursor de Transact-SQL nem de API for solicitado, o SQL Server devolve por defeito um conjunto completo de resultados, conhecido como conjunto de resultados por defeito, à aplicação.

Processo do cursor

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

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

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

3. Recupera as linhas no cursor que queres ver. A operação para recuperar uma linha ou um bloco de linhas a partir de um cursor chama-se fetch. Realizar uma série de buscas para recuperar linhas numa direção para a frente ou para trás chama-se scrolling.

4. Opcionalmente, execute operações de modificação (atualização ou eliminação) na linha na posição atual do cursor.

5. Fecha o cursor.

Conteúdo relacionado

• Comportamentos do cursor
• Como os Cursores São Implementados
• DECLARAR CURSOR (Transact-SQL)
• Cursores (Transact-SQL)
• Funções do cursor (Transact-SQL)
• Procedimentos armazenados por cursor (Transact-SQL)
• DEFINIR O NÍVEL DE ISOLAMENTO DA TRANSAÇÃO (Transact-SQL)