Utilizando Vetores no Oracle - Parte 2

1. Introdução
 

Neste segundo artigo sobre o uso de vetores no Oracle, é apresentada a forma de copiar dados de tabelas para vetores, com o uso da linguagem PL/SQL.


2. Copiando Dados de uma Tabela para um Vetor no PL/SQL

No artigo anterior, a sintaxe para a declaração de vetores (tipo VARRAY) em programas PL/SQL foi apresentada.  Também demonstrou-se um procedimento simples para a inicialização de vetores (através de um construtor padrão) e a forma utilizada para a atribuição de valores nas suas  diferentes posições.

Dando continuidade a série de artigos sobre o tipo VARRAY, este texto apresenta a maneira pela qual um vetor pode ser populado com dados provenientes de tabelas do banco de dados. Para facilitar a explicação, será utilizado um exemplo. Considere um banco de dados que possua uma tabela chamada T_PRODUTO, composta por dois campos: COD_PRODUTO (chave primária – indica o código do produto) e DSC_PRODUTO (descrição do produto).

Suponha ainda que T_PRODUTO contenha 1000 registros. Imagine que, num programa PL/SQL, você desejasse armazenar todas as descrições dos produtos num vetor, de modo que o primeiro elemento do vetor contivesse a descrição do produto de Código 1 (“Amendoin cru Pct. 500g”), o segundo elemento contivesse a descrição do produto de Código 2 (“Arroz Integral Quilo”), e assim por diante.

Conforme visto no artigo anterior, para implementar este programa, seria preciso declarar um vetor de 1000 posições e inicializar cada uma destas posições. Só é possível utilizar um determinado subscrito de um VARRAY se este subscrito já houver sido inicializado num passo anterior. Para ser mais claro: um valor pode ser atribuído numa posição específica do vetor, apenas se esta posição tiver sido previamente “marcada” como “disponível”. Esta característica dos vetores no Oracle é bastante esquisita, pois, geralmente, nas linguagens de programação basta declarar um vetor para poder usar suas posições. No entanto, com o VARRAY do Oracle não é deste jeito: o programador precisa fazer duas coisas: declarar e inicializar.

Felizmente existe uma maneira simples para inicializar todos os subscritos de um vetor “de uma tacada só”. Para isso, utiliza-se o método EXTEND (o tipo VARRAY possui um pequeno conjunto métodos internos que podem ser utilizados pelo programador. Consulte o manual de PL/SQL na seção a respeito de COLLETIONS para obter maiores informações). O método EXTEND pode ser utilizado de três maneiras distintas. Observe os exemplos a seguir, que apresentam estas três formas, aplicadas sobre um vetor chamado V.

• V.EXTEND:  inicializa um subscrito de V e atribui o valor NULL ao mesmo;

• V.EXTEND(n): inicializa “n” subscritos de V e atribui NULL a todos eles;

• V.EXTEND(n,i): adiciona “n” cópias do elemento de subscrito “i” ao final do vetor V (desta forma, os “n” últimos elementos de V são inicializados automaticamente)

A seguir apresenta-se o programa (procedure) P_CARGA_PRODUTO, que representa um exemplo prático do uso do método EXTEND. Resumidamente, o programa funciona da seguinte forma: através do uso de um cursor, todas as descrições dos produtos armazenados na tabela T_PRODUTO são copiadas para um vetor V. Este vetor V possui 1000 posições, que são inicializadas com o uso do método EXTEND. Maiores detalhes sobre o funcionamento do programa são apresentados após a sua especificação.

1.   CREATE OR REPLACE PROCEDURE P_CARGA_PRODUTOS IS

2.

3.   TYPE tVETOR IS VARRAY(1000) OF VARCHAR2(80); --define o tipo do vetor

4.

5.   -- declaração  do cursor para a tabela T_PRODUTO

6.     CURSOR cPRODUTO IS

7.     SELECT DSC_PRODUTO FROM T_PRODUTO

8.     ORDER BY COD_PRODUTO;

9.

10.   -- declaração  de variáveis

11.   V                   tVETOR;              -- declara o vetor

12.   vPROD   VARCHAR2(80);  --auxiliar para fetch

13.   I           PLS_INTEGER;

14.

15.BEGIN

16.   --estes dois comandos inicializam V e as suas 1000 posições

17.    V:=tVETOR();

18.    V.EXTEND(1000);

19.

20.  -- estes comandos realizam loop no cursor cProduto, para preencher V;

21.    I:=1;

22.    open cPRODUTO;

23.    loop

24.                 fetch cPRODUTO into vPROD;

25.                 exit when cPRODUTO%notfound;

26.                 V(I) := vPROD;

27.      I:= I+1;

28.     end loop;

29.     close cPRODUTO;

30.

31.    -- exibe algumas posições do vetor

32.    -- (use a opção SETSERVEROUT ON no SQL *Plus)

33.

34.    DBMS_OUTPUT.PUT_LINE('1: ' || V(1));

35.    DBMS_OUTPUT.PUT_LINE('45: ' || V(45));

36.    DBMS_OUTPUT.PUT_LINE('330: ' || V(330));

37.    DBMS_OUTPUT.PUT_LINE('500: ' || V(500));

38.    DBMS_OUTPUT.PUT_LINE('999: ' || V(999));

39.    DBMS_OUTPUT.PUT_LINE('1000: ' || V(1000));

40.

41. END;

A procedure P_CARGA_PRODUTOS começa com a especificação do tipo tVETOR, na linha 2: é um tipo VARRAY com 1000 posições que podem armazenar informações do tipo VARCHAR(80). Na linha 11 ocorre a declaração de um vetor chamado V, do tipo tVETOR. Esta forma para a declaração de vetores já havia sido mostrada no primeiro artigo desta série sobre VARRAY’s. As novidades estão localizadas nas linhas 17 e 18 da procedure. Os comandos nessas linhas são as responsáveis pela inicialização do vetor e de seus subscritos, respectivamente.

A linha 17 é a responsável pela inicialização de V, através do uso do construtor padrão para o vetor. Explicando melhor: no Oracle, todo vetor é considerado atomicamente nulo, enquanto não for inicializado. Esta inicialização precisa ser feita com o uso do tal construtor padrão, que representa uma função de sistema (automaticamente criada pelo PL/SQL) que possui o mesmo nome do tipo do vetor V (tVETOR).  Quando num programa tenta-se atribuir um valor em qualquer posição de um vetor atomicamente nulo, o Oracle dispara a exceção COLLECTION_IS_NULL.

Por sua vez, a linha 18 é a responsável pela inicialização dos subscritos de V. Para tal, bastou utilizar o método EXTEND com o parâmetro 1000. O efeito deste comando é o seguinte: 1000 posições de V são, de uma só vez,  inicializadas com o valor NULL. Com isto, as posições poderão ser utilizadas normalmente em qualquer seção do programa PL/SQL. Quando num programa tenta-se atribuir um valor em um subscrito não inicializado, o Oracle dispara a exceção SUBSCRIPT_BEYOUND_COUNT.

O restante do programa é bastante simples. Um loop percorre o cursor cPRODUTO (linhas 22 a 28). Dentro do loop as descrições dos produtos vão sendo inseridas nas diferentes posições do vetor V. A seguir, nas linhas 34 a 39 alguns elementos do vetor são impressos na tela.

3. Comentários Finais

Este artigo descreveu a forma pela qual um vetor pode ser declarado, inicializado e preenchido com dados provenientes de uma tabela de um banco de dados Oracle. No próximo artigo (último da série) será descrita a maneira pela qual um vetor pode ser armazenado como uma coluna de tabela Oracle.

Eduardo Corrêa Gonçalves

Instituto Brasileiro de Geografia e Estatística – IBGE

E-mail: eduardo.correa@ibge.gov.br

Utilizando Vetores no Oracle - Parte 1

1. Introdução

Este artigo representa o primeiro de uma série de trabalhos que irão abordar as características e as formas de manipulação de vetores no Oracle. Este SGBD disponibiliza um tipo de dados específico para a representação de vetores (tanto em programas PL/SQL, como em colunas de tabelas), denominado VARRAY (variable-size array), cujos conceitos básicos serão apresentados neste texto.

2. Utilização de vetores no em Programas PL/SQL

Assim como em qualquer linguagem de programação, um vetor em PL/SQL representa um grupo de posições contíguas em memória que possuem o mesmo nome e o mesmo tipo. A Figura 1 ilustra um exemplo de vetor chamado “V” que contém 10 posições e armazena as notas médias dos alunos de um curso de programação em PL/SQL.

Figura 1. Vetor “V”  com 10 posições para armazenar notas médias de alunos.

 

Para referenciar um elemento (valor) no vetor, devemos utilizar o nome do vetor em questão e o índice (ou subscrito) do elemento que desejamos acessar entre parênteses. Tem-se, por exemplo, que V(7) constitui uma referência ao sétimo elemento do vetor V, cujo valor é 8.5. No entanto, o primeiro passo para a utilização de um vetor em PL/SQL consiste na sua declaração (criação). O código a seguir apresenta uma maneira que pode ser utilizada para declarar o vetor “V” dentro de um programa PL/SQL.

CREATE OR REPLACE PROCEDURE P_TESTE IS

TYPE tVETOR IS VARRAY(10) OF NUMBER(4,2); -- define o tipo do vetor

V tVETOR; --declara um vetor com o nome V e o tipo tVetor

BEGIN

     <corpo do programa...>

END;

No exemplo acima, o vetor foi “V” foi declarado dentro de uma procedure denominada P_TESTE. Para que o vetor pudesse ser declarado, primeiro foi necessário criar o seu tipo (com o uso do comando TYPE). Observe que foi criado um tipo denominado tVETOR, que especifica um vetor de 10 posições para valores do tipo NUMBER(4,2). Após a definição do tipo é que deve  ocorrer, de fato, a declaração do vetor. Em nosso exemplo, o vetor foi declarado com nome V e com o tipo tVETOR. É importante saber que, uma vez declarado, um vetor não poderá ser redimensionado durante a execução do programa PL/SQL.

Para que qualquer elemento do vetor possa ser referenciado dentro do corpo do programa, é preciso que este seja inicializado - caso contrário o PL/SQL irá disparar uma exceção (ou seja, um erro. Os diferentes tipos exceção serão examinados no próximo artigo desta série). Para exemplificar o processo de inicialização de vetores, considere a função F_APROVADOS, cuja especificação é apresentada na Figura 2. Esta função cria e preenche um vetor contendo a média final de 10 alunos. Em seguida, o vetor é percorrido por um loop (comando for) e o número de alunos aprovados é contabilizado (alunos cuja média final é igual ou superior a 7.0). O resultado deste cálculo é retornado pela função para o usuário.

Figura 2. Função F_APROVADOS

Examinando a função F_APROVADOS, é possível observar a declaração do vetor V nas linhas 3 e 4. Na linha 9 ocorre a inicialização do vetor. São atribuídos valores para cada uma de suas 10 posições (note que são os mesmos valores apresentados na Figura 1!). Para inicializar nosso VARRAY, utilizou-se um construtor, que representa uma função de sistema (automaticamente criada pelo PL/SQL) que possui o mesmo nome do tipo do vetor V (tVetor).

V := tVETOR(7.8, 5.0, 8.5, 9.0, 6.4, 10.0, 8.5, 8.6, 7.5, 9.2);

Com a utilização do construtor, seria possível também inicializar o vetor com o valor zero em todas as suas dez posições, conforme indica o exemplo a seguir:

V := tVETOR(0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

O detalhe mais importante que você precisa saber é o seguinte: no PL/SQL, para que uma posição de um VARRAY possa ser referenciada (ou seja, para a posição poder ter um valor lido ou atribuído), primeiro é necessário que o seu subscrito seja inicializado.

Vamos agora retornar ao programa da Figura 2. Observe que após a inicialização do vetor, os seus elementos são examinados num loop, para que a quantidade de médias superiores ao valor 7 possa ser contabilizada (linhas 14 a 18)  e então retornada pela função (linha 21). Para testar a função (caso você a tenha criado em seu banco Oracle), basta utilizar o seguinte comando SELECT:

SELECT F_APROVADOS FROM DUAL;

3. Comentários Finais

Este artigo apresentou alguns conceitos elementares sobre  o tipo VARRAY do Oracle. Em artigos futuros serão exploradas mais características deste recurso, tais como a forma de passar um VARRAY como parâmetro em procedimentos e funções PL/SQL, como fazer vetores receberem dados oriundos de tabelas Oracle num programa e também a maneira pela qual um vetor pode ser armazenado numa tabela.

Eduardo Corrêa Gonçalves

Instituto Brasileiro de Geografia e Estatística – IBGE

E-mail: eduardo.correa@ibge.gov.br

Aritmética de Datas no Oracle

1. Introdução

Este artigo descreve a maneira pela qual podem ser realizadas operações aritméticas envolvendo campos DATE no SGBD Oracle.

2. O Tipo de Dado DATE
        

   Conforme todo desenvolvedor Oracle sabe, os campos do tipo DATE costumam ser usados em tabelas para a representação de informação referente à data e hora de algum evento. Campos do tipo DATE ocupam apenas 7 bytes e são capazes armazenar datas com a precisão na casa dos segundos; Melhor detalhando, um campo DATE é capaz de manter as seguintes informações temporais associadas a um determinado evento: século, ano, mês, dia, hora, minuto e segundo de ocorrência do evento.

 

   Para aplicações onde necessita-se manter informações de tempo com maior precisão, o Oracle disponibiliza outro tipo de dado, denominado TIMESTAMP. Este tipo representa uma extensão do tipo DATE que é capaz de gerenciar informações na ordem de fração de segundo (milissegundos, microssegundos, etc).

 

3. Aritmética de Datas
 

   O que poucos desenvolvedores Oracle sabem é que este SGBD disponibiliza uma aritmética bastante simples para a manipulação do tipo de dado DATE. É possível somar (e subtrair) constantes numéricas a uma data de maneira rápida e fácil.

 

   Em operações aritméticas envolvendo datas, o Oracle interpreta constantes numéricas como quantidade de dias. Desta forma, se você deseja obter a data do dia seguinte através de uma instrução SQL, basta fazer SYSDATE + 1:

 

SELECT SYSDATE + 1 as AMANHA FROM DUAL;

 

   De maneira análoga, se você quiser recuperar num mesmo SELECT a data corrente e também a data equivalente a data corrente mais 30 dias, basta executar a seguinte instrução:

 

SQL> SELECT SYSDATE, SYSDATE + 30 FROM DUAL;

 

SYSDATE              SYSDATE+30

--------------      -------------------

25/06/07             25/07/07

   Para obter a data da “semana passada” (data de 7 dias atrás), utilize a seguinte instrução:

 

SQL> SELECT SYSDATE, SYSDATE - 7 FROM DUAL;

 

SYSDATE                     SYSDATE - 7

--------------             -------------------

25/06/07                   18/06/07

 

3.1 Lidando com Horas, Minutos e Segundos
 

   Em muitas situações práticas poderemos estar interessados em adicionar ou subtrair não dias, mas horas, minutos ou segundos do valor de uma data. Isto também é possível no Oracle. No entanto, o SGBD solicitará com que seja especificada uma “fração de dia” adequada para a operação aritmética. Sabemos que um dia possui:

 

   -  24 horas.

   -  24 x 60 = 1440 minutos.

   -  24 x 60 x 60 = 86.400 segundos.

 

   Com isto, se desejamos somar 5 minutos a uma determinada data no Oracle, devemos somar a fração 5/1440 a esta data!  Observe este cálculo no exemplo a seguir:

 

SQL> SELECT

          TO_CHAR(SYSDATE,‘DD/MM/YYYY HH24:MI:SS’) AS AGORA,

          TO_CHAR(SYSDATE + 5/1440, ‘DD/MM/YYYY HH24:MI:SS’) AGORA_MAIS_5_MIN

          FROM DUAL;

 

AGORA                                        AGORA_MAIS_5_MIN

-------------------                   -------------------

25/06/2007 18:18:29            25/06/2007 18:23:29

 

 

   Para, por exemplo, subtrair 30 segundos, pode-se utilizar o SELECT abaixo:

 

SQL> SELECT

    TO_CHAR(SYSDATE,‘DD/MM/YYYY HH24:MI:SS’) AS AGORA,

    TO_CHAR(SYSDATE - 30/86400, ‘DD/MM/YYYY HH24:MI:SS’) TRINTA_SEG_ATRAS

    FROM DUAL;

 

AGORA                                      TRINTA_SEG_ATRAS

--------------------                ------------------

25/06/2007 18:22:42          25/06/2007 18:22:12

 

 

   Em resumo: Adicionar 1/24 significa somar uma hora a data; Adicionar 1/1440, significa adicionar um minuto; e somar 1/86400 significa somar um segundo. É importante deixar claro que a aritmética do Oracle não permite operações de multiplicação e divisão de constantes sobre datas. Apenas a soma e subtração são disponibilizadas.

 

SQL Dinâmico no Oracle - Parte 3

1. Introdução

Este artigo apresenta um roteiro para implementar consultas dinâmicas que retornam múltiplas linhas no Oracle.

2. Consultas Dinâmicas que Retornam Múltiplas Linhas

Nos artigos anteriores, foi demonstrada a utilização do comando Execute Immediate na implementação de SQL’s dinâmicos. Este comando pode ser utilizado para a montagem e execução de diversos tipos de instrução SQL, tais como: create, drop, grant, insert, update, delete e select’s que retornem apenas uma linha. No entanto, o Execute Immediate não pode ser utilizado na implementação de consultas dinâmicas que resultam num conjunto composto por muitos registros. Neste caso é preciso utilizar os comandos OPER-FOR, FETCH e CLOSE.

O código abaixo ilustra uma forma de implementar um SQL dinâmico que retorna múltiplas linhas. Seu funcionamento é explicado logo a seguir.

CREATE OR REPLACE

PROCEDURE P_SQL_DINAMICO(vTAB IN VARCHAR, vCAMPO IN VARCHAR) is

--ESTA PROCEDURE SELECIONA O CAMPO "vCAMPO" DA TABELA "vTAB"

--(recupera apenas as 100 primeiras linhas desta tabela)

type tpTeste is REF CURSOR; --PRIMEIRO É PRECISO ESPECIFICAR UM

                                                    --TIPO "REF CURSOR"

cCursorTeste  tpTeste;             --DEPOIS DECLARE UMA VARIÁVEL DESSE TIPO

AUX VARCHAR(4000);

vSQL VARCHAR2(255);

begin

-- passo 1: monta o SELECT dinâmico num VARCHAR2

vSQL := 'SELECT ' || vCAMPO || ' FROM  ' || vTAB || ' WHERE ROWNUM <= 100';

-- passo 2: abre e executa o cursor dinâmico usando a cursor variable

  open cCursorTeste for vSQL;

  loop

    fetch cCursorTeste into AUX;

    exit when cCursorTeste%notfound;

   

    DBMS_OUTPUT.PUT_LINE(trim(AUX));

   

  end loop;

  

  close cCursorTeste;

end P_SQL_DINAMICO;

Agora será apresentada a explicação sobre o funcionamento da procedure P_SQL_DINAMICO. Esta procedure recebe dois parâmetros como entrada: o nome de uma tabela qualquer (vTABELA) e o nome de um campo desta tabela (vCAMPO). No corpo da procedure,  um SELECT dinâmico é montado para imprimir os 100 primeiros valores de vCAMPO (ou seja, os valores presentes nos 100 primeiros registros de vTABELA).

O primeiro passo para implementar a rotina, consiste na declaração de uma variável do tipo cursor (cursor variable), nas linhas 5 e 6. Uma variável do tipo cursor representa um recurso do PL/SQL que assemelha-se a um ponteiro. Este tipo de variável aponta para um endereço de memória que, por sua vez, contém alguma informação de interesse (ou seja, a cursor variable armazena um endereço e não um item). Quando o Oracle executa uma consulta que retorna muitas linhas, uma área de trabalho é aberta para o processamento dos dados obtidos pela consulta e uma  cursor variable é um objeto capaz de acessar esta área de trabalho.

Dentro do corpo da procedure, o SQL dinâmico é montado numa variável VARCHAR2 em função dos parâmetros entrada (passo 1 - linha 11). A seguir, no passo 2 (linhas 12 a 19) os comandos OPEN-FOR, FETCH e CLOSE são utilizados para executar o SQL dinâmico. O comando OPEN-FOR (linha 13) é o mais importante na implementação da consulta dinâmica. Ele realiza as seguintes tarefas:

1. Associa a variável cursor com a consulta montada numa variável VARCHAR2 (no caso de nossa procedure exemplo, associa a cursor variáble “cCursorTeste” com a string “vSQL”).  
2. Executa a consulta e identifica o conjunto de registros resultante.
3. Posiciona o cursor na primeira linha do conjunto.

Depois de executar a consulta dinâmica com o OPEN-FOR, basta fazer um loop com o comando FETCH, da mesma maneira que você faria com um cursor convencional (linhas 14 a 18). Ao final do processamento, o cursor deve ser fechado com o comando CLOSE (linha 19).

SQL Dinâmico no Oracle - Parte 2

1. Introdução


Este artigo discute a utilização do comando EXECUTE IMMEDIATE na implementação de consultas dinâmicas no Oracle.  

2. Bind Variables

O processo de associar valores de variáveis PL/SQL em consultas SQL, especificadas no corpo de funções ou procedures escritas nesta linguagem, é conhecido como binding (“ligação”). Para observar a aplicação deste conceito na prática, considere a procedure P_APAGA_CLIENTE, especificada abaixo. Esta procedure recebe como entrada um número de CPF e, em seu corpo, contém três comandos SQL DELETE responsáveis por excluir todos os registros do cliente especificado em três diferentes  tabelas. Nesta procedure, a variável vCOD_CPF atua como uma bind variable, pois ela foi utilizada como condição da cláusula WHERE em três instruções SQL.

CREATE OR REPLACE PROCEDURE P_APAGA_CLIENTE(vCOD_CPF IN CHAR) IS

BEGIN    

    
      DELETE FROM T_PEDIDO WHERE COD_CPF = vCOD_CPF;

      DELETE FROM T_COMPRA WHERE COD_CPF = vCOD_CPF;

      DELETE FROM T_CLIENTE WHERE COD_CPF = vCOD_CPF;

END;     
          

Infelizmente as bind variables não podem ser utilizadas para especificar nomes de objetos (ou seja tabelas, visões, seqüences, etc) e nomes de campos em consultas SQL. Isto quer dizer, por exemplo, que a seguinte procedure não pode ser compilada pelo Oracle:

CREATE OR REPLACE PROCEDURE P_APAGA_TABELA(vTABELA IN VARCHAR) IS

BEGIN    

      DELETE FROM vTABELA;

END;     

No exemplo acima ocorreu a tentativa de utilizar a variável de entrada vTABELA como uma bind variable para especificar o nome de uma tabela.  Ao se tentar criar a procedure, ocorrerá o um erro: o compilador PL/SQL “pensará” que vTABELA corresponde ao nome de uma tabela do banco de dados. Ele não tentará utilizar vTABELA como bind variable na instrução DELETE.

Errors for PROCEDURE P_APAGA_TABELA:

LINE/COL ERROR

-------- -----------------------------------------------------------------

5/7      PL/SQL: SQL Statement ignored

5/19     PL/SQL: ORA-00942: a tabela ou view não existe

3. Utilização do Comando EXECUTE IMMEDIATE

        

Para casos onde seja necessária a criação de instruções SQL DML (INSERT, UPDATE e DELETE) em que nomes de objetos ou campos precisem ser definidos através de valores armazenados em variáveis PL/SQL, o desenvolvedor Oracle deve utilizar o comando EXECUTE IMMEDIATE. Na realidade o comando faz muito mais do que isto: ele permite com que, até mesmo, instruções DDL (como CREATE e DROP) e DCL (como GRANT e REVOKE) possam ser executados via PL/SQL (estes comandos não podem ser executados de forma convencional no PL/SQL). Além disso, o EXECUTE IMMEDIATE permite a montagem de consultas SELECT dinâmicas que retornem apenas uma linha (para consultas que retornem mais de uma linha é necessário utilizar outro método, a ser apresentado na parte 3 desta série).

        

O exemplo abaixo, adaptado do manual da Oracle, ilustra um bloco PL/SQL que contém diversos exemplos de instruções SQL dinâmicas executadas com o uso do comando EXECUTE IMMEDIATE.

DECLARE

vSQL      VARCHAR2(4000);    --string que recebe o comando SQL dinâmico

vID  NUMBER(2) := 99;

vNOME  VARCHAR2(30) := ‘Tangerina’;

BEGIN

--PASSO 1: executa um comando DDL

EXECUTE IMMEDIATE ‘CREATE TABLE T_PRODUTO (ID NUMBER, NOME VARCHAR2(30))’;

--PASSO 2: executa um comando DML passando duas variáveis como parâmetro

 vSQL := ‘INSERT INTO T_PRODUTO VALUES (:1, :2)’;

 EXECUTE IMMEDIATE vSQL USING vID, vNOME;

--PASSO 3: executa um comando DCL

EXECUTE IMMEDIATE ‘GRANT SELECT ON T_PRODUTO TO SCOTT’;

END;

O programa exemplo funciona da seguinte forma. No passo 1, o comando EXECUTE IMMEDIATE cria tabela T_PRODUTO (que contém dois campos: ID e NOME) de forma dinâmica. A seguir, no passo 2 ocorre um processamento bastante interessante. Uma instrução INSERT é montada com dois bind arguments  ( :1  e  :2). Na linha seguinte o EXECUTE IMMEDIATE é chamado para executar este comando INSERT. A cláusula USING é aplicada para substituir os bind arguments pelos valores das variáveis vID e vNOME (99 e ‘Tangerina’, respectivamente). Finalizando um programa, o comando EXECUTE IMMEDIATE é novamente chamado para atribuir GRANT de SELECT para o usuário SCOTT (o que representa a execução de um comando DCL).

SQL Dinâmico no Oracle - Parte 1

1. Introdução

O uso de comandos SQL montados de forma dinâmica - ou seja, em tempo de execução - corresponde a um dos recursos mais interessantes da linguagem PL/SQL (linguagem do SGBD Oracle). É possível executar dinamicamente não apenas consultas SQL ou comandos DML (INSERT, UPDATE e DELETE), mas também comandos DDL (como CREATE ou DROP) e DCL (como GRANT e REVOKE). Este artigo inicia uma série de trabalhos que abordarão as características e formas de implementação deste recurso em ambiente Oracle.

2. SQL Dinâmico: um Exemplo Simples         

Um comando SQL Dinâmico representa uma instrução SQL que é construída e armazenada numa variável do tipo VARCHAR2 (o equivalente ao tipo “string” no mundo Oracle) durante a execução de uma função ou procedure PL/SQL. Esta instrução pode ser montada de diferentes maneiras, de acordo com parâmetros de entrada. Para facilitar a compreensão deste conceito, será apresentado um primeiro exemplo prático. Considere uma função que recebe o nome de duas tabelas como entrada e que, como saída, retorne numa string concatenada o nome e a quantidade de registros da tabela que possuir o maior número de registros. O código desta função é apresentado a seguir:

CREATE OR REPLACE FUNCTION F_MAIOR(vTAB1 IN VARCHAR, vTAB2 IN VARCHAR) RETURN VARCHAR IS

vSQL      VARCHAR2(256);    --string que recebe o comando SQL dinâmico

vTOT1     PLS_INTEGER;      --total de registros da Tabela 1

vTOT2     PLS_INTEGER;      --total de registros da Tabela 2

BEGIN

  

   --PASSO 1: monta e executa SQL dinâmico referente a Tabela 1

   vSQL := ‘SELECT COUNT(*) FROM ’ || vTAB1;

  

   EXECUTE IMMEDIATE vSQL INTO vTOT1;

 

   --PASSO 2: monta e executa SQL dinâmico referente a Tabela 2

   vSQL := ‘SELECT COUNT(*) FROM ’ || vTAB2;

  

   EXECUTE IMMEDIATE vSQL INTO vTOT2;

--PASSO 3: retorna o nome e a quantidade de registros da tabela com mais linhas

   IF vTOT1 > vTOT2 THEN

     RETURN  vTAB1 ||  ‘ --- ’ || TO_CHAR(vTOT1);

   ELSIF vTOT1 < vTOT2 THEN

      RETURN  vTAB2 ||  ‘ --- ’ || TO_CHAR(vTOT2);

    ELSE

      RETURN  ‘EMPATE --- ’ || TO_CHAR(vTOT2);

    END IF;

END;

O programa exemplo funciona da seguinte forma. No passo 1, uma instrução SQL é montada dinamicamente e armazenada na variável vSQL (do tipo VARCHAR2). Observe que o parâmetro de entrada da função vTAB1 (nome da tabela 1) é concatenado no final do literal ‘SELECT COUNT(*)’. A seguir encontra-se a linha de código mais importante do programa:

EXECUTE IMMEDIATE vSQL INTO vTOT1; 

O comando EXECUTE IMMEDIATE é o responsável pela interpretação e execução de instruções SQL montadas de forma dinâmica. Para realizar esta tarefa, basta especificar a string SQL (no exemplo, vSQL) e o nome da variável que armazenará o resultado obtido pela execução do SQL (no exemplo, o resultado corresponde ao total de registros da tabela vTAB1 e será armazenado na variável vTOT1).

O passo 2 do programa é semelhante ao passo 1. A única diferença é o fato de que agora o SQL dinâmico armazenado em vSQL será montado com o nome do parâmetro vTAB2 no final (nome da segunda tabela cuja quantidade de registros deseja-se determinar). Finalizando a função, o passo 3 simplesmente testa qual é a tabela que contém mais linhas e retorna essa informação ao usuário.

Para criar a função, basta executar o SQL Plus, efetuar o login em uma base Oracle e copiar e colar o código. Para executá-la, você pode utilizar, por exemplo, a seguinte chamada:

SQL> SELECT F_MAIOR(‘USER_TABLES’, ‘USER_VIEWS’) FROM DUAL; 

F_MAIOR('USER_TABLES','USER_VIEWS')

--------------------------------------------------------------------------------

USER_TABLES --- 152

Neste exemplo, o Oracle compara a quantidade de registros das views de sistema USER_TABLES e USER_VIEWS e indica qual das duas retorna mais linhas.