Collection in PL/SQL

Collection: 

The collection is the composite data type which groups elements of the same type in a specified order. Each element in the collection has a unique subscript/index that determines its position.

Record 

The record is a composite data type where the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with the following syntax:variable_name.field_name. 

To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.

Type Emp_Rec is Record
         (Emp_No      Emp.Emp_No%Type,
   Emp_Name   Emp.Emp_Name%Type,
          salary       Emp.Salary%Type,
          Hire_Date    Emp.Hire_Date%Type);

Record can hold maximum of one record/row.
PL/SQL has three kinds of collections:

• Associative arrays (formerly called "PL/SQL tables" or "index-by tables")
• Nested tables
• Variable-size arrays (varrays)

To create a collection, you first define a collection type, and then declare a variable of that type.

Associative array (index-by table)

• Can be indexed by either integers or strings. Normally index in the associate array is PLS_INTEGER. 
• In 10g, the oracle recommendation was to use the index as Binary_Integer. But as Binary_Integer and Pls_Integer are same in 11g, oracle recommends Pls_Integer as index for  the Associative arrary.

An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.

An Associative array is created using the following syntax.

Type  <type_name> Is  Table of <table.%rowtype> Index by Pls_integer;

Note: Create the Associative array in package specification and access anywhere in the application.

This topic applies to collection types that you define inside a PL/SQL block or package,

Associative arrays and nested tables can be sparse (have nonconsecutive subscripts), but varrays are always dense (have consecutive subscripts). Unlike nested tables, varrays retain their ordering and subscripts when stored in the database. Initially nested table is dense but can be sparse on deletion of element from the middle.

Normally collections are defined in the package specification so that they can be accessed from anywhere in the application.

Until you initialize it, a nested table or varray is atomically null (that is, the collection itself is null, not its elements). To initialize a nested table or varray, you use a constructor, which is a system-defined function with the same name as the collection type. This function constructs (creates) a collection from the elements passed to it.

Collection Methods:

COUNT 
Returns the number of elements that a collection currently contains, which is useful because the current size of a collection is not always known. You can use COUNT wherever an integer expression is allowed.

For varrays, COUNT always equals LAST. For nested tables, normally, COUNT equals LAST. But, if you delete elements from the middle of a nested table, COUNT is smaller than LAST.

DELETE

This procedure has three forms. DELETE removes all elements from a collection. DELETE(n) removes the nth element from an associative array or nested table. If n is null, DELETE(n) does nothing. DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

EXISTS

EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. You can also use EXISTS to avoid raising an exception when you reference a nonexistent element. When passed an out-of-range subscript, EXISTS returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT.

EXTEND

This procedure has three forms. EXTEND appends one null element to a collection. EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to a collection.
EXTEND operates on the internal size of a collection. If EXTEND encounters deleted elements, it includes them in its tally.
You cannot use EXTEND with associative arrays.

FIRST, LAST

FIRST and LAST return the first and last (smallest and largest) subscript values in a collection. The subscript values are usually integers, but can also be strings for associative arrays. If the collection is empty, FIRST and LAST return NULL. If the collection contains only one element, FIRST and LAST return the same subscript value.

For varrays, FIRST always returns 1 and LAST always equals COUNT. For nested tables, normally, LAST equals COUNT. But, if you delete elements from the middle of a nested table, LAST is larger than COUNT.

INDEX

An expression that must return (or convert implicitly to) an integer in most cases, or a string for an associative array declared with string keys.

LIMIT

For nested tables, which have no maximum size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain (which you must specify in its type definition).

NEXT, PRIOR

PRIOR(n) returns the subscript that precedes index n in a collection. NEXT(n) returns the subscript that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. Likewise, if n has no successor, NEXT(n) returns NULL.

TRIM

This procedure has two forms. TRIM removes one element from the end of a collection. TRIM(n) removes n elements from the end of a collection. If n is greater than COUNT, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT. You cannot use TRIM with index-by tables.

TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally.

Usage Notes

You cannot use collection methods in a SQL statement. If you try, you get a compilation error.

Only EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.

If the collection elements have sequential subscripts, you can use collection.FIRST .. collection.LAST in a FOR loop to iterate through all the elements.

You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. For example, you can use PRIOR or NEXT to traverse a nested table from which some elements have been deleted, or an associative array where the subscripts are string values.

EXTEND operates on the internal size of a collection, which includes deleted elements. You cannot use EXTEND to initialize an atomically null collection. Also, if you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.

If an element to be deleted does not exist, DELETE simply skips it; no exception is raised. Varrays are dense, so you cannot delete their individual elements.

Because PL/SQL keeps placeholders for deleted elements, you can replace a deleted element by assigning it a new value. However, PL/SQL does not keep placeholders for trimmed elements.

The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.

In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.

Within a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply methods FIRST, LAST, COUNT, and so on to such parameters. For varray parameters, the value of LIMIT is always derived from the parameter type definition, regardless of the parameter mode.

S.N. Method Name & Purpose

1 EXISTS(n)
Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2 COUNT
Returns the number of elements that a collection currently contains.
3 LIMIT
Checks the Maximum Size of a Collection.
4 FIRST
Returns the first (smallest) index numbers in a collection that uses integer subscripts.
5 LAST
Returns the last (largest) index numbers in a collection that uses integer subscripts.
6 PRIOR(n)
Returns the index number that precedes index n in a collection.
7 NEXT(n)
Returns the index number that succeeds index n.
8 EXTEND
Appends one null element to a collection.
9 EXTEND(n)
Appends n null elements to a collection.
10 EXTEND(n,i)
Appends n copies of the ith element to a collection.
11 TRIM
Removes one element from the end of a collection.
12 TRIM(n)
Removes n elements from the end of a collection.
13 DELETE
Removes all elements from a collection, setting COUNT to 0.
14 DELETE(n)
Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
15 DELETE(m,n)
Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Declaring and Using an Associative Array : 
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)] --subscript_type;
table_name type_name;  -- Associative array variable

•   TYPE assoc_array_num_type      IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
•    TYPE assoc_array_str_type    IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
•    TYPE assoc_array_str_type2    IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);

eg:   a ) DECLARE
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
   salary_list salary;     -- salary_list is a variable of salary type.

b)  DECLARE
 TYPE bin_array IS TABLE OF VARCHAR2(30)  INDEX BY BINARY_INTEGER;

Ex-1: PL/SQL Associative array example (index by binary integer):

SET SERVEROUTPUT ON
SET VERIFY OFF
accept cc prompt 'Enter the country code: '

DECLARE TYPE country_type IS RECORD (
iso_code VARCHAR2(5),
name VARCHAR2(50)
);

TYPE country_tab IS TABLE OF country_type  INDEX BY BINARY_INTEGER;

t_country country_tab;

BEGIN

-- Populate lookup
t_country(1).iso_code := 'UK';
t_country(1).name := 'United Kingdom';
t_country(2).iso_code := 'US';
t_country(2).name := 'United States of America';
t_country(3).iso_code := 'FR';
t_country(3).name := 'France';
t_country(4).iso_code := 'DE';
t_country(4).name := 'Germany';

-- Find country name for ISO code "DE"
                 << lookup >>
          FOR i IN 1 .. 4 LOOP
                 IF t_country(i).iso_code = upper('&cc') THEN
                DBMS_OUTPUT.PUT_LINE('ISO code "&cc" = ' || t_country(i).name);
                EXIT lookup;
               END IF;
          END LOOP;

END;
/

Ex-2 : PL/SQL Associative array example (index by string ):

DECLARE
    -- Associative array indexed by string:
        TYPE population IS TABLE OF NUMBER  -- Associative array type
        INDEX BY VARCHAR2(64);
        city_population  population;        -- Associative array variable
          i                VARCHAR2(64);
      BEGIN
     -- Add new elements to associative array:
       city_population('Smallville')  := 2000;
     city_population('Midland')     := 750000;
     city_population('Megalopolis') := 1000000;
        -- Change value associated with key 'Smallville':
       city_population('Smallville') := 2001;
       -- Print associative array:
       i := city_population.FIRST;
        WHILE i IS NOT NULL LOOP
       DBMS_Output.PUT_LINE
         ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));
       i := city_population.NEXT(i);
     END LOOP;
   END;
   /
Ex-3 : Following example shows how to create a table to store integer values along with names and later it prints the same list of names.

SET SERVEROUTPUT ON
DECLARE
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
   salary_list salary;
   name   VARCHAR2(20);
BEGIN
   -- adding elements to the table
   salary_list('Rajnish')  := 62000;
   salary_list('Minakshi')  := 75000;
   salary_list('Martin') := 100000;
   salary_list('James') := 78000;

   -- printing the table
   name := salary_list.FIRST;
   WHILE name IS NOT null LOOP
      dbms_output.put_line
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
      name := salary_list.NEXT(name);
   END LOOP;
END;
/
Salary of James is 78000
Salary of Martin is 100000
Salary of Minakshi is 75000
Salary of Rajnish is 62000
PL/SQL procedure successfully completed.
Example:
Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
SET SERVEROUTPUT ON
DECLARE
   CURSOR c_customers is
      select  name from customers;
 
   TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;
   name_list c_list;
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter +1;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter|| '):'||name_list(counter));
  END LOOP;
END;
/
Customer(1):Ramesh
Customer(2):Khilan
Customer(3):Kaushik
Customer(4):Chaitali
Customer(5):Hardik
Customer(6):Komal
PL/SQL procedure successfully completed.

Like a database table, an associative array holds a data set of arbitrary size, and you can access its elements without knowing their positions in the array. An associative array does not need the disk space or network operations of a database table, but an associative array cannot be manipulated by SQL statements (such as INSERT and DELETE).

An associative array is intended for temporary data storage. To make an associative array persistent for the life of a database session, declare the associative array (the type and the variable of that type) in a package, and assign values to its elements in the package body.

Nested Tables

A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects:

An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

A nested table is created using the following syntax:

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

This declaration is similar to declaration of an index-by table, but there is no INDEX BY clause.

A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

Example:

The following examples illustrate the use of nested table:

Ex-1:

DECLARE
   TYPE names_table IS TABLE OF VARCHAR2(10);
   TYPE grades IS TABLE OF INTEGER;

   names names_table;
   marks grades;
   total integer;
BEGIN
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i IN 1 .. total LOOP
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
   end loop;
END;
/
When the above code is executed at SQL prompt, it produces the following result:

Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92
PL/SQL procedure successfully completed.

Example:
Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:

Ex-2:

SET SERVEROUTPUT ON
DECLARE
   CURSOR c_customers is
      SELECT  name FROM customers;

   TYPE c_list IS TABLE of customers.name%type;
   name_list c_list := c_list();
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter +1;
      name_list.extend;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter));
   END LOOP;
END;
Customer(1):Ramesh
Customer(2):Khilan
Customer(3):Kaushik
Customer(4):Chaitali
Customer(5):Hardik
Customer(6):Komal
PL/SQL procedure successfully completed.

Variable-size arrays (varrays)

PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. A varray is used to store an ordered collection of data, but it is often more useful to think of an array as a collection of variables of the same type.

All varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

                              

An array is a part of collection type data and it stands for variable-size arrays. We will study other collection types in a later chapter 'PL/SQL Collections'.

Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically.

Creating a Varray Type :

A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray.

The basic syntax for creating a VRRAY type at the schema level is:

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Where,

• varray_type_name is a valid attribute name,
• n is the number of elements (maximum) in the varray,
• element_type is the data type of the elements of the array.

Maximum size of a varray can be changed using the ALTER TYPE statement.

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);

The basic syntax for creating a VRRAY type within a PL/SQL block is:

TYPE varray_type_name IS VARRAY(n) of <element_type>

For example:

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);

Type grades IS VARRAY(5) OF INTEGER;

Ex-1:

DECLARE
   type namesarray IS VARRAY(5) OF VARCHAR2(10);
   type grades IS VARRAY(5) OF INTEGER;
   names namesarray;
   marks grades;
   total integer;
BEGIN
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i in 1 .. total LOOP
      dbms_output.put_line('Student: ' || names(i) || '
      Marks: ' || marks(i));
   END LOOP;
END;
/
Total 5 Students
Student: Kavita
      Marks: 98
Student: Pritam
      Marks: 97
Student: Ayan
      Marks: 78
Student: Rishav
      Marks: 87
Student: Aziz
      Marks: 92
PL/SQL procedure successfully completed.

Please note:

• In oracle environment, the starting index for varrays is always 1.
• You can initialize the varray elements using the constructor method of the varray type, which has the same name as the varray.
• Varrays are one-dimensional arrays.
• A varray is automatically NULL when it is declared and must be initialized before its elements can be referenced.

Ex-2:

Elements of a varray could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept:
We will use the CUSTOMERS table stored in our database as:
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

Following example makes use of cursor .

SET SERVEROUTPUT ON
DECLARE
   CURSOR c_customers is
   SELECT  name FROM customers;
   type c_list is varray (6) of customers.name%type;
   name_list c_list := c_list();
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter + 1;
      name_list.extend;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter));
   END LOOP;
END;
/
Customer(1):Ramesh
Customer(2):Khilan
Customer(3):Kaushik
Customer(4):Chaitali
Customer(5):Hardik
Customer(6):Komal
PL/SQL procedure successfully completed.

Collection Exceptions:

Additional:

Associative array (index-by table)-------------------------------------------------
Declare
  Type Emp_Rec_Type Is Record
        (Empno Emp.Empno%Type,
Ename Emp.Ename%Type,
Salary Emp.Salary%Type,
Job Emp.Job%Type
);

Type Emp_Tab_Type Is Table Of Emp_Rec_Type Index By Pls_Integer;

V_Tab   Emp_Tab_Type;

Cursor Cur_Emp Is
   Select Empno,Ename,Salary,Job From Emp;

Ind    Pls_Integer := 0;
 
Begin
  For V_Rec In Cur_Emp
  Loop
    Ind := Ind + 1;
V_Tab(Ind).Empno  := V_Rec.Empno ;
V_Tab(Ind).Ename  := V_Rec.Ename ;
V_Tab(Ind).Salary := V_Rec.Salary ;
V_Tab(Ind).Job    := V_Rec.Job ;
  End Loop;

  If V_Tab.Count > 0 Then
     Ind := V_Tab.First;
While Ind Is Not Null
Loop
  Dbms_output.put_line('Emp no:='||V_Tab(Ind).Empno||'  Name:='||V_Tab(Ind).Ename||' Salary :='||V_Tab(Ind).Salary||' Job:='||V_Tab(Ind).Job);
  Ind := V_Tab.Next(Ind);
End Loop;
  End If;
End;
/

-------------------------Nested Table------------------------------------
Declare
  Type Emp_Rec_Type Is Record
        (Empno Emp.Empno%Type,
Ename Emp.Ename%Type,
Salary Emp.Salary%Type,
Job Emp.Job%Type
);

Type Emp_Tab_Type Is Table Of Emp_Rec_Type;

V_Tab   Emp_Tab_Type := Emp_Tab_Type( );

Cursor Cur_Emp Is
   Select Empno,Ename,Salary,Job From Emp;

Ind    Pls_Integer := 0;
 
Begin
  For V_Rec In Cur_Emp
  Loop
    V_Tab.Extend;
V_Tab(V_Tab.Count).Empno  := V_Rec.Empno ;
V_Tab(V_Tab.Count).Ename  := V_Rec.Ename ;
V_Tab(V_Tab.Count).Salary := V_Rec.Salary ;
V_Tab(V_Tab.Count).Job    := V_Rec.Job ;
  End Loop;

  If V_Tab.Count > 0 Then
     Ind := V_Tab.First;
While Ind Is Not Null
Loop
  Dbms_output.put_line('Emp no:='||V_Tab(Ind).Empno||'  Name:='||V_Tab(Ind).Ename||' Salary :='||V_Tab(Ind).Salary||' Job:='||V_Tab(Ind).Job);
  Ind := V_Tab.Next(Ind);
End Loop;
  End If;
End;
/



V-Array ----------------------------------------------
Declare

Type Tab_Type Is Table Of Number;

V_Tab   Tab_Type := Tab_Type();

 
Begin
   If V_Tab Is Null Then
      Dbms_output.put_line('table is null.');
   Else
      Dbms_output.put_line('table is not null.');
      Dbms_output.put_line('total no of records:='||v_tab.count);
   End if;
   V_tab.extend;  
  Dbms_output.put_line('total no of records after extend:='||v_tab.count);
 End;
/  


Cheers
Rajani