Oracle Coleções PL/SQL: Varrays, aninhados e indexados por tabelas

O que é Coleção?

Uma coleção é um grupo ordenado de elementos de tipos de dados específicos. Pode ser uma coleção de tipos de dados simples ou complexos (como tipos de registro ou definidos pelo usuário).

Na coleção, cada elemento é identificado por um termo denominado "subscrito." Cada item da coleção recebe um subscrito exclusivo. Os dados nessa coleção podem ser manipulados ou obtidos referindo-se a esse subscrito exclusivo.

As coleções são mais úteis quando grandes dados do mesmo tipo precisam ser processados ou manipulados. As coleções podem ser preenchidas e manipuladas como um todo usando a opção 'BULK' em Oracle.

As coleções são classificadas com base na estrutura, subscrito e armazenamento conforme mostrado abaixo.

Índice por tabelas (também conhecido como Matriz Associativa)
Tabelas aninhadas
Varrays

A qualquer momento, os dados na coleção podem ser referidos por três termos Nome da coleção, Subscrito, Nome do campo/coluna como “ ( ). ”. Você aprenderá mais sobre essas categorias de coleção mencionadas acima na seção abaixo.

Varrays

Varray é um método de coleta em que o tamanho do array é fixo. O tamanho da matriz não pode ser excedido do seu valor fixo. O subscrito do Varray tem um valor numérico. A seguir estão os atributos de Varrays.

O tamanho do limite superior é fixo
Preenchido sequencialmente começando com o subscrito '1'
Este tipo de coleção é sempre denso, ou seja, não podemos excluir nenhum elemento do array. Varray pode ser excluído como um todo ou cortado do final.
Por ser sempre denso por natureza, tem muito menos flexibilidade.
É mais apropriado usar quando o tamanho do array é conhecido e realizar atividades semelhantes em todos os elementos do array.
O subscrito e a sequência sempre permanecem estáveis, ou seja, o subscrito e a contagem da coleção são sempre iguais.
Eles precisam ser inicializados antes de serem usados em programas. Qualquer operação (exceto a operação EXISTS) em uma coleção não inicializada gerará um erro.
Ele pode ser criado como um objeto de banco de dados, visível em todo o banco de dados ou dentro do subprograma, que pode ser utilizado apenas naquele subprograma.

A figura abaixo explicará a alocação de memória do Varray (denso) diagramaticamente.

Subscrito	1	2	3	4	5	6	7
Valor	Xyz	dfv	Sde	Cxs	Vbc	Nhu	Qnós

Sintaxe para VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

Na sintaxe acima, type_name é declarado como VARRAY do tipo 'DATA_TYPE' para o limite de tamanho fornecido. O tipo de dados pode ser simples ou complexo.

Mesas Aninhadas

Uma tabela aninhada é uma coleção na qual o tamanho do array não é fixo. Possui o tipo de subscrito numérico. Abaixo estão mais descrições sobre o tipo de tabela aninhada.

A tabela aninhada não tem limite máximo de tamanho.
Como o limite superior de tamanho não é fixo, a memória da coleção precisa ser estendida sempre antes de usá-la. Podemos estender a coleção usando a palavra-chave 'EXTEND'.
Preenchido sequencialmente começando com o subscrito '1'.
Este tipo de coleção pode ser de ambos denso e esparso, ou seja, podemos criar a coleção como densa e também podemos excluir o elemento individual do array aleatoriamente, o que o torna esparso.
Oferece mais flexibilidade em relação à exclusão do elemento da matriz.
Ele é armazenado na tabela de banco de dados gerada pelo sistema e pode ser usado na consulta de seleção para buscar os valores.
O subscrito e a sequência não são estáveis, ou seja, o subscrito e a contagem do elemento do array podem variar.
Eles precisam ser inicializados antes de serem usados em programas. Qualquer operação (exceto a operação EXISTS) na coleção não inicializada gerará um erro.
Ele pode ser criado como um objeto de banco de dados, visível em todo o banco de dados ou dentro do subprograma, que pode ser utilizado apenas naquele subprograma.

A figura abaixo explicará a alocação de memória da tabela aninhada (densa e esparsa) diagramaticamente. O espaço do elemento preto denota o elemento vazio em uma coleção, ou seja, esparso.

Subscrito	1	2	3	4	5	6	7
Valor (denso)	Xyz	dfv	Sde	Cxs	Vbc	Nhu	Qnós
Valor (esparso)	Qnós		Asd	AFG		Asd	Quem

Sintaxe para tabela aninhada:

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;

Na sintaxe acima, type_name é declarado como uma coleção de tabelas aninhadas do tipo 'DATA_TYPE'. O tipo de dados pode ser simples ou complexo.

Índice por tabela

Índice por tabela é uma coleção na qual o tamanho do array não é fixo. Ao contrário dos outros tipos de coleção, na coleção índice por tabela o subscrito pode consistir em ser definido pelo usuário. A seguir estão os atributos de índice por tabela.

O subscrito pode ser inteiro ou strings. No momento da criação da coleção deve ser mencionado o tipo de subscrito.
Essas coleções não são armazenadas sequencialmente.
Eles são sempre esparsos por natureza.
O tamanho da matriz não é fixo.
Eles não podem ser armazenados na coluna do banco de dados. Eles devem ser criados e usados em qualquer programa naquela sessão específica.
Eles dão mais flexibilidade em termos de manutenção de subscritos.
Os subscritos também podem ser de sequência de subscritos negativos.
Eles são mais apropriados para valores coletivos relativamente menores, nos quais a coleção pode ser inicializada e usada nos mesmos subprogramas.
Eles não precisam ser inicializados antes de começar a usá-los.
Não pode ser criado como um objeto de banco de dados. Só pode ser criado dentro do subprograma, que só pode ser utilizado nesse subprograma.
BULK COLLECT não pode ser usado neste tipo de coleção, pois o subscrito deve ser fornecido explicitamente para cada registro na coleção.

A figura abaixo explicará a alocação de memória da tabela aninhada (esparsa) diagramaticamente. O espaço do elemento preto denota o elemento vazio em uma coleção, ou seja, esparso.

Subscrito (varchar)	PRIMEIRO	SEGUNDA	TERCEIRO	QUARTO	QUINTA	SEXTA	SÉTIMO
Valor (esparso)	Qnós		Asd	AFG		Asd	Quem

Sintaxe para índice por tabela

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

Na sintaxe acima, type_name é declarado como uma coleção de índice por tabela do tipo 'DATA_TYPE'. O tipo de dados pode ser simples ou complexo. A variável subsciprt/index é fornecida como tipo VARCHAR2 com tamanho máximo de 10.

Conceito de construtor e inicialização em coleções

Construtores são funções embutidas fornecidas pelo oráculo que possuem o mesmo nome do objeto ou das coleções. Eles são executados primeiro sempre que objetos ou coleções são referidos pela primeira vez em uma sessão. Abaixo estão os detalhes importantes do construtor no contexto da coleção:

Para coleções, esses construtores devem ser chamados explicitamente para inicializá-las.
As tabelas Varray e Nested precisam ser inicializadas por meio desses construtores antes de serem encaminhadas para o programa.
O construtor estende implicitamente a alocação de memória para uma coleção (exceto Varray), portanto, o construtor também pode atribuir as variáveis às coleções.
Atribuir valores à coleção por meio de construtores nunca tornará a coleção esparsa.

Métodos de coleta

Oracle fornece muitas funções para manipular e trabalhar com as coleções. Essas funções são muito úteis no programa para determinar e modificar os diferentes atributos das coleções. A tabela a seguir dará as diferentes funções e suas descrições.

Forma	Descrição	SINTAXE
EXISTE (n)	Este método retornará resultados booleanos. Ele retornará 'TRUE' se n^th existe nessa coleção, caso contrário, ele retornará FALSE. Somente funções EXISTS podem ser usadas em coleções não inicializadas	.EXISTS(elemento_posição)
CONTAGEM	Fornece a contagem total dos elementos presentes em uma coleção	.CONTAR
LIMITE	Ele retorna o tamanho máximo da coleção. Para Varray, retornará o tamanho fixo que foi definido. Para tabela aninhada e índice por tabela, fornece NULL	.LIMITE
PRIMEIRO	Retorna o valor da primeira variável de índice (subscrito) das coleções	.PRIMEIRO
ÚLTIMA	Retorna o valor da última variável de índice (subscrito) das coleções	.DURAR
ANTES (n)	O retorno precede a variável de índice em uma coleção de n^th elemento. Se não houver nenhum valor de índice precedente, NULL será retornado	.PRIOR(n)
PRÓXIMO (n)	Retorna uma variável de índice bem-sucedida em uma coleção de n^th elemento. Se não houver sucesso, o valor do índice NULL será retornado	.PRÓXIMO(n)
AMPLIAR	Estende um elemento em uma coleção no final	.AMPLIAR
ESTENDER (n)	Estende n elementos no final de uma coleção	.ESTENDER(n)
ESTENDER (n,i)	Estende n cópias do i^th elemento no final da coleção	.EXTEND(n,i)
TRIM	Remove um elemento do final da coleção	.APARAR
TRIM (n)	Remove n elementos do final da coleção	.TRIM (n)
EXCLUIR	Exclui todos os elementos da coleção. Torna a coleção vazia	.EXCLUIR
EXCLUIR (n)	Exclui o enésimo elemento da coleção. Se então^th elemento é NULL, então isso não fará nada	.DELETE(n)
EXCLUIR (m,n)	Exclui o elemento no intervalo m^th tonelada^th na coleção	.DELETE(m,n)

Exemplo 1: Tipo de registro no nível do subprograma

Neste exemplo, veremos como preencher a coleção usando 'COLETA A GRANEL' e como consultar os dados da coleta.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det; guru99_emp_rec emp_det_tbl:= emp_det_tbl(); 
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,’AAA’,25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX’,10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,’ZZZ’,'7500,1000);
COMMIT:
SELECT emp no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line (‘Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line (‘Employee Number: '||guru99_emp_rec(i).emp_no); 
dbms_output.put_line (‘Employee Name: '||guru99_emp_rec(i).emp_name); 
dbms_output.put_line (‘Employee Salary:'|| guru99_emp_rec(i).salary); 
dbms_output.put_line(‘Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/

Explicação do código:

Linha de código 2-8: Tipo de registro 'emp_det' é declarado com colunas emp_no, emp_name, salário e gerente do tipo de dados NUMBER, VARCHAR2, NUMBER, NUMBER.
Linha de código 9: Criando a coleção 'emp_det_tbl' do elemento do tipo de registro 'emp_det'
Linha de código 10: Declarando a variável 'guru99_emp_rec' como tipo 'emp_det_tbl' e inicializada com construtor nulo.
Linha de código 12-15: Inserindo os dados de amostra na tabela 'emp'.
Linha de código 16: Confirmando a transação de inserção.
Linha de código 17: Buscando os registros da tabela 'emp' e preenchendo a variável de coleção em massa usando o comando “BULK COLLECT”. Agora a variável 'guru99_emp_rec' contém todos os registros que estão presentes na tabela 'emp'.
Linha de código 19-26: Configurando o loop 'FOR' para imprimir todos os registros da coleção um por um. O método de coleta FIRST e LAST é utilizado como limite inferior e superior do laço.

saída: Como você pode ver na captura de tela acima, quando o código acima for executado, você obterá a seguinte saída

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------

Você pode gostar: