PLSQL Collection

Collection

PLSQL Collection :-A collection is an ordered group of elements having

the same data type. Each element is identified by a unique subscript that
represents its position in the collection.

PL/SQL collection types:- There are 3 types of collections in Oracle PLSQL listed below

1)  Index by table (PLSQL Tables) Assosiate Array
2)  Nested table
3)  Variable size Array (Varray)

Nested Tables:- This is the most common form of collection. A nested table variable is a variable which can hold many values with similar type just like collection of records of a table.Nested tables can be collections of any data type, including composites such as PL/SQL records or user-defined types. 

Declaration of Nested Table :-

declare
type emp_nt is table of emp%rowtype;
emp_rec_nt emp_nt;
begin
Executable statement;
end;
/

Here "emp_rec_nt" is a nested table variable.

Note:- When we use "BULK COLLECT" then only collection variable can hold the data.

Example:-

declare
type emp_nt is table of emp%rowtype;
emp_rec_nt emp_nt;
begin
select * bulk collect into emp_rec_nt from emp;
end;
/

Collection Feature :- We can navigate backwards as well as forwards, even skip to the end or the beginning,which cannot do with a cursor. 


Index By table:-Index By table is also called as Associative Array . These are simple collections of single attributes with an index. The index can be a number, a string.
 

Declaration of Index by table :-

declare
type emp_sal is table of emp.sal%type index by emp.empno%type;
l_emp_sal emp_sal;
begin
Executable statement;
end;
/


SQL>l_emp_sal(l_emp_no) := l_emp_sal;


Variable arrays:-Variable arrays are just nested tables with a pre-defined limit on the number of elements. So perhaps the name is misleading:
they are actually fixed arrays. There's little we can do with VArrays which we can't do with nested tables (except constrain the number of elements and it's pretty rare that we would want to do that).


Declaration of Variable array :-

declare
type emp_va is varray(14) of emp%rowtype;
emp_rec_va emp_va;
begin
Executable statement;
end;
/


select * bulk collect into emp_rec_va from employees;
 
The one big advantage of Varray over nested tables is that they guarantee the order of the elements. So if you must get elements out in the same order as you inserted them use a Varray.

Associative arrays:- Associative array is also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.
 
Nested tables:- hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
 
Varrays:- (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

1. Choosing Between Nested Tables and Associative Arrays:-
Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation,
but they have different characteristics when it comes to persistence and ease of parameter passing.

Nested tables can be stored in a database column, but associative arrays cannot.
Nested tables can simplify SQL operations where you would normally join a single-column table with a larger table.

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be non sequential, and can use string values instead of numbers.

PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).
 
2. Choosing Between Nested Tables and Varrays:-
Varrays are a good choice when: 
1) The number of elements is known in advance.
2) The elements are usually all accessed in sequence.

When stored in the database, varrays keep their ordering and subscripts.

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Nested tables are a good choice when:
1) The index values are not consecutive.
2) There is no set number of index values. However, a maximum limit is imposed.
3) You need to delete or update some elements, but not all the elements at once.

You would usually create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end.

Nested table data is stored in a separate store table, a system-generated database table associated with the nested table. The database joins the tables for you when you access the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection.

You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.