Introdução
Este artigo discute a utilização do comando EXECUTE IMMEDIATE na implementação de consultas dinâmicas no Oracle.
Bind Variables
O processo de associar valores de variáveis PL/SQL em consultas SQL, especificadas no corpo de funções ou procedures escritas nesta linguagem, é conhecido como binding (“ligação”). Para observar a aplicação deste conceito na prática, considere a procedure P_APAGA_CLIENTE, especificada abaixo. Esta procedure recebe como entrada um número de CPF e, em seu corpo, contém três comandos SQL DELETE responsáveis por excluir todos os registros do cliente especificado em três diferentes tabelas. Nesta procedure, a variável vCOD_CPF atua como uma bind variable, pois ela foi utilizada como condição da cláusula WHERE em três instruções SQL.
CREATE OR REPLACE PROCEDURE P_APAGA_CLIENTE(vCOD_CPF IN CHAR) IS
BEGIN
DELETE FROM T_PEDIDO WHERE COD_CPF = vCOD_CPF;
DELETE FROM T_COMPRA WHERE COD_CPF = vCOD_CPF;
DELETE FROM T_CLIENTE WHERE COD_CPF = vCOD_CPF;
END;
Infelizmente as bind variables não podem ser utilizadas para especificar nomes de objetos (ou seja tabelas, visões, sequences, etc) e nomes de campos em consultas SQL. Isto quer dizer, por exemplo, que a seguinte procedure não pode ser compilada pelo Oracle:
CREATE OR REPLACE PROCEDURE P_APAGA_TABELA(vTABELA IN VARCHAR) IS
BEGIN
DELETE FROM vTABELA;
END;
No exemplo acima ocorreu a tentativa de utilizar a variável de entrada vTABELA como uma bind variable para especificar o nome de uma tabela. Ao se tentar criar a procedure, ocorrerá o um erro: o compilador PL/SQL “pensará” que vTABELA corresponde ao nome de uma tabela do banco de dados. Ele não tentará utilizar vTABELA como bind variable na instrução DELETE.
Errors for PROCEDURE P_APAGA_TABELA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/7 PL/SQL: SQL Statement ignored
5/19 PL/SQL: ORA-00942: a tabela ou view não existe
Utilização do Comando EXECUTE IMMEDIATE
Para casos onde seja necessária a criação de instruções SQL DML (INSERT, UPDATE e DELETE) em que nomes de objetos ou campos precisem ser definidos através de valores armazenados em variáveis PL/SQL, o desenvolvedor Oracle deve utilizar o comando EXECUTE IMMEDIATE. Na realidade o comando faz muito mais do que isto: ele permite com que, até mesmo, instruções DDL (como CREATE e DROP) e DCL (como GRANT e REVOKE) possam ser executados via PL/SQL (estes comandos não podem ser executados de forma convencional no PL/SQL). Além disso, o EXECUTE IMMEDIATE permite a montagem de consultas SELECT dinâmicas que retornem apenas uma linha (para consultas que retornem mais de uma linha é necessário utilizar outro método, a ser apresentado na parte 3 desta série).
O exemplo abaixo, adaptado do manual da Oracle, ilustra um bloco PL/SQL que contém diversos exemplos de instruções SQL dinâmicas executadas com o uso do comando EXECUTE IMMEDIATE.
DECLARE
vSQL VARCHAR2(4000); --string que recebe o comando SQL dinâmico
vID NUMBER(2) := 99;
vNOME VARCHAR2(30) := ‘Tangerina’;
BEGIN
--PASSO 1: executa um comando DDL
EXECUTE IMMEDIATE ‘CREATE TABLE T_PRODUTO (ID NUMBER, NOME VARCHAR2(30))’;
--PASSO 2: executa um comando DML passando duas variáveis como parâmetro
vSQL := ‘INSERT INTO T_PRODUTO VALUES (:1, :2)’;
EXECUTE IMMEDIATE vSQL USING vID, vNOME;
--PASSO 3: executa um comando DCL
EXECUTE IMMEDIATE ‘GRANT SELECT ON T_PRODUTO TO SCOTT’;
END;
O programa exemplo funciona da seguinte forma. No passo 1, o comando EXECUTE IMMEDIATE cria tabela T_PRODUTO (que contém dois campos: ID e NOME) de forma dinâmica. A seguir, no passo 2 ocorre um processamento bastante interessante. Uma instrução INSERT é montada com dois bind arguments (:1 e :2). Na linha seguinte o EXECUTE IMMEDIATE é chamado para executar este comando INSERT. A cláusula USING é aplicada para substituir os bind arguments pelos valores das variáveis vID e vNOME (99 e ‘Tangerina’, respectivamente). Finalizando um programa, o comando EXECUTE IMMEDIATE é novamente chamado para atribuir GRANT de SELECT para o usuário SCOTT (o que representa a execução de um comando DCL).
