Using Collection Methods

Using Collection Methods
Collection methods make collections easier to use, and make your applications easier
to maintain. These methods include COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST,
LIMIT, NEXT, PRIOR, and TRIM.
A collection method is a built-in function or procedure that operates on collections and
is called using dot notation. The following apply to collection methods:
■ Collection methods cannot be called from SQL statements.
■ EXTEND and TRIM cannot be used with associative arrays.
■ EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND,
TRIM, and DELETE are procedures.
■ EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters
corresponding to collection subscripts, which are usually integers but can also be
strings for associative arrays.
■ Only EXISTS can be applied to atomically null collections. If you apply another
method to such collections, PL/SQL raises COLLECTION_IS_NULL.

 

Checking If a Collection Element Exists (EXISTS Method)
EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise,
EXISTS(n) returns FALSE. By combining EXISTS with DELETE, you can work with

sparse nested tables. You can also use EXISTS to avoid referencing a nonexistent
element, which raises an exception. When passed an out-of-range subscript, EXISTS
returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT.

 

Example 5–28 Checking Whether a Collection Element EXISTS
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);
BEGIN
n.DELETE(2); -- Delete the second element
IF n.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.');
END IF;
IF n.EXISTS(2) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('OK, element #2 has been deleted.');
END IF;
IF n.EXISTS(99) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');
END IF;
END;
/