Oracle Cursor PL/SQL: Implícito, Explícito, For Loop com Exemplo

O que é CURSOR em PL/SQL?

Um Cursor é um ponteiro para esta área de contexto. Oracle cria uma área de contexto para processar uma instrução SQL que contém todas as informações sobre a instrução.

PL/SQL permite ao programador controlar a área de contexto através do cursor. Um cursor contém as linhas retornadas pela instrução SQL. O conjunto de linhas que o cursor contém é referido como conjunto ativo. Esses cursores também podem ser nomeados para que possam ser referenciados de outro local do código.

O cursor é de dois tipos.

• Cursor implícito
• Cursor explícito

Cursor implícito

Sempre que ocorre alguma operação DML no banco de dados, é criado um cursor implícito que contém as linhas afetadas, naquela operação específica. Esses cursores não podem ser nomeados e, portanto, não podem ser controlados ou referenciados de outro local do código. Podemos nos referir apenas ao cursor mais recente através dos atributos do cursor.

Cursor explícito

Os programadores podem criar uma área de contexto nomeada para executar suas operações DML para obter mais controle sobre ela. O cursor explícito deve ser definido na seção de declaração do Bloco PL/SQL, e é criado para a instrução 'SELECT' que precisa ser usada no código.

Abaixo estão as etapas envolvidas no trabalho com cursores explícitos.

• Declarando o cursor Declarar o cursor significa simplesmente criar uma área de contexto nomeada para a instrução 'SELECT' que é definida na parte da declaração. O nome desta área de contexto é igual ao nome do cursor.
• Cursor de aberturaAbrir o cursor instruirá o PL/SQL para alocar memória para este cursor. Isso deixará o cursor pronto para buscar os registros.
• Buscando dados do cursorNeste processo, a instrução 'SELECT' é executada e as linhas buscadas são armazenadas na memória alocada. Eles agora são chamados de conjuntos ativos. Buscar dados do cursor é uma atividade em nível de registro, o que significa que podemos acessar os dados registro por registro. Cada instrução de busca buscará um conjunto ativo e conterá as informações desse registro específico. Esta instrução é igual à instrução 'SELECT' que busca o registro e atribui à variável na cláusula 'INTO', mas não lançará nenhuma exceção.
• Fechando o CursorUma vez obtido todo o registro, precisamos fechar o cursor para que a memória alocada para esta área de contexto seja liberada.

Sintaxe

DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

• Na sintaxe acima, a parte da declaração contém a declaração do cursor e a variável do cursor na qual os dados buscados serão atribuídos.
• O cursor é criado para a instrução 'SELECT' fornecida na declaração do cursor.
• Na parte de execução, o cursor declarado é aberto, buscado e fechado.

Atributos do cursor

Tanto o cursor implícito quanto o cursor explícito possuem certos atributos que podem ser acessados. Esses atributos fornecem mais informações sobre as operações do cursor. Abaixo estão os diferentes atributos do cursor e seu uso.

Exemplo de cursor explícito:

Neste exemplo veremos como declarar, abrir, buscar e fechar o cursor explícito.

Projetaremos todos os nomes dos funcionários da tabela emp usando um cursor. Também usaremos o atributo cursor para definir o loop para buscar todos os registros do cursor.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;

BEGIN
OPEN guru99_det;

LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name);
END LOOP;
Dbms_output.put_line(‘Total rows fetched is‘||guru99_det%R0WCOUNT);
CLOSE guru99_det;
END:
/

saída

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY 
Total rows fetched is 3

Code Explicação

• Code linha 2: Declarando o cursor guru99_det para a instrução 'SELECT emp_name FROM emp'.
• Code linha 3: Declarando a variável lv_emp_name.

• Code linha 5: Abrindo o cursor guru99_det.
• Code linha 6: Configurando a instrução de loop Basic para buscar todos os registros na tabela 'emp'.
• Code linha 7: Busca os dados guru99_det e atribui o valor a lv_emp_name.
• Code linha 9: Usando o atributo do cursor '%NOTFOUND' para descobrir se todos os registros no cursor foram buscados. Se buscado, ele retornará 'TRUE' e o controle sairá do loop, caso contrário, o controle continuará buscando os dados do cursor e imprimindo os dados.
• Code linha 11: Condição EXIT para a instrução de loop.
• Code linha 12: Imprima o nome do funcionário obtido.
• Code linha 14: Usando o atributo do cursor '%ROWCOUNT' para encontrar o número total de registros que foram afetados/buscados no cursor.
• Code linha 15: Após sair do loop, o cursor é fechado e a memória alocada é liberada.

Instrução FOR Loop Cursor

A instrução “FOR LOOP” pode ser usada para trabalhar com cursores. Podemos fornecer o nome do cursor em vez do limite de intervalo na instrução do loop FOR para que o loop funcione do primeiro registro do cursor até o último registro do cursor. A variável do cursor, abertura do cursor, busca e fechamento do cursor serão feitas implicitamente pelo loop FOR.

Sintaxe

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
  FOR I IN <cursor_name>
  LOOP
  .
  .
  END LOOP;
END;

• Na sintaxe acima, a parte da declaração contém a declaração do cursor.
• O cursor é criado para a instrução 'SELECT' fornecida na declaração do cursor.
• Na parte de execução, o cursor declarado é configurado no loop FOR e a variável do loop 'I' se comportará como variável de cursor neste caso.

Oracle Exemplo de cursor para loop:
Neste exemplo, projetaremos todos os nomes dos funcionários da tabela emp usando um loop cursor-FOR.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp; 
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name.emp_name);
END LOOP;
END;
/

saída

Employee Fetched:BBB 
Employee Fetched:XXX
Employee Fetched:YYY

Code Explicação

• Code linha 2: Declarando o cursor guru99_det para a instrução 'SELECT emp_name FROM emp'.
• Code linha 4: Construindo o loop 'FOR' para o cursor com a variável de loop lv_emp_name.
• Code linha 5: Imprimindo o nome do funcionário em cada iteração do loop.
• Code linha 8: Saia do ciclo

Observação: No loop Cursor-FOR, os atributos do cursor não podem ser usados, pois a abertura, a busca e o fechamento do cursor são feitos implicitamente por laço FOR.