Write a delete Command for a nested table or Varray
Write delete Command for nested table or Varray
Deleting data from a nested table requires the use of a DELETE statement,
whereas deleting data from a varray requires the value of the varray to be set to NULL via an UPDATE statement.
To drop a particular nested table, you can use the DELETE statement with the THE or the TABLE keyword, as the following example demonstrates:
DELETE TABLE (SELECT d.detail_nest FROM SALE_HEADER d
WHERE d.SALE_ID = 35) p
WHERE p.item_qty = 1;
This DML statement selects a particular record from the nested table for a particular record within the main table and deletes the record.
To delete the elements of a varray, the varray must be set to NULL, as the following example shows:
SET phone_array = NULL
WHERE cust_id = 25;
The DML statement above deletes all the elements of a varray.
The following MouseOver shows the syntax of deleting data from a nested table: