SQL Dinâmico no Oracle - Parte 1

1. Introdução

O uso de comandos SQL montados de forma dinâmica - ou seja, em tempo de execução - corresponde a um dos recursos mais interessantes da linguagem PL/SQL (linguagem do SGBD Oracle). É possível executar dinamicamente não apenas consultas SQL ou comandos DML (INSERT, UPDATE e DELETE), mas também comandos DDL (como CREATE ou DROP) e DCL (como GRANT e REVOKE). Este artigo inicia uma série de trabalhos que abordarão as características e formas de implementação deste recurso em ambiente Oracle.

2. SQL Dinâmico: um Exemplo Simples         

Um comando SQL Dinâmico representa uma instrução SQL que é construída e armazenada numa variável do tipo VARCHAR2 (o equivalente ao tipo “string” no mundo Oracle) durante a execução de uma função ou procedure PL/SQL. Esta instrução pode ser montada de diferentes maneiras, de acordo com parâmetros de entrada. Para facilitar a compreensão deste conceito, será apresentado um primeiro exemplo prático. Considere uma função que recebe o nome de duas tabelas como entrada e que, como saída, retorne numa string concatenada o nome e a quantidade de registros da tabela que possuir o maior número de registros. O código desta função é apresentado a seguir:

CREATE OR REPLACE FUNCTION F_MAIOR(vTAB1 IN VARCHAR, vTAB2 IN VARCHAR) RETURN VARCHAR IS

vSQL      VARCHAR2(256);    --string que recebe o comando SQL dinâmico

vTOT1     PLS_INTEGER;      --total de registros da Tabela 1

vTOT2     PLS_INTEGER;      --total de registros da Tabela 2

BEGIN

  

   --PASSO 1: monta e executa SQL dinâmico referente a Tabela 1

   vSQL := ‘SELECT COUNT(*) FROM ’ || vTAB1;

  

   EXECUTE IMMEDIATE vSQL INTO vTOT1;

 

   --PASSO 2: monta e executa SQL dinâmico referente a Tabela 2

   vSQL := ‘SELECT COUNT(*) FROM ’ || vTAB2;

  

   EXECUTE IMMEDIATE vSQL INTO vTOT2;

--PASSO 3: retorna o nome e a quantidade de registros da tabela com mais linhas

   IF vTOT1 > vTOT2 THEN

     RETURN  vTAB1 ||  ‘ --- ’ || TO_CHAR(vTOT1);

   ELSIF vTOT1 < vTOT2 THEN

      RETURN  vTAB2 ||  ‘ --- ’ || TO_CHAR(vTOT2);

    ELSE

      RETURN  ‘EMPATE --- ’ || TO_CHAR(vTOT2);

    END IF;

END;

O programa exemplo funciona da seguinte forma. No passo 1, uma instrução SQL é montada dinamicamente e armazenada na variável vSQL (do tipo VARCHAR2). Observe que o parâmetro de entrada da função vTAB1 (nome da tabela 1) é concatenado no final do literal ‘SELECT COUNT(*)’. A seguir encontra-se a linha de código mais importante do programa:

EXECUTE IMMEDIATE vSQL INTO vTOT1; 

O comando EXECUTE IMMEDIATE é o responsável pela interpretação e execução de instruções SQL montadas de forma dinâmica. Para realizar esta tarefa, basta especificar a string SQL (no exemplo, vSQL) e o nome da variável que armazenará o resultado obtido pela execução do SQL (no exemplo, o resultado corresponde ao total de registros da tabela vTAB1 e será armazenado na variável vTOT1).

O passo 2 do programa é semelhante ao passo 1. A única diferença é o fato de que agora o SQL dinâmico armazenado em vSQL será montado com o nome do parâmetro vTAB2 no final (nome da segunda tabela cuja quantidade de registros deseja-se determinar). Finalizando a função, o passo 3 simplesmente testa qual é a tabela que contém mais linhas e retorna essa informação ao usuário.

Para criar a função, basta executar o SQL Plus, efetuar o login em uma base Oracle e copiar e colar o código. Para executá-la, você pode utilizar, por exemplo, a seguinte chamada:

SQL> SELECT F_MAIOR(‘USER_TABLES’, ‘USER_VIEWS’) FROM DUAL; 

F_MAIOR('USER_TABLES','USER_VIEWS')

--------------------------------------------------------------------------------

USER_TABLES --- 152

Neste exemplo, o Oracle compara a quantidade de registros das views de sistema USER_TABLES e USER_VIEWS e indica qual das duas retorna mais linhas.