Artigos Banco de Dados 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.

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.