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.

PL retains placeholders for the elements deleted by these two DELETE operations. Therefore, the deleted 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.