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

O que é CURSOR em PL/SQL?

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

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

O cursor é de dois tipos:

• Cursor implícito
• Cursor explícito

Cursor implícito

Sempre que houver Operação DML Quando uma operação ocorre no banco de dados, um cursor implícito é criado, contendo as linhas afetadas por essa operação específica. Esses cursores não podem ser nomeados e, portanto, não podem ser controlados ou referenciados de outro local no 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 e obter maior controle sobre elas. O cursor explícito deve ser definido na seção de declaração do arquivo. Bloco PL/SQLe é criada para a instrução SELECT que precisa ser usada no código.

A seguir estão os passos envolvidos 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 definida na parte de declaração. O nome dessa área de contexto é o mesmo que o nome do cursor.
• Abrindo o cursor: Abrir o cursor instrui o PL/SQL a alocar memória para ele. Isso prepara o cursor para buscar os registros.
• Obtendo dados do cursor: Nesse processo, a instrução SELECT é executada e as linhas obtidas são armazenadas na memória alocada. Essas linhas são chamadas de conjuntos ativos. A obtenção de dados do cursor é uma atividade em nível de registro, o que significa que podemos acessar os dados registro por registro. Cada instrução fetch obtém um conjunto ativo e armazena as informações daquele registro específico. Essa instrução é semelhante a uma instrução SELECT que obtém o registro e o atribui à variável na cláusula INTO, mas não lançará nenhuma exceção. exceções.
• Fechando o cursor: Após todos os registros serem obtidos, precisamos fechar o cursor para que a memória alocada a essa á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 de declaração contém a declaração do cursor e a variável do cursor na qual os dados obtidos serão atribuídos. O cursor é criado para a instrução SELECT que é fornecida na declaração do cursor. Na parte de execução, o cursor declarado é aberto, os dados são obtidos e os dados são fechados.

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 seus usos.

Exemplo explícito de cursor: Neste exemplo, veremos como declarar, abrir, buscar e fechar um cursor explícito. Projetaremos todos os nomes dos funcionários da tabela `emp` usando um cursor. Também usaremos um atributo do cursor para definir o loop que buscará todos os registros do cursor.

A captura de tela abaixo mostra este exemplo explícito de cursor e sua saída em Oracle.

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%ROWCOUNT);
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 com o %tipo ancorado a emp.emp_name.
• Code linha 5: Abrindo o cursor guru99_det.
• Code linha 6: Definindo a instrução de loop básica para buscar todos os registros na tabela emp.
• Code linha 7: Recupera os dados guru99_det e atribui o valor a lv_emp_name.
• Code linha 8: Utilizando o atributo %NOTFOUND do cursor para verificar se todos os registros no cursor foram obtidos. Se todos foram obtidos, retorna TRUE e o controle sai do loop; caso contrário, o controle continua buscando os dados do cursor e os imprime.
• Code linha 10: Condição EXIT para a instrução de loop.
• Code linha 12: Imprima o nome do funcionário obtido.
• Code linha 14: Utilizando o atributo %ROWCOUNT do cursor para encontrar o número total de registros obtidos pelo cursor.
• Code linha 15: Após sair do loop, o cursor é fechado e a memória alocada é liberada.

Instrução FOR Loop Cursor

Um cursor laço FOR Pode ser usado para trabalhar com cursores. Podemos fornecer o nome do cursor em vez de um limite de intervalo na instrução do laço FOR, de modo que o laço percorra do primeiro ao último registro do cursor. A variável do cursor, a abertura do cursor, a busca e o fechamento do cursor são todos realizados implicitamente pelo laço 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 que é fornecida na declaração do cursor. Na parte da execução, o cursor declarado é configurado no laço FOR, e a variável de laço 'I' se comporta como a variável do cursor neste caso.

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

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 laço FOR para o cursor com a variável de laço lv_emp_name.
• Code linha 6: Imprimindo o nome do funcionário em cada iteração do loop.
• Code linha 7: Sair do loop (END LOOP).

Observação: Em um loop FOR com cursor, os atributos do cursor não podem ser usados, uma vez que a abertura, a busca e o fechamento do cursor são feitos implicitamente pelo loop FOR.

Perguntas Frequentes