Coleções Oracle PL / SQL: Varrays, Nested & Índice por tabelas

Índice:

Anonim

O que é coleção?

Uma coleção é um grupo ordenado de elementos de tipos de dados específicos. Pode ser uma coleção de tipo de dados simples ou tipo de dados 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 é atribuído a um subscrito exclusivo. Os dados dessa coleção podem ser manipulados ou buscados referindo-se a esse subscrito exclusivo.

As coleções são as coisas mais úteis quando um grande volume de dados do mesmo tipo precisa ser processado ou manipulado. As coleções podem ser preenchidas e manipuladas como um todo usando a opção 'BULK' no Oracle.

Neste tutorial, você aprenderá-

  • O que é coleção?
  • Varrays
  • Mesas Aninhadas
  • Índice por tabela
  • Construtor e conceito de inicialização em coleções
  • Métodos de coleta

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

Em qualquer ponto, 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 da matriz é fixo. O tamanho da matriz não pode ser excedido do que seu valor fixo. O subscrito do Varray tem um valor numérico. A seguir estão os atributos dos 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 pode ser cortado a partir do final.
  • Como sempre é de natureza densa, tem muito menos flexibilidade.
  • É mais apropriado usar quando o tamanho da matriz é conhecido e executar atividades semelhantes em todos os elementos da matriz.
  • O subscrito e a sequência sempre permanecem estáveis, ou seja, o subscrito e a contagem da coleção são sempre os mesmos.
  • Eles precisam ser inicializados antes de serem usados ​​em programas. Qualquer operação (exceto operação EXISTS) em uma coleção não inicializada lançará um erro.
  • Ele pode ser criado como um objeto de banco de dados, que é visível em todo o banco de dados ou dentro do subprograma, que pode ser usado apenas naquele subprograma.

A figura abaixo irá explicar a alocação de memória de Varray (densa) em forma de diagrama.

Subscrito 1 2 3 4 5 6 7
Valor Xyz Dfv Sde Cxs Vbc Nhu Qwe

Sintaxe para VARRAY:

TYPE  IS VARRAY () OF ;
  • 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 da matriz 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 de tamanho superior.
  • Como o limite superior de tamanho não é fixo, a coleção e a memória precisam ser estendidas a cada vez antes de serem usadas. 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 denso e esparso , ou seja, podemos criar a coleção como densa e também podemos excluir o elemento individual da matriz aleatoriamente, o que o torna tão esparso.
  • Isso dá 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 operação EXISTS) na coleção não inicializada lançará um erro.
  • Ele pode ser criado como um objeto de banco de dados, que é visível em todo o banco de dados ou dentro do subprograma, que pode ser usado apenas naquele subprograma.

A figura abaixo irá explicar a alocação de memória da tabela aninhada (densa e esparsa) em forma de diagrama. O espaço do elemento de cor preta 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 Qwe
Valor (esparso) Qwe Asd Afg Asd Nós somos

Sintaxe para tabela aninhada:

TYPE  IS TABLE OF ;
  • 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 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, o tipo de subscrito deve ser mencionado.
  • 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 nessa sessão particular.
  • Eles oferecem mais flexibilidade em termos de manutenção do subscrito.
  • Os subscritos também podem ser de sequência de subscritos negativos.
  • Eles são mais apropriados para uso em valores coletivos relativamente menores, nos quais a coleção pode ser inicializada e usada dentro dos mesmos subprogramas.
  • Eles não precisam ser inicializados antes de começar a usá-los.
  • Ele não pode ser criado como um objeto de banco de dados. Ele só pode ser criado dentro do subprograma, que pode ser usado somente naquele 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 irá explicar a alocação de memória da tabela aninhada (esparsa) em forma de diagrama. O espaço do elemento de cor preta denota o elemento vazio em uma coleção, ou seja, esparso.

Subscrito (varchar) PRIMEIRO SEGUNDO TERCEIRO QUARTO QUINTO SEXTO SÉTIMO
Valor (esparso) Qwe Asd Afg Asd Nós somos

Sintaxe para índice por tabela

TYPE  IS TABLE OF  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.

Construtor e conceito de inicialização em coleções

Construtores são as funções embutidas fornecidas pelo oráculo que tem o mesmo nome do objeto ou 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á-los.
  • Ambas as tabelas Varray e Nested precisam ser inicializadas por meio desses construtores antes de serem referidas no 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

O Oracle oferece muitas funções para manipular e trabalhar com as coleções. Estas funções são muito úteis no programa para determinar e modificar os diferentes atributos das coleções. A tabela a seguir fornecerá as diferentes funções e sua descrição.

Método Descrição SINTAXE
EXISTE (n) Este método retornará resultados booleanos. Ele retornará 'TRUE' se o enésimo elemento existir nessa coleção, caso contrário, ele retornará FALSE. Apenas funções EXISTS podem ser usadas na coleção não inicializada .EXISTS (element_position)
CONTAR Dá a contagem total dos elementos presentes em uma coleção .COUNT
LIMITE Ele retorna o tamanho máximo da coleção. Para Varray, ele retornará o tamanho fixo que foi definido. Para tabela aninhada e índice por tabela, dá NULL .LIMIT
PRIMEIRO Retorna o valor da primeira variável de índice (subscrito) das coleções .FIRST
ÚLTIMO Retorna o valor da última variável de índice (subscrito) das coleções .LAST
ANTERIOR (n) Retorna precede a variável de índice em uma coleção do enésimo elemento. Se não houver nenhum valor de índice anterior, NULL é retornado .PRIOR (n)
PRÓXIMO (n) Retorna a variável de índice bem-sucedida em uma coleção do enésimo elemento. Se não houver sucesso, o valor do índice NULL é retornado . PRÓXIMO (n)
AMPLIAR Estende um elemento em uma coleção no final .EXTEND
EXTEND (n) Estende n elementos no final de uma coleção .EXTEND (n)
EXTEND (n, i) Estende n cópias do iésimo elemento no final da coleção .EXTEND (n, i)
APARAR Remove um elemento do final da coleção .TRIM
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 .DELETE
DELETE (n) Exclui o enésimo elemento da coleção. Se o enésimo elemento for NULL, isso não fará nada .DELETE (n)
DELETE (m, n) Exclui o elemento no intervalo m th ao n th na coleção .DELETE (m, n)

Exemplo 1: Tipo de registro em nível de subprograma

Neste exemplo, veremos como preencher a coleção usando 'BULK COLLECT' e como consultar os dados da coleção.

DECLARETYPE 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();BEGININSERT 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_recFROM emp;dbms_output.put_line (‘Employee Detail');FOR i IN guru99_emp_rec.FIRST… guru99_emp_rec.LASTLOOPdbms_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 : O tipo de registro 'emp_det' é declarado com as colunas emp_no, emp_name, salário e gerenciador de tipo de dados NUMBER, VARCHAR2, NUMBER, NUMBER.
  • Linha de código 9: Criando a coleção 'emp_det_tbl' do elemento de 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 como um volume 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' usando para imprimir todos os registros na coleção um por um. O método de coleta FIRST e LAST é usado como limite inferior e superior do loop.

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

Employee DetailEmployee Number: 1000Employee Name: AAAEmployee Salary: 25000Employee Manager Number: 1000----------------------------------------------Employee Number: 1001Employee Name: XXXEmployee Salary: 10000Employee Manager Number: 1000----------------------------------------------Employee Number: 1002Employee Name: YYYEmployee Salary: 15000Employee Manager Number: 1000----------------------------------------------Employee Number: 1003Employee Name: ZZZEmployee Salary: 7500Employee Manager Number: 1000----------------------------------------------