Expressões de Cursor no Oracle PL/SQL

Neste conteúdo, abordaremos as Expressões de Cursor no Oracle PL/SQL, um recurso avançado que permite criar cursores aninhados para manipulação eficiente de dados hierárquicos ou relacionados. Veremos como utilizá-las para estruturar consultas de forma clara e otimizada, atendendo a cenários complexos em bancos de dados Oracle.

O que são Expressões de Cursor?

Imagine que você tem uma caixa (cursor principal) que contém várias outras caixinhas (cursores aninhados). Cada caixinha interna está relacionada de alguma forma com a caixa externa. Essa é a essência de uma expressão de cursor.

Uma expressão de cursor tem a seguinte sintaxe:

CURSOR(subquery)

1	CURSOR(subquery)

Esta expressão retorna um cursor aninhado do tipo REF CURSOR.

Exemplo Prático com o Schema HR

Vamos ver um exemplo usando as tabelas do schema HR:

SELECT d.department_name, d.location_id,
       CURSOR(SELECT e.employee_id, e.first_name, e.last_name
              FROM employees e
              WHERE e.department_id = d.department_id)
FROM departments d
WHERE d.department_id IN (10, 20, 30);

SELECT d.department_name, d.location_id,

CURSOR(SELECT e.employee_id, e.first_name, e.last_name

FROM employees e

WHERE e.department_id = d.department_id)

FROM departments d

WHERE d.department_id IN (10, 20, 30);

Neste exemplo, para cada departamento, temos um cursor aninhado que lista os funcionários daquele departamento.

Processando Expressões de Cursor

Agora, vamos ver como processar essas expressões de cursor em PL/SQL:

 DECLARE
    TYPE emp_cursor_type IS REF CURSOR;
    cv_employees emp_cursor_type;
    
    v_dept_name departments.department_name%TYPE;
    v_location_id departments.location_id%TYPE;
    v_emp_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    
    CURSOR dept_cur IS
        SELECT d.department_name, d.location_id,
               CURSOR(SELECT e.employee_id, e.first_name, e.last_name
                      FROM employees e
                      WHERE e.department_id = d.department_id) as emp_cur
        FROM departments d
        WHERE d.department_id IN (10, 20, 30);
BEGIN
    OPEN dept_cur;
    LOOP
        FETCH dept_cur INTO v_dept_name, v_location_id, cv_employees;
        EXIT WHEN dept_cur%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Departamento: ' || v_dept_name || ' (Local ID: ' || v_location_id || ')');
        
        LOOP
            FETCH cv_employees INTO v_emp_id, v_first_name, v_last_name;
            EXIT WHEN cv_employees%NOTFOUND;
            
            DBMS_OUTPUT.PUT_LINE('  ' || v_emp_id || ': ' || v_first_name || ' ' || v_last_name);
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('---');
    END LOOP;
    CLOSE dept_cur;
END;
/

DECLARE

TYPE emp_cursor_type IS REF CURSOR;

cv_employees emp_cursor_type;

v_dept_name departments.department_name%TYPE;

v_location_id departments.location_id%TYPE;

v_emp_id employees.employee_id%TYPE;

v_first_name employees.first_name%TYPE;

v_last_name employees.last_name%TYPE;

CURSOR dept_cur IS

SELECT d.department_name, d.location_id,

CURSOR(SELECT e.employee_id, e.first_name, e.last_name

FROM employees e

WHERE e.department_id = d.department_id) as emp_cur

FROM departments d

WHERE d.department_id IN (10, 20, 30);

BEGIN

OPEN dept_cur;

LOOP

FETCH dept_cur INTO v_dept_name, v_location_id, cv_employees;

EXIT WHEN dept_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Departamento: ' || v_dept_name || ' (Local ID: ' || v_location_id || ')');

LOOP

FETCH cv_employees INTO v_emp_id, v_first_name, v_last_name;

EXIT WHEN cv_employees%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(' ' || v_emp_id || ': ' || v_first_name || ' ' || v_last_name);

END LOOP;

DBMS_OUTPUT.PUT_LINE('---');

END LOOP;

CLOSE dept_cur;

END;

Vamos analisar passo a passo para que você compreenda melhor.

Declaração de Tipo de Cursor Genérico

TYPE emp_cursor_type IS REF CURSOR;

1	TYPE emp_cursor_type IS REF CURSOR;

Define um tipo de cursor genérico chamado emp_cursor_type.
Este é um cursor fraco (weakly typed), que pode ser associado a qualquer consulta SQL.

Variável de Cursor:

cv_employees emp_cursor_type;

1	cv_employees emp_cursor_type;

Declara uma variável de cursor cv_employees do tipo emp_cursor_type.
Esta variável será usada para armazenar o cursor aninhado retornado pela consulta principal.

Variáveis para Armazenar Resultados

v_dept_name departments.department_name%TYPE;
v_location_id departments.location_id%TYPE;
v_emp_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;

v_dept_name departments.department_name%TYPE;

v_location_id departments.location_id%TYPE;

v_emp_id employees.employee_id%TYPE;

v_first_name employees.first_name%TYPE;

v_last_name employees.last_name%TYPE;

Estas variáveis são declaradas usando o atributo %TYPE, que herda o tipo de dados da coluna correspondente na tabela.
Elas serão usadas para armazenar os resultados das consultas.

Declaração do Cursor Explícito principal dept_cur

CURSOR dept_cur IS
    SELECT d.department_name, d.location_id,
           CURSOR(SELECT e.employee_id, e.first_name, e.last_name
                  FROM employees e
                  WHERE e.department_id = d.department_id) as emp_cur
    FROM departments d
    WHERE d.department_id IN (10, 20, 30);

CURSOR dept_cur IS

SELECT d.department_name, d.location_id,

CURSOR(SELECT e.employee_id, e.first_name, e.last_name

FROM employees e

WHERE e.department_id = d.department_id) as emp_cur

FROM departments d

WHERE d.department_id IN (10, 20, 30);

Nesta declaração:

O cursor principal dept_cur seleciona informações de departamentos.
Para cada departamento, ele também define um cursor aninhado (emp_cur) que seleciona informações dos funcionários daquele departamento específico.
O cursor aninhado é correlacionado com o cursor externo através da condição e.department_id = d.department_id.

Processamento do Cursor no Loop Principal

LOOP
    FETCH dept_cur INTO v_dept_name, v_location_id, cv_employees;
    EXIT WHEN dept_cur%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('Departamento: ' || v_dept_name || ' (Local ID: ' || v_location_id || ')');
    
    LOOP
        FETCH cv_employees INTO v_emp_id, v_first_name, v_last_name;
        EXIT WHEN cv_employees%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('  ' || v_emp_id || ': ' || v_first_name || ' ' || v_last_name);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('---');
END LOOP;

LOOP

FETCH dept_cur INTO v_dept_name, v_location_id, cv_employees;

EXIT WHEN dept_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Departamento: ' || v_dept_name || ' (Local ID: ' || v_location_id || ')');

LOOP

FETCH cv_employees INTO v_emp_id, v_first_name, v_last_name;

EXIT WHEN cv_employees%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(' ' || v_emp_id || ': ' || v_first_name || ' ' || v_last_name);

END LOOP;

DBMS_OUTPUT.PUT_LINE('---');

END LOOP;

Agora, vamos entender a relação e como cv_employees recebe seus dados:

Fetch do Cursor Principal:
- A instrução FETCH dept_cur INTO v_dept_name, v_location_id, cv_employees; é crucial.
- Ela recupera uma linha do cursor dept_cur, que contém:
  - O nome do departamento (v_dept_name)
  - O ID da localização (v_location_id)
  - Um cursor aninhado (cv_employees)
Atribuição do Cursor Aninhado:
- O terceiro item recuperado, cv_employees, é na verdade o cursor aninhado definido como emp_cur na declaração de dept_cur.
- Quando o FETCH é executado, cv_employees recebe o cursor aninhado correspondente ao departamento atual.
Processamento do Cursor Aninhado:
- Após recuperar os dados do departamento, o código entra em um loop interno.
- Este loop processa cv_employees, que agora contém o cursor com os dados dos funcionários do departamento atual.
- A instrução FETCH cv_employees INTO v_emp_id, v_first_name, v_last_name; recupera os dados de cada funcionário.
Correlação Automática:
- O Oracle gerencia automaticamente a correlação entre o departamento atual e seus funcionários.
- Quando cv_employees é atribuído, ele já contém apenas os funcionários do departamento que está sendo processado no loop externo.
Ciclo de Vida do Cursor Aninhado:
- Para cada iteração do loop externo (cada departamento), um novo cursor aninhado é atribuído a cv_employees.
- Isso significa que cv_employees é “reiniciado” para cada departamento, contendo apenas os funcionários relevantes.
Eficiência:
- Esta abordagem é eficiente porque o Oracle otimiza a execução, evitando múltiplas consultas separadas para cada departamento e seus funcionários.

Em resumo, a relação entre a declaração do cursor e o loop de processamento é direta:

O cursor principal dept_cur define a estrutura dos dados a serem recuperados, incluindo o cursor aninhado para funcionários.
No loop, cv_employees recebe dinamicamente o cursor aninhado para cada departamento.
Isso permite processar os funcionários de cada departamento de forma eficiente e estruturada, mantendo a relação hierárquica entre departamentos e funcionários.

Esta técnica de cursores aninhados é poderosa para lidar com dados hierárquicos em PL/SQL, permitindo uma recuperação e processamento eficientes de dados relacionados.

Pontos Importantes

Não é necessário abrir explicitamente o cursor aninhado (cv_employees). Ele já está pronto para ser processado após o FETCH do cursor principal.
O cursor aninhado é processado completamente para cada iteração do cursor principal.
Essa abordagem é particularmente útil quando você precisa lidar com dados hierárquicos ou relacionados de forma eficiente.

Comparação com Cursores Aninhados Tradicionais

O mesmo resultado poderia ser alcançado usando cursores aninhados tradicionais:

BEGIN
    FOR dept_rec IN (SELECT department_id, department_name, location_id
                     FROM departments
                     WHERE department_id IN (10, 20, 30)) LOOP
        DBMS_OUTPUT.PUT_LINE('Departamento: ' || dept_rec.department_name || 
                             ' (Local ID: ' || dept_rec.location_id || ')');
        
        FOR emp_rec IN (SELECT employee_id, first_name, last_name
                        FROM employees
                        WHERE department_id = dept_rec.department_id) LOOP
            DBMS_OUTPUT.PUT_LINE('  ' || emp_rec.employee_id || ': ' || 
                                 emp_rec.first_name || ' ' || emp_rec.last_name);
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('---');
    END LOOP;
END;
/

BEGIN

FOR dept_rec IN (SELECT department_id, department_name, location_id

FROM departments

WHERE department_id IN (10, 20, 30)) LOOP

DBMS_OUTPUT.PUT_LINE('Departamento: ' || dept_rec.department_name ||

' (Local ID: ' || dept_rec.location_id || ')');

FOR emp_rec IN (SELECT employee_id, first_name, last_name

FROM employees

WHERE department_id = dept_rec.department_id) LOOP

DBMS_OUTPUT.PUT_LINE(' ' || emp_rec.employee_id || ': ' ||

emp_rec.first_name || ' ' || emp_rec.last_name);

END LOOP;

DBMS_OUTPUT.PUT_LINE('---');

END LOOP;

END;

Vantagens das Expressões de Cursor

Performance: Em alguns casos, as expressões de cursor podem ser mais eficientes, especialmente com grandes conjuntos de dados.
Flexibilidade: Permitem que você passe cursores como parâmetros ou os retorne de funções.
Clareza: Podem tornar o código mais legível em certos cenários, especialmente quando lidando com dados hierárquicos complexos.

Conclusão

As expressões de cursor são uma ferramenta poderosa no seu arsenal PL/SQL. Elas oferecem uma maneira elegante de lidar com dados relacionados ou hierárquicos, permitindo que você crie consultas mais complexas e eficientes.

Lembre-se:

Use expressões de cursor quando precisar trabalhar com dados aninhados ou relacionados.
Elas são particularmente úteis em situações onde você precisa passar cursores como parâmetros ou retorná-los de funções.
Compare o desempenho com abordagens alternativas, como cursores aninhados tradicionais, para determinar a melhor solução para o seu caso específico.

Pratique bastante! Experimente criar diferentes expressões de cursor usando várias tabelas do schema HR. Tente, por exemplo, criar uma expressão de cursor que retorne os funcionários de um departamento junto com seus históricos de trabalho.

O que são Expressões de Cursor?

Exemplo Prático com o Schema HR

Processando Expressões de Cursor

Pontos Importantes

Comparação com Cursores Aninhados Tradicionais

Vantagens das Expressões de Cursor

Conclusão

Related Posts