Artigos Banco de Dados SQL Dinâmico no Oracle – Parte 1



Eduardo Corrêa Gonçalves

Instituto Brasileiro de Geografia e Estatística – IBGE

E-mail: eduardo.correa@ibge.gov.br

 

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.


3. Próximos Artigos

        

No próximo artigo desta série serão detalhadas  as características do comando EXECUTE IMMEDIATE. Este comando é o mais utilizado para a implementação de SQL’s dinâmicos no PL/SQL, sendo empregado toda vez que se deseja executar comandos DDL, DCL e DML e consultas que retornem apenas uma linha. No entanto, quando o objetivo é processar consultas dinâmicas que resultarão em muitas linhas é preciso utilizar outro recurso do Oracle: os cursores dinâmicos. Estes cursores são implementados através dos comandos OPER-FOR, FETCH e CLOSE. Os cursores dinâmicos serão o tema do terceiro artigo sobre SQL dinâmico no Oracle.