DELETE v17

The DELETE method deletes entries from a collection. You can call the DELETE method in three different ways.

Removing all entries from a collection

Use this form of the DELETE method to remove all entries from a collection:

<collection>.DELETE

Removing a specified entry from a collection

Use this form of the DELETE method to remove the specified entry from a collection:

<collection>.DELETE(<subscript>)

Removing all entries in a range

Use this form of the DELETE method to remove the entries that fall in 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 elements that don't exist, 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.

When you delete an entry, the subscript remains in the collection. You can reuse the subscript with an alternate entry. If you specify a subscript that doesn't exist in the call to the DELETE method, DELETE doesn't raise an exception.

Example

This example uses 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 five elements in the collection. After the DELETE method is invoked, the collection contains four elements.