PL/SQL�Collections

As aplicaç�es PL/SQL consistem tipicamente de instruç�es SQL misturadas com l�gica processual para processar os dados obtidos a partir da base de dados. Se compilado como um procedimento armazenado, o c�digo PL/SQL residir� no servidor, o qual � o melhor local para programas que requerem interaç�o com o banco de dados. Dito isto, a qualquer hora um software executa chamadas a um banco de dados, o que quer dizer que h� um preço a ser pago com relaç�o ao desempenho.

Uma collection nada mais � que uma estrutura tridimensional �nica e homog�nea, o que constitui um conjunto ordenado de elementos de um determinado tipo similar. Sendo uma estrutura homog�nea, todos os elementos s�o do mesmo tipo de dados. A estrutura do elemento cont�m c�lulas com um subscrito. Os elementos residem nestas c�lulas para fazer o �ndice como sendo a sua informaç�o de localizaç�o. O �ndice subscrito ou c�lula torna-se de fato, a identificaç�o de um elemento e � utilizada para o seu acesso.

Um elemento da collection pode ser de qualquer tipo de dados SQL v�lido ou um tipo definido pelo usu�rio. Um elemento do tipo de dados primitivo SQL � um valor escala, enquanto um elemento do tipo definido pelo usu�rio � uma inst�ncia de tipo de objeto. A coleç�o pode ser usada dentro de um programa PL/SQL, declarando uma vari�vel PL/SQL do tipo de coleç�o. A vari�vel PL/SQL local pode conter como sabemos as inst�ncias de seu tipo de coleç�o. Al�m disso, uma coluna do banco de dados em uma tabela tamb�m pode ser do tipo de coleç�o de esquema.

As coleç�es no Oracle s�o estritamente unidimensionais. Elas n�o podem ser realizadas em coordenadas bidimensionais. No entanto, arrays multidimensionais podem ser realizados quando a coleç�o tem um tipo de objeto ou um tipo de coleç�o de atributos. De toda forma, uma coleç�o pode ser limitada ou ilimitada. As coleç�es limitadas podem acomodar um n�mero limitado de elementos, enquanto que as coleç�es ilimitadas n�o t�m limite superior para subscritos.

Confira os cursos de banco de dados da DevMedia e se torne um DBA de sucesso.

As coleç�es fornecem uma maneira eficiente de organizaç�o dos dados em uma matriz ou definir o formato ao fazer o uso de recursos orientados a objetos. Uma inst�ncia de uma tabela aninhada ou um tipo de coleç�o varray � acessada como sendo um objeto enquanto os dados ainda s�o armazenados nas colunas do banco de dados. As coleç�es podem ser usadas para aproveitar o cache de dados em programas e aumentar o desempenho das operaç�es SQL. Em conex�es de servidores dedicados, por exemplo, uma sess�o sempre usa uma �rea de usu�rio global (UGA � User Global Area), que � um componente do PGA, para operaç�es de coleta. Por outro lado, para o modo de servidor compartilhado, as operaç�es de coleta s�o ainda realizadas em UGA; mas UGA � uma parte da �rea de sistema global (SGA � System Global Area), assim, indiretamente, em SGA. Isto porque, em conex�es de servidor compartilhadas, v�rios processos do servidor podem afetar uma sess�o, assim os UGA�s devem ser alocados fora do SGA.

Por que devemos usar Collections?

Uma coleç�o pode ser vagamente definida como um conjunto de elementos ordenados, todos do mesmo tipo, que permite o acesso program�tico para os seus elementos atrav�s de um �ndice. Tipos de coleç�o s�o usados comumente no mundo da programaç�o incluindo matrizes, mapas e listas.

Por�m, armazenar elementos em uma coleç�o pode nos fornecer uma s�rie de vantagens. Para começar, as coleç�es nos ajudam a simplificar o c�digo. Se precisarmos processar um n�mero de itens de um tipo semelhante, armazenar esses itens em uma determinada coleç�o lhe permitir� percorrer cada elemento com facilidade, fazendo refer�ncias a cada um deles por um �ndice espec�fico. Al�m disso, a maioria das linguagens definem tipos de coleç�o que cont�m m�todos internos para operar nas coleç�es. Provavelmente, a maior vantagem de uma coleç�o poderia fornecer a melhoria no desempenho das aplicaç�es. N�s desenvolvedores utilizar�amos coleç�es para dados est�ticos 'cache' que precisam ser acessados regularmente. Isso resultaria em chamadas reduzidas a uma base de dados.

Categorizando os tipos de Collections

As coleç�es podem ser de dois tipos, persistentes e n�o persistentes. A coleç�o � do tipo persistente se ela armazena a estrutura de coleta e os elementos fisicamente no banco de dados. J� no caso de uma coleç�o n�o persistente, esta seria ativa para um programa s�, ou seja, no m�ximo at� uma sess�o.

Al�m das categorias anteriores, uma coleç�o pode ser apresentada em tr�s formatos, ou seja, matriz associativa, tabela aninhada ou VARRAY. Esta classificaç�o � puramente baseada nas propriedades objetivas e comportamentais em um programa PL/SQL.

Array associativo (tabelas index-by)

Esta � a forma mais simples de apresentaç�o das coleç�es ilimitadas n�o persistentes. Como sendo uma coleç�o n�o persistente, elas n�o podem ser armazenadas no banco de dados, mas elas est�o dispon�veis apenas dentro de blocos PL/SQL. A estrutura de coleta e os dados da matriz associativa n�o podem ser mantidos, uma vez que o programa for conclu�do. Inicialmente, no Oracle 7, elas eram conhecidas como tabelas PL/SQL. Mais tarde, a partir vers�o 8 do Oracle, foram lançadas como tabelas index-by, pois utilizam um �ndice para identificar um elemento.

O uso das matrizes associativas � necess�rio quando:

Temos que armazenar os dados do programa em cache temporariamente em um formato de matriz para fins de pesquisa.
Quando precisamos de sequ�ncia de caracteres para os elementos subscritos da coleç�o. Um ponto importante a ser informado � que ele tamb�m aceita �ndices negativos.
Tabelas de mapeamento hash do cliente para o banco de dados.

As matrizes associativas s�o an�logas �s matrizes convencionais ou listas que podem ser definidas dentro de apenas um programa PL/SQL. Nem a estrutura da matriz, nem os dados que ela possui podem ser armazenados na base de dados. Ela pode conter os elementos de um tipo semelhante em uma estrutura de valor chave, sem qualquer limite superior para a matriz. Cada c�lula da matriz � distinguida pelo seu �ndice e esse pode ser um n�mero ou mesmo uma string.

Com o lançamento do Oracle 10g foi reconhecido tamb�m o comportamento dos �ndices das tabelas como matrizes, de modo a renome�-los como matrizes associativas devido � associaç�o de um �ndice com uma matriz. Como arrays associativos, estes s�o destinados ao armazenamento tempor�rio de dados em vez do armazenamento persistentes dos dados, eles n�o podem fazer uso de instruç�es SQL como INSERT e SELECT INTO. O que eles podem fazer � persistir as sess�es de banco de dados, declarando o tipo de pacote e atribuiç�o de valores no corpo do pacote.

Os arrays associativos s�o muito mais convenientes quando precisamos apenas declarar e usar dentro de c�digos PL/SQL, como apresentado na Listagem 1.

Listagem 1. Declarando e inicializando um array associativo.

DECLARE
     TYPE EmpTabTyp IS TABLE OF empregado%ROWTYPE
        INDEX BY PLS_INTEGER;
     emp_tab EmpTabTyp;
  BEGIN
     -- Recuperando um registro. --
     SELECT * INTO empregado_tabela(100) FROM empregados
       WHERE empregado_id = 100;
  END;
  /

Em resumo, temos que as matrizes associativas s�o apropriadas para tabelas de pesquisa relativamente pequenas onde a coleç�o pode ser constru�da na mem�ria cada vez que um procedimento � chamado ou um pacote � inicializado, al�m de serem bons coletores de informaç�es, cujo o volume de informaç�es n�o � conhecido anteriormente, e por �ltimo, os valores dos �ndices s�o flex�veis, al�m do fato de que podem tamb�m ser negativos.

Varrays (Variables arrays)

Varray (Variable array - array de tamanho vari�vel) � uma forma persistente, mas limitada, de coleç�o que pode ser criada no banco de dados, bem como em uma PL/SQL. Semelhante a uma tabela aninhada, um varray tamb�m � uma coleç�o homog�nea unidimensional. O tamanho da coleç�o e o esquema de armazenamento s�o os fatores que diferenciam os varrays das tabelas aninhadas. Ao contr�rio de uma tabela aninhada, um varray pode acomodar apenas um n�mero (fixo) definido de elementos.

Devemos utilizar as Varrays quando:

Temos que armazenar o volume calculado ou pr�-definido de dados no banco de dados. O varray oferece um armazenamento limitado e definido de linhas em uma coleç�o.
A ordem dos elementos tem que ser preservada.

O varray armazena elementos do mesmo tipo, na ordem em que estes s�o adicionados. O n�mero de elementos em uma varray deve ser conhecido no momento da sua declaraç�o. Em outras palavras, um varray fixa limites inferior e superior, tornando-o mais parecido com os tipos de coleç�o de outras linguagens de programaç�o. Uma vez que � criado e preenchido, cada elemento pode ser acessado por um �ndice num�rico.

Por exemplo, vamos supor que tenhamos um sistema de gerenciamento de contatos muito primitivo onde armazenamos nomes e dois endereços de e-mail (trabalho e pessoal) e dois n�meros de telefone para contato (trabalho, celular).

Neste caso, sabemos de antem�o com relaç�o a quantidade m�xima de cada tipo de atributo que queremos armazenar, onde ent�o os varrays s�o ideais para essa tarefa. Se n�s n�o soub�ssemos quantos seriam, ent�o poder�amos usar tabelas aninhadas. Para o prop�sito deste exemplo, vamos ent�o �fingir� que o PL/SQL armazena automaticamente tudo em um banco de dados para n�s, como podemos ver de acordo com a Listagem 2.

Listagem 2. Declaraç�o e inicializaç�o de um Varray.

TYPE fone_tab_type IS VARRAY(2) OF NUMBER ;
 TYPE email_tab_type IS VARRAY(2) OF VARCHAR2(100);
 TYPE nome_tab_type IS TABLE OF VARCHAR2(100);
  fone_numeros   fone_tab_type;
 nomes       nome_tab_type; 
 emails  email_tab_type;

Percebam que, neste momento, cada um dos tipos de dados foi armazenado separadamente e, portanto, s�o trabalhados separadamente. Para colocar tudo isso junto � preciso criar outro tipo como um registro(). Em seguida, criar uma tabela aninhada para manter os registros da seguinte forma apresentada na Listagem 3.

Listagem 3. Criando um record para as informaç�es do varray.

TYPE contato_rec IS RECORD (
  nome VARCHAR2(100);
 fone_numeros fone_tab_type;
 emails  email_tab_type;
  );
  TYPE contatos_tab_type IS TABLE OF contato_rec;
  contatos contatos_tab_type;

Desta forma percebemos que os varrays s�o realmente ideais para armazenamento de valores fixos que ser�o processados coletivamente, al�m de ser uma excelente opç�o quando temos a informaç�o da quantidade de elementos que ser�o utilizados e tamb�m podemos manter a ordem com a qual estes registros foram inseridos na base de dados. O ponto negativo de sua utilizaç�o � devido ao fato de n�o ser poss�vel � realizaç�o de inserç�o, deleç�o ou ediç�o em seus elementos individuais.

Trabalhando com tabelas aninhadas

Estas s�o formas persistentes de coleç�es ilimitadas que podem ser criadas no banco de dados, bem como em blocos PL/SQL.

Com relaç�o a tabelas aninhadas, devemos us�-las quando:

Temos que armazenar dados como conjuntos no banco de dados. Colunas do banco de dados do tipo tabela aninhada podem ser declaradas para armazenar os dados persistentes.
Realizaç�o de grandes operaç�es da matriz, tais como inserç�o e exclus�o, por um grande volume de dados.

Tabelas aninhadas, como o varray, podem ser armazenadas em uma tabela relacional, bem como funcionar como uma vari�vel de programa PL/SQL. Ao contr�rio do varray, as tabelas aninhadas n�o necessitam de especificaç�o de tamanho. Em outras palavras, elas s�o n�o acopladas. Apresentaremos ent�o de acordo com as Listagens 4 e 5 como seria a sua utilizaç�o.

Listagem 4. Exemplo de utilizaç�o de tabelas aninhadas e pacotes

CREATE PACKAGE pessoas AS
     TYPE lista_bonus IS TABLE OF empregados.empregado_id%TYPE;
     PROCEDURE bonus (melhor_empregado IN lista_bonus);
  END pessoas;
  /
   
  CREATE PACKAGE BODY pessoas AS
   PROCEDURE bonus (melhor_empregado lista_bonus) IS
    BEGIN
      FOR i IN melhor_empregado.FIRST.. melhor_empregado.LAST
      LOOP
       UPDATE empregados SET salario = salario + 100 
           WHERE empregados.empregado_id = melhor_empregado(i);
     END LOOP;
    END;
   END;
  /

Para chamarmos pessoas.bonus de fora do pacote, declaramos uma vari�vel do tipo pessoas.lista_bonus e passamos essa vari�vel par�metro, como apresentado na Listagem 5.

Listagem 5. Invocando uma procedure com tabelas aninhadas.

DECLARE
    melhores_empregados pessoas.lista_bonus;
  BEGIN
    melhores_empregados :=  pessoas.lista_bonus(100, 103, 107);
    pessoas.bonus (melhores_empregados);
  END;
  /

Com tabelas aninhadas, uma tabela a parte do banco de dados ir� armazenar os dados. Esta tabela � especificada ap�s a cl�usula �STORE AS". Se uma tabela de banco de dados tem mais de um tipo de tabela aninhada, a mesma tabela de armazenamento ir� armazenar os dados para todas as tabelas aninhadas nessa tabela pai. Essas tabelas de armazenamento cont�m uma coluna chamada NESTED_TABLE_ID que permite que a tabela pai faça refer�ncia aos dados das tabelas aninhadas de uma linha.

Como operar com elementos individuais da coleç�o?

Para operar com elementos da coleç�o, precisamos utilizar o comando TABLE. O operador de comando TABLE informa ao Oracle que queremos as suas operaç�es para ser dirigido para a coleç�o, em vez de sua tabela pai assim como apresentado pela Listagem 6.

Listagem 6. Exemplos utilizando os operadores de tabelas no Oracle.

SELECT valor_qualquer FROM TABLE(SELECT departamento_tabela
FROM Departamento WHERE setor = 'engenharia')
   WHERE valor_qualquer LIKE '%eletro%';
 
UPDATE TABLE(SELECT departamento_tabela 
             FROM Departamento WHERE setor = 'engenharia')
  SET valor_qualquer   = 'produç�o'
  WHERE valor_qualquer = 'producao sand%';
 
SELECT valor_qualquer FROM TABLE(SELECT departamento_tabela
                               FROM Departamento WHERE setor = 'engenharia');

O que podemos resumir com relaç�o �s tabelas aninhadas � que:

S�o apropriadas para relacionamentos de dados importantes que devem ser armazenados persistentemente, al�m disso, nas tabelas aninhadas � poss�vel eliminar elementos arbitr�rios, ao inv�s de apenas remover um item a partir do final.
A ordem e os �ndices de elementos da tabela aninhada n�o s�o preservados quando ela � armazenada no banco de dados.
As tabelas aninhadas t�m uma vantagem sobre varrays em que eles permitem inserç�es, atualizaç�es e exclus�es em elementos individuais.

Usando m�todos de coleç�o

Um m�todo de coleç�o � um subprograma PL/SQL que retorna informaç�es sobre a coleç�o ou operadores em uma coleç�o. Esses m�todos tornam mais f�ceis � utilizaç�o das coleç�es e por consequ�ncia, tornam nossas aplicaç�es mais simples inclusive para as horas de manutenç�o. Alguns dos m�todos mais utilizados que podemos citar seriam os m�todos EXISTS, COUNT, LIMIT, PRIOR, NEXT, EXTEND, dentre outros tantos.

Quando devemos usar cada um dos tipos apresentados?

Podemos utilizar os varray�s quando precisarmos preservar listas de forma ordenada, quando estamos trabalhando com um conjunto fixo de entrada ou quando precisamos armazenar no banco de dados.

No caso das tabelas aninhadas, estas n�s usamos quando trabalhamos com listas ilimitadas que precisam aumentar dinamicamente e al�m disso, quando precisamos armazenar informaç�es na base de dados e operar ao mesmo tempo com elementos individualmente.

Por �ltimo, mas n�o menos importante, utilizamos os arrays (matrizes) associativos quando temos a necessidade de armazenar uma coleç�o de dados na base de dados. Al�m disso, sua velocidade e flexibilidade melhoram consideravelmente com relaç�o a indexaç�o.

Quais as diferenças entre tabelas aninhadas, Arrays associativos e Varrays?

A principal diferença entre as tabelas aninhadas e os arrays associativos � com relaç�o � persist�ncia e a facilidade de passagem de par�metros. A tabela aninhada pode ser armazenada em uma coluna de dados, com isso, podemos us�-la para simplificar as operaç�es SQL onde unimos uma coluna de uma tabela com outra tabela. Uma matriz associativa n�o pode ser armazenada na base de dados. Uma matriz associativa, no entanto, possui uma tabela de pesquisa relativamente pequena, onde a coleç�o pode ser constru�da na mem�ria cada vez que um subprograma � invocado ou um pacote � inicializado.

Como bem vimos, os varrays s�o uma melhor opç�o vistos quando temos o numero de elementos conhecido com anteced�ncia. Estes elementos s�o geralmente acessados sequencialmente. Os varrays quando armazenados, s�o armazenados como um �nico objeto. A sua diferença com relaç�o �s tabelas aninhadas � que os valores dos �ndices n�o s�o consecutivos, nem mesmo h� um numero definido de valores de �ndices.

Em suma, essas s�o as diferenças b�sicas com relaç�o � utilizaç�o desses tr�s tipos de coleç�es que podemos utilizar em nossas pesquisas com o Oracle.

O Oracle PL/SQL n�o � uma linguagem de programaç�o dif�cil de aprender, no entanto, como todas as linguagens de programaç�o, h� muitas coisas que podemos fazer para maximizar a efici�ncia e minimizar a complexidade. Considerando o poder que o PL/SQL tem para interagir com o banco de dados, as coleç�es podem nos ajudar a construir aplicaç�es mais simples e mais r�pidas, onde este � praticamente o objetivo de todo bom desenvolvedor PL/SQL.