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.
