DevMedia
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login

SQL Dinâmico no Oracle – Parte 2

Veja neste artigo a utilização do comando EXECUTE IMMEDIATE na implementação de consultas dinâmicas no Oracle.

[fechar]

Você não gostou da qualidade deste conteúdo?

(opcional) Você poderia comentar o que não lhe agradou?

Confirmo meu voto negativo



Eduardo Corrêa Gonçalves

Instituto Brasileiro de Geografia e Estatística – IBGE

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

 

1. Introdução


Este artigo discute a utilização do comando EXECUTE IMMEDIATE na implementação de consultas dinâmicas no Oracle.  

2. 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, seqüences, 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

 

3. 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).

3. Próximos Artigos

        

O próximo artigo desta série apresentará os cursores dinâmicos. Estes cursores são implementados através dos comandos OPER-FOR, FETCH e CLOSE e devem ser utilizados sempre que houver a necessidade de executar um comando SELECT dinâmico que retorne mais de um registro.



Doutorando e mestre em Ciência da Computação pelo Instituto de Computação da Universidade Federal Fluminense (IC/UFF). Atua principalmente nas seguintes linhas de pesquisa: Mineração de Dados, Algoritmos, Banco de Dados e XML.

O que você achou deste post?
Conhece a assinatura MVP?
Publicidade
Serviços

Mais posts