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 Informix® Guide to SQL:
Syntax.