Executando SQL dinâmico no Oracle Database

Olá pessoal,

     No artigo de hoje vou comentar sobre SQL dinâmico, além de explicar quando você realmente deve utilizá-lo, ao invés de escrever SQL estático. 

      Primeiramente, para ajudar aqueles que são leigos no assunto, instruções SQL dinâmicas foram criadas no Oracle 8i e são usadas para criar instruções SQL que possuem estrutura indefinida em tempo de compilação, ou seja, durante a codificação, não há ainda a definição exata da instrução SQL, ela será "montada" dinamicamente. As instruções SQL dinâmicas podem ser codificadas através de 2 formas:

          1- Package DBMS_SQL:

              - Possui alguns recursos a mais, mas é mais difícil de usar (requer mais trabalho e mais código);

              - Método menos performático, pois não executa SQL Nativo.

          2- Instrução EXECUTE IMMEDIATE:

              - Método recomendado pela Oracle;

              - É mais simples de codificar e mais rápido de executar;

              - Sintaxe:

          EXECUTE IMMEDIATE string_dinamica

          [INTO {variavel [, variavel]... | registro}]

          [USING [IN | OUT | IN OUT] valor [, IN | OUT | IN OUT] valor] ... ];

     Para entender melhor o conceito de SQL dinâmico, tente criar um stored procedure (SP) contendo uma instrução SQL estática com a sintaxe errada. Exemplo:

CREATE OR REPLACE PROCEDURE SP_TESTE_SQL IS
  V_CONT NUMBER;
BEGIN
    SELECT COUNT(1) INTO V_CONT FROM ALL_TABELAS;
    DBMS_OUTPUT.PUT_LINE('Total de tabelas: ' || TO_CHAR(V_CONT));
END;

     Se você executou o comando DDL acima no SQL Developer, você verá na janela de saída do script, uma mensagem de erro de compilação similar à da imagem abaixo:

Imagem 01: SP_TESTE_SQL compilada com erros

     A mensagem indica que o SP SP_TESTE_SQL foi compilado com erros. O erro está ocorrendo na compilação porque o código SQL está tentando recuperar dados de um objeto inexistente no Banco de Dados (BD), chamado ALL_TABELAS. O nome correto deste objeto é ALL_TABLES, portanto, para que a compilação ocorra sem erros, você terá que corrigir o nome do objeto dentro do código do SP, como no exemplo abaixo:

CREATE OR REPLACE PROCEDURE SP_TESTE_SQL IS
  V_CONT NUMBER;
BEGIN
    SELECT COUNT(1) INTO V_CONT FROM ALL_TABLES;
    DBMS_OUTPUT.PUT_LINE('Total de tabelas: ' || TO_CHAR(V_CONT));
END;

  Veja na imagem a seguir, que após executar o código acima, a mensagem de retorno na janela de saída do script agora é diferente, indicando apenas que a compilação ocorreu com sucesso:

Imagem 02: SP_TESTE_SQL compilada com sucesso

     Observe agora que temos SQL estático nas 2 versões do SP, mas como podemos chegar à conclusão de que estes SQLs são realmente estáticos? Simples: no momento da compilação o Oracle faz a validação da sintaxe dos SQLs sem executá-los, por isso, a 1ª versão do SP contendo um nome de objeto inválido foi compilada com erros.

     Agora vamos escrever código SQL dinâmico similar ao código da 1ª versão do SP:

CREATE OR REPLACE PROCEDURE SP_TESTE_SQL IS
BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM ALL_TABELAS';
END;

     Ao executá-lo veremos uma diferença no resultado da compilação (o código foi compilado com sucesso):  
 

Imagem 03: SP_TESTE_SQL contendo SQL dinâmico compilado com sucesso

     Agora vem a pergunta principal:
          - Por que o objeto SP_TESTE_SQL contendo SQL dinâmico foi compilado com sucesso?

          Porque SQL dinâmico é interpretado somente em tempo de execução, ou seja, no momento em que o SP foi compilado o SQL não foi interpretado!

Obs.: Agora apague o SP criado executando o comando abaixo:
     DROP PROCEDURE SP_TESTE_SQL;
  
  

Quando devemos executar SQL dinâmico?

     SQL dinâmico deve ser utilizado em 2 situações: quando SQL estático não pode ser utilizado (exemplo: código SQL em que os objetos a serem acessados não são conhecidos no momento em você está escrevendo o código SQL) ou para substituir muitas linhas de código SQL (estático) por poucas linhas de código (dinâmico).

Por que evitar SQL dinâmico nas demais situações?

     Em instruções SQL estáticas, após a 1º execução de uma instrução SQL, o otimizador do Oracle executará um Soft Parse, reutilizando código e um plano de execução que foi gerado anteriormente, otimizando deste modo, a execução da instrução SQL. Em instruções SQL dinâmicas, o otimizador do Oracle sempre executará um Hard Parse, ou seja, ele nunca reutilizará código e planos de execução da mesma instrução SQL que foi executada anteriormente. Em termos de performance, Hard Parse é pior do que Soft Parse, por isso que SQL dinâmico é indicado em pouquíssimas situações.

     Para aprender mais sobre SQL dinâmico e ver exemplos reais de situações onde ele pode ser muito bem utilizado, inclusive para otimizar determinados tipos de instruções SQL, participe do treinamento de SQL Tuning.

Bom pessoal, por hoje é só!

Qualquer dúvida é só deixar um comentário.