Deleting a Collection Element

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:

1. Declare a collection variable and an element variable.
2. Select the collection from the database into the collection variable.
3. Declare a cursor so that you can select elements one at a time from the collection variable.
4. Write a loop or branch that locates the element that you want to delete.
5. Delete the element from the collection using a DELETE WHERE CURRENT OF statement that uses the collection variable as a collection-derived table.

Figure 436 shows a routine that deletes one of the four points in vertexes, so that the polygon becomes a triangle instead of a rectangle.

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 Figure 436, 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.

The syntax for the DELETE statement is described in the IBM Informix: Guide to SQL Syntax.

Updating the Collection in the Database

After you change the contents of a collection variable in an SPL routine (by deleting, updating, or inserting an element), you must update the database with the new collection.

To update a collection in the database, add an UPDATE statement that sets the collection column in the table to the contents of the updated collection variable. For example, the UPDATE statement in Figure 437 shows how to update the polygons table to set the definition column to the new collection stored in the collection variable vertexes.

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

   UPDATE polygons SET definition = vertexes
      WHERE id = 207;

END PROCEDURE;

Now the shapes() routine is complete. After you run shapes(), the collection stored in the row whose ID column is 207 is updated so that it contains three values instead of four.

You can use the shapes() routine as a framework for writing other SPL routines that manipulate collections.

The elements of the collection now stored in the definition column of row 207 of the polygons table are listed as follows:

'(3,1)'
'(8,1)'
'(8,4)'

Deleting 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.

Figure 438 shows the statements that you might use in an 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 IBM Informix: Guide to SQL Syntax.