Example of Deleting from a Collection

Suppose that the set_col column of a row in the table1 table is defined as a SET and one row contains the values {1,8,4,5,2}. The following SPL code fragment uses a FOREACH loop and a DELETE statement with a WHERE CURRENT OF clause to delete the element whose value is 4:

CREATE_PROCEDURE test6()

   DEFINE a SMALLINT;
   DEFINE b SET(SMALLINT NOT NULL);
   SELECT set_col INTO b FROM table1
      WHERE id = 6;
      -- Select the set in one row from the table
      -- into a collection variable
   FOREACH cursor1 FOR
      SELECT * INTO a FROM TABLE(b);
         -- Select each element one at a time from
         -- the collection derived table b into a
      IF a = 4 THEN
         DELETE FROM TABLE(b) 
            WHERE CURRENT OF cursor1;
            -- Delete the element if it has the value 4
         EXIT FOREACH;
      END IF;
   END FOREACH;

   UPDATE table1 SET set_col = b
      WHERE id = 6;
      -- Update the base table with the new collection

END PROCEDURE;