Introdução aos Cursores no SQL Server

Cursores no SQL Server

Nesta lição vamos explicar o que são Cursores em Bancos de Dados, e mostrar como utilizar esse recurso em SQL Server.

O que é um Cursor

A declaração SELECT, empregada para realizar consultas em SQL, retorna um conjunto de linhas que chamamos de “resultset“, ou conjunto-resultado. Porém, às vezes é necessário processar dados retornados em uma consulta não em conjuntos, mas uma linha por vez.

Para que isto seja possível, usamos um objeto de banco de dados chamado de “Cursor”.

Os cursores podem ajudar a criar um processo repetível que opere em um registro por vez.

Lembrando que manipular registros no SQL Server um por vez quase sempre irá resultar em uma performance inferior em comparação com a manipulação padrão de dados em grupos (blocos).

Um cursor seleciona um conjunto de dados, busca um registro por vez desse conjunto, e então modifica o registro atual. Ao terminar a ação requerida no registro, o próximo registro é buscado para ser processado. Basicamente, um cursor é um ponteiro para uma linha em um conjunto de resultados ou uma tabela.

Antes de falar sobre operações e modo de funcionamento de cursores, um aviso: Cursores podem ser extremamente lentos. Sempre que possível, tente usar outro tipo de estrutura no lugar de um cursor, como subconsultas, procedimentos armazenados, funções definidas pelo usuário, etc.; o ganho de performance pode variar na faixa de 5 a 10 vezes, ou mais em alguns casos!

Operações de Cursores

Existem diversas operações que devem ser realizadas para que possamos usar um cursor. As operações incluem abrir o cursor, buscar linhas, fechar e desalocar o cursor, entre outras.

Primeiramente, é preciso declarar um cursor para que seja possível usá-lo. Usamos a seguinte sintaxe para realizar a declaração de um cursor no SQL Server:

DECLARE nome_cursor CURSOR [opções]
FOR declaração_SELECT;

Quando abrimos um cursor, a declaração SELECT criada para ele é executada, e o conjunto-resultado da consulta é armazenado em um local na memória do sistema.

Para abrir um cursor em SQL Server e preenchê-lo com dados usamos a seguinte sintaxe:

OPEN nome_cursor

Para recuperar os dados contidos no resultado da consulta do cursor (já aberto), usamos a instrução FETCH, que irá buscar as linhas do resultado uma a uma, como segue:

FETCH direção FROM nome_cursor INTO lista_variáveis

Usamos um laço WHILE para iterar pelas linhas até que o cursor atinja o final do conjunto de dados.

A direção pode ser (dependendo do tipo de cursor) um dos métodos a seguir:

  • FIRST Busca apenas a primeira linha da tabela retornada ao cursor.
  • LAST Busca apenas a última linha da tabela retornada ao cursor.
  • NEXT Busca dados na direção “para frente”.
  • PRIOR Busca dados na direção “para trás”
  • ABSOLUTE n Busca exatamente a linha de número n
  • RELATIVE n Busca os dados de forma incremental ou decremental.

O mais comum é usar a direção NEXT (“próximo registro à frente”), como veremos no exemplo do cursor FORWARD ONLY.

Após terminar de processar todos os registros, o cursor deve ser fechado, de modo que não estará mais disponível para o programa que o executou. Para tal, basta usar a instrução CLOSE:

CLOSE nome_cursor

Após fechá-lo, é necessário liberar a memória que foi alocada a ele para armazenar os resultados de sua consulta. Isso é importante pois, em muitas implementações de bancos de dados, fechar um cursor não libera automaticamente a área de memória utilizada.

Para liberar a memória, devemos desalocá-la usando a operação DEALLOCATE, como segue:

DEALLOCATE nome_cursor

Agora a memória está livre para ser usada por outros processos.

A ilustração abaixo mostra o fluxo das operações de cursor explicadas:

Como funciona um cursor no SQL Server

Sequência de operações em um cursor

Funções de sistema

Além dos comandos apresentados anteriormente, também usamos algumas funções de sistema para gerenciar cursores. As mais importantes são:

  • Função @@CURSOR_ROWS – Retorna o número de linhas no cursor.
  • Função @@FETCH_STATUS – Reporta o estado do cursor após o último comando FETCH executado. Os estados são os seguintes:
    • 0: O último FETCH obteve uma linha com sucesso
    • -1:O último FETCH falhou pois alcançar o final do conjunto de dados, ou falhou por outro motivo não especificado.
    • -2: A última linha buscada não estava disponível – a linha foi excluída.

Tipos de Cursores

Existem alguns tipos básicos de cursores no SQL Server, tais como:

  • Forward-Only (cursor padrão)
  • Estático
  • Keyset
  • Dinâmico

Vamos explicar do que se tratam esses tipos de cursores, e ver um exemplo completo de uso do cursor Forward-Only, que é o mais comum.

Cursor Forward-Only

Neste tipo de cursor os dados somente podem ser buscados em uma direção. É possível realizar as operações padrão insert, update e delete nos dados.

Exemplo

SET NOCOUNT ON;

DECLARE @idLivro SMALLINT, @nomeLivro VARCHAR(50), @mensagem VARCHAR(60);

PRINT '----Listagem de Livros----';

DECLARE cursor_livros CURSOR FORWARD_ONLY
FOR
SELECT ID_Livro, Nome_Livro FROM tbl_livros
ORDER BY ID_Livro;

OPEN cursor_livros

FETCH NEXT FROM cursor_livros -- NEXT busca o próximo registro "á frente"
INTO @idLivro,@nomeLivro

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT ' '
 SELECT @mensagem = '---Dados do livro ' + @nomeLivro + '---'
 PRINT @mensagem

 SELECT L.Nome_Livro Livro, L.Preco_Livro 'Preço', A.Nome_AUTOR + ' ' + A.SOBRENOME_Autor Autor, E.Nome_Editora Editora
 FROM tbl_livros L
  JOIN tbl_autores A
  ON L.ID_Autor = A.ID_Autor
  JOIN tbl_editoras E
  ON L.ID_editora = E.ID_Editora
 WHERE L.ID_Livro = @idLivro

 FETCH NEXT FROM cursor_livros
 INTO @idLivro,@nomeLivro

END
CLOSE cursor_livros;
DEALLOCATE cursor_livros;

-- Comparar com consulta sem cursores:
SELECT L.Nome_Livro Livro, L.Preco_Livro 'Preço', A.Nome_AUTOR + ' ' + A.SOBRENOME_Autor Autor, E.Nome_Editora Editora
FROM tbl_livros L
JOIN tbl_autores A
ON L.ID_Autor = A.ID_Autor
JOIN tbl_editoras E
ON L.ID_editora = E.ID_Editora;

Cursor Estático

Algumas vezes precisamos nos mover para frente e para trás ao executar um cursor. Quando usamos um cursor estático, o conjunto de resultados não é alterado após o cursor ter sido aberto. Este tipo de cursor nos dá a opção de ser somente-leitura ou de permitir leitura e escrita de dados. Em um cursor somente-leitura, os dados não podem ser modificados – se você tentar modificá-los, não existe a garantia de que o cursor irá retornar esses dados alterados.

Ou seja, as operações update, insert e delete não modificam o conjunto de dados de um cursor estático, a menos, é claro, que o cursor seja fechado e reaberto após a realização dessas operações.

Cursor Keyset

Ao definir um novo cursor, pode ser que haja um conjunto de colunas que criam, em conjunto, uma entrada única. Caso seja possível identificar essas colunas, e se você precisar interagir com linhas que foram alteradas, usar esse tipo de cursor é uma opção interessante.

O “keyset” é um “conjunto de chaves” derivadas deste conjunto único de colunas – são identificadores únicos. Aqui, o cursor pode se mover para frente e para trás. É sensível a alterações na fonte de dados, e suporta as operações triviais de atualização e exclusão de dados.

Cursor Dinâmico

Um cursor dinâmico permite visualizar as alterações realizadas nos dados pelas operações insert, update e delete enquanto o cursor está aberto, sendo, desta forma, sensível a atualizações na fonte dos dados.

Perguntas e Respostas

  1. É recomendável usar um cursor para realizar consultas em uma tabela?
    No geral, não. Sempre que possível, tente escrever suas consultas ou outras declarações SQL utilizando técnicas que não envolvam a iteração linha a linha em um conjunto de dados.
  2. Quais os cinco comandos básicos requeridos para criar e usar um cursor no SQL Server?
    Os comandos são: DECLARE, OPEN, FETCH, CLOSE e DEALLOCATE.
  3. Quais os tipos de cursores suportados pelo SQL Server?
    O SQL Server suporta cursores dos tipos Forward-Only, Estático (Static), Dinâmico (Dynamic) e Keyset.

Referências

  • Nielsen, P.; White, M.; Parui, U. Microsoft SQL Server 2008 Bible. Editora Wiley
  • Plew, R. R.; Stephens, R. K. Aprenda em 24 horas SQL. Editora Campus. 2ª Edição.

 

Sobre Fábio dos Reis (1192 Artigos)
Fábio dos Reis trabalha com tecnologias variadas há mais de 30 anos, tendo atuado nos campos de Eletrônica, Telecomunicações, Programação de Computadores e Redes de Dados. É um entusiasta de Ciência e Tecnologia em geral, adora Viagens e Música, e estuda idiomas, além de ministrar cursos e palestras sobre diversas tecnologias em São Paulo e outras cidades do Brasil.
Contato: Website

Escreva um comentário

Seu e-mail não será divulgado


*