domingo, 4 de maio de 2008

SQL dinâmico no Oracle

Existe alguma forma de criar dinamicamente um comando SQL, tanto DML quanto DDL, no Oracle?

Sim, é possível. A partir da versão 8i podemos usar o "EXECUTE IMMEDIATE" statement para executar um comando SQL montado em tempo de execução. Essa funcionalidade foi introduzida para substituir o “DBMS_SQL package”. Veja alguns exemplos abaixo:

-- Executando comando DDL “Create”
EXECUTE IMMEDIATE 'CREATE TABLE MyZNTable (ZN_ID NUMBER, ZNDescricao Varchar2(30))';

--- executando comando DML “INSERT”
EXECUTE IMMEDIATE 'insert into MyZNTable values (1, ''ZNteste'') ’;



Esse comando é um recurso nativo do Oracle para abordagem de SQL dinâmico, apropriado para tuning pois reduz overhead, otimiza a performance do comando SQL. Considerando que, Comandos SQL criados e executados dinamicamente apresentam overhead em performance. Utilização do comando "EXECUTE IMMEDIATE" reduz este overhead e proporciona maior performance.
Através dessa técnica podemos também usar placeholders para bind.
O que é placeholder? R - Um placeholder é um identificador não declarado que vai servir como parâmetro, cujo o valor será atribuído dinamicamente. O compilador SQL vai entender o identificador porque ele estará marcado com o prefixo, o sinal de “:” (dois pontos).

DECLARE
V_NUM_CONTRATO varchar2(20) := 'ZN1000';
V_NUM_GUIA varchar2(10) := '3000ZN';
ZnSQL_TMP VARCHAR2 (100);
BEGIN
-- Busca ASSOCIADO, DATA DE CADASTRO, e ORIGEM
ZnSQL_TMP := ' SELECT
DATA_CADASTRO, COD_ASSOCIADO, ASSOCIADO_ORIGEM
FROM
ASSOCIADOS
WHERE
NUM_CONTRATO = :NUM_CONTRATO AND
NUM_GUIA = :NUM_GUIA AND
ROWNUM = 1';

EXECUTE IMMEDIATE ZnSQL_TMP
USING V_NUM_CONTRATO
, V_NUM_GUIA;

END;



Acredito que isso pode facilitar muito o trabalho de desenvolvedor. Portanto, vejamos maiores detalhes sobre a sintaxe desse comando:

EXECUTE IMMEDIATE ZNSQL_string
[INTO {variável[, variável]...
| record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...];

Analisando a sintaxe:

ZNSQL_string : Trata-se de uma string com comando SQL construído dinamicamente, conforme exemplificamos anteriormente. Essa string pode conter qualquer comando SQL sem o terminador. Comandos DML “SELECT” não devem retornar mais de uma linha. A string também pode conter placeholders (exemplo: :ZN_ASSOCIADO) e bind_arguments. Um detalhe importante, caso o camando SQL na string for uma chamada a uma procedure ou function (ou seja, uma PL/SQL) é necessário o emprego de “begin” e “end”.

INTO (palavra reservada): Indica quais variáveis (uma ou mais) cujos valores selecionados, em uma consulta, serão atribuídos. O record é um TYPE ou %ROWTYPE definido pelo usuário. Ele pode receber uma linha inteira retornada por uma consulta. Atenção, esta cláusula é opcional. É utilizada somente quando a String for uma consulta que retorne somente uma linha. Além disso, tanto a quantidade de variáveis empregadas, quanto a ordem em que elas estarão dispostas no comando, deve respeitar a quantidade e a ordem das colunas retornadas na consulta. Do mesmo modo, o tipo da variável ou registro (record) deve ser compatível com o valor que lhe será atribuído.

USING (palavra reservada): seção onde declaramos os valores atribuídos a cada argumento na bind variables da string. Não é permitido fazer bind para repassar nomes de objetos de um esquema, como nomes de tabelas ou de colunas. Também não é permitido valor booleano ou NULL. Suponha que o código exemplificado abaixo seja o trecho de uma procedure ou function:


SQL_TMP VARCHAR2 (1000);
BEGIN

SQL_TMP := ' SELECT
DATA_REALIZACAO, COD_ASSOCIADO, ORIGEM
FROM
ASSOCIADOS
WHERE
MES_REF = :MES_REF AND
COD_CONTADOR = :COD_CONTADOR AND
NUM_CONTRATO = :NUM_CONTRATO AND
NUM_GUIA = :NUM_GUIA AND
ROWNUM = 1';
BEGIN
EXECUTE IMMEDIATE SQL_TMP
INTO V_DATA_REALIZACAO, V_COD_ASSOCIADO, V_ORIGEM
USING V_MES_REF
, V_COD_CONTADOR
, V_NUM_CONTRATO
, V_NUM_GUIA;
EXCEPTION
WHEN no_data_found THEN
V_DATa_ZN := NULL;
V_NUM_ASSOCIADO_ZN := NULL;
END;

Preciso retornar várias linhas de uma consulta usando SQL dinâmico. Isso é possível?

Como mencionei anteriormente, consultas que retornem mais de uma linha não são suportadas como valor de retorno. Entretanto, existem alternativas: Ou utilizar uma tabela temporária para armazenar os registros, ou ref cursores.

Adiante, pretendo falar mais sobre o emprego do “EXECUTE IMMEDIATE”.

2 comentários:

  1. Porque a Oracle foi deixar para a versão 8i um recurso básico como esse, comum a outros bancos de dados a muito mais tempo, enquanto por outro lado adiantou-se em features originais e avançados muito antes de todos os outros eu não consigo entender. :-)

    A propósito, acho que tem um "BEGIN" a mais no segundo bloco de código.

    Um abraço,
    Malta.

    ResponderExcluir
  2. Malta, obrigado pelo comentário!
    Vou aproveitar o gancho e esclarecer um pouco mais para outras pessoas ...
    Nas versões anteriores ao 8i o único recurso no Oracle para SQLs dinâmicos era a “DBMS_SQL package”. Tanto para comando DDL, quanto para DML. Nesta package encontra-se as pls próprias para este fim:
    procedures: PARSE, BIND_ARRAY, BIND_VARIABLE, CLOSE_CURSOR, DEFINE_ARRAY etc ..
    functions: EXCEUTE, FETCH_ROWS, IS_OPEN, e OPEN_CURSOR, etc.

    O “EXECUTE IMMEDIATE” veio trazer vantagens, vejamos:
    Reduzir sensivelmente a quantidade de código em relação “DBMS_SQL package”.
    Ele faz o parse e executa o comando imediatamente (ganho de performance).
    Vou tentar colocar um exemplo possibilitando a comparação em termos de codificação entre a aplicação das duas abordagens.

    Begin sobrando
    Obrigado pelo aviso, tem um “Begin” sobrando realmente. Originalmente, havia um bloco de tratamento (Begin | exception | End), na adaptação para o exemplo acabei deixando o “Begin” sobrando. Já estou corrigindo.

    Concordo com vc na falta de justificativa para ausência de várias features importantes, presente em produtos de outros fornecedores, na PL/SQL. Por exemplo, algo que me incomoda muito é a abordagem do conceito de domínios pela ferramenta, acho obscura (se é que podemos considerar que exista). Outra coisa, a não adoção do conceito de domínios pela comunidade desenvolvedores e DBAs Oracle, acho isso um absurdo.
    Vlw Malta, obrigado pela força!!!
    A equipe do Estação ZN agradece.

    ResponderExcluir

 
BlogBlogs.Com.Br