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

O que é uma Coleção?

Uma coleção é um grupo ordenado de elementos de um determinado tipo de dados. Pode ser uma coleção de um tipo de dados simples ou de um tipo de dados complexo, como tipos definidos pelo usuário ou tipos de registro.

Em uma coleção, cada elemento é identificado por um termo chamado título. "subscrito." A cada item é atribuído um índice único, e os dados podem ser manipulados ou obtidos por meio da referência a esse índice único.

As coleções são mais úteis quando uma grande quantidade de dados do mesmo tipo precisa ser processada ou manipulada. 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, no índice e no armazenamento, conforme mostrado abaixo:

• Tabelas indexadas (também conhecidas como matrizes associativas)
• Tabelas aninhadas
• Varrays

Em qualquer ponto, os dados em uma coleção podem ser referidos por três termos: nome da coleção, índice e nome do campo ou coluna, como “ ( ). Você aprenderá sobre essas categorias de coleções nas seções abaixo.

Tipos de coleções em resumo

Os três tipos de coleção apresentam diferentes vantagens e desvantagens. A tabela abaixo os compara lado a lado antes de cada um ser abordado em detalhes.

Varrays

Um Varray é uma coleção na qual o tamanho da matriz é fixo e não pode ser excedido. O índice de um Varray é um valor numérico. Os atributos de um Varray são:

• O tamanho limite superior é fixo.
• Preenchido sequencialmente a partir do índice '1'.
• Este tipo de coleção é sempre denso; não podemos excluir elementos individuais da matriz. Um Varray pode ser excluído por inteiro ou ter seu final removido.
• Por ser sempre denso, possui muito pouca flexibilidade.
• É mais apropriado quando o tamanho da matriz é conhecido e atividades semelhantes são realizadas em todos os elementos.
• O índice e a contagem da coleção permanecem sempre estáveis.
• É necessário inicializá-la antes de usar. Qualquer operação, exceto EXISTS, em uma coleção não inicializada gera um erro.
• Ele pode ser criado como um objeto de banco de dados visível em todo o banco de dados ou dentro de um subprograma para uso exclusivo nesse subprograma.

A figura abaixo explica a alocação de memória de um Varray (denso).

Sintaxe para VARRAY:

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

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

Mesas Aninhadas

Uma tabela aninhada é uma coleção na qual o tamanho da matriz não é fixo. Ela possui um tipo de índice numérico. Saiba mais sobre o tipo de tabela aninhada:

• A tabela aninhada não possui limite máximo de tamanho.
• Como o limite superior não é fixo, a memória precisa ser expandida a cada vez antes do uso, utilizando a palavra-chave 'EXTEND'.
• Preenchido sequencialmente a partir do índice '1'.
• Este tipo de coleção pode ser ambos denso e esparsoPodemos criá-la densa e também excluir elementos individuais aleatoriamente, o que a torna esparsa.
• Isso proporciona mais flexibilidade para excluir elementos da matriz.
• Ele é armazenado em uma tabela de banco de dados gerada pelo sistema e pode ser usado em uma consulta SELECT para buscar valores.
• O índice e a contagem podem variar.
• É necessário inicializá-la antes de usar. Qualquer operação, exceto EXISTS, em uma coleção não inicializada gera um erro.
• Ele pode ser criado como um objeto de banco de dados visível em todo o banco de dados ou dentro de um subprograma para uso exclusivo nesse subprograma.

A figura abaixo explica a alocação de memória de uma tabela aninhada (densa e esparsa). Um espaço vazio representa um elemento esparso.

Sintaxe para tabela aninhada:

TYPE <type_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

Uma tabela indexada é uma coleção na qual o tamanho da matriz não é fixo. Ao contrário de outros tipos de coleção, o índice de uma tabela indexada pode ser definido pelo usuário. Os atributos de uma tabela indexada são:

• O índice pode ser um número inteiro ou uma string. O tipo do índice deve ser mencionado ao criar a coleção.
• 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 em uma coluna de banco de dados. São criados e usados ​​dentro de uma sessão específica.
• Eles oferecem mais flexibilidade na manutenção do subscrito.
• Os índices podem ser uma sequência negativa.
• São mais adequadas para valores de coleta relativamente menores usados ​​dentro do mesmo subprograma.
• Não precisam ser inicializados antes do uso.
• Eles não podem ser criados como um objeto de banco de dados; são criados apenas dentro de um subprograma.
• O comando BULK COLLECT não pode ser usado com este tipo de coleção, pois o índice deve ser fornecido explicitamente para cada registro.

A figura abaixo explica a alocação de memória de uma tabela indexada (esparsa). Um espaço vazio no espaço do elemento indica um elemento esparso.

Sintaxe para indexação 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 tabela indexada do tipo 'DATA_TYPE'. A variável de índice é definida como do tipo VARCHAR2 com um tamanho máximo de 10.

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

Os construtores são funções integradas fornecidas por Oracle que têm o mesmo nome que o objeto ou coleção. Eles são executados primeiro sempre que um objeto ou coleção é referenciado pela primeira vez em uma sessão. Detalhes importantes de um construtor no contexto da coleção:

• Para coleções, esses construtores devem ser chamados explicitamente para inicializar a coleção.
• Tanto o Varray quanto as tabelas aninhadas precisam ser inicializadas por meio desses construtores antes de serem referenciadas no programa.
• Um construtor estende implicitamente a alocação de memória para uma coleção (exceto Varray), portanto, ele também pode atribuir variáveis ​​à coleção.
• Atribuir valores por meio de construtores nunca torna a coleção esparsa.

Métodos de coleta

Oracle Oferece diversas funções para manipular e trabalhar com coleções. Essas funções determinam e modificam os diferentes atributos de uma coleção. A tabela abaixo apresenta as diferentes funções e suas descrições.

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

Neste exemplo, veremos como preencher a coleção usando 'COLETA A GRANELe como se referir aos 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;
/

Code Explicação

• Code linhas 2-8: Tipo de registro 'emp_det' é declarado com as colunas emp_no, emp_name, manager e salary dos tipos de dados NUMBER, VARCHAR2, NUMBER e NUMBER.
• Code linha 9: Criando a coleção 'emp_det_tbl' do tipo de registro 'emp_det'.
• Code linha 10: Declarando a variável 'guru99_emp_rec' como do tipo 'emp_det_tbl' e inicializando-a com um construtor nulo.
• Code linhas 12-15: Inserindo os dados de amostra na tabela 'emp'.
• Code linha 16: Confirmando a transação de inserção.
• Code linha 17: Os registros da tabela 'emp' foram recuperados e a variável de coleção foi preenchida em massa usando "BULK COLLECT". A variável 'guru99_emp_rec' agora contém todos os registros presentes na tabela 'emp'.
• Code linhas 19-26: O loop 'FOR' foi configurado para imprimir todos os registros da coleção um por um. Os métodos de coleção FIRST e LAST são usados ​​como limites inferior e superior da coleção. laço.

Saída: Ao executar o código acima, 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
----------------------------------------------

Perguntas Frequentes