SQL Dinâmico no Oracle

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
Confirmar voto
0
 (12)  (1)

Veja recursos que auxiliam o uso de SQL dinâmico nativo do banco de dados Oracle.

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 em 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

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?