A collection method is a built-in function or procedure that operates on collections and is called using dot notation. You can use the methods EXISTS
, COUNT
, LIMIT
, FIRST
, LAST
, PRIOR
, NEXT
, EXTEND
, TRIM
, and DELETE
to manage collections whose size is unknown or varies.
EXISTS
, COUNT
, LIMIT
, FIRST
, LAST
, PRIOR
, and NEXT
are functions that check the properties of a collection or individual collection elements. EXTEND
, TRIM
, and DELETE
are procedures that modify a collection.
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".
collection call method ::=
Keyword and Parameter Description
collection_name
An associative array, nested table, or varray previously declared within the current scope.
R
eturns 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
.
This procedure has three forms. DELETE
removes all elements from a collection. DELETE(n)
removes the n
th 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(n)
returns TRUE
if the n
th 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
.
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 i
th 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
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.
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).
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
.
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.
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.
For examples, see the following: