SQL Dinâmico

SQL Dinâmico

O SQL dinâmico permite que se crie procedimentos de propósito mais geral. Por exemplo, utilizando o SQL dinâmico você pode escrever um procedimento que opere sobre uma tabela cujo nome só será conhecido em tempo de execução. Ela permite ainda o uso da linguagem de definição de dados (DDL), isto é, permite que se crie (CREATE), altere (ALTER) e remova (DROP) objetos dentro de um procedimento ou bloco PL/SQL, o que não é permitido com declarações do SQL estático.

O SQL dinâmico é implementado pela package DBMS_SQL.

Usando a DBMS_SQL

Para que uma declaração SQL dinâmico possa ser processada é preciso que haja um cursor que guarde o identificador da declaração, este cursor (do tipo INTEGER) é diferente dos cursores convencionais da PL/SQL e é usado apenas pela biblioteca DBMS_SQL. O identificador é atribuído ao cursor pela função OPEN_CURSOR.

O próximo passo é se analisar gramaticalmente a declaração, utilizando o procedimento PARSE, ele analisa a sintaxe da consulta e a associa ao cursor. Caso a declaração analisada seja uma DDL ela será executada pelo procedimento e um commit implícito também será executado, portanto não é necessário executar o procedimento EXECUTE.

Se a declaração possuir referências a variáveis de ambiente, então é necessário que se insira um marcador para estas variáveis dentro da declaração, colocando o sinal (:) antes da variável, como em :nome. Depois que a declaração foi analisada pode-se ligar o valor às variáveis utilizando o procedimento BIND_VARIABLE, e quando a declaração for executada as variáveis serão substituídas pelos respectivos valores.

As colunas selecionadas por uma declaração SELECT são identificadas por suas posições relativas dentro da declaração. Em uma consulta você deve usar o procedimento DEFINE_COLUMN para especificar quais as variáveis que receberão que serão gerados quando a consulta for executada. Para colunas do tipo LONG é utilizado o procedimento DEFINE_COLUMN_LONG.

A declaração é executada pela função EXECUTE. Caso a declaração seja INSERT, UPDATE ou DELETE a função retorna o número de linhas processadas, caso contrário seu valor pode ser desprezado.

Se você definiu uma declaração SELECT então os valores retornados pela consulta podem ser extraídos do cursor utilizando-se a função FETCH_ROWS, esta função retorna 0 (zero) caso não haja mais registros a serem extraídos do cursor.

Se a declaração for uma consulta, os valores obtidos como resultado podem ser atribuídos a variáveis usando o procedimento COLUMN_VALUE ou COLUMN_VALUE_LONG para colunas do tipo LONG, caso a declaração seja uma chamada a um bloco PL/SQL, então o valor retornado por este bloco pode ser obtido com o procedimento VARIABLE_VALUE.

Quando você não for mais utilizar o cursor, ele deve ser fechado com o procedimento CLOSE_CURSOR para evitar que o bloco da memória não permaneça desnecessariamente alocada.

Procedimentos, Funções e Parâmetros

BIND_VARIABLE(cursor, variável, valor, [tamanho]) ; Permite a atribuição de um valor à variáveis de ambiente, tamanho é utilizado para variáveis do tipo VARCHAR2.

CLOSE_CURSOR(cursor) ; Fecha o cursor e libera a de memória associada.

COLUMN_VALUE(cursor, posição, variável[,erro][,tamanho]) ; Recupera um valor do cursor para a variável.

DEFINE_COLUMN(cursor, posição, variável) ; Associa colunas ou expressões da consulta com a variável.

EXECUTE(cursor) ; retorna INTEGER Executa a declaração associada ao cursor e retorna o número de linhas processadas.

FETCH_ROWS(cursor) ; retorna INTEGER Extrai do cursor os valores resultantes da consulta, retorna 0 (zero) caso não haja mais linhas.

IS_OPEN(cursor) ; retorna BOOLEAN Retorna TRUE se o cursor já estiver aberto e FALSE caso contrário.

OPEN_CURSOR ; retorna INTEGER Esta função "abre" o cursor e retorna um identificador para ele.

PARSE(cursor, declaração, flag) ; Analisa a declaração. flag especifica o comportamento que o Oracle terá durante a análise, pode ser: DBMS_SQL.V6 (para Versão 6), DBMS_SQL.V7 (para Versão 7) e

DBMS_SQL.NATIVE (para utilizar o comportamento de acordo com a base à qual ele está conectado)

VARIABLE_VALUE (cursor, variável_da_declaração, variável,[tamanho]) ; Recupera o valor da variável_da_declaração para a variável do bloco PL/SQL.

Usando SQL Dinâmico para Chamar Stored Procedures e Functions

Você pode também utilizar o SQL dinâmico para chamar stored procedures e functions de dentro de seu bloco PL/SQL. Para isto os passos são os seguintes:

- Chamar DBMS_SQL.OPEN_CURSOR para abrir um cursor;

- Se existir variáveis bind, vincule-as utilizando a procedure DBMS_SQL.BIND_VARIABLE;

- Execute a procedure ou function utilizando DBMS_SQL.EXECUTE;

- Se houver variáveis bind, chame DBMS_SQL.VARIABLE_VALUE para obter os valores associados a elas;

- Feche o cursor com a procedure DBMS_SQL.CLOSE_CURSOR.

Quando você utiliza a package DBMS_SQL para executar uma procedure ou function as variáveis passadas como parâmetro são tratadas como sendo bind, o mesmo acontece para variáveis que recebem os valores retornados por functions.

As variáveis associadas a parâmetros do tipo IN ou IN OUT devem ter valores associados antes que eles sejam passados para a procedure ou function. As variáveis do tipo VARCHAR2 ou CHAR que serão vinculadas a parâmetros do tipo OUT, devem ser inicializadas ou seu tamanho máximo deve ser passado para a procedure DBMS_OUTPUT.BIND_VARIABLE através do parâmetro tamanho.

Exemplos de SQL Dinâmico

Exemplo 1

O exemplo seguinte cria um procedimento (stored procedure) que realiza uma consulta em uma coluna de uma tabela, o nome da coluna e da tabela são passados como parâmetro para o procedimento.

CREATE OR REPLACE PROCEDURE SQL_DINAMICO(coluna IN VARCHAR2, tabela IN

VARCHAR2) IS

cur_handle INTEGER;

texto VARCHAR2(80);

temp NUMBER;

BEGIN

cur_handle := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur_handle, 'SELECT '||coluna||' FROM '||tabela, DBMS_SQL.V7);

DBMS_SQL.DEFINE_COLUMN(cur_handle, 1, texto, 80);

temp := DBMS_SQL.EXECUTE(cur_handle);

LOOP

IF DBMS_SQL.FETCH_ROWS(cur_handle) = 0

THEN EXIT;

ELSE

DBMS_SQL.COLUMN_VALUE(cur_handle, 1, texto);

DBMS_OUTPUT.PUT_LINE(texto);

END IF;

END LOOP;

DBMS_SQL.CLOSE_CURSOR(cur_handle);

END SQL_DINAMICO;

/

Como neste caso a declaração SQL é uma consulta (SELECT) o valor da variável temp pode ser desprezado.

O procedimento pode ser criado através do SQL*Plus com o seguinte comando (supondo que o código esteja no arquivo EXEMPLO1.SQL) :

SQL> @EXEMPLO1.SQL

Este procedimento pode ser utilizado dentro de outros procedimentos ou blocos PL/SQL, ou pode ainda ser executado no SQL*Plus com o comando:

SQL> EXECUTE SQL_DINAMICO('ENAME','EMP')

que irá retornar os dados da coluna ENAME da tabela EMP.

Exemplo 2

Este segundo exemplo mostra a utilização de variáveis de ambiente (bind variables) em declarações SQL. Os marcadores :valor1 e :valor2 são inseridos na declaração SQL para indicar que serão utilizados valores obtidos a partir de variáveis de ambientes (no exemplo são bind1 e bind2) e o procedimento BIND_VARIABLE faz a ligação entre estas variáveis e os marcadores .

DECLARE

cur_handle INTEGER;

BEGIN

cur_handle := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur_handle,

'INSERT INTO FONE VALUES (:valor1,:valor2)',DBMS_SQL.V7);

DBMS_SQL.BIND_VARIABLE(cur_handle,':valor1',:bind1);

DBMS_SQL.BIND_VARIABLE(cur_handle,':valor2',:bind2);

DBMS_OUTPUT.PUT_LINE('NUM. DE LINHAS INSERIDAS: '||DBMS_SQL.EXECUTE(cur_handle));

DBMS_SQL.CLOSE_CURSOR(cur_handle);

END;

/

Para a declaração utilizada (INSERT), a função EXECUTE retorna o número de

linhas inseridas.

As variáveis de ambiente poderiam ser ítens do Oracle Forms, seus valores poderiam ter sido atribuidos em outros blocos e procedimentos ou ainda podem ser difinidos a partir do SQL*Plus :

SQL> VARIABLE bind1 VARCHAR2(40)

SQL> VARIABLE bind2 VARCHAR2(40)

SQL> BEGIN

1 :bind1 := 'Oracle do Brasil';

2 :bind2 := 'http://www.oracle.com.br';

3 end;

4 /

e o bloco pode ser executado fazendo:

SQL> @EXEMPLO2.SQL

que irá inserir os dados na tabela FONE e emitir a mensagem:

NUM. DE LINHAS INSERIDAS: 1

PL/SQL procedure successfully completed.

Exemplo 3

Uma utilização da package DBMS_SQL para tornar os procedimentos ainda mais genéricos é mostrada neste exemplo. Ele cria um procedimento que recebe como argumento a declaração SQL inteira, não apenas parte dela. As declarações podem ser de inserção, alteração ou remoção. Sua listagem é mostrada a seguir.

CREATE OR REPLACE PROCEDURE QUERY(query_in IN VARCHAR2) IS

cur_handle INTEGER;

temp NUMBER;

BEGIN

cur_handle := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur_handle, query_in, DBMS_SQL.V7);

temp := DBMS_SQL.EXECUTE(cur_handle);

DBMS_SQL.CLOSE_CURSOR(cur_handle);

DBMS_OUTPUT.PUT_LINE('Num. de linhas tratadas : '||temp);

END QUERY;

/

Novamente o procedimento deve ser criado com o comando :

SQL> @EXEMPLO3.SQL

e um exemplo de sua utilização é :

SQL> EXECUTE QUERY('INSERT INTO FONE VALUES (''DISCOVER TECHNOLOGY'',''(011)853-0692'')')

que irá retornar a mensagem :

Num. de linhas tratadas : 1

PL/SQL procedure successfully completed.

Exemplo 4

Neste exemplo, a package DBMS_SQL é utilizada para chamar uma stored function que retorna um VARCHAR2 e recebe como parâmetro uma variável do tipo IN NUMBER.

CREATE OR REPLACE FUNCTION RETORNA_TEXTO (numero IN NUMBER) RETURN VARCHAR2 AS

temp_var VARCHAR2(30);

BEGIN

temp_var := 'O valor passado foi ' || TO_CHAR(numero);

RETURN temp_var;

END;

/

DECLARE

cursor_handle NUMBER;

temp INTEGER;

valor NUMBER := 1;

-- Variável que receberá o valor retornado pela função é inicializada

-- com 30 espaços em branco

var_text VARCHAR2(30) := ' ';

BEGIN

cursor_handle := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cursor_handle,'BEGIN :x1:=RETORNA_TEXTO(:x2); END;',DBMS_SQL.V7);

DBMS_SQL.BIND_VARIABLE(cursor_handle, ':x1', var_text);

DBMS_SQL.BIND_VARIABLE(cursor_handle, ':x2', valor);

temp := DBMS_SQL.EXECUTE(cursor_handle);

DBMS_SQL.VARIABLE_VALUE(cursor_handle, ':x1', var_text);

DBMS_SQL.CLOSE_CURSOR(cursor_handle);

DBMS_OUTPUT.PUT_LINE('var_text = ' || var_text);

END;

/

Para o caso em que a variável var_text não é inicializada, o bloco fica da seguinte forma:

DECLARE

cursor_handle NUMBER;

temp INTEGER;

valor NUMBER := 1;

var_text VARCHAR2(30);

BEGIN

cursor_handle := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cursor_handle,'BEGIN :x1:=RETORNA_TEXTO(:x2); END;',DBMS_SQL.V7);

DBMS_SQL.BIND_VARIABLE(cursor_handle, ':x1', var_text, 30);

DBMS_SQL.BIND_VARIABLE(cursor_handle, ':x2', valor);

temp := DBMS_SQL.EXECUTE(cursor_handle);

DBMS_SQL.VARIABLE_VALUE(cursor_handle, ':x1', var_text);

DBMS_SQL.CLOSE_CURSOR(cursor_handle);

DBMS_OUTPUT.PUT_LINE('var_text = ' || var_text);

END;

/