A collection method is a built-in PL/SQL subprogram that returns information about a collection or operates on a collection.
Keyword and Parameter Descriptions
The name of a collection declared within the current scope.
A function that returns the current number of elements in collection_name
.
A procedure whose action depends on the number of indexes specified.
DELETE
with no indexes specified deletes all elements from collection_name
.
DELETE(
n
)
deletes the n
th element from an associative array or nested table. If the n
th element is null, DELETE(
n
)
does nothing.
DELETE(
m,n
)
deletes 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.
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.
A function that returns TRUE
if the index
th element of collection_name
exists; otherwise, it returns FALSE
.
Typically, you use EXISTS
to avoid raising an exception when you reference a nonexistent element, and with DELETE
to maintain sparse nested tables.
You cannot use EXISTS
if collection_name
is an associative array.
A procedure whose action depends on the number of indexes specified.
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. If EXTEND
encounters deleted elements, it includes them in its tally.
You cannot use EXTEND
if collection_name
is an associative array.
A function that returns the first (smallest) subscript or key value in a collection. If the collection is empty, FIRST
returns NULL
. If the collection contains only one element, FIRST
and LAST
return the same subscript value. If the collection is a varray, FIRST
always returns 1.
For a collection indexed by integers, FIRST
and LAST
return the first and last (smallest and largest) index numbers.
For an associative array indexed by strings, FIRST
and LAST
return the lowest and highest key values. If the NLS_COMP
initialization parameter is set to ANSI
, the order is based on the sort order specified by the NLS_SORT
initialization parameter.
A numeric expression whose value has data type PLS_INTEGER
or a data type implicitly convertible to PLS_INTEGER
(see Table 3-10, "Possible Implicit PL/SQL Data Type Conversions").
A function that returns the last (largest) subscript value in a collection. If the collection is empty, LAST
returns NULL
. If the collection contains only one element, FIRST
and LAST
return the same subscript value. For varrays, 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
.
A function that returns the maximum number of elements that collection_name
can have. If collection_name
has no maximum size, LIMIT
returns NULL
.
A function that returns the subscript that succeeds index n. If n has no successor, NEXT(
n
)
returns NULL
.
A function that returns the subscript that precedes index n in a collection. If n has no predecessor, PRIOR(
n
)
returns NULL
.
A procedure.
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
. TRIM
operates on the internal size of a collection. If TRIM
encounters deleted elements, it includes them in its tally.
You cannot use TRIM
if is collection_name
is an associative array.
A collection method call can appear wherever a PL/SQL subprogram invocation can appear in a PL/SQL statement (but not in a SQL statement).
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 were 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.
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.
Example 5-28, "Checking Whether a Collection Element EXISTS"
Example 5-30, "Checking the Maximum Size of a Collection with LIMIT"
Example 5-32, "Using PRIOR and NEXT to Access Collection Elements"
Example 5-34, "Using EXTEND to Increase the Size of a Collection"
Example 5-35, "Using TRIM to Decrease the Size of a Collection"