DELETE method

You can use the DELETE method to delete elements from a collection.

Considerations for DELETE are as follows:

• The DELETE method can delete all elements of any types from a collection.

  The DELETE operation will immediately release the memory space allocated to the deleted elements.

• For associative arrays or nested tables (except variable-size arrays, or varrays):

  • DELETE(n) : deletes the element whose index is n. If the element does not exist, DELETE does not take effect.
  • DELETE(m,n) : deletes elements whose index is in the range of m to n if both m and n exist and m is not greater than n. Otherwise, DELETE does not take effect.

For DELETE in the preceding two forms, PL reserves the placeholder of a deleted element. Therefore, the element is still counted in the collection size. You can assign a valid value to the deleted element to restore it.

Here is an example:

obclient> CREATE OR REPLACE TYPE nested_type IS TABLE OF VARCHAR(20);/
Query OK, 0 rows affected

obclient> CREATE OR REPLACE PROCEDURE print_t (t nested_type) AUTHID DEFINER IS
  i  NUMBER;
BEGIN
  i := t.FIRST;

  IF i IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('t is empty');
  ELSE
    WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT('t.(' || i || ') = ');
      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(t(i)), 'NULL'));
      i := t.NEXT(i);
    END LOOP;
  END IF;

  DBMS_OUTPUT.PUT_LINE('---');
END print_t;
/
Query OK, 0 rows affected

obclient> DECLARE
  t nested_type:= nested_type('A', 'B', 'C', 'D', 'E', 'F');
BEGIN
  print_t(t);

  t.DELETE(3);     -- Delete the third element.
  print_t(t);

  t(3) := 'cc';    -- Restore the third element.
  print_t(t);

  t.DELETE(2, 4);  -- Delete a series of elements.
  print_t(t);

  t(3) := 'cccc';    -- Restore the third element.
  print_t(t);

  t.DELETE;        -- Delete all elements.
  print_t(t);
END;
/
Query OK, 0 rows affected

t.(1) = A
t.(2) = B
t.(3) = C
t.(4) = D
t.(5) = E
t.(6) = F
---
t.(1) = A
t.(2) = B
t.(4) = D
t.(5) = E
t.(6) = F
---
t.(1) = A
t.(2) = B
t.(3) = cc
t.(4) = D
t.(5) = E
t.(6) = F
---
t.(1) = A
t.(5) = E
t.(6) = F
---
t.(1) = A
t.(3) = cccc
t.(5) = E
t.(6) = F
---
t is empty
---

The preceding example declares a nested table variable, initializes and assigns values to six elements, and performs the following operations on the elements:

1. Delete the third element and then restore it.

2. Delete a series of elements and then restore one of them.

3. Delete all elements.

The restored elements occupy the same memory space as before they are deleted. The print_nt stored procedure outputs the nested table variable after initialization and after each DELETE operation.