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);