Table of Contents Previous Next


3 Stored Procedure Language : 3.11 Collection Methods
3.11 Collection Methods
Collection methods are functions and procedures that provide useful information about a collection that can aid in the processing of data in the collection. The following sections discuss the collection methods supported by Advanced Server.
3.11.1 COUNT
COUNT is a method that returns the number of elements in a collection. The syntax for using COUNT is as follows:
collection.COUNT
collection is the name of a collection.
For a varray, COUNT always equals LAST.
The following example shows that an associative array can be sparsely populated (i.e., there are “gaps” in the sequence of assigned elements). COUNT includes only the elements that have been assigned a value.
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
END;
The following output shows that there are five populated elements included in COUNT.
COUNT: 5
3.11.2 DELETE
The DELETE method deletes entries from a collection. You can call the DELETE method in three different ways.
Use the first form of the DELETE method to remove all entries from a collection:
collection.DELETE
Use the second form of the DELETE method to remove the specified entry from a collection:
collection.DELETE(subscript)
Use the third form of the DELETE method to remove the entries that are within the range specified by first_subscript and last_subscript (including the entries for the first_subscript and the last_subscript) from a collection.
collection.DELETE(first_subscript, last_subscript)
If first_subscript and last_subscript refer to non-existent elements, elements that are in the range between the specified subscripts are deleted. If first_subscript is greater than last_subscript, or if you specify a value of NULL for one of the arguments, DELETE has no effect.
Note that when you delete an entry, the subscript remains in the collection; you can re-use the subscript with an alternate entry. If you specify a subscript that does not exist in the call to the DELETE method, DELETE does not raise an exception.
The following example demonstrates using the DELETE method to remove the element with subscript 0 from the collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
v_results VARCHAR2(50);
v_sub NUMBER;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.DELETE(0);
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
v_sub := sparse_arr.FIRST;
WHILE v_sub IS NOT NULL LOOP
IF sparse_arr(v_sub) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(v_sub) || ' ';
END IF;
v_sub := sparse_arr.NEXT(v_sub);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
 
COUNT: 5
COUNT: 4
Results: -100 -10 10 100
COUNT indicates that before the DELETE method, there were 5 elements in the collection; after the DELETE method was invoked, the collection contains 4 elements.
3.11.3 EXISTS
The EXISTS method verifies that a subscript exists within a collection. EXISTS returns TRUE if the subscript exists; if the subscript does not exist, EXISTS returns FALSE. The method takes a single argument; the subscript that you are testing for. The syntax is:
collection.EXISTS(subscript)
collection is the name of the collection.
subscript is the value that you are testing for. If you specify a value of NULL, EXISTS returns false.
The following example verifies that subscript number 10 exists within the associative array:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('The index exists: ' ||
CASE WHEN sparse_arr.exists(10) = TRUE THEN 'true' ELSE 'false' END);
END;
 
The index exists: true
Some collection methods raise an exception if you call them with a subscript that does not exist within the specified collection. Rather than raising an error, the EXISTS method returns a value of FALSE.
3.11.4 EXTEND
The EXTEND method increases the size of a collection. There are three variations of the EXTEND method. The first variation appends a single NULL element to a collection; the syntax for the first variation is:
collection.EXTEND
collection is the name of a collection.
The following example demonstrates using the EXTEND method to append a single, null element to a collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
v_results VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.EXTEND;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
FOR i IN sparse_arr.FIRST .. sparse_arr.LAST LOOP
IF sparse_arr(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
 
COUNT: 5
COUNT: 6
Results: -100 -10 0 10 100 NULL
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 6 elements.
The second variation of the EXTEND method appends a specified number of elements to the end of a collection.
collection.EXTEND(count)
collection is the name of a collection.
count is the number of null elements added to the end of the collection.
The following example demonstrates using the EXTEND method to append multiple null elements to a collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
v_results VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.EXTEND(3);
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
FOR i IN sparse_arr.FIRST .. sparse_arr.LAST LOOP
IF sparse_arr(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
 
COUNT: 5
COUNT: 8
Results: -100 -10 0 10 100 NULL NULL NULL
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 8 elements.
The third variation of the EXTEND method appends a specified number of copies of a particular element to the end of a collection.
collection.EXTEND(count, index_number)
collection is the name of a collection.
count is the number of elements added to the end of the collection.
index_number is the subscript of the element that is being copied to the collection.
The following example demonstrates using the EXTEND method to append multiple copies of the second element to the collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
v_results VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.EXTEND(3, 2);
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
FOR i IN sparse_arr.FIRST .. sparse_arr.LAST LOOP
IF sparse_arr(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
 
COUNT: 5
COUNT: 8
Results: -100 -10 0 10 100 -10 -10 -10
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 8 elements.
Note: The EXTEND method cannot be used on a null or empty collection.
3.11.5 FIRST
FIRST is a method that returns the subscript of the first element in a collection. The syntax for using FIRST is as follows:
collection.FIRST
collection is the name of a collection.
The following example displays the first element of the associative array.
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('FIRST element: ' || sparse_arr(sparse_arr.FIRST));
END;
 
FIRST element: -100
 
3.11.6 LAST
LAST is a method that returns the subscript of the last element in a collection. The syntax for using LAST is as follows:
collection.LAST
collection is the name of a collection.
The following example displays the last element of the associative array.
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('LAST element: ' || sparse_arr(sparse_arr.LAST));
END;
 
LAST element: 100
 
3.11.7 LIMIT
LIMIT is a method that returns the maximum number of elements permitted in a collection. LIMIT is applicable only to varrays. The syntax for using LIMIT is as follows:
collection.LIMIT
collection is the name of a collection.
For an initialized varray, LIMIT returns the maximum size limit determined by the varray type definition. If the varray is uninitialized (that is, it is a null varray), an exception is thrown.
For an associative array or an initialized nested table, LIMIT returns NULL. If the nested table is uninitialized (that is, it is a null nested table), an exception is thrown.
3.11.8 NEXT
NEXT is a method that returns the subscript that follows a specified subscript. The method takes a single argument; the subscript that you are testing for.
collection.NEXT(subscript)
collection is the name of the collection.
If the specified subscript is less than the first subscript in the collection, the function returns the first subscript. If the subscript does not have a successor, NEXT returns NULL. If you specify a NULL subscript, PRIOR does not return a value.
The following example demonstrates using NEXT to return the subscript that follows subscript 10 in the associative array, sparse_arr:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('NEXT element: ' || sparse_arr.next(10));
END;
 
NEXT element: 100
 
3.11.9 PRIOR
The PRIOR method returns the subscript that precedes a specified subscript in a collection. The method takes a single argument; the subscript that you are testing for. The syntax is:
collection.PRIOR(subscript)
collection is the name of the collection.
If the subscript specified does not have a predecessor, PRIOR returns NULL. If the specified subscript is greater than the last subscript in the collection, the method returns the last subscript. If you specify a NULL subscript, PRIOR does not return a value.
The following example returns the subscript that precedes subscript 100 in the associative array, sparse_arr:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('PRIOR element: ' || sparse_arr.prior(100));
END;
 
PRIOR element: 10
3.11.10 TRIM
The TRIM method removes an element or elements from the end of a collection. The syntax for the TRIM method is:
collection.TRIM[(count)]
collection is the name of a collection.
count is the number of elements removed from the end of the collection. Advanced Server will return an error if count is less than 0 or greater than the number of elements in the collection.
The following example demonstrates using the TRIM method to remove an element from the end of a collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.TRIM;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
END;
 
COUNT: 5
COUNT: 4
COUNT indicates that before the TRIM method, there were 5 elements in the collection; after the TRIM method was invoked, the collection contains 4 elements.
You can also specify the number of elements to remove from the end of the collection with the TRIM method:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
v_results VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.TRIM(2);
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
FOR i IN sparse_arr.FIRST .. sparse_arr.LAST LOOP
IF sparse_arr(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
 
COUNT: 5
COUNT: 3
Results: -100 -10 0
COUNT indicates that before the TRIM method, there were 5 elements in the collection; after the TRIM method was invoked, the collection contains 3 elements.
3 Stored Procedure Language : 3.11 Collection Methods

Table of Contents Previous Next