Social Icons

14 de jun de 2015

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.
  

0 comentários:

Postar um comentário

 

Meus últimos Links Favoritos

Suporte remoto alunos

Seguidores

Meu One Drive (antigo Sky Drive)