Collection types

Index-by tables

Index-by tables are similar to usual arrays. In order to declare an index-by table, we need to define a table type, and then declare a variable of the type. The general syntax for defining an index-by type is 
type table_type_name is table of element_type index by binary_integer;
In the following example we define two table types (one table will contain integer elements and the other will contain the same type of elements as field login of the table Student) and declare two variables of the types 
declare
  type TDataTable is table of number index by binary_integer;
  type TLoginTable is table of Student.login%type index by binary_integer;
  v_Login TLoginTable;
  v_data  TDataTable;
begin
...
When a type and a variable of the type are declared, we can refer to any particular element of the table by its index putting it in parenthesis after the variable name, for example to refer to the 3-rd element of the v_Login table we need to write 
v_Login(3)
Thus, we can assign some elements of the table by doing this: 
v_data(3) := 123;
v_data(6) := 17;
v_data(-2) := 10;
By running this code we defined three elements of the table. That means that the table v_data contains only three elements with indices -2, 3, and 6. If now we try to read from any elements with an index other than these three the no_data_found exception will be generated.

These are the several things we need to know about index-by tables:

Any element that has been inserted in an index-by table can be deleted by the delete method. Once an element deleted we cannot access it.

Nested tables

The nested tables are similar to index-by tables. One difference between these two types are that nested tables must be created with sequential keys, and the keys cannot be negative. However, elements can be deleted later from the middle of the nested table, leaving the table with non-sequential keys. Another big difference between nested tables and index-by tables is that nested tables can be stored in the database while index-by tables cannot.

The general syntax for creating a nested table is 

type table_type_name is table of table_type [not null];
The table_type cannot be boolean, nchar, nclob, nvarchar2, or ref cursor.

One more difference between index-by and nested tables is initialization. While an index-by table can be created but empty (no elements), a nested table needs to be initialized. If it's not initialized in the declaration section, it will be automatically initialized by NULL and any attempt to add an element to it will raise "ORA-6531: COLLECTION_IS_NULL" exception. To initialize a nested table we need to use a constructor. The constructor for a nested table has the same name as the table type itself. The arguments of the constructor become elements of the table (with sequential indexes starting with 1). The following example show how to declared a nested table and initialize it 

declare
  type TDataTable is table of number;
  -- create a table with 5 elements
  v_data1 TDataTable := TDataTable(1, 23, 456, 7, 9);
  -- create a table with one element
  v_data2 TDataTable := TDataTable(23);
  -- create an empty table 
  v_data3 TDataTable := TDataTable();  
  ...
Once a table is created we can increase the number of elements it can hold by using extend method.

Collection methods

The following methods are available for collection types:
Method Return type Description Valid for
exists boolean Returns true if the specified element exists in the collection all
count number Returns the number of elements in the collection all
limit number Returns the max number of elements in the collection nested tables (always returns null), varrays
first binary_integer returns the index of the first element in the collection all
last binary_integer returns the index of the last element in the collection all
next binary_integer returns the index of the next element, relative to the the given element, in the collection all
prior binary_integer returns the index of the prior element, relative to the the given element, in the collection all
extend n/a adds elements to the collection nested tables, varrays
trim n/a removes elements from the end of the collection nested tables, varrays
delete n/a removes specified element from the collection index-by tables, nested tables

bulk collect clause

The bulk collect clause is used as a part of select into, fetch into, and returning into statements and allows to retrieve more than one row. The following example illustrates how to do that 
declare
 type TName is table of pl_test.name%type;
 v_name TName := TName(); 
 type TID is table of pl_test.id%type;
 v_id TID := TID(); 
begin
 v_name.extend(100);
 v_id.extend(100);
 select name, id
   bulk collect into v_name, v_id
   from pl_test;
 dbms_output.put_line('retrieved ' || v_name.count || ' elements' );
 for c in 1..v_name.count loop
   dbms_output.put_line(v_name(c) || ' ' || v_id(c));
 end loop;   
end;

In the returning into clause the bulk collect can be used to retrieve information about all changes (deletes) made to a table. The following example shows how to do that: 

declare
 type TName is table of pl_test.name%type;
 v_name TName := TName(); 
begin
 v_name.extend(100);
 update pl_test
    set id = id-1 
   returning name bulk collect into v_name; 
 dbms_output.put_line('retrieved ' || v_name.count || ' elements' );
 for c in 1..v_name.count loop
   dbms_output.put_line(v_name(c));
 end loop;
end;

forall statement

The forall statement allows to to pass all rows in a collection to the SQL engine with one operation. For example, if we need to insert whole collections v_name and v_id into a table we can execute 
forall ind in 1..v_name.count 
  insert into pl_test values (v_name(ind), v_id(ind));
Similarly we can pass a collection to the delete or update statement. The following example shows how to use that statement to send either a whole collection or a part of a collection to the SQL engine: 
declare
 type TName is table of pl_test.name%type;
 v_name TName := TName(); 
 type TID is table of pl_test.id%type;
 v_id TID := TID();
begin
 v_name.extend(100);
 v_id.extend(100);
 -- retrieve all IDs from the table
 select id
   bulk collect into v_id
   from pl_test;
 dbms_output.put_line('Table has ' || v_id.count || ' elements' );
 -- delete all but one rows from the table
 forall ind in 1..v_id.count-1
   delete from pl_test
    where id = v_id(ind)
    returning name bulk collect into v_name;
 dbms_output.put_line(v_name.count || ' elements deleted' );
 -- insert these elements back
 forall ind in 1..v_name.count
  insert into pl_test values (v_name(ind), v_id(ind));
 dbms_output.put_line(v_name.count || ' elements inserted' );
 -- print the whole table back
 select name, id
   bulk collect into v_name, v_id
   from pl_test;
 dbms_output.put_line('retrieved ' || v_name.count || ' elements' );
 for c in 1..v_name.count loop
   dbms_output.put_line(v_name(c) || ' ' || v_id(c));
 end loop;
end;