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.
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.
