#

| Chapter 1 Oracle PL/SQL Language Pocket Reference |

## 1.17 Collections

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

Index-by table

  Single-dimension, unbounded collections of homogeneous elements available only in PL/SQL, not in the database. Index-by tables are initially sparse; they have nonconsecutive subscripts.

Nested table

  Single-dimension, unbounded collections of homogeneous elements available in both PL/SQL and the database as columns or tables. Nested tables are initially dense (they have consecutive subscripts), but can become sparse through deletions.

VARRAYs

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

The following table compares these similar collection types.

| Collection Type
--- | ---
Characteristic | Index-by Table | 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) | 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 procedure or TRIM to condense, with no predefined maximum | 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

### 1.17.1 Syntax for Declaring Collection Datatypes

Collections are implemented as TYPEs. Like 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 index-by table is:

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

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];

The CREATE keyword defines the statement to be DDL and indicates that this type will exist in the database. The optional OR REPLACE keywords are used to rebuild an existing type, preserving the privileges. type_name is any valid identifier that will be used later to declare the collection. max_elements is the maximum size of the VARRAY. element_type is the type of the collection's elements. All elements are of a single type, which can be most scalar datatypes, an object type, or a REF object type. If the elements are objects, the object type itself cannot have an attribute that is a collection. Explicitly disallowed collection datatypes are BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE, and VARRAY.

NOT NULL indicates that a collection of this type cannot have any null elements. However, the collection can be atomically null (uninitialized).

### 1.17.2 Initializing Collections

Initializing an index-by table is trivial -- simply declaring it also initializes it. Initializing a nested table or VARRAY can be done explicitly, with a constructor, or implicitly with a fetch from the database or 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. We can create a nested table of colors and 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

We can create our nested table of colors and 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 initialization technique is by 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';

### 1.17.3 Adding and Removing Elements

Elements in an index-by table 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.

### 1.17.4 Collection Pseudo-Functions

There are several psuedo-functions defined for collections. They include THE, CAST, MULTISET, and TABLE.

THE

  Maps a single column's value in a single row into a virtual database table. Syntactically, this is similar to an inline view, where a subquery is used in the FROM clause.

  >     SELECT VALUE(c)
  >        FROM THE(SELECT colors
  >                   FROM color_models
  >                  WHERE model_type = 'RGB') c;

  The pseudo-function THE will work in Oracle8i but its use is discouraged. Use the 8i TABLE( ) function instead.

CAST

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

  >     SELECT column_value
  >     FROM THE(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));

  In Oracle8i PL/SQL, 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)))

### 1.17.5 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 described 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 index-by table. 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 smallest index in use. Returns NULL when applied to empty initialized collections.
LAST function | Returns the largest 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 index-by tables and nested tables.
PRIOR ( i ) function | Return the index immediately before element i. Returns NULL if i is less than or equal to FIRST.
NEXT ( i ) function | Return 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. Index-by tables cannot be TRIMmed.

The EXISTS function returns a BOOLEAN and all other functions return BINARY_INTEGER. 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, since TRIM can remove previously DELETED elements.

Here is an example of some collection methods in use:

>     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 gives 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=

### 1.17.6 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.

### 1.17.7 Bulk Binds (Oracle8i)

Starting with Oracle8i, you can use collections to improve the performance of SQL operations executed iteratively by using bulk binds. Bulk binds reduce the number of round-trips that must be made between a client application and the database. Two PL/SQL language constructs implement bulk binds: FORALL and BULK COLLECT INTO.

The syntax for the FORALL statement is:

>     FORALL bulk_index IN lower_bound..upper_bound
>        sql_statement;

bulk_index can be used only in the sql_statement and only as a collection index (subscript). When PL/SQL processes this statement, the whole collection, instead of each individual collection element, is sent to the database server for processing. To delete all the accounts in the collection inactives from the table ledger, do this:

>     FORALL i IN inactives.FIRST..inactives.LAST
>        DELETE FROM ledger WHERE acct_no = inactives(i);

The syntax for the BULK COLLECT INTO clause is:

>     BULK COLLECT INTO collection_name_list;

where collection_name_list is a comma-delimited list of collections, one for each column in the SELECT. As of Oracle8i, collections of records cannot be a target of a BULK COLLECT INTO clause. However, 8i does support retrieving a set of typed objects and "bulk collecting" them into a collection of objects.

The BULK COLLECT INTO clause can be used in SELECT INTO, FETCH INTO, or RETURNING INTO statements. For example:

>     DECLARE
>        TYPE vendor_name_tab IS TABLE OF
>           vendors.name%TYPE;
>        TYPE vendor_term_tab IS TABLE OF
>           vendors.terms%TYPE;
>        v_names vendor_name_tab;
>        v_terms vendor_term_tab;
>     BEGIN
>        SELECT name, terms
>          BULK COLLECT INTO v_names, v_terms
>          FROM vendors
>         WHERE terms < 30;
>        ...
>     END;

The next function deletes employees in an input list of departments, and the (Oracle8) SQL RETURNING clause returns a list of deleted employees:

>     FUNCTION whack_emps_by_dept (deptlist dlist_t)
>     RETURN enolist_t
>     IS
>        enolist enolist_t;
>     BEGIN
>        FORALL adept IN deptlist.FIRST..deptlist.LAST
>           DELETE FROM emp WHERE deptno IN
>              deptlist(adept)
>           RETURNING empno BULK COLLECT INTO enolist;
>        RETURN Enolist;
>     END;

You can use the SQL%BULK_ROWCOUNT cursor attribute for bulk bind operations. It is like an index-by table containing the number of rows affected by the executions of the bulk bound statements. The nth element of SQL%BULK_ROWCOUNT contains the number of rows affected by nth execution of the SQL statement. For example:

>     FORALL i IN inactives.FIRST..inactives.LAST
>        DELETE FROM ledger WHERE acct_no = inactives(i);
>     FOR counter IN inactives.FIRST..inactives.LAST
>     LOOP
>        IF SQL%BULK_ROWCOUNT(counter) = 0
>        THEN
>           DBMS_OUTPUT.PUT_LINE('No rows deleted for '||
>              counter);
>        END IF;
>     END LOOP;

You cannot pass SQL%BULK_ROWCOUNT as a parameter to another program, or use an aggregate assignment to another collection. %ROWCOUNT contains a summation of all %BULK_ROWCOUNT elements. %FOUND and %NOTFOUND reflect only the last execution of the SQL statement.

1.16 Oracle8 Objects |  | 1.18 External Procedures

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.