PL / SQL - Coleções

Neste capítulo, discutiremos as Coleções em PL / SQL. Uma coleção é um grupo ordenado de elementos com o mesmo tipo de dados. Cada elemento é identificado por um subscrito exclusivo que representa sua posição na coleção.

PL / SQL fornece três tipos de coleção -

Tabelas de indexação ou matriz associativa
Mesa aninhada
Matriz de tamanho variável ou Varray

A documentação Oracle fornece as seguintes características para cada tipo de coleção -

Tipo de coleção	Número de Elementos	Tipo de Subscrito	Denso ou esparso	Onde foi criado	Pode ser atributo de tipo de objeto
Matriz associativa (ou índice por tabela)	Sem limites	String ou inteiro	Ou	Apenas em bloco PL / SQL	Não
Mesa aninhada	Sem limites	Inteiro	Começa denso, pode se tornar esparso	Tanto no bloco PL / SQL quanto no nível do esquema	sim
Matriz de tamanho variável (Varray)	Limitado	Inteiro	Sempre denso	Tanto no bloco PL / SQL quanto no nível do esquema	sim

Já discutimos varray no capítulo 'PL/SQL arrays'. Neste capítulo, discutiremos as tabelas PL / SQL.

Ambos os tipos de tabelas PL / SQL, ou seja, as tabelas index-by e as tabelas aninhadas têm a mesma estrutura e suas linhas são acessadas usando a notação de subscrito. No entanto, esses dois tipos de tabelas diferem em um aspecto; as tabelas aninhadas podem ser armazenadas em uma coluna do banco de dados e as tabelas index-by não podem.

Índice por tabela

A index-by tabela (também chamada de associative array) é um conjunto de key-valuepares. Cada chave é única e é usada para localizar o valor correspondente. A chave pode ser um número inteiro ou uma string.

Uma tabela index-by é criada usando a seguinte sintaxe. Aqui, estamos criando umindex-by mesa chamada table_name, as chaves serão do subscript_type e os valores associados serão do element_type

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
 
table_name type_name;

Exemplo

O exemplo a seguir mostra como criar uma tabela para armazenar valores inteiros junto com nomes e posteriormente imprimir a mesma lista de nomes.

DECLARE 
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
   salary_list salary; 
   name   VARCHAR2(20); 
BEGIN 
   -- adding elements to the table 
   salary_list('Rajnish') := 62000; 
   salary_list('Minakshi') := 75000; 
   salary_list('Martin') := 100000; 
   salary_list('James') := 78000;  
   
   -- printing the table 
   name := salary_list.FIRST; 
   WHILE name IS NOT null LOOP 
      dbms_output.put_line 
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); 
      name := salary_list.NEXT(name); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Salary of James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

Exemplo

Os elementos de uma tabela index-by também podem ser um %ROWTYPE de qualquer tabela de banco de dados ou %TYPEde qualquer campo da tabela do banco de dados. O exemplo a seguir ilustra o conceito. Vamos usar oCUSTOMERS tabela armazenada em nosso banco de dados como -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is 
      select name from customers; 

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; 
   name_list c_list; 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed

Mesas Aninhadas

UMA nested tableé como uma matriz unidimensional com um número arbitrário de elementos. No entanto, uma tabela aninhada difere de uma matriz nos seguintes aspectos -

Uma matriz tem um número declarado de elementos, mas uma tabela aninhada não. O tamanho de uma tabela aninhada pode aumentar dinamicamente.
Um array é sempre denso, ou seja, sempre possui subscritos consecutivos. Uma matriz aninhada é inicialmente densa, mas pode se tornar esparsa quando os elementos são excluídos dela.

Uma tabela aninhada é criada usando a seguinte sintaxe -

TYPE type_name IS TABLE OF element_type [NOT NULL]; 
 
table_name type_name;

Esta declaração é semelhante à declaração de um index-by mesa, mas não há INDEX BY cláusula.

Uma tabela aninhada pode ser armazenada em uma coluna do banco de dados. Ele também pode ser usado para simplificar as operações SQL, em que você associa uma tabela de coluna única a uma tabela maior. Uma matriz associativa não pode ser armazenada no banco de dados.

Exemplo

Os exemplos a seguir ilustram o uso de tabela aninhada -

DECLARE 
   TYPE names_table IS TABLE OF VARCHAR2(10); 
   TYPE grades IS TABLE OF INTEGER;  
   names names_table; 
   marks grades; 
   total integer; 
BEGIN 
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i IN 1 .. total LOOP 
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); 
   end loop; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Total 5 Students 
Student:Kavita, Marks:98 
Student:Pritam, Marks:97 
Student:Ayan, Marks:78 
Student:Rishav, Marks:87 
Student:Aziz, Marks:92  

PL/SQL procedure successfully completed.

Exemplo

Elementos de um nested table também pode ser um %ROWTYPEde qualquer tabela do banco de dados ou% TYPE de qualquer campo da tabela do banco de dados. O exemplo a seguir ilustra o conceito. Usaremos a tabela CUSTOMERS armazenada em nosso banco de dados como -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is  
      SELECT  name FROM customers;  
   TYPE c_list IS TABLE of customerS.No.ame%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed.

Métodos de coleta

A PL / SQL fornece os métodos de coleta integrados que tornam as coleções mais fáceis de usar. A tabela a seguir lista os métodos e suas finalidades -

S.No	Nome e objetivo do método
1	EXISTS(n) Retorna TRUE se o enésimo elemento em uma coleção existir; caso contrário, retorna FALSE.
2	COUNT Retorna o número de elementos que uma coleção contém atualmente.
3	LIMIT Verifica o tamanho máximo de uma coleção.
4	FIRST Retorna os primeiros (menores) números de índice em uma coleção que usa os subscritos inteiros.
5	LAST Retorna os últimos (maiores) números de índice em uma coleção que usa os subscritos inteiros.
6	PRIOR(n) Retorna o número do índice que precede o índice n em uma coleção.
7	NEXT(n) Retorna o número do índice que sucede ao índice n.
8	EXTEND Acrescenta um elemento nulo a uma coleção.
9	EXTEND(n) Acrescenta n elementos nulos a uma coleção.
10	EXTEND(n,i) Anexos ncópias do i ^ésimo elemento para uma coleção.
11	TRIM Remove um elemento do final de uma coleção.
12	TRIM(n) Remove n elementos do final de uma coleção.
13	DELETE Remove todos os elementos de uma coleção, definindo COUNT como 0.
14	DELETE(n) Remove o n^thelemento de uma matriz associativa com uma chave numérica ou uma tabela aninhada. Se a matriz associativa tiver uma chave de string, o elemento correspondente ao valor da chave será excluído. E sen é nulo, DELETE(n) faz nada.
15	DELETE(m,n) Remove todos os elementos do intervalo m..nde uma matriz associativa ou tabela aninhada. E sem é maior que n ou se m ou n é nulo, DELETE(m,n) faz nada.

Exceções de coleção

A tabela a seguir fornece as exceções de coleção e quando elas são levantadas -

Exceção de coleção	Criado em Situações
COLLECTION_IS_NULL	Você tenta operar em uma coleção atomicamente nula.
NENHUM DADO ENCONTRADO	Um subscrito designa um elemento que foi excluído ou um elemento inexistente de uma matriz associativa.
SUBSCRIPT_BEYOND_COUNT	Um subscrito excede o número de elementos em uma coleção.
SUBSCRIPT_OUTSIDE_LIMIT	Um subscrito está fora do intervalo permitido.
VALUE_ERROR	Um subscrito é nulo ou não conversível para o tipo de chave. Esta exceção pode ocorrer se a chave for definida como umPLS_INTEGER intervalo, e o subscrito está fora desse intervalo.