OPEN FOR, FETCH e CLOSE Statements para Dynamic SQL em PL/SQL Oracle

As instruções OPEN FOR, FETCH e CLOSE são componentes essenciais do PL/SQL Oracle para a manipulação eficiente de cursores dinâmicos. Este artigo técnico explora em profundidade a implementação, funcionalidades e melhores práticas dessas instruções, fornecendo insights valiosos para desenvolvedores que buscam otimizar o processamento de conjuntos de resultados dinâmicos em ambientes Oracle.

Conceitos Fundamentais

OPEN FOR: Associa uma variável de cursor a uma declaração SQL dinâmica, preparando-a para execução.
FETCH: Recupera linhas individuais do conjunto de resultados associado ao cursor.
CLOSE: Libera os recursos do cursor, finalizando o processamento do conjunto de resultados.

Exemplo Básico: Consultando Funcionários por Localização

Vamos criar um exemplo que lista os funcionários de uma determinada localização usando o schema HR do Oracle:

DECLARE
   TYPE emp_cur_type IS REF CURSOR;
   c_emp       emp_cur_type;
   sql_stmt    VARCHAR2(300);
   v_location_id NUMBER := &location_id;
   v_first_name employees.first_name%TYPE;
   v_last_name  employees.last_name%TYPE;
BEGIN
   sql_stmt :=
      'SELECT e.first_name, e.last_name
       FROM employees e
       JOIN departments d ON e.department_id = d.department_id
       WHERE d.location_id = :1';

   OPEN c_emp FOR sql_stmt USING v_location_id;

   LOOP
      FETCH c_emp INTO v_first_name, v_last_name;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Nome: '||v_first_name||' '||v_last_name);
   END LOOP;

   CLOSE c_emp;
EXCEPTION
   WHEN OTHERS THEN
      IF c_emp%ISOPEN THEN
         CLOSE c_emp;
      END IF;
      DBMS_OUTPUT.PUT_LINE('ERRO: '||SUBSTR(SQLERRM, 1, 200));
END;
/

DECLARE

TYPE emp_cur_type IS REF CURSOR;

c_emp emp_cur_type;

sql_stmt VARCHAR2(300);

v_location_id NUMBER := &location_id;

v_first_name employees.first_name%TYPE;

v_last_name employees.last_name%TYPE;

BEGIN

sql_stmt :=

'SELECT e.first_name, e.last_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.location_id = :1';

OPEN c_emp FOR sql_stmt USING v_location_id;

LOOP

FETCH c_emp INTO v_first_name, v_last_name;

EXIT WHEN c_emp%NOTFOUND;

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

END LOOP;

CLOSE c_emp;

EXCEPTION

WHEN OTHERS THEN

IF c_emp%ISOPEN THEN

CLOSE c_emp;

END IF;

DBMS_OUTPUT.PUT_LINE('ERRO: '||SUBSTR(SQLERRM, 1, 200));

END;

Características Principais:

Uso de JOIN para relacionar dados de diferentes tabelas.
Tipagem de variáveis com %TYPE para garantir compatibilidade de tipos.
Parâmetro de entrada usando substituição de variável (&location_id).
Tratamento de exceções para garantir o fechamento do cursor.

Exemplo Avançado: Consulta Dinâmica com Nome de Tabela Variável

Agora, vamos tornar nosso exemplo mais flexível, permitindo que o usuário escolha entre consultar 'employees' ou ‘job_history':

DECLARE
   TYPE emp_cur_type IS REF CURSOR;
   TYPE emp_rec_type IS RECORD
      (id   NUMBER,
       info VARCHAR2(100));
   c_emp        emp_cur_type;
   emp_rec      emp_rec_type;
   v_table_name VARCHAR2(20) := LOWER('&sv_table_name');
   v_location_id NUMBER := &location_id;
   sql_stmt     VARCHAR2(300);
BEGIN
   DBMS_OUTPUT.PUT_LINE('Tabela: '||v_table_name);
   
   sql_stmt := 
      'SELECT ' ||
      CASE 
         WHEN v_table_name = 'employees' THEN 'employee_id, first_name || '' '' || last_name'
         WHEN v_table_name = 'job_history' THEN 'employee_id, TO_CHAR(start_date, ''DD-MON-YYYY'')'
         ELSE 'NULL, ''Tabela não reconhecida'''
      END ||
      ' FROM '||v_table_name||
      ' WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = :1)';

   OPEN c_emp FOR sql_stmt USING v_location_id;
   
   LOOP
      FETCH c_emp INTO emp_rec;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('ID: '||emp_rec.id||', Dado: '||emp_rec.info);
   END LOOP;
   
   CLOSE c_emp;
EXCEPTION
   WHEN OTHERS THEN
      IF c_emp%ISOPEN THEN
         CLOSE c_emp;
      END IF;
      DBMS_OUTPUT.PUT_LINE('ERRO: '||SUBSTR(SQLERRM, 1, 200));
END;
/

DECLARE

TYPE emp_cur_type IS REF CURSOR;

TYPE emp_rec_type IS RECORD

(id NUMBER,

info VARCHAR2(100));

c_emp emp_cur_type;

emp_rec emp_rec_type;

v_table_name VARCHAR2(20) := LOWER('&sv_table_name');

v_location_id NUMBER := &location_id;

sql_stmt VARCHAR2(300);

BEGIN

DBMS_OUTPUT.PUT_LINE('Tabela: '||v_table_name);

sql_stmt :=

'SELECT ' ||

CASE

WHEN v_table_name = 'employees' THEN 'employee_id, first_name || '' '' || last_name'

WHEN v_table_name = 'job_history' THEN 'employee_id, TO_CHAR(start_date, ''DD-MON-YYYY'')'

ELSE 'NULL, ''Tabela não reconhecida'''

END ||

' FROM '||v_table_name||

' WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = :1)';

OPEN c_emp FOR sql_stmt USING v_location_id;

LOOP

FETCH c_emp INTO emp_rec;

EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('ID: '||emp_rec.id||', Dado: '||emp_rec.info);

END LOOP;

CLOSE c_emp;

EXCEPTION

WHEN OTHERS THEN

IF c_emp%ISOPEN THEN

CLOSE c_emp;

END IF;

DBMS_OUTPUT.PUT_LINE('ERRO: '||SUBSTR(SQLERRM, 1, 200));

END;

Vamos analisar com mais detalhes:

Declarações:

a. TYPE emp_cur_type IS REF CURSOR;

Define um tipo de cursor REF (referência).
Permite criar cursores flexíveis que podem ser associados a diferentes consultas SQL em tempo de execução.

b. TYPE emp_rec_type IS RECORD (id NUMBER, info VARCHAR2(100));

Define um tipo de registro personalizado.
id: Campo para armazenar o ID do funcionário ou job_history.
info: Campo para armazenar o nome completo do funcionário ou a data de início formatada.

c. c_emp emp_cur_type;

Declara uma variável do tipo cursor REF.
Será usada para executar a consulta SQL dinâmica.

d. emp_rec emp_rec_type;

Declara uma variável do tipo registro personalizado.
Será usada para armazenar cada linha retornada pela consulta.

e. v_table_name VARCHAR2(20) := LOWER('&sv_table_name');

Armazena o nome da tabela fornecido pelo usuário.
&sv_table_name é uma substituição de variável para entrada do usuário.
LOWER() converte o nome para minúsculas para consistência.

f. v_location_id NUMBER := &location_id;

Armazena o ID da localização fornecido pelo usuário.
&location_id é outra substituição de variável para entrada do usuário.

g. sql_stmt VARCHAR2(300);

Variável para armazenar a declaração SQL dinâmica.
Tamanho máximo de 300 caracteres.

Bloco Principal:

a. DBMS_OUTPUT.PUT_LINE('Tabela: '||v_table_name);

Imprime o nome da tabela fornecido pelo usuário.

b. Construção da consulta SQL dinâmica:

sql_stmt := 
   'SELECT ' ||
   CASE 
      WHEN v_table_name = 'employees' THEN 'employee_id, first_name || '' '' || last_name'
      WHEN v_table_name = 'job_history' THEN 'employee_id, TO_CHAR(start_date, ''DD-MON-YYYY'')'
      ELSE 'NULL, ''Tabela não reconhecida'''
   END ||
   ' FROM '||v_table_name||
   ' WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = :1)';

sql_stmt :=

'SELECT ' ||

CASE

WHEN v_table_name = 'employees' THEN 'employee_id, first_name || '' '' || last_name'

WHEN v_table_name = 'job_history' THEN 'employee_id, TO_CHAR(start_date, ''DD-MON-YYYY'')'

ELSE 'NULL, ''Tabela não reconhecida'''

END ||

' FROM '||v_table_name||

' WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = :1)';

Usa um CASE para adaptar a consulta baseada no nome da tabela.
Para 'employees': seleciona ID e nome completo.
Para ‘job_history': seleciona ID e data de início formatada.
Inclui um caso ELSE para lidar com nomes de tabela não reconhecidos.
A subconsulta filtra por departamentos na localização especificada.

c. OPEN c_emp FOR sql_stmt USING v_location_id;

Abre o cursor com a consulta dinâmica.
Usa v_location_id como parâmetro de bind para :1 na consulta.

d. Loop para processar resultados:

LOOP
   FETCH c_emp INTO emp_rec;
   EXIT WHEN c_emp%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('ID: '||emp_rec.id||', Dado: '||emp_rec.info);
END LOOP;

LOOP

FETCH c_emp INTO emp_rec;

EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('ID: '||emp_rec.id||', Dado: '||emp_rec.info);

END LOOP;

FETCH recupera cada linha do resultado no registro emp_rec.
EXIT WHEN c_emp%NOTFOUND sai do loop quando não há mais registros.
Imprime o ID e a informação de cada registro.

e. CLOSE c_emp;

Fecha o cursor, liberando recursos.

Tratamento de Exceções:

EXCEPTION
   WHEN OTHERS THEN
      IF c_emp%ISOPEN THEN
         CLOSE c_emp;
      END IF;
      DBMS_OUTPUT.PUT_LINE('ERRO: '||SUBSTR(SQLERRM, 1, 200));

EXCEPTION

WHEN OTHERS THEN

IF c_emp%ISOPEN THEN

CLOSE c_emp;

END IF;

DBMS_OUTPUT.PUT_LINE('ERRO: '||SUBSTR(SQLERRM, 1, 200));

Captura qualquer erro não tratado.
Verifica se o cursor está aberto e o fecha se necessário.
Imprime os primeiros 200 caracteres da mensagem de erro.

Este código demonstra técnicas avançadas de PL/SQL, incluindo o uso de tipos personalizados, cursores REF para SQL dinâmico, e construção flexível de consultas. Ele oferece a capacidade de consultar diferentes tabelas mantendo uma estrutura de código consistente, sendo útil em cenários onde a lógica de consulta pode variar com base em parâmetros de entrada.

Características Avançadas:

Flexibilidade: Permite consultar diferentes tabelas ('employees' ou ‘job_history') com base na entrada do usuário.
Uso de Registro Personalizado: Utiliza um tipo de registro (emp_rec_type) para armazenar os resultados da consulta, tornando o código mais organizado.
SQL Dinâmico: Constrói a consulta SQL dinamicamente baseada no nome da tabela fornecido.
Tratamento de Casos Inválidos: Inclui um caso ELSE para lidar com nomes de tabela não reconhecidos.

Dicas Importantes

Formatação do SQL Dinâmico:
- Adicione espaços extras (como em ‘ ‘||) para evitar erros de sintaxe na concatenação.
- Use concatenação de strings para melhor legibilidade.
Segurança:
- Sempre valide entradas do usuário para prevenir injeção de SQL.
- Considere usar listas de valores permitidos para nomes de tabelas.
Tratamento de Exceções:
- Sempre inclua um bloco EXCEPTION para lidar com erros.
- Garanta que os recursos sejam liberados adequadamente, especialmente o fechamento de cursores.
Tipagem e Estrutura de Dados:
- Use %TYPE para garantir compatibilidade de tipos com as colunas do banco de dados.
- Considere o uso de registros para tornar o código mais limpo e fácil de manter.

Vejamos um exemplo com as melhrias de segurança, tipagem e estrutura de dados:

DECLARE
    TYPE emp_cur_type IS REF CURSOR;
    TYPE emp_rec_type IS RECORD (
        employee_id employees.employee_id%TYPE,
        first_name  employees.first_name%TYPE,
        last_name   employees.last_name%TYPE,
        salary      employees.salary%TYPE
    );
    c_emp           emp_cur_type;
    emp_rec         emp_rec_type;
    sql_stmt        VARCHAR2(1000);
    v_location_id   locations.location_id%TYPE := &location_id;
    v_department_id departments.department_id%TYPE := &department_id;
    v_hire_date     employees.hire_date%TYPE := TO_DATE('&hire_date', 'YYYY-MM-DD');
    v_min_salary    employees.salary%TYPE := 50000;

    -- Lista de tabelas permitidas
    TYPE allowed_tables_type IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
    allowed_tables allowed_tables_type;

BEGIN
    -- Inicializar lista de tabelas permitidas
    allowed_tables('EMPLOYEES') := 'EMPLOYEES';
    allowed_tables('DEPARTMENTS') := 'DEPARTMENTS';

    -- Validar entrada do usuário
    IF NOT allowed_tables.EXISTS(UPPER('&table_name')) THEN
        RAISE_APPLICATION_ERROR(-20001, 'Tabela não permitida');
    END IF;

    sql_stmt := 
        'SELECT e.employee_id, e.first_name, e.last_name, e.salary ' ||
        'FROM employees e ' ||
        'JOIN departments d ON e.department_id = d.department_id ' ||
        'WHERE d.location_id = :location_id ' ||
        'AND e.department_id = :department_id ' ||
        'AND e.hire_date >= :hire_date ' ||
        'AND e.salary > :min_salary';

    OPEN c_emp FOR sql_stmt USING 
        v_location_id, 
        v_department_id, 
        v_hire_date, 
        v_min_salary;

    DBMS_OUTPUT.PUT_LINE('Funcionários que atendem aos critérios:');
    LOOP
        FETCH c_emp INTO emp_rec;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || emp_rec.employee_id || 
            ', Nome: ' || emp_rec.first_name || ' ' || emp_rec.last_name || 
            ', Salário: ' || emp_rec.salary
        );
    END LOOP;

    CLOSE c_emp;

EXCEPTION
    WHEN OTHERS THEN
        IF c_emp%ISOPEN THEN
            CLOSE c_emp;
        END IF;
        DBMS_OUTPUT.PUT_LINE('ERRO: ' || SUBSTR(SQLERRM, 1, 200));
        RAISE;  -- Re-raise the exception after handling
END;
/

DECLARE

TYPE emp_cur_type IS REF CURSOR;

TYPE emp_rec_type IS RECORD (

employee_id employees.employee_id%TYPE,

first_name employees.first_name%TYPE,

last_name employees.last_name%TYPE,

salary employees.salary%TYPE

);

c_emp emp_cur_type;

emp_rec emp_rec_type;

sql_stmt VARCHAR2(1000);

v_location_id locations.location_id%TYPE := &location_id;

v_department_id departments.department_id%TYPE := &department_id;

v_hire_date employees.hire_date%TYPE := TO_DATE('&hire_date', 'YYYY-MM-DD');

v_min_salary employees.salary%TYPE := 50000;

-- Lista de tabelas permitidas

TYPE allowed_tables_type IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);

allowed_tables allowed_tables_type;

BEGIN

-- Inicializar lista de tabelas permitidas

allowed_tables('EMPLOYEES') := 'EMPLOYEES';

allowed_tables('DEPARTMENTS') := 'DEPARTMENTS';

-- Validar entrada do usuário

IF NOT allowed_tables.EXISTS(UPPER('&table_name')) THEN

RAISE_APPLICATION_ERROR(-20001, 'Tabela não permitida');

END IF;

sql_stmt :=

'SELECT e.employee_id, e.first_name, e.last_name, e.salary ' ||

'FROM employees e ' ||

'JOIN departments d ON e.department_id = d.department_id ' ||

'WHERE d.location_id = :location_id ' ||

'AND e.department_id = :department_id ' ||

'AND e.hire_date >= :hire_date ' ||

'AND e.salary > :min_salary';

OPEN c_emp FOR sql_stmt USING

v_location_id,

v_department_id,

v_hire_date,

v_min_salary;

DBMS_OUTPUT.PUT_LINE('Funcionários que atendem aos critérios:');

LOOP

FETCH c_emp INTO emp_rec;

EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(

'ID: ' || emp_rec.employee_id ||

', Nome: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||

', Salário: ' || emp_rec.salary

);

END LOOP;

CLOSE c_emp;

EXCEPTION

WHEN OTHERS THEN

IF c_emp%ISOPEN THEN

CLOSE c_emp;

END IF;

DBMS_OUTPUT.PUT_LINE('ERRO: ' || SUBSTR(SQLERRM, 1, 200));

RAISE; -- Re-raise the exception after handling

END;

Melhorias aplicadas:

Formatação do SQL Dinâmico:
- Usei concatenação de strings com || e adicionei espaços extras para melhor legibilidade.
Segurança:
- Implementei uma lista de tabelas permitidas para validar a entrada do usuário.
- Uso binds nomeados em vez de posicionais para maior clareza e segurança.
Tratamento de Exceções:
- Mantive o bloco EXCEPTION, garantindo o fechamento do cursor.
- Adicionei RAISE para propagar a exceção após o tratamento local.
Tipagem e Estrutura de Dados:
- Usei %TYPE para todas as variáveis, garantindo compatibilidade com as colunas do banco de dados.
- Criei um tipo de registro (emp_rec_type) para armazenar os resultados da consulta.
Outras Melhorias:
- Aumentei o tamanho da variável sql_stmt para 1000 caracteres para acomodar consultas maiores.
- Usei binds nomeados na consulta SQL para maior clareza.

Este código agora incorpora as boas práticas mencionadas, tornando-o mais seguro, legível e fácil de manter. Ele demonstra como aplicar essas dicas importantes em um cenário real de PL/SQL.

Conclusão

As instruções OPEN FOR, FETCH e CLOSE são ferramentas poderosas para trabalhar com SQL dinâmico em PL/SQL. Elas permitem criar consultas flexíveis que se adaptam às necessidades em tempo de execução.

Pontos-chave para lembrar:

OPEN FOR prepara o cursor e associa a consulta dinâmica.
FETCH recupera os dados linha por linha.
CLOSE libera os recursos do cursor.
Use registros para melhorar a organização e manutenção do código.
Priorize a segurança ao trabalhar com SQL dinâmico.
Trate exceções adequadamente para garantir a robustez do código.

Com prática e experimentação, você se tornará proficiente em criar consultas dinâmicas eficientes e flexíveis. Continue explorando diferentes cenários e as possibilidades que o SQL dinâmico oferece no PL/SQL Oracle.

Conceitos Fundamentais

Exemplo Básico: Consultando Funcionários por Localização

Características Principais:

Exemplo Avançado: Consulta Dinâmica com Nome de Tabela Variável

Características Avançadas:

Dicas Importantes

Conclusão

Related Posts