Oracle Tutorial de SQL Dinâmico PL/SQL: Executar Imediato e DBMS_SQL

O que é SQL Dinâmico?

Dinâmico SQL SQL é uma metodologia de programação para gerar e executar instruções em tempo de execução. É usada principalmente para escrever programas de propósito geral e flexíveis, nos quais as instruções SQL são criadas e executadas em tempo de execução com base na necessidade, por exemplo, quando os nomes das tabelas, listas de colunas ou condições WHERE não são conhecidos até a execução do programa.

Formas de escrever SQL dinâmico

PL/SQL oferece duas maneiras de escrever SQL dinâmico:

1. NDS – SQL Dinâmico Nativo (as instruções EXECUTE IMMEDIATE e OPEN-FOR)
2. SGBD_SQL (um pacote fornecido)

A regra geral é simples: se o número e os tipos de dados das variáveis ​​de entrada e saída forem conhecidos em tempo de compilação, use SQL Dinâmico Nativo, pois é mais rápido e requer menos código. Quando essa informação só é conhecida em tempo de execução, use o pacote DBMS_SQL.

NDS (Native Dynamic SQL) – Executar Imediato

O SQL dinâmico nativo é a maneira mais fácil de escrever SQL dinâmico. Ele usa o comando EXECUTE IMMEDIATE para criar e executar o SQL em tempo de execução. Para usar essa abordagem, o tipo de dados e o número de variáveis ​​usadas em tempo de execução devem ser conhecidos antecipadamente. Ele também oferece melhor desempenho e menor complexidade em comparação com o DBMS_SQL.

Sintaxe

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• string_sql_dinâmica: Uma expressão de cadeia de caracteres (VARCHAR2 ou CHAR, não NVARCHAR2/NCHAR) que contém uma única instrução SQL ou bloco PL/SQL.
• Cláusula INTO: Opcional. Usado apenas quando o SQL dinâmico é um SELECT de linha única; ele captura os valores retornados em variáveis ​​ou em um registro. Cada coluna selecionada precisa de uma variável compatível com o tipo.
• Cláusula USING: Opcional. Fornece variáveis ​​de vinculação. O modo padrão é IN; OUT e IN OUT são usados ​​para receber valores de volta.
• Cláusula RETORNANDO PARA: Utilizado com instruções DML que contêm uma cláusula RETURNING, para capturar os valores das linhas afetadas em argumentos de vinculação.

1 exemplo: Neste exemplo, buscamos os dados da tabela emp para emp_no '1001' usando uma instrução NDS com uma variável de ligação.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

saída

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Explicação:

• Linhas 2-6: Declarando as variáveis.
• Linha 8: Definindo o SQL em tempo de execução. O SQL contém a variável de ligação ':empno' na condição WHERE.
• Linhas 9-11: Executando o SQL encapsulado com EXECUTE IMMEDIATE. As variáveis ​​da cláusula INTO armazenam os valores obtidos e a cláusula USING fornece o valor para a variável de ligação :empno.
• Linhas 12-15: Exibindo os valores obtidos.

Utilizando SQL dinâmico para DDL

O PL/SQL estático não consegue executar instruções DDL como CREATE, ALTER ou DROP diretamente. O EXECUTE IMMEDIATE resolve isso construindo a instrução como uma string, o que também é útil quando um nome de objeto é fornecido em tempo de execução.

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

Os nomes dos objetos (tabela, coluna, esquema) não podem ser passados ​​como variáveis ​​de ligação, portanto, devem ser concatenados na string. Sempre valide essa entrada, por exemplo, com DBMS_ASSERT.SIMPLE_SQL_NAME, para evitar injeção de SQL.

DBMS_SQL para SQL Dinâmico

PL/SQL fornece o pacote DBMS_SQL para trabalhar com SQL dinâmico quando a estrutura da instrução não é conhecida até o momento da execução. O processo de criação e execução do SQL dinâmico envolve as seguintes etapas:

• ABRIR CURSOR: O SQL dinâmico é executado como um cursorPara executar a instrução SQL, primeiro precisamos abrir o cursor.
• ANALISAR SQL: Analisa o SQL dinâmico. Isso verifica a sintaxe e mantém a consulta pronta para ser executada.
• Valores da variável de ligação: Atribua os valores às variáveis ​​de ligação, se houver.
• DEFINIR COLUNA: Defina cada coluna usando sua posição relativa na instrução SELECT.
• EXECUTAR: Execute a consulta analisada.
• VALORES DE BUSCA: Recupere os valores executados.
• FECHAR CURSOR: Assim que os resultados forem obtidos, feche o cursor.

1 exemplo: Neste exemplo, recuperamos os dados da tabela emp para emp_no '1001' usando uma instrução DBMS_SQL. O bloco EXCEPTION fecha o cursor mesmo se ocorrer um erro.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

saída

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Explicação:

• Linhas 1-8: Declaração de variáveis.
• Linha 10: Estruturando a instrução SQL.
• Linha 11: Abrindo o cursor usando DBMS_SQL.OPEN_CURSOR, que retorna o ID do cursor aberto.
• Linha 12: Após o cursor ser aberto, o SQL é analisado.
• Linha 13: O valor de vinculação '1001' é atribuído no lugar de ':empno'.
• Linhas 14-17: Definindo as colunas por sua posição relativa: (1) nome_funcionário, (2) número_funcionário, (3) salário, (4) gerente.
• Linha 18: Executando a consulta com DBMS_SQL.EXECUTE, que retorna o número de registros processados.
• Linhas 19-32: A função FETCH_ROWS busca os registros em um loop. Ela retorna 0 quando não há mais linhas, encerrando o loop.
• Bloco de EXCEÇÃO: Garante que o cursor seja fechado para que cursores abertos não causem vazamento de memória caso ocorra um erro.

NDS vs DBMS_SQL: Quando usar cada um?

Ambas as abordagens executam SQL em tempo de execução, mas são adequadas para situações diferentes:

• Use SQL dinâmico nativo (EXECUTE IMMEDIATE / OPEN-FOR) Quando o número e os tipos de dados de entradas e saídas são conhecidos em tempo de compilação, o código é mais rápido, mais fácil de ler e requer menos código.
• Use DBMS_SQL Quando a estrutura é desconhecida até o momento da execução, por exemplo, uma consulta cujo número de colunas selecionadas ou variáveis ​​de ligação varia, conhecida como SQL dinâmico do método 4, ou uma instrução muito grande para caber em uma única variável VARCHAR2 de 32K.

Perguntas Frequentes