Working with Collections in Oracle

To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters, to move data between client-side applications and stored subprograms. To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.

Defining Collection Types

To create collections, you define a collection type, then declare variables of that type. You can define TABLE and VARRAY types in the declarative part of any PL/SQL block, subprogram, or package.

For associative arrays (also known as index-by tables), use the syntax:

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

The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760). The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.

For nested tables, use the syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL];

type_name is a type specifier used later to declare collections. For nested tables declared within PL/SQL, element_type is any PL/SQL datatype except REF CURSOR.

For varrays, use the syntax:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) 
   OF element_type [NOT NULL];

The meanings of type_name and element_type are the same as for nested tables. size_limit is a positive integer literal representing the maximum number of elements in the array. When defining a VARRAY type, you must specify its maximum size.

Defining Collection Variables

Once you define a collection type, you can declare variables of that type. You use the new type name in the declaration, the same as with predefined types such as NUMBER and INTEGER.

Example: Declaring Nested Tables, Varrays, and Associative Arrays

DECLARE

   TYPE nested_type IS TABLE OF VARCHAR2(20);

   TYPE varray_type IS VARRAY(50) OF INTEGER;

   TYPE associative_array_type IS TABLE OF NUMBER
      INDEXED BY BINARY_INTEGER;

   v1 nested_type;

   v2 varray_type;

   v3 associative_array_type;

   v4 v1%TYPE;

   TYPE EmpList IS TABLE OF emp.ename%TYPE;  -- based on column

   CURSOR c1 IS SELECT * FROM dept;

   TYPE DeptFile IS VARRAY(20) OF c1%ROWTYPE;  -- based on cursor

   TYPE AnEntry IS RECORD (
      term    VARCHAR2(20), 
      meaning VARCHAR2(200)); 

   TYPE Glossary IS VARRAY(250) OF AnEntry;

   TYPE EmpList IS TABLE OF emp.empno%TYPE NOT NULL;

Example: Declaring a Procedure Parameter as a Nested Table

You can declare collections as the formal parameters of functions and procedures. That way, you can pass collections to stored subprograms and from one subprogram to another. The following example declares a nested table as a parameter of a packaged procedure:

CREATE PACKAGE personnel AS

   TYPE Staff IS TABLE OF Employee;

   ...

   PROCEDURE award_bonuses (members IN Staff);

END personnel;

 You can also specify a collection type in the RETURN clause of a function specification:

DECLARE

   TYPE SalesForce IS VARRAY(25) OF Salesperson;

   FUNCTION top_performers (n INTEGER) RETURN SalesForce IS ...

Initializing and Referencing Collections

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

You must explicitly call a constructor for each varray and nested table variable. (Associative arrays, the third kind of collection, do not use constructors.) Constructor calls are allowed wherever function calls are allowed.

Example: Constructor for a Nested Table and a Varray

DECLARE
   TYPE CourseList IS TABLE OF VARCHAR2(16);
   my_courses CourseList;
   
   TYPE ProjectList IS VARRAY(50) OF VARCHAR2(16);
   accounting_projects ProjectList;
BEGIN
   my_courses :=
      CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100');

   accounting_projects := 
      ProjectList('Expense Report', 'Outsourcing', 'Auditing');
END;

Referencing Collection Elements

Every reference to an element includes a collection name and a subscript enclosed in parentheses. The subscript determines which element is processed. To reference an element, you specify its subscript using the syntax

collection_name(subscript)

where subscript is an expression that yields an integer in most cases, or a VARCHAR2 for associative arrays declared with strings as keys.

The allowed subscript ranges are:

• For nested tables, 1 .. 2**31.
• For varrays, 1 ..  size_limit, where you specify the limit in the declaration.
• For associative arrays with a numeric key, -2**31 .. 2**31.
• For associative arrays with a string key, the length of the key and number of possible values depends on the VARCHAR2 length limit in the type declaration, and the database character set.

This example shows how to reference an element in the nested table NAMES:

DECLARE
   TYPE Roster IS TABLE OF VARCHAR2(15);
   names Roster := Roster('J Hamil', 'D Caruso', 'R Singh');
BEGIN
  FOR i IN names.FIRST .. names.LAST
  LOOP
      IF names(i) = 'J Hamil' THEN
        NULL;
      END IF;
  END LOOP;
END;

Assigning Collections

One collection can be assigned to another by an INSERT, UPDATE, FETCH, or SELECT statement, an assignment statement, or a subprogram call.

You can assign the value of an expression to a specific element in a collection using the syntax:

collection_name(subscript) := expression;

where expression yields a value of the type specified for elements in the collection type definition.

Comparing Collections

You can check whether a collection is null, but not test whether two collections are the same. Conditions such as greater than, less than, and so on are also not allowed.

Choosing Collection

If you already have code or business logic that uses some other language, you can usually translate that language's array and set types directly to PL/SQL collection types.

• Arrays in other languages become VARRAYs in PL/SQL.
• Sets and bags in other languages become nested tables in PL/SQL.
• Hash tables and other kinds of unordered lookup tables in other languages become associative arrays in PL/SQL.

When you are writing original code or designing the business logic from the start, you should consider the strengths of each collection type to decide which is appropriate for each situation.

Choosing Between Nested Tables and Associative Arrays

Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.

Nested tables can be stored in a database column, but associative arrays cannot. Nested tables are appropriate for important data relationships that must be stored persistently.

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers when appropriate.

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 use anonymous PL/SQL blocks to bulk-bind input and output host arrays to associative arrays.

Choosing Between Nested Tables and Varrays

Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence. When stored in the database, varrays retain their ordering and subscripts.

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end. Nested table data is stored out-of-line in a store table, a system-generated database table associated with the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection. You cannot rely on the order and subscripts of a nested table remaining stable as the table is stored and retrieved, because the order and subscripts are not preserved when a nested table is stored in the database.

Collection Constructors for Varrays and Nested Tables

A collection constructor (constructor) is a system-defined function with the same name as a collection type, which returns a collection of that type. The syntax of a constructor invocation is:

collection_type ( [ value [, value ]... ] )

If the parameter list is empty, the constructor returns an empty collection. Otherwise, the constructor returns a collection that contains the specified values.

You can assign the returned collection to a collection variable (of the same type) in the variable declaration and in the executable part of a block.

The following example invokes a constructor twice: to initialize the varray variable team to empty in its declaration, and to give it new values in the executable part of the block. The procedure print_team shows the initial and final values of team. To determine when team is empty, print_team uses the collection method COUNT.

DECLARE
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
  team Foursome := Foursome();  -- initialize to empty
 
  PROCEDURE print_team (heading VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    IF team.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Empty');
    ELSE 
      FOR i IN 1..4 LOOP
        DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
      END LOOP;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('---'); 
  END;
 
BEGIN
  print_team('Team:');
  team := Foursome('John', 'Mary', 'Alberto', 'Juanita');
  print_team('Team:');
END;
/

Team:
Empty
---
Team:
1.John
2.Mary
3.Alberto
4.Juanita
---