O comando EXECUTE IMMEDIATE está presente no “package” DBMS_SQL desde a
versão 8i. Este comando permite executar imediatamente um SQL ou um bloco
PL/SQL durante a sua criação.
Muitas vezes a criação e execução de SQL’s dinâmicos podem comprometer
significativamente o desempenho do banco de dados. Porém o comando “execute
immediate” reduz esse problema e ajuda a obter um desempenho melhor, além de
tornar mais amigável a programação de códigos PL/SQL.
A partir da versão do Oracle 11g o comando “execute immediate” permite o uso de tipos de dados CLOB como um argumento.
Dicas de uso
§ O comando “execute immediate” não
grava uma transação de DML, para gravá-lo uma instrução de “commit” deverá ser
executada. Já um comando DDL, quando
processado via “execute immediate”, será gravado diretamente.
§ Para consultas que retornam mais de uma linha este comando não é
suportado, como alternativa deve-se criar uma tabela temporária para gravar os
registros (ver exemplo abaixo), ou usar cursores do tipo REF.
Abaixo são apresentadas
as possibilidades para utilizar o comando “execute immediate”.
Exemplos
1.
Executando um comando DDL em um código PL/SQL.
begin
execute immediate 'set role all';
end;
2.
Passando valores para um SQL dinâmico (utilizando a
cláusula – USING).
declare
l_depnome varchar2(20) := 'TESTE';
l_local varchar2(10) := 'Brasil';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnome, l_local;
commit;
end;
3.
Retornando valores de um SQL dinâmico (utilizando a
cláusula – INTO).
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
4. Chamando uma rotina dinamicamente.
l_rotina varchar2(100) := 'gen2161.get_rowcnt';
l_nome_tbl varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_rotina || '(:2, :3, :4); end;'
using in l_nome_tbl, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('Erro !');
end if;
end;
5.
Retornando um valor para dentro de um
registro em um código PL/SQL. A mesma opção pode ser usada para variáveis do
tipo “%rowtype”.
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
6.
Passando e retornando valores. Na cláusula
INTO deve-se utilizar a cláusula USING.
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
7.
Para consultas que retornem mais de uma linha. Usar o
comando “insert” para popular uma tabela temporária.
declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;
Importante: O comando “execute immediate” possui um método muito mais fácil e
eficiente para processar SQL’s dinâmicos. Porém não esquecer
que quando a intenção é executar SQL’s dinâmicos, o tratamento de exceções
torna-se muito importante.
Referências: Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2)
MSc.
Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com
