Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 19.5 Collection Pseudo-FunctionsChapter 19
Nested Tables and VARRAYs
Next: 19.7 Example: PL/SQL-to-Server Integration
 

19.6 Collection Built-Ins

As we've seen already, there are a number of built-in functions and procedures that apply to collection variables. These functions are collectively known as "collection methods" in honor of their object-like invocation syntax. That is, you invoke them using a "variable-dot-method " style. For functions, use this syntax:

result := collection_variable.function_method (method_argument);

where "result" must be of a datatype that is type-compatible with the method. For procedures, the syntax is:

collection_variable.procedure_method (method_arguments);

The following methods are not available from within SQL; they can only be used within PL/SQL programs. The first seven are functions (and are the same methods available for PL/SQL or index-by tables in PL/SQL Release 2.3), and the last three are procedures. For quick reference purposes, this section documents each method using a standard format, which includes an example.

COUNT function

Returns the current number of elements in a collection.

DELETE procedure

Removes one or more elements from the "middle" of a nested table. Reduces COUNT if the element is not already DELETEd. Does not apply to VARRAYs.

EXISTS function

Returns TRUE or FALSE to indicate whether the specified element exists.

EXTEND procedure

Increases the number of elements in a collection. Increases COUNT.

FIRST, LAST functions

Return the smallest (FIRST) and largest (LAST) subscripts in use.

LIMIT function

Returns the maximum number of allowed elements in a VARRAY.

PRIOR, NEXT functions

Return the subscript immediately before (PRIOR) or after (NEXT) a specified subscript. Useful for nested tables that might be sparse.

TRIM procedure

Removes collection elements at the "end" of the collection. Reduces COUNT if elements are not DELETEd.

19.6.1 COUNT

Specification
FUNCTION COUNT RETURN BINARY_INTEGER;
Example
FOR element IN 1..my_list.COUNT
LOOP
   DBMS_OUTPUT.PUT_LINE (my_list(element));
END LOOP;
/* Note: If my_list is a nested table with any deleted elements
|| in the middle, the my_list(element) reference above will
|| generate a NO_DATA_FOUND exception.
*/
Returns

Current number of elements in a collection. If elements have been DELETEd or TRIMmed from the collection, they are not included in COUNT.

Applies to

Nested tables, index-by tables, VARRAYs.

Boundary considerations

If applied to an initialized collection with no elements, returns zero. Also returns zero if applied to empty index-by table.

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception. (This exception is not possible for index-by tables, which do not require initialization.)

19.6.2 DELETE [ ( i [ , j ] ) ]

Specification (overloaded)
PROCEDURE DELETE;
PROCEDURE DELETE (i BINARY_INTEGER);
PROCEDURE DELETE (i BINARY_INTEGER, j BINARY_INTEGER);
Example
CREATE PROCEDURE keep_last (the_list IN OUT List_t)
AS
   first_elt BINARY_INTEGER := the_list.FIRST;
   next_to_last_elt BINARY_INTEGER := the_list.PRIOR(the_list.LAST);
BEGIN
   the_list.DELETE(first_elt, next_to_last_elt);
END;
Action

DELETE without arguments removes all the elements of a collection. DELETE(i) removes the ith element from the nested table or index-by table. DELETE(i,j) removes all elements in an inclusive range beginning with i and ending with j. When you use parameters, DELETE actually keeps a placeholder for the "removed" element, and you can later reassign a value to that element.

WARNING: Confusing behavior results if you TRIM and DELETE the same collection.

Applies to

Nested tables, index-by tables. Also, DELETE without arguments can be applied to VARRAYs.

Boundary considerations

If i and/or j refer to nonexistent elements, DELETE will attempt to "do the right thing" and will not raise an exception. For example, if you have three elements in a TABLE item and DELETE(-5,1), the first element will be deleted. However, DELETE(-5) will do nothing.

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.

19.6.3 EXISTS(i)

Specification
FUNCTION EXISTS (i IN BINARY_INTEGER) RETURN BOOLEAN;
Example
DECLARE
   my_list Color_tab_t := Color_tab_t();
   element INTEGER := 1;
BEGIN
   ...
   IF my_list.EXISTS(element)
   THEN
     my_list(element) := NULL;
   END IF;
END;
Returns

Boolean TRUE if i th element exists, FALSE otherwise. Never returns NULL. If you have used TRIM or DELETE to remove an element i that existed previously, EXISTS(i) returns false.

Applies to

Nested tables, index-by tables, VARRAYs.

Boundary considerations

If applied to an uninitialized (atomically null) nested table or VARRAY, or to an initialized collection with no elements, simply returns FALSE. You can use EXISTS beyond the COUNT without raising an exception.

Exceptions possible

If i is not an integer and cannot be converted to an integer, EXISTS will raise VALUE_ERROR. This exception is possible for any collection method which accepts an argument.

19.6.4 EXTEND [ (n [,i] ) ]

Specification (overloaded)
PROCEDURE EXTEND (n BINARY_INTEGER:=1);
PROCEDURE EXTEND (n BINARY_INTEGER, i BINARY_INTEGER);
Example
CREATE PROCEDURE push (the_list IN OUT List_t, new_value IN VARCHAR2)
AS
BEGIN
   the_list.EXTEND;
   the_list(the_list.LAST) := new_value;
END;
Action

Appends element(s) to a collection. EXTEND with no arguments appends a single null element. EXTEND(n) appends n null elements. EXTEND(n,i) appends n elements and sets each to the same value as the ith element; this form of EXTEND is required for collections with NOT NULL elements.

Applies to

Nested tables, VARRAYs. Applying it to an index-by table will cause a compile-time error.

Boundary considerations

If you have deleted or trimmed from the end of a collection, EXTEND will "jump over" (skip) the deleted elements when it assigns a new index. If n is null, EXTEND will do nothing.

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception. An attempt to EXTEND a VARRAY beyond its declared limit raises SUBSCRIPT_BEYOND_LIMIT. If the ith element does not exist, EXTEND will raise SUBSCRIPT_BEYOND_COUNT or, in the case of a VARRAY with a limit less than i, EXTEND will raise SUBSCRIPT_BEYOND_LIMIT.

19.6.5 FIRST, LAST

Specification
FUNCTION FIRST RETURN BINARY_INTEGER;


FUNCTION LAST RETURN BINARY_INTEGER;
Example
IF my_list.EXISTS(my_list.FIRST)
THEN
   my_list(my_list.FIRST) := 42;
ELSE
   my_list.EXTEND;
   my_list(my_list.FIRST) := 42;
END IF;
Returns

FIRST returns the lowest index in use in the collection; LAST returns the highest.

Applies to

Nested tables, index-by tables, VARRAYs.

Boundary considerations

FIRST and LAST return NULL when applied to initialized collections which have no elements. For VARRAYs which have at least one element, FIRST is always 1, and LAST is always equal to COUNT.

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.

19.6.6 LIMIT

Specification
FUNCTION LIMIT RETURN BINARY_INTEGER;
Example
IF my_list.LAST < my_list.LIMIT
THEN
   my_list.EXTEND;
END IF;
Returns

The maximum number of elements that is possible for a given VARRAY.

Applies to

VARRAYs only. Returns NULL if applied to nested tables or index-by tables.

Boundary considerations

None

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.

19.6.7 PRIOR(i), NEXT(i)

Specification
FUNCTION PRIOR (i BINARY_INTEGER) RETURN BINARY_INTEGER;
FUNCTION NEXT (i BINARY_INTEGER) RETURN BINARY_INTEGER;
Example

This function returns the sum of elements in a List_t collection of numbers:

CREATE FUNCTION compute_sum (the_list IN List_t) RETURN NUMBER
AS
   elt BINARY_INTEGER := the_list.FIRST;
   total NUMBER := 0;
BEGIN
   LOOP
      EXIT WHEN elt IS NULL;
      total := total + the_list(elt);
      elt := the_list.NEXT(elt);
   END LOOP;
   RETURN total;
END;
Returns

PRIOR returns the next lower index in use relative to i; NEXT returns the next higher.

Applies to

Nested tables, index-by tables, VARRAYs.

Boundary considerations

If applied to initialized collections which have no elements, returns NULL. If i is greater than or equal to COUNT, NEXT returns NULL; if i is less than or equal to FIRST, PRIOR returns NULL. (Currently, if the collection has elements, and i is greater than COUNT, PRIOR returns LAST; if i is less than FIRST, NEXT returns FIRST; however, do not rely on this behavior in future Oracle versions.)

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.

19.6.8 TRIM [ (n ) ]

Specification
PROCEDURE TRIM (n BINARY_INTEGER:=1);
Example
CREATE FUNCTION pop (the_list IN OUT List_t) RETURN VARCHAR2
AS
   l_value VARCHAR2(30);
BEGIN
   IF the_list.COUNT >= 1
   THEN
      /* Save the value of the last element in the collection
      || so it can be returned
      */
      l_value := the_list(the_list.LAST);
      the_list.TRIM;
   END IF;
   RETURN l_value;
END;
Action

Removes n elements from the end of a collection. Without arguments, TRIM removes exactly one element. Confusing behavior occurs if you combine DELETE and TRIM actions on a collection; for example, if an element that you are trimming has previously been DELETEd, TRIM "repeats" the deletion but counts this as part of n, meaning that you may be TRIMming fewer actual elements than you think.

Applies to

Nested tables, VARRAYs. Attempting to TRIM an index-by table will produce a compile-time error.

Boundary considerations

If n is null, TRIM will do nothing.

Exceptions possible

Will raise SUBSCRIPT_BEYOND_COUNT if you attempt to TRIM more elements than actually exist. If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.


Previous: 19.5 Collection Pseudo-FunctionsOracle PL/SQL Programming, 2nd EditionNext: 19.7 Example: PL/SQL-to-Server Integration
19.5 Collection Pseudo-FunctionsBook Index19.7 Example: PL/SQL-to-Server Integration

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference