Collection Methods

A collection method is a built-in function or procedure that operates on collections and is called using dot notation. The methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE help generalize code, make collections easier to use, and make your applications easier to maintain.

EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions, which appear as part of an expression. EXTEND, TRIM, and DELETE are procedures, which appear as a statement. EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take integer parameters. EXISTS, PRIOR, NEXT, and DELETE can also take VARCHAR2 parameters for associative arrays with string keys. EXTEND and TRIM cannot be used with index-by tables.

For more information, see "Using Collection Methods".

Syntax

Text description of the illustration collection_method_call.gif

Keyword and Parameter Description

collection_name

This identifies an index-by table, nested table, or varray previously declared within the current scope.

COUNT

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 index-by table or nested table. If n is null, DELETE(n) does nothing. DELETE(m,n) removes all elements in the range m..n from an index-by table 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. So, if EXTEND encounters deleted elements, it includes them in its tally. You cannot use EXTEND with index-by tables.

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

This is an expression that must yield (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. So, 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.

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.

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.

PL/SQL keeps placeholders for deleted elements. So, you can replace a deleted element simply 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. So, 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.

Examples

In the following example, you use NEXT to traverse a nested table from which some elements have been deleted:

i := courses.FIRST;  -- get subscript of first element
WHILE i IS NOT NULL LOOP
   -- do something with courses(i) 
   i := courses.NEXT(i);  -- get subscript of next element
END LOOP;

In the following example, PL/SQL executes the assignment statement only if element i exists:

IF courses.EXISTS(i) THEN
   courses(i) := new_course;
END IF;

The next example shows that you can use FIRST and LAST to specify the lower and upper bounds of a loop range provided each element in that range exists:

FOR i IN courses.FIRST..courses.LAST LOOP ...

In the following example, you delete elements 2 through 5 from a nested table:

courses.DELETE(2, 5);

In the final example, you use LIMIT to determine if you can add 20 more elements to varray projects:

IF (projects.COUNT + 20) < projects.LIMIT THEN 
   -- add 20 more elements

Related Topics

Collections, Functions, Procedures