Collections in Oracle

There are three types of collections: associative arrays (formerly known as index-by tables or PL/SQL tables), nested tables, and VARRAYs. 

Associative arrays

1. Single-dimension, 
2. unbounded collections of homogeneous elements available only in PL/SQL declarative part of a block, subprogram, package specification, or package body, not in the database. 
3. Associative arrays are initially sparse; they have nonconsecutive subscripts. 
4. 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.

• The index can be a number, or (since 9iR2) a string which can be very useful. For instance, here is an associative array of salaries which is indexed by the employee identifier.

type emp_sal_aa is table of emp.sql%type

index by emp.empno%type;

l_emp_sales emp_sal_aa;

Nested tables

1. Single-dimension, 
2. Unbounded collections of homogeneous elements available in both PL/SQL and the database as columns or tables. and in the CREATE TYPE Statement (in which case it is a standalone stored type).
3. Nested tables are initially dense (they have consecutive subscripts), but they can become sparse through deletions.
4. Nested table data is stored in a separate store table, a system-generated database table. When you access a nested table, the database joins the nested table with its store table. This makes nested tables suitable for queries and updates that only affect some elements of the collection.
5. 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.

Declaring a Procedure Parameter as a Nested Table

 TYPE staff_list IS TABLE OF employees.employee_id%TYPE;

  PROCEDURE award_bonuses (empleos_buenos IN staff_list); 

A nested table is a variable which can hold more than one instance of something, often a record from a database table. They might be declared like this:

type emp_nt is table of emp%rowtype;

emp_rec_nt emp_nt;

• They are useful whenever we want to store multiple instances of data against which we want to do the same thing. The classic example is using BULK COLLECT to store multiple records:

select * 

bulk collect into emp_rec_nt

from employees;

VARRAYs

1. Single-dimension, 
2. bounded collections of homogeneous elements available in both PL/SQL and the database. 
3. VARRAYs are never sparse. 
4. Unlike nested tables, their element order is preserved when you store and retrieve them from the database. 

We can use bulk collect to populate a VArray ...

select *
bulk collect into emp_rec_va
from employees;

Until you initialize it, a nested table or varray is atomically null

The following table compares these similar collection types:

		Collection type
Characteristic	Associative array	Nested table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL?	No	Yes	Yes
Usable as a column datatype in a table?	No	Yes; data stored "out of line" (in a separate table)	Yes; data typically stored "in line" (in the same table)
Uninitialized state	Empty (cannot be NULL); elements are undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic, when declared	Via constructor, fetch, assignment	Via constructor, fetch, assignment
In PL/SQL, elements referenced by	BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647) or character string (VARCHAR2); maximum length of VARCHAR2 is 30, minimum length is 1	Positive integer between 1 and 2,147483,647	Positive integer between 1 and 2,147483,647
Sparse?	Yes	Initially no; after deletions, yes	No
Bounded?	No	Can be extended	Yes
Can assign a value to any element at any time?	Yes	No; may need to EXTEND first	No; may need to EXTEND first, and cannot EXTEND past the upper bound
Means of extending	Assign value to element with a new subscript	Use built-in EXTEND or TRIM function to condense, with no predefined maximum	Use EXTEND or TRIM, but only up to declared maximum size.
Can be compared for equality?	No	No	No
Elements retain ordinal position and subscript when stored and retrieved from the database	N/A?can't be stored in database	No	Yes

 Choosing Between Nested Tables and Associative Arrays

A nested table can be stored in a database column; therefore, you can use a nested table to simplify SQL operations in which you join a single-column table with a larger table. An associative array cannot be stored in the database.

An associative array is appropriate for the following:

    A relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked or a package is initialized

    Passing collections to and from the database server

    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, and then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).

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 accessed sequentially.
3.  When stored in the database, varrays keep their ordering and subscripts.
4.  A varray is stored as a single object. If a varray is less than 4 KB, it is stored inside the table of which it is a column; otherwise, it is stored outside the table but in the same table-space.
5. You must store or retrieve all elements of a varray at the same time, which is appropriate when operating on all the elements at once. However, this might be impractical for large numbers of elements.

Nested tables are a good choice when:

1. Index values are not consecutive.
2. There is no set number of index values.
3. You must delete or update some elements, but not all elements at once.
4. You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Declaring a Collection

Collections are implemented as TYPEs. As with any programmer-defined type, you must first define the type; then you can declare instances of that type. The TYPE definition can be stored in the database or declared in the PL/SQL program. Each instance of the TYPE is a collection. 

 The syntax for declaring an associative array is

TYPE type_name IS TABLE OF element_type [NOT NULL]

   INDEX BY {BINARY_INTEGER | VARCHAR2 (size_limit)};

The syntax for a nested table is: 

[CREATE [OR REPLACE]] TYPE type_name IS TABLE OF

   element_type [NOT NULL];

The syntax for a VARRAY is: 

[CREATE [OR REPLACE]] TYPE type_name IS VARRAY |

   VARYING ARRAY (max_elements) OF element_type

   [NOT NULL];

 Initializing Collections 

Initializing an associative array is trivial? simply declaring it also initializes it. 

Initializing a nested table or a VARRAY can be done in any of three ways: explicitly with a constructor, or implicitly with a fetch from the database or with a direct assignment of another collection variable. 

The constructor is a built-in function with the same name as the collection. It constructs the collection from the elements passed to it. 

The first example shows how you can create a nested table of colors and explicitly initialize it to three elements with a constructor: 

DECLARE

   TYPE colors_tab_t IS TABLE OF VARCHAR2(30);

   colors_tab_t('RED','GREEN','BLUE');

BEGIN

The next example shows how you can create the nested table of colors and implicitly initialize it with a fetch from the database: 

-- Create the nested table to exist in the database.

CREATE TYPE colors_tab_t IS TABLE OF VARCHAR2(32);

-- Create table with nested table type as column.

CREATE TABLE color_models

(model_type   VARCHAR2(12)

,colors       color_tab_t)

NESTED TABLE colors STORE AS

   color_model_colors_tab;

-- Add some data to the table.

INSERT INTO color_models

   VALUES('RGB',color_tab_t('RED','GREEN','BLUE'));

INSERT INTO color_models

   VALUES('CYMK',color_tab_t('CYAN','YELLOW',

      'MAGENTA' 'BLACK'));

-- Initialize a collection of colors from the table.

DECLARE

   basic_colors colors_tab_t;

BEGIN

   SELECT colors INTO basic_colors

     FROM color_models

    WHERE model_type = 'RGB';

...

END;

The third example shows how you can implicitly initialize the table via an assignment from an existing collection: 

DECLARE

   basic_colors Color_tab_t :=

      Color_tab_t ('RED','GREEN','BLUE');

   my_colors Color_tab_t;

BEGIN

   my_colors := basic_colors;

   my_colors(2) := 'MUSTARD';

Adding and Removing Elements

Elements in an associative array can be added simply by referencing new subscripts. 

To add elements to nested tables or VARRAYs, you must first enlarge the collection with the EXTEND function, and then you can assign a value to a new element using one of the methods described in the previous section. 

Use the DELETE function to remove an element in a nested table regardless of its position. 

The TRIM function can also be used to remove elements, but only from the end of a collection. 

To avoid unexpected results, do not use both DELETE and TRIM on the same collection.

Collection Pseudo-Functions 

There are several pseudo-functions defined for collections: CAST, MULTISET, and TABLE. 

CAST

Maps a collection of one type to a collection of another type. 

SELECT column_value

FROM TABLE(SELECT CAST(colors AS color_tab_t)

           FROM color_models_a

          WHERE model_type ='RGB');

MULTISET

Maps a database table to a collection. With MULTISET and CAST, you can retrieve rows from a database table as a collection-typed column. 

SELECT b.genus ,b.species,

      CAST(MULTISET(SELECT bh.country

                      FROM bird_habitats bh

                     WHERE bh.genus = b.genus

                       AND bh.species = b.species)

          AS country_tab_t)

FROM birds b;

TABLE

Maps a collection to a database table (the inverse of MULTISET). 

 

SELECT *

  FROM color_models c

 WHERE 'RED' IN (SELECT * FROM TABLE(c.colors));

You can use TABLE( ) to unnest a transient collection:

DECLARE

   birthdays Birthdate_t :=

      Birthdate_t('24-SEP-1984', '19-JUN-1993');

BEGIN

   FOR the_rec IN

      (SELECT COLUMN_VALUE

         FROM TABLE(CAST(birthdays AS Birthdate_t)))

Collection Methods 

There are a number of built-in functions (methods) defined for all collections. These methods are called with dot notation:

collection_name.method_name[(parameters)]

The methods are listed in the following table:

Collection method	Description
COUNT function	Returns the current number of elements in the collection.
DELETE [( i [ , j ] )] procedure	Removes element i or elements i through j from a nested table or associative array. When called with no parameters, removes all elements in the collection. Reduces the COUNT if the element is not already DELETEd. Does not apply to VARRAYs.
EXISTS ( i ) function	Returns TRUE or FALSE to indicate whether element i exists. If the collection is an uninitialized nested table or VARRAY, returns FALSE.
EXTEND [( n [ , i ] )] procedure	Appends n elements to a collection, initializing them to the value of element i. n is optional and defaults to 1.
FIRST function	Returns the lowest index in use. Returns NULL when applied to empty initialized collections.
LAST function	Returns the greatest index in use. Returns NULL when applied to empty initialized collections.
LIMIT function	Returns the maximum number of allowed elements in a VARRAY. Returns NULL for associative arrays and nested tables.
PRIOR ( i ) function	Returns the index immediately before element i. Returns NULL if i is less than or equal to FIRST.
NEXT ( i ) function	Returns the index immediately after element i. Returns NULL if i is greater than or equal to COUNT.
TRIM [( n )] procedure	Removes n elements at the end of the collection with the largest index. n is optional and defaults to 1. If n is NULL, TRIM does nothing. Associative arrays cannot be TRIMmed.

The EXISTS function returns a BOOLEAN, and all other functions and procedures return BINARY_INTEGER except for collections indexed by VARCHAR2, which can return character strings. 

All parameters are of the BINARY_INTEGER type. Only EXISTS can be used on uninitialized nested tables or VARRAYs. Other methods applied to these atomically null collections will raise the COLLECTION_IS_NULL exception. 

DELETE and TRIM both remove elements from a nested table, but TRIM also removes the placeholder, while DELETE does not. This behavior may be confusing, because TRIM can remove previously deleted elements. 

Here is an example of some collection methods in use with an associative array: 

DECLARE

   TYPE population_type IS

      TABLE OF NUMBER INDEX BY VARCHAR2(64);

   continent_population population_type;

   howmany NUMBER;

   limit VARCHAR2(64);

BEGIN

   continent_population('Australia') := 30000000;

   -- Create new entry

   continent_population('Antarctica') := 1000;

   -- Replace old value

   continent_population('Antarctica') := 1001;

   limit := continent_population.FIRST;

   DBMS_OUTPUT.PUT_LINE (limit);

   DBMS_OUTPUT.PUT_LINE (continent_population(limit));

   limit := continent_population.LAST;

   DBMS_OUTPUT.PUT_LINE (limit);

   DBMS_OUTPUT.PUT_LINE (continent_population(limit));

END;

/

This example produces the following output:

Antarctica

1001

Australia

30000000

Here is an example of some collection methods in use with a nested table: 

DECLARE

   TYPE colors_tab_t IS TABLE OF VARCHAR2(30);

   my_list colors_tab_t :=

      colors_tab_t('RED','GREEN','BLUE');

   element BINARY_INTEGER;

BEGIN

   DBMS_OUTPUT.PUT_LINE('my_list has '

      ||my_list.COUNT||' elements');

   my_list.DELETE(2); -- delete element two

   DBMS_OUTPUT.PUT_LINE('my_list has '

      ||my_list.COUNT||' elements');

   FOR element IN my_list.FIRST..my_list.LAST

   LOOP

      IF my_list.EXISTS(element)

      THEN

         DBMS_OUTPUT.PUT_LINE(my_list(element)

            || ' Prior= '||my_list.PRIOR(element)

            || ' Next= ' ||my_list.NEXT(element));

      ELSE

         DBMS_OUTPUT.PUT_LINE('Element '|| element

            ||' deleted. Prior= '||my_

               list.PRIOR(element)

            || ' Next= '||my_list.NEXT(element));

      END IF;

   END LOOP;

END;

This example produces the output:

my_list has 3 elements

my_list has 2 elements

RED Prior=  Next= 3

Element 2 deleted. Prior= 1 Next= 3

BLUE Prior= 1 Next=

 Collections and Privileges

As with other TYPEs in the database, you need the EXECUTE privilege on that TYPE in order to use a collection type created by another schema (user account) in the database.

Note that Oracle9i Release 2 made it possible to use synonyms for user-defined TYPE names.

Nested Collections

Nested collections are collections contained in members that are collections themselves. Nesting collections is a powerful way to implement object-oriented programming constructs within PL/SQL programs. 

For example:

CREATE TYPE books IS TABLE OF VARCHAR2(64);

CREATE TYPE our_books IS TABLE OF books;