PLSQL Collection

Many times we require a number of data elements on which similar type of operation is required. For such needs PL/SQL provides an ordered group of specific data type called PLSQL collection. PLSQL collection can be group of elements of inbuilt data type or complex data type. Complex data types user-defined data types or table records.

Just like arrays the elements of the collection can be accessed or identified by a subscript. Every element in the collection gets a different subscript. The subscript of the element can be used to manipulate or process the element by fetching it by using the subscript of the required element. The collection must be declared in the DECLARE section  of a PLSQL block.

There are two types of PLSQL collection on the basis of subscript, number of elements, time of creation, density and element structure.

Associative array (or index-by table)

 The Associative array can contain unbounded count of elements and its subscripts can be string or integer type. It can be either dense or sparse and is created when PLSQL block in which it is declared is executed.

SYNTAX

TYPE type_name IS TABLE OF DATATYPE [NOT NULL] INDEX BY VARCHAR2(length)/ INTEGER;

type_name used in the above syntax is used to create a variable of this type. This is the way to use the associative array:

type_name_variable  type_name;

The elements of this Associative  array type declared by user is accesses by mentioning the index of the element in parenthesis after the Associative  array variable

PLSQL Collection EXAMPLE

TYPE emp_name_type  IS TABLE OF emp.first_name%TYPE  INDEX BY INTEGER;
emp_name_list emp_name_type;
emp_name_list(3);/* access the 4th element of the associative array.*/

Example
DECLARE 
CURSOR champ_cur IS SELECT f_name FROM champion WHERE rownum <= 5;
TYPE f_name_type IS TABLE OF champion.f_name%TYPE INDEX BY BINARY_INTEGER; 
f_name_list f_name_type;
v_count INTEGER := 0; 
BEGIN 
FOR champ_nam IN champ_cur LOOP 
    v_count := v_count + 1; 
    f_name_list(v_count) := champ_nam.f_name; 
    DBMS_OUTPUT.PUT_LINE ('First Name-'||v_count||': '|| f_name_list(v_count)); 
END LOOP; 
END;

Nested Table

Aa nested table is another type of PLSQL collection which contains unbounded count of elements. Its subscript can be integer only and when created it is dense and can become sparse as the elements are deleted from it. You can create it when PLSQL block is executed or it is created as a data object in the database schema.

SYNTAX

TYPE type_name IS TABLE OF DATA TYPE;

Using the Nested Table:

type_name_var type_name:= type_name();

Adding elements in the Nested Table

type_name_var(index):=value;

PLSQL Collection EXAMPLE

DECLARE 
CURSOR champ_cur IS SELECT f_name FROM champion WHERE rownum <= 5;
TYPE f_name_type IS TABLE OF champion.f_name%TYPE; 
f_name_list f_name_type := f_name_type();
v_count INTEGER := 0; 
BEGIN 
FOR champ_nam IN champ_cur LOOP 
    v_count := v_count + 1; 
    f_name_list.EXTEND; 
    f_name_list(v_count) := champ_nam.f_name; 
    DBMS_OUTPUT.PUT_LINE ('First Name-'||v_count||': '|| f_name_list(v_count)); 
END LOOP; 
END;

admin