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