Delete the entire collection

If you want to delete all the elements of a collection, you can use a single SQL statement. You do not need to declare a cursor. To delete an entire collection, you must perform the following tasks:

Define a collection variable.
Select the collection from the database into a collection variable.
Enter a DELETE statement that uses the collection variable as a collection-derived table.
Update the collection from the database.

The following figure shows the statements that you might use in an SPL routine to delete an entire collection.

Figure 1: SPL routine to delete an entire collection.

DEFINE vertexes SET( INTEGER NOT NULL );

SELECT definition INTO vertexes FROM polygons
   WHERE id = 207;

DELETE FROM TABLE(vertexes);

UPDATE polygons SET definition = vertexes
   WHERE id = 207;

This form of the DELETE statement deletes the entire collection in the collection variable vertexes. You cannot use a WHERE clause in a DELETE statement that uses a collection-derived table.

After the UPDATE statement, the polygons table contains an empty collection where the id column is equal to 207.

The syntax for the DELETE statement is described in the HCL OneDB™ Guide to SQL: Syntax.