After you select an individual element from a collection
variable into an element variable, you can delete the element from
the collection. For example, after you select a point from the collection
variable vertexes with a collection query, you can remove the
point from the collection.
The steps involved in deleting a
collection element include:
Declare a collection variable and an element variable.
Select the collection from the database into the collection variable.
Declare a cursor so that you can select elements one at a time
from the collection variable.
Write a loop or branch that locates the element that you want
to delete.
Delete the element from the collection using a DELETE WHERE CURRENT
OF statement that uses the collection variable as a collection-derived
table.
The following figure shows a routine that deletes one
of the four points in vertexes, so that the polygon becomes
a triangle instead of a rectangle.Figure 1: Routine that deletes one of the four points.
CREATE PROCEDURE shapes()
DEFINE vertexes SET( point NOT NULL );
DEFINE pnt point;
SELECT definition INTO vertexes FROM polygons
WHERE id = 207;
FOREACH cursor1 FOR
SELECT * INTO pnt FROM TABLE(vertexes)
IF pnt = '(3,4)' THEN
-- calls the equals function that
-- compares two values of point type
DELETE FROM TABLE(vertexes)
WHERE CURRENT OF cursor1;
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH
. . .
END PROCEDURE;
In previous figure, the FOREACH statement declares a
cursor. The SELECT statement is a collection-derived query that selects
one element at a time from the collection variable vertexes into
the element variable pnt.
The IF THEN ELSE structure
tests the value currently in pnt to see if it is the point '(3,4)'.
Note that the expression pnt = '(3,4)' calls the
instance of the equal() function defined on the
point data type. If the current value in pnt is '(3,4)',
the DELETE statement deletes it, and the EXIT FOREACH statement exits
the cursor.
Tip: Deleting an element from a collection
stored in a collection variable does not delete it from the collection
stored in the database. After you delete the element from a collection
variable, you must update the collection stored in the database with
the new collection. For an example that shows how to update a collection
column, see Update the collection in the database.
The
syntax for the DELETE statement is described in the HCL OneDB™ Guide to SQL:
Syntax.