Deleting Collection Elements (DELETE Method)
This procedure has various forms:
- DELETE removes all elements from a collection.
- DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
- DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
For example:
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
nicknames.DELETE('Chip'); -- deletes element denoted by this key
nicknames.DELETE('Buffy','Fluffy'); -- deletes elements with keys
-- in this alphabetic range
END;
Varrays are dense, so you cannot delete their individual elements.
If an element to be deleted does not exist, DELETE simply skips it; no exception is raised. PL/SQL keeps placeholders for deleted elements. So, you can replace a deleted element simply by assigning it a new value.
DELETE lets you maintain sparse nested tables. In the following example, you retrieve nested table prospects into a temporary table, prune it, 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 a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.