Cursores PL/SQL
Autor: Prof. Paulo Kaupa
Ao final deste artigo, o leitor terá aprendido o que são cursores e a necessidade do uso destes elementos, o leitor também será capaz de diferenciar os tipos de cursores existentes e então declarar e controlá-los com o uso do PL/SQL.
Este artigo apresenta exemplos da aplicação de cursores e também propoe exercícios teóricos e práticos envolvendo cursores para auxiliar no aprendizado deste tema.
Os Cursores são áreas compostas de linhas e colunas em memória que servem para armazenar o resultado de uma seleção que retorna 0 (zero) ou mais linhas.
Quando existir a necessidade de inserir em váriavies os dados disponíveis em uma tabela, é comum o uso do comando INTO (Colocar link para o artigo que fala sobre INTO), porém com o uso do INTO esta tarefa fica limitada a apenas um registro por vez, caso a consulta retornar mais que uma linha um erro será gerado indicando que muitas linhas foram retornadas (TOO_MANY_ROWS). Com o uso de cursores é possível trazer de uma só vez todos os dados de uma ou mais tabelas.
Na linguagem PL/SQL, os cursores podem ser de dois tipos:
- Implícitos.
- Explícitos.
Os Cursores do tipo Implícito são de uso interno do Oracle para o processamento das instruções SQL. Toda instrução SQL que retorna dado(s) e que não esteja associada a um cursor explicito (exemplo consulta SELECT) tem suas linhas de retorno armazenadas em um cursor do tipo Implícito que é gerenciado exclusivamente pelo Oracle.
Os Cursores do tipo Explícito devem ser declarados na seção DECLARE de seu Bloco de Códigos (clique aqui para saber mais sobre Blocos de Código PL/SQL)
As linhas resultam do retorno de uma consulta recebem o nome de Conjunto Ativo. Quando o conjunto ativo é armazenado em um Cursor pode-se manipular este conjunto conforme for necessário, possibilitando total controle sobre os dados presentes em cada linha.
Para a utilização de Cursores em um bloco de código PL/SQL, os passos a seguir precisam ser seguidos:
- Declare o cursor na seção DECLARE do bloco de código PL/SQL.
- Escolha um nome sugestivo para o cursor, precedido de c_. Por convenção os nomes para Cursores devem ser precedidos pelo prefixo c_. Um cursor que irá armazenar dados da tabela TB_ALUNO pode ser nomeado como c_aluno.
Nota: O nome do cursor não pode ser o mesmo nome da tabela.
- Para usar o cursor declarado é necessário abri-lo com o uso da instrução OPEN, nesta etapa a consulta será executada e conjunto ativo associado ao cursor.
- A instrução FETCH possibilita a extração dos dados do conjunto ativo associado ao cursor, uma linha de cada vez.
- Após terminar o trabalho com o cursor use a instrução CLOSE que fecha o cursor, retirando os dados da memória temporária do computador onde a instrução está sendo executada.
A figura a seguir ilustra as linhas descritas anteriormente:
Fonte: Material D73724 - Oracle DataBase: Fundamentos de PL/SQL. Guia do Aluno.
Sintaxe para a declaração de um cursor:
CURSOR nome_do_cursor IS
Consulta_que_será_executada;
Exemplo 1:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_aluno IS
SELECT
ID_ALUNO,
NOME_ALUNO
FROM
TB_ALUNO;
v_idAluno NUMBER(38);
v_nomeAluno VARCHAR2(40);
BEGIN
OPEN c_aluno;
FETCH c_aluno INTO v_idAluno, v_nomeAluno;
DBMS_OUTPUT.PUT_LINE (v_idAluno || ' - ' ||v_nomeAluno);
CLOSE c_aluno;
CLOSE c_aluno;
END;
No exemplo 1, um cursor foi declarado com o nome c_aluno e receberá o conjunto ativo resultado da consulta feita na tabela TB_ALUNO. Note que foram declaradas variáveis para a manipulação do conjunto ativo inserido no cursor. Este exemplo usa uma consulta SELECT simples, mas é permitido o uso de JOINS, cláusulas WHERE e ORDER BY entre outras disponíveis para a consulta, apenas o comando INTO não é permitido, este é usado junto à instrução FETCH.
Caso o conjunto ativo tenha mais que uma linha de dados o código apresentado no Exemplo 1 irá apenas imprimir na tela a primeira linha, isso acontece por que a instrução FETCH armazena uma linha de cada vez, é necessário criar um laço para que todas as linhas de uma tabela sejam recuperadas e exibidas na tela.
Observação: As variáveis que receberão os dados do conjunto ativo devem ser declaradas com o mesmo tipo de dados que o campo correspondente na tabela.
Exemplo 2:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_aluno IS
SELECT
ID_ALUNO,
NOME_ALUNO
FROM
TB_ALUNO;
v_idAluno NUMBER(38);
v_nomeAluno VARCHAR2(40);
BEGIN
OPEN c_aluno;
LOOP
FETCH c_aluno INTO v_idAluno, v_nomeAluno;
EXIT WHEN c_aluno%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_idAluno || ' - ' ||v_nomeAluno);
END LOOP;
CLOSE c_aluno;
END;
No exemplo 2, foi usado um Loop para varrer todo o conteúdo do conjunto de dados presente no cursor. Note que para a condição de saída do laço usou-se o atributo %NOTFOUND que verifica se foi recuperada alguma linha no FETCH executado imediatamente antes, saindo do laço quando não houver mais linhas a serem exibidas.
Também neste exemplo usou-se a instrução CLOSE que fecha o cursor, liberando memória e também o cursor caso haja a necessidade de usa-lo novamente. Caso o cursor não for fechado o bloco de código será executado corretamente, mas existe um limite máximo padrão para cursores abertos em uma mesma sessão que é de 50 cursores, então é uma boa prática sempre fechar o cursor após seu uso.
1 – Criar uma tabela chamada TB_PRODUTO com os seguintes campos.
- ID_PRODUTO chave primária deve receber valores sequencias vindos de uma SEQUENCE.
- NOME_PRODUTO
- PRECO_PRODUTO
2 – Insira pelo menos três registros na TB_PRODUTO.
3 – Crie um cursor para armazenar todos os produtos cadastrados.
4 – Relacione na tela todos os produtos cadastrados.
5 – O cursor criado nesta atividade é do tipo Implícito ou Explícito?
6 – Qual a diferença entre cursores Implícitos e Explícitos?
7 – Elabore um código que tenha o conjunto de dados inserido em um cursor Implícito.
8 – Qual a função da instrução %NOTFOUND?
