Oracle PL / SQL Cursor: Implícito, Explícito, Cursor FOR Loop (Exemplo)

O que é CURSOR em PL / SQL?

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

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

Neste tutorial, você aprenderá-

• Cursor Implícito
• Cursor explícito
• Atributos do Cursor
• Instrução FOR Loop Cursor

O cursor é de dois tipos.

• Cursor Implícito
• Cursor explícito

Cursor Implícito

Sempre que qualquer operação DML ocorre no banco de dados, um cursor implícito é criado para conter 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 por meio 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 Abertura

  Abrir o cursor instruirá o PL / SQL a alocar a memória para este cursor. Isso deixará o cursor pronto para buscar os registros.

• Buscando Dados do Cursor

  Neste 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 de nível de registro que significa que podemos acessar os dados de uma forma registro por registro.

  Cada instrução fetch buscará um conjunto ativo e contém 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 Cursor

  Uma vez que todo o registro foi buscado agora, precisamos fechar o cursor para que a memória alocada para esta área de contexto seja liberada.

Sintaxe:

DECLARECURSOR  IS BEGINOPEN ;FETCH  INTO ;… CLOSE ;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 têm 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.


 

 
 
Atributo do Cursor 
 
Descrição 
 

 
 
%ENCONTRADO 
 
Ele retorna o resultado booleano 'TRUE' se a operação de busca mais recente obteve um registro com sucesso, caso contrário, ele retornará FALSE. 
 

 
 
%NÃO ENCONTRADO 
 
Isso funciona de forma oposta a% FOUND e retornará 'TRUE' se a operação de busca mais recente não puder buscar nenhum registro. 
 

 
 
%ESTÁ ABERTO 
 
Retorna o resultado booleano 'TRUE' se o cursor fornecido já estiver aberto, caso contrário, retorna 'FALSE' 
 

 
 
%CONTAGEM DE LINHAS 
 
Ele retorna o valor numérico. Ele fornece a contagem real de registros que foram afetados pela atividade de DML.
 

 

 

Exemplo 1 : Neste exemplo, vamos ver como declarar, abrir, buscar e fechar o cursor explícito.
Vamos projetar 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. 
DECLARECURSOR guru99_det IS SELECT emp_name FROM emp;lv_emp_name emp.emp_name%type;BEGINOPEN guru99_det;LOOPFETCH guru99_det INTO lv_emp_name;IF guru99_det%NOTFOUNDTHENEXIT;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:/
Resultado


Employee Fetched:BBBEmployee Fetched:XXXEmployee Fetched:YYYTotal rows fetched is 3
Explicação do código: 

Linha de código 2 : Declarando o cursor guru99_det para a instrução 'SELECT emp_name FROM emp'.
Linha de código 3 : Declarando a variável lv_emp_name.


Linha de código 5 : abrindo o cursor guru99_det.
Linha de código 6: Configurando a instrução de loop Basic para buscar todos os registros na tabela 'emp'.
Linha de código 7: busca os dados guru99_det e atribui o valor a lv_emp_name.
Linha de código 9: Usando o atributo de cursor '% NOTFOUND' para descobrir se todos os registros no cursor são buscados. Se buscado, ele retornará 'TRUE' e o controle sairá do loop, caso contrário, o controle continuará buscando os dados do cursor e imprimirá os dados.
Linha de código 11: condição EXIT para a instrução de loop.
Linha de código 12: Imprima o nome do funcionário buscado.
Linha de código 14: Usando o atributo de cursor '% ROWCOUNT' para encontrar o número total de registros que foram afetados / buscados no cursor.
Linha de código 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 do 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 cursor, abertura do cursor, busca e fechamento do cursor serão feitos implicitamente pelo loop FOR.


Sintaxe:
DECLARECURSOR  IS ;BEGINFOR I IN 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 de loop 'I' se comportará como variável de cursor neste caso.

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

DECLARECURSOR guru99_det IS SELECT emp_name FROM emp;BEGINFOR lv_emp_name IN guru99_detLOOPDbms_output.put_line(‘Employee Fetched:‘||lv_emp_name.emp_name);END LOOP;END;/

Resultado

Employee Fetched:BBBEmployee Fetched:XXXEmployee Fetched:YYY

Explicação do código:

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

Nota: 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 pelo loop FOR.