SQL Dinmico no Oracle

SQL Dinmico no Oracle Com esta dica pretendo apresentar recursos que auxiliem no uso de SQL Dinmico nativo do banco de dados Oracle. Este recurso permite a criao de interfaces de programao (com um PL/SQL, um Pro*Cobol, Form, etc) mais flexveis e versteis (com construes de comandos em tempo de execuo). Muitos programas realizam operaes especficas e previsveis, como solicitar um cdigo 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 compilao, pois tal comando no muda de execuo para execuo, o que conhecido como comando SQL esttico. Por outro lado, alguns programas precisam construir e processar comandos SQL definidos em tempo de execuo. Por exemplo, em um relatrio genrico o desenvolvedor precisa construir diferentes comandos SELECT para a gerao de vrios relatrios diferentes, com base em escolhas selecionadas em tempo de execuo pelo usurio final da aplicao. Neste caso, o texto completo do comando conhecido somente no momento de sua execuo (ou seja, ele pode mudar de execuo para execuo), sendo conhecido como comando SQL dinmico. Um SQL Dinmico um comando SQL ou um bloco PL/SQL vlido, codificado dentro de uma string (populada em tempo de execuo) e pode ser executado atravs do uso do comando EXECUTE IMMEDIATE, a partir da verso 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 no declarado, ento o valor que lhe atribudo substitui uma varivel da composio da sintaxe do comando, sendo que preciso prefixar tal coluna com o smbolo : (dois pontos).

Com o uso de SQL Dinmico possvel, ento, flexibilizar sistemas e adicionar a possibilidade de execuo de comandos DDL (Data Definition Language Linguagem de Definio 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 no podem ser executados estaticamente). Um comando DML (Data Manipulation Language Linguagem de Manipulao de Dados, como SELECT, UPDATE, DELETE e INSERT) de contedo flexvel (como diferentes condies para uma clusula WHERE ou SELECT) tambm pode ser executado atravs de SQL Dinmico. Sintaxe:

EXECUTE IMMEDIATE SQL string' [INTO {varivel[, varivel]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...]; Onde: A SQL String uma string que contm aquilo que se deseja executar. Com exceo de consultas que retornem mais de uma linha, a string pode conter qualquer comando SQL (sem o terminador, seno ser considerado um bloco PL/SQL) ou qualquer bloco PL/SQL (com o terminador). A string tambm pode conter placeholders (por exemplo :vl_salario) e bind_arguments. No caso da SQL String representar um PL/SQL necessrio que este contenha, pelo menos begin e end. Na clusula INTO , a especificao de variveis opcional e indica uma ou mais variveis para as quais valores selecionados (em uma consulta constante na SQL String) sero atribudos. J um record baseado em um TYPE ou %ROWTYPE especificado pelo usurio e que pode receber uma linha inteira retornada por uma consulta constante na SQL String. Em suma, esta clusula somente utilizada quando a SQL String for uma consulta (SELECT) que retorne somente uma linha e o tipo da varivel ou registro deve ser compatvel com o valor a ser recebido. Na clusula USING , a seo bind_argument (parmetros) opcional e designa uma valor / argumento a ser atribudo / repassado para bind variables na SQL string. Os bind_arguments no podem ser utilizados para repassar nomes de objetos de um esquema (como nomes de tabelas ou de colunas). Podem ser utilizadas expresses numricas, alfanumricas e de datas, mas nunca um valor do tipo booleano ou de contedo NULL. Os tipos definidos pelo usurio, como objetos, colees e REFs (tipos no suportados pela package DBMS_SQL) tambm so suportados pelo comando EXECUTE IMMEDIATE. Todo bind_argument deve constar na clusula USING e em tempo de execuo, todo bind_argument na clusula USING ir repassar um correspondente placeholder na SQL string. Observao 1: Para a execuo de commandos DDL, necessrio que tenham sido concedidos os privilgios necessrios para o usurio de execuo, caso contrrio, o erro ORA-1031 Insufficient privileges ser apresentado. Observao 2: O comando EXECUTE IMMEDIATE no reconhecido na ferramenta Oracle Developer 6.0, bem como pelo PL/SQL 8.0.6.3. Algumas consideraes devem ser feitas: 1) EXECUTE IMMEDIATE no realizar automaticamente o COMMIT de uma transao DML anterior. Se um comando DML processado via EXECUTE IMMEDIATE necessrio um COMMIT explcito para efetivar transaes pendentes antes ou como parte do prprio EXECUTE IMMEDIATE. Se um commando DDL processado via EXECUTE IMMEDIATE ir, automaticamente, as transaes pendentes. 2) Consultas que retornem mais de uma linha no so suportadas como valor de retorno e neste caso, a alternativa utilizar uma tabela temporria para armazenar os registros ou utilizar REF cursores. A seguir apresentado um exemplo de um comando EXECUTE IMMEDIATE que popula uma tabela temporria para futuro processamento. 3) No possvel utilizar o estilo de comentrio do padro ANSI (- - ...) em um bloco PL/SQL que ser processado dinamicamente, pois o que houver aps estes caracteres ser ignorado. Aconselha-se ento, a utilizar o estilo de comentrio 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 no 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 execues seguintes do procedimento, em ambos os casos o tempo ser de 0.04 segundos, pois o cdigo compilado e passa a residir em memria, sendo executado to rapidamente como se fosse de execuo 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 dinmico interpretado todas as vezes que executado, sendo necessrio configurar a varivel de ambiente HOLD_CURSOR=YES. 5) At a verso 8 do banco de dados Oracle, comandos SQL dinmicos podiam ser executados apenas atravs da package DBMS_SQL, porm o comando EXECUTE IMMEDIATE um mtodo nativo que analisa a sintaxe e executa imediatamente um comando SQL dinmico. O SQL dinmico nativo de mais fcil codificao, mais amigvel, eficiente e de melhor performance que os comandos e funes da package DBMS_SQL. Por ser integrado com o SQL possvel utiliz-lo da mesma forma que se utilizaria um SQL esttico, sendo mais compacto e mais facilmente legvel do que com o uso dos componentes da package DBMS_SQL. A maior dificuldade no uso da package DBMS_SQL a existncia de muitos procedimentos e funes que devem ser utilizadas em uma determinada sequncia, o que faz com que operaes simples requeiram uma codificao muito grande com o uso desta package. Alguns Exemplos: Exemplo 1: Incluso de dados em uma tabela com base em um SELECT que recebe parmetros: Exemplo 2: Criao 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 demonstrao o esquema ser DBEXEMPLO). O usurio somente conseguir executar tal procedimento caso tenha os privilgios de sistema e objetos necessrios:Exemplo 3: Funo que retorna a quantidade de linhas de uma tabela de um esquema em determinada condio: Exemplo 4: Carga de dados, onde as tabelas so truncadas (no gerando transao), ao invs de serem deletadas, gerando ganho em performance. Criao de tabela de Log para a carga, com a incluso de seus respectivos dados: