11.5.1 The count Method

Use COUNT to compute the number of elements defined in an associative array, nested table, or VARRAY. If elements have been DELETEd or TRIMmed from the collection, they are not included in COUNT.

The specification for COUNT is:

FUNCTION COUNT RETURN PLS_INTEGER;

Let's look at an example. Before I do anything with my collection, I verify that it contains some information:

DECLARE

volunteer_list volunteer_list_ar := volunteer_list_ar('Steven');

BEGIN

IF volunteer_list.COUNT > 0

THEN

assign_tasks (volunteer_list);

END IF;

END;

11.5.1.1 Boundary considerations

If COUNT is applied to an initialized collection with no elements, it returns zero. It also returns zero if it's applied to an empty associative array.

11.5.1.2 Exceptions possible

If COUNT is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception. Note that this exception is not possible for associative arrays, which do not require initialization.

11.5.2 The delete Method

Use DELETE to remove one, some, or all elements of an associative array, nested table, or VARRAY. DELETE without arguments removes all of the elements of a collection. DELETE(i) removes the ith element from the nested table or associative array. 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.

In physical terms, PL/SQL actually releases the memory only when your program deletes a sufficient number of elements to free an entire page of memory (unless you DELETE all the elements, which frees all the memory immediately). This de-allocation happens automatically and requires no accommodations or devices in your code.

When DELETE is applied toVARRAYs, you can issue DELETE only without arguments (i.e., remove all rows). In other words, you cannot delete individual rows of a VARRAY, possibly making it sparse. The only way to remove a row from a VARRAY is to TRIM from the end of the collection.

The overloaded specification for this method is as follows:

PROCEDURE DELETE;

PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)]);

PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)],

j [BINARY_INTEGER | VARCHAR2(size_limit)]);

The following procedure removes everything but the last element in the collection. It actually makes use of four collection methods: FIRST, to obtain the first defined row; LAST, to obtain the last defined row; PRIOR, to determine the next-to-last row; and DELETE to remove all but the last.

CREATE PROCEDURE keep_last (the_list IN OUT List_t)

AS

first_elt PLS_INTEGER := the_list.FIRST;

next_to_last_elt PLS_INTEGER := the_list.PRIOR(the_list.LAST);

BEGIN

the_list.DELETE(first_elt, next_to_last_elt);

END;

Here are some additional examples:

• Delete all the rows from the names table:

names.DELETE;

• Delete the 77th row from the globals table:

globals.DELETE (77);

• Delete all the rows in the temperature readings table between the 0th row and the -15,000th row, inclusively:

temp_readings.DELETE (-15000, 0);