PL/SQL data types TABLE and VARRAY allow you to define collections, an ordered set of elements, all of the same type. Each element is addressed by a unique index.

You can define collection types in a procedure, function, or package. You can pass collection variables as parameters to PL/SQL subprograms.

PL/SQL offers 3 collection types:

• Associative Arrays
• Nested Tables
• Varrays

Associative array is a set of pairs: key and value, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string. The keys do not have to be consecutive.

Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry.

DECLARE 
  TYPE continent_type IS TABLE OF NUMBER INDEX BY VARCHAR2(80);
  v_continent continent_type;
  v_count NUMBER;
BEGIN
  v_continent('Europe') := 100;      -- New entry created
  v_count := v_continent('Europe');  -- 100 assigned
END;

To create an associative array, you need to define TABLE collection type, and then declare a variable of that type.

The key data type can be PLS_INTEGER (BINARY_INTEGER) or VARCHAR2.

DECLARE 
  TYPE emp_type IS TABLE OF employees%ROWTYPE% INDEX BY PLS_INTEGER;
  v_emp emp_type;

• COUNT returns the number of elements that a collection currently contains

• FIRST returns the smallest key value in a collection. If the collection is empty FIRST returns NULL.

• LAST returns the largest key value in a collection. If the collection is empty LAST returns NULL.

• NEXT(n) returns the key value that succeeds the key n. if n has no successor, NEXT returns NULL.

• PRIOR(n) returns the key value that precedes the key n. if n has no predecessor, PRIOR returns NULL.

v_continent('Europe') := 100;      
v_continent('America') := 200;
v_continent('Asia') := 300;      
v_continent('Africa') := 400;      

v_continent.COUNT returns 4
v_continent.FIRST returns Africa
v_continent.LAST returns Europe
v_continent.NEXT('Africa') returns America
v_continent.PRIOR('Europe') returns Asia

• EXISTS(n) function returns TRUE if element with the n key value exists in a collection, otherwise, it returns FALSE. You can use EXISTS to avoid referencing a nonexistent element, which raises an exception.

IF t_array.EXISTS(iParamID) THEN
 t_array(iParamID).Status := 0;
END IF;