Using DELETE
This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then DELETE(n) does nothing. DELETE(m,n) removes all the elements in the range m to n from an index-by table or a nested table. If the m is bigger than n or if m or n is null, then DELETE(m,n) does nothing. Some of the examples are shown below:
BEGIN
...
courses.DELETE(2); -- deletes element 2
courses.DELETE(7,7); -- deletes element 7
courses.DELETE(6,3); -- does nothing
courses.DELETE(3,6); -- deletes elements 3 through 6
projects.DELETE; -- deletes all elements
END;
Varrays are dense; therefore you cannot delete their individual elements. When an element to be deleted does not exist, then DELETE simply skips it; no exception is raised. The PL/SQL keeps placeholders for the deleted elements. Therefore, you can replace a deleted element simply by assigning it a new value.
The DELETE allows you to sustain a sparse nested table. In the example below, you retrieve nested table prospects into a temporary table, prune it, and then store it back in the database:
DECLARE
my_prospects ProspectList;
revenue NUMBER;
BEGIN
SELECT prospects INTO my_prospects FROM customers WHERE ...
FOR i IN my_prospects.FIRST..my_prospects.LAST LOOP
estimate_revenue(my_prospects(i), revenue); -- call procedure
IF revenue < 25000 THEN
my_prospects.DELETE(i);
END IF;
END LOOP;
UPDATE customers SET prospects = my_prospects WHERE...
The amount of memory allocated to the nested table can increase or decrease dynamically. As you delete the elements, then the memory is freed page by page. If you delete the whole table, then all the memory is freed.