You can use the DELETE method to delete elements from a collection.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Usage notes for DELETE:
The
DELETEmethod can delete all elements of any types from a collection.The
DELETEoperation will immediately release the memory space allocated to the deleted elements.For associative arrays or nested tables (except variable-size arrays, or varrays):
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:
Delete the third element and then restore it.
Delete a series of elements and then restore one of them.
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.