To the point guide to PL/SQL

A collection can be defined as a set of ordered elements of similar data type. Collection types used in the coding include maps, lists and arrays.

PL/SQL provides three collection types:

• Variable-size array(Varray)
• Nested table
• Index-by tables / Associative array

Variable-size array (Varray):

• A varray is pretty much same as a Java array.
• User can store an ordered set of elements having an index associated with it by using a varray.
• The varray elements are of the similar type.
• When creating a varray, we have to set maximum size.
• varray elements cannot be modify individually, can be modified as a whole.
• User can change the size of a varray later.

Example:

DECLARE
  TYPE t_type IS VARRAY(3) OF NUMBER(15);
  v_tab  t_type;
  idx  NUMBER;
BEGIN
  v_tab := t_type(1, 2);

  -- Extending with extra values.
  << load_loop >>
  FOR x IN 1.. 3 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := x;
  END LOOP load_loop;
  
  idx := v_tab.FIRST;
  << display_loop >>
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(idx));
    idx := v_tab.NEXT(idx);
  END LOOP display_loop;
END;
/

Result:

The number 1
The number 2
The number 3

PL/SQL procedure successfully completed. 

Nested Tables:

A nested table is a one-dimensional array with number of elements and nested table is embedded with another table.

In a nested table user can update, delete and insert individual elements this makes more flexible than varray. In nested tables the elements are stored in separate tables.

Difference between array and a nested table:

• The size of nested table can dynamically increase where array size cannot be change dynamically.
• A nested array is dense initially, but when elements are deleted, it can become sparse where array is always dense.

Syntax:

TYPE name_of_type IS TABLE OF element_type [NOT NULL];
name_of_table type_name;

Example:

DECLARE
  TYPE t_type IS TABLE OF NUMBER(15);
  v_tab  t_type;
  idx  NUMBER;
BEGIN
  v_tab := t_type(1, 2);

  -- Extending with extra values.
  << load_loop >>
  FOR x IN 1.. 4 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := x;
  END LOOP load_loop;

-- to delete the second position item.
  v_tab.DELETE(2);

  
  idx := v_tab.FIRST;
  << display_loop >>
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(idx));
    idx := v_tab.NEXT(idx);
  END LOOP display_loop;
END;
/

Result:

The number 1
The number 3
The number 4

PL/SQL procedure successfully completed.

Index-By Table

Index-By Table is same as arrays but is more like a set of key-value pairs. An index-by table also known an associative array where each key is unique and used to locate the corresponding value.

Syntax:

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

Example:

DECLARE
  TYPE t_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;
  v_tab  t_type;
  idx  NUMBER;
BEGIN

  -- Extending with extra values.
  << load_loop >>
  FOR x IN 1.. 4 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := x;
  END LOOP load_loop;

-- To delete the second position item.
  v_tab.DELETE(2);
  
  idx := v_tab.FIRST;
  << display_loop >>
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(idx));
    idx := v_tab.NEXT(idx);
  END LOOP display_loop;
END;
/

Result:

The number 1
The number 3
The number 4

PL/SQL procedure successfully completed.

Collection Methods

The collection methods are listed below, some of them will be used very frequently in your development of procedures and functions:

• COUNT: To get the number of elements.
• DELETE: To remove entire elements.
• DELETE(n): To remove ‘n’ element.
• DELETE(n1,n2): To remove elements from n1 to n2.
• EXISTS(n): If the specified element is present returns TRUE.
• EXTEND : To append a null element.
• EXTEND(n): To append ‘n’ null elements.
• EXTEND(n1,n2): To appends n1 copies of the n2th element.
• FIRST: To get the index of the first element.
• LAST: To get the index of the first element.
• LIMIT: To get the maximum number of NULL for nested tables or elements for VARRAY.
• NEXT(n): To get the index of the specified element of next element.
• PRIOR(n): To get the index of the element prior to the specified element.
• TRIM: To remove an element from the end.
• TRIM(n): To remove ‘n’ elements from the end.

Collection Exceptions

The collection exceptions are listed below:

COLLECTION_IS_NULL:

To operate on an atomically collection null.

NO_DATA_FOUND:

A subscript designates an element that was deleted, or a nonexistent element of an associative array.

SUBSCRIPT_BEYOND_COUNT:

A subscript beyond the elements number in a collection.

SUBSCRIPT_OUTSIDE_LIMIT:

A subscript is beyond the given range.

VALUE_ERROR: A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is beyond this range.