SQL Dinâmico no Oracle
Com esta dica pretendo apresentar recursos que auxiliem no uso de SQL Dinâmico nativo do banco de dados Oracle. Este recurso permite a criação de interfaces de programação (com um PL/SQL, um Pro*Cobol, Form, etc) mais flexíveis e versáteis (com construções de comandos em tempo de execução).
Muitos programas realizam operações específicas e previsíveis, como solicitar um código de um produto e atualizar o seu valor em um percentual pré-determinado. Neste caso, o texto completo do comando, por exemplo, UPDATE, é conhecido em tempo de compilação, pois tal comando não muda de execução para execução, o que é conhecido como comando SQL estático.
Por outro lado, alguns programas precisam construir e processar comandos SQL definidos em tempo de execução. Por exemplo, em um relatório genérico o desenvolvedor precisa construir diferentes comandos SELECT para a geração de vários relatórios diferentes, com base em escolhas selecionadas em tempo de execução pelo usuário final da aplicação. Neste caso, o texto completo do comando é conhecido somente no momento de sua execução (ou seja, ele pode mudar de execução para execução), sendo conhecido como comando SQL dinâmico.
|
Um SQL Dinâmico é um comando SQL ou um bloco PL/SQL válido, codificado dentro de uma string (populada em tempo de execução) e pode ser executado através do uso do comando EXECUTE IMMEDIATE, a partir da versão 8i do banco de dados Oracle. Esse tipo de comando SQL pode conter placeholders para bind (host) arguments (como demonstrado na sintaxe adiante). Um placeholder é um identificador não declarado, então o valor que lhe é atribuído substitui uma variável da composição da sintaxe do comando, sendo que é preciso prefixar tal coluna com o símbolo : (dois pontos). |
Com o uso de SQL Dinâmico é possível, então, flexibilizar sistemas e adicionar a possibilidade de execução de comandos DDL (Data Definition Language – Linguagem de Definição de Dados, como CREATE TABLE, TRUNCATE TABLE, ALTER TABLE, ALTER SESSION, GRANT, etc) dentro de blocos PL/SQL (em um bloco PL/SQL esses comandos não podem ser executados estaticamente).
Um comando DML (Data Manipulation Language – Linguagem de Manipulação de Dados, como SELECT, UPDATE, DELETE e INSERT) de conteúdo flexível (como diferentes condições para uma cláusula WHERE ou SELECT) também pode ser executado através de SQL Dinâmico.
Sintaxe:
EXECUTE IMMEDIATE ‘SQL string'
[INTO {variável[, variável]...
| record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...];
Onde:
• A SQL String é uma string que contém aquilo que se deseja executar. Com exceção de consultas que retornem mais de uma linha, a string pode conter qualquer comando SQL (sem o terminador, senão será considerado um bloco PL/SQL) ou qualquer bloco PL/SQL (com o terminador). A string também pode conter placeholders (por exemplo :vl_salario) e bind_arguments. No caso da SQL String representar um PL/SQL é necessário que este contenha, pelo menos “begin” e “end”.
• Na cláusula INTO , a especificação de variáveis é opcional e indica uma ou mais variáveis para as quais valores selecionados (em uma consulta constante na SQL String) serão atribuídos. Já um record é baseado em um TYPE ou %ROWTYPE especificado pelo usuário e que pode receber uma linha inteira retornada por uma consulta constante na SQL String. Em suma, esta cláusula somente é utilizada quando a SQL String for uma consulta (SELECT) que retorne somente uma linha e o tipo da variável ou registro deve ser compatível com o valor a ser recebido.
• Na cláusula USING , a seção bind_argument (parâmetros) é opcional e designa uma valor / argumento a ser atribuído / repassado para bind variables na SQL string. Os bind_arguments não podem ser utilizados para repassar nomes de objetos de um esquema (como nomes de tabelas ou de colunas). Podem ser utilizadas expressões numéricas, alfanuméricas e de datas, mas nunca um valor do tipo booleano ou de conteúdo NULL. Os tipos definidos pelo usuário, como objetos, coleções e REFs (tipos não suportados pela package DBMS_SQL) também são suportados pelo comando EXECUTE IMMEDIATE. Todo bind_argument deve constar na cláusula USING e em tempo de execução, todo bind_argument na cláusula USING irá repassar um correspondente placeholder na SQL string.
Observação 1: Para a execução de commandos DDL, é necessário que tenham sido concedidos os privilégios necessários para o usuário de execução, caso contrário, o erro ORA-1031 “Insufficient privileges” será apresentado.
Observação 2: O comando EXECUTE IMMEDIATE não é reconhecido na ferramenta Oracle Developer 6.0, bem como pelo PL/SQL 8.0.6.3.
Algumas considerações devem ser feitas:
1) EXECUTE IMMEDIATE não realizará automaticamente o COMMIT de uma transação DML anterior. Se um comando DML é processado via EXECUTE IMMEDIATE é necessário um COMMIT explícito para efetivar transações pendentes antes ou como parte do próprio EXECUTE IMMEDIATE. Se um commando DDL é processado via EXECUTE IMMEDIATE irá, automaticamente, as transações pendentes.
2) Consultas que retornem mais de uma linha não são suportadas como valor de retorno e neste caso, a alternativa é utilizar uma tabela temporária para armazenar os registros ou utilizar REF cursores. A seguir é apresentado um exemplo de um comando EXECUTE IMMEDIATE que popula uma tabela temporária para futuro processamento.
3) Não é possível utilizar o estilo de comentário do padrão ANSI (- - ...) em um bloco PL/SQL que será processado dinamicamente, pois o que houver após estes caracteres será ignorado. Aconselha-se então, a utilizar o estilo de comentário da linguagem C (/* ... */).
4) Comandos SQL criados e executados dinamicamente apresentam overhead em performance, mas o comando EXECUTE IMMEDIATE visa reduzir este overhead e dar maior elasticidade de performance. Em geral, o impacto em performance não é muito significante, mas pode ocorrer. Se um procedimento executado diretamente usar 0.04 segundos, executado dinamicamente poderia levar 0.4 segundos. No entanto, isso ocorre apenas na primeira vez em que a rotina for executada. Nas execuções seguintes do procedimento, em ambos os casos o tempo será de 0.04 segundos, pois o código é compilado e passa a residir em memória, sendo executado tão rapidamente como se fosse de execução direta. A performance de um PL/SQL utilizando EXECUTE IMMEDIATE é muito melhor do que utilizando os componentes da package DBMS_SQL. Com EXECUTE IMMEDIATE o comando SQL dinâmico é interpretado todas as vezes que é executado, sendo necessário configurar a variável de ambiente HOLD_CURSOR=YES.
5) Até a versão 8 do banco de dados Oracle, comandos SQL dinâmicos podiam ser executados apenas através da package DBMS_SQL, porém o comando EXECUTE IMMEDIATE é um método nativo que analisa a sintaxe e executa imediatamente um comando SQL dinâmico. O SQL dinâmico nativo é de mais fácil codificação, mais amigável, eficiente e de melhor performance que os comandos e funções da package DBMS_SQL. Por ser integrado com o SQL é possível utilizá-lo da mesma forma que se utilizaria um SQL estático, sendo mais compacto e mais facilmente legível do que com o uso dos componentes da package DBMS_SQL. A maior dificuldade no uso da package DBMS_SQL é a existência de muitos procedimentos e funções que devem ser utilizadas em uma determinada sequência, o que faz com que operações simples requeiram uma codificação muito grande com o uso desta package.
Alguns Exemplos:
Exemplo 1: Inclusão de dados em uma tabela com base em um SELECT que recebe parâmetros:
Clique aqui para baixar o script.
Exemplo 2: Criação de um procedimento para reorganizar os objetos de um esquema, redistribuindo-os nas suas tablespaces de dados e de índices, desfragmentando tais tablespaces e excutando o ANALYZE do esquema (para demonstração o esquema será DBEXEMPLO). O usuário somente conseguirá executar tal procedimento caso tenha os privilégios de sistema e objetos necessários:
Clique aqui para baixar o script.
Exemplo 3: Função que retorna a quantidade de linhas de uma tabela de um esquema em determinada condição:
Clique aqui para baixar o script.
Exemplo 4: Carga de dados, onde as tabelas são truncadas (não gerando transação), ao invés de serem deletadas, gerando ganho performance. Criação de tabela de Log para a carga, com a inclusão de seus respectivos dados:
Clique aqui para baixar o script.
Até Mais
Damaris Fanderuff
