Cursores
Introdução
As operações com conjuntos de linhas é uma das características que define um banco de dados relacional. Um conjunto pode ser vazio ou poderá conter uma única linha, mas ainda assim será um conjunto. Isso é útil e necessário para operações relacionais e algumas vezes inconvenientes para os aplicativos.
Apesar da expansão do SQL fornecida pelo T-SQL para tornar muito mais programável, ainda existem operações de alto cost, difíceis ou simplesmente impossíveis de se realizar em conjuntos.
Os cursores ajudam a lidar com algumas destas situações, pois é um objeto que aponta para uma determinada linha dentro de um conjunto. Podendo assim executar operações como: atualizar, excluir ou mover dados.
Entendendo os Cursores
O SQL Server suporta dois tipos de cursores: os cursores T-SQL e os cursores API (Application Programming Interface). Neste artigo falarei dos cursores do tipo T-SQL, isso porque para utilizarmos um cursor API teremos que ter a documentação apropriada de cada API que será utilizada, ou seja, os cursores do tipo API são específicos de cada API.
Os cursores T-SQL são criados usando o comando DECLARE CURSOR. Quando estamos utilizando um cursor a partir de uma aplicação conectada a um servidor Microsoft SQL Server, cada operação no cursor requer uma viagem de ida e volta através da rede.
O conjunto de linhas para o qual um cursor aponta é definido pelo comando SELECT. Existem algumas restrições SELECT ao se criar um cursor T-SQL:
- Não poderá retornar vários conjuntos de linhas.
- Não poderá conter a cláusula INTO para criar uma nova tabela.
- Não poderá conter a cláusulas COMPUTE ou COMPUTE BY, contudo poderá conter funções agregadas, tais como AVG.
Vamos analisar algumas características dos cursores e para não confundir muito dividirei em três grupos de cursores pelas suas características:
- Capacidade de refletir alterações em dados subjacentes.
- Capacidade de rolar pelo conjunto de linhas.
- Capacidade de atualizar o conjunto de linhas.
Refletindo alterações
Se criarmos um cursor com a instrução abaixo:
SELECT * FROM rg_cidade WHERE nmcidade LIKE '%i%'
O banco de dados irá retornar algo similar à imagem abaixo.
Imagine se alguém alterar a linha do registro ‘Farroupilha’ o que acontecerá com o conjunto de dados apontados pelo cursor?
Existem dois tipos de reflexos que podem ser determinados separadamente quando você criar o seu cursor:
- Alterações nas quais as linhas estejam incluídas no conjunto
- Alterações nos valores das linhas subjacentes.
Rolagem
Outra característica é se você poderá utilizar o cursor para rolar para frente e para trás ou somente para frente. Aqui encontraremos o velho dilema velocidade X flexibilidade. Os cursores que vão apenas para frente são significativamente mais rápidos, mas menos flexíveis.
Atualização
Por último, é se as linhas podem ser atualizadas pelo cursor. Mais uma vez, os cursores de somente leitura geralmente são mais eficientes, porem menos flexíveis.
Tipo de cursor
O T-SQL suporta quatro tipos de cursor diferentes:
- Static
- Keyset
- Dynamic
- Firehose
Cada tipo de cursor armazena os dados de maneira diferente e cada um suporta diferentes tipos de combinações de características descritas anteriormente. Veja a descrição de cada tipo de cursor abaixo:
Static: os cursores static fazem uma cópia dos dados especificados pela instrução SELECT e a armazena no banco de dados tempdb. Este tipo de cursor não permite alterações nos valores de associação ou dados, como qualquer atualização refletiria apenas uma cópia, este tipo é sempre somente leitura. Static podem, contudo ser declarados como forward-only (apenas para frente) ou scrollable (roláveis).
Keyset: um cursor keyset copia para o tempdb apenas as colunas necessárias para identificar exclusivamente cada linha. Para declarar um cursor deste tipo, cada tabela envolvida na instrução SELECT de definição deverá ter um índice exclusivo que defina o conjunto de chaves a ser copiado.
Os cursores keyset podem ser updatable (atualizáveis) ou read-only (somente-leitura) e também scrollable (roláveis) ou forward-only (apenas para frente).
Um cursor keyset é fixado quando você declara o cursor. Se uma linha que satisfaça as condições selecionadas for adicionada enquanto o cursor estiver aberto ela não será adicionada ao conjunto de dados.
Embora a associação na definição do cursor seja fixada quando você abre o cursor, as alterações aos valores de dados nas tabelas subjacentes geralmente são refletidas. Por exemplo, as alterações para o valor nmcidade da linha “Farroupilha” seriam retornadas pelo cursor. As alterações para os valores definidos por chaves, contudo refletirão no cursor apenas se forem feitas pelo cursor. Para continuar o exemplo anterior, se o valor fosse alterado pelo cursor, este retornaria, então, o valor alterado. Porém, se a alteração fosse feita por outro usuário, este cursor continuará retornando o valor anterior.
Dynamic: um cursor dynamic comporta-se como se uma instrução SELECT fosse lançada novamente sempre que uma linha fosse referenciada. Os cursores Dynamic refletem as alterações de valor tanto da associação quanto dos dados subjacentes, quer essas alterações tenham sido feitas pelo cursor ou por qualquer outro usuário.
Firehose: Esse tipo de cursor é declarado usando FAST_FORWARD, mas é mais conhecido como um cursor firehose. Existem duas restrições importantes quanto ao seu uso.
- Se a instrução SELECT que define o cursor faz referencia a colunas do tipo text, ntext ou image e contiver a cláusula TOP, o SQL Server converterá o cursor firehose em um cursor keyset.
- Se a instrução SELECT combinar tabelas que contenham triggers o cursor será convertido para static.
Criando cursores
Para criar um cursor utilizamos a instrução DECLARE CURSOR. A sintaxe da instrução DECLARE CURSOR copiada do Books Online é:
Veja abaixo a explicação para cada linha da instrução acima:
- É usado para definir o escopo do cursor assim como funciona em tabelas temporárias (@local ou @@global).
- Indica a rolagem a ser definida para o cursor e aceita as palavras-chaves: FORWARD_ONLY e SCROLL.
- Usado para definir o tipo do cursor a ser criado: STATIC, KEYSET, DYNAMIC e FAST_FORWARD.
- Indica o tipo de bloqueio, se as linhas poderão ser atualizadas pelo cursor e, se assim for, se outros usuários também poderão atualizá-los.
- Este parâmetro instrui o SQL Server para enviar uma mensagem de aviso para o cliente se um cursor for convertido do tipo especificado em outro tipo.
- Específica às linhas a serem incluídas no conjunto do cursor.
- Este parâmetro é opcional, por padrão os cursores são atualizáveis a não ser que o parâmetro de bloqueio seja READ_ONLY. Neste parâmetro podem-se especificar as linhas que permitem a atualização. Se forem omitidas todas as colunas na instrução serão atualizáveis.
Variáveis de cursor
O T-SQL permite declarar variáveis do tipo CURSOR. A sintaxe DECLARE padrão não cria o cursor, para isso use o SET a variável explicitamente. Este tipo de sintaxe é útil quando desejamos criar variáveis que possam ser atribuídas a diferentes cursores, o que poderá fazer se criar um procedimento genérico que opere em vários conjuntos de resultados.
Abrir um cursor
A declaração de um cursor cria um objeto cursor, mas não cria o conjunto de linhas que serão manipuladas pelo cursor. O conjunto do cursor não será criado até que se abra o cursor.
OPEN [GLOBAL] cursor_ou_variável
Feche um cursor
Após ter terminado de usar um cursor, devemos fechá-lo. A instrução CLOSE libera os recursos usados para manter o conjunto do cursor e também liberta quaisquer bloqueios que tenham sido colocados nas linhas se tiver usado parâmetros como: SCROLLOCKS.
CLOSE [GLOBAL] cursor_ou_variável
Desalocar um cursor
Na seqüência de criação de um cursor o DEALLOCATE é o último comando. Sua sintaxe é parecida com os comandos anteriores:
DEALLOCATE [GLOBAL] cursor_ou_variável
Este comando remove o identificador do cursor e não o cursor ou variável. O cursor não será removido até que os identificadores sejam desalocados ou fiquem fora do escopo.
Manipulando linhas com um cursor
O T-SQL suporta três comandos diferentes para trabalhar com cursores: FETCH, UPDATE e DELETE.
O comando FETCH recupera uma linha especifica do conjunto do cursor. Em sua forma mais simples, o comando FETCH possuí a seguinte sintaxe:
FETCH cursor_ou_variável
Um comando FETCH simples
Abaixo veremos um cursor criado para retornar o primeiro registro da tabela de cidades.
Podemos também utilizar o FETCH para armazenar o resultado em uma variável utilizando o FETCH cursor INTO variável. Veja o exemplo abaixo:
Além disso, podemos utilizar o FETCH para com uma combinação de palavras chaves, por exemplo:
- FETCH FIRST – retorna a primeira linha da variável.
- FETCH NEXT – retorna a linha seguinte.
- FETCH PRIOR – retorna a linha anterior.
- FETCH RELATIVE n – retorna a linha n.
- FETCH ABSOLUNT n – pode especificar linhas antes da linha atual.
FETCH NEXT com um cursor firehose
Atualizando linhas com um cursor
Desde que o cursor seja atualizável, alterar os valores subjacentes em um conjunto do cursor é bastante simples. Vejamos o exemplo abaixo:
Observe que este script irá retornar duas consultas, uma com o valor inicial do cursor e outro é o SELECT depois do UPDATE.
Monitoramento dos cursores
O T-SQL fornece duas variáveis globais e uma função para auxiliar a compreender o que está acontecendo com os cursores, @@CURSOR_ROWS que retorna o número de linhas no cursor aberto por último pela conexão, abaixo veremos uma tabela com os valores que podem ser retornados pela @@CURSOR_ROWS.
-m |
O cursor ainda não foi completamente preenchido. |
-1 |
O cursor é dinâmico e o número de linhas pode variar. |
0 |
Ou nenhum cursor foi aberto ou o mais recente não foi fechado e liberado ou o cursor contem 0 linhas. |
N |
O número de linhas no cursor. |
@@FETCH_STATUS retorna informações sobre o último comando FETCH que foi lançado, a tabela baixo mostra os valores de retorno para @@FETCH_STATUS.
0 |
O FETCH foi realizado com sucesso. |
-1 |
O FETCH falhou. |
-2 |
O registro trazido foi perdido. |
CURSOR_STATUS é uma função T-SQL que possuí a seguinte sintaxe:
CURSOR_STATUS (tipo, cursor_ou_variável)
O tipo pode ser: local, global ou variable. Os resultados da função seguem na tabela abaixo:
1 |
Se a função for chamada para um cursor dynamic, o conjunto do cursor possuirá zero, uma ou mais linhas. Se a função for chamada para outro tipo de cursor, ela possuirá ao menos uma linha. |
0 |
Conjunto de cursores está vazio. |
-1 |
O cursor está fechado. |
-2 |
É retornado apenas para as variáveis de cursor. Ou o cursor atribuído à variável especificada está fechado ou nenhum cursor foi atribuído à variável ainda. |
-3 |
O cursor ou variável de cursor especificada não existe. |
Conclusão
No dia-a-dia os cursores facilitam o desenvolvimento e facilitam o trabalho em conjunto de dados, ou seja, dados que não poderiam ser manipulados somente utilizando cláusulas básicas do SQL.
Entretanto, os cursores são uma “faca de dois gumes”. Tudo o que ele provém de facilidade, pode também converter-se em cost. Um exemplo clássico é uma consulta com um cursor em relação a um comando SELECT. O SELECT tem menos cost e o resultado final será o mesmo.
Partindo desta premissa recomendo utilizar cursores quando realmente sejam imprescindíveis.
Web site: www.crespi.pro.br
email: rodrigo@crespi.pro.br
