Informix Guide to SQL: Tutorial Creating and Using SPL Routines

A collection is a group of elements of the same data type, such as a SET, MULTISET, or LIST.

A table might contain a collection stored as the contents of a column or as a field of a row type within a column. A collection can be either simple or nested. A simple collection is a SET, MULTISET, or LIST of built-in, opaque, or distinct data types. A nested collection is a collection that contains other collections.

The following sections of the chapter rely on several different examples to show how you can manipulate collections in SPL programs.

The basics of handling collections in SPL programs are illustrated with the numbers table, as Figure 10-61 shows.

CREATE TABLE numbers
(
	id			INTEGER PRIMARY KEY,
	primes			SET( INTEGER NOT NULL ),
	evens			LIST( INTEGER NOT NULL ),
	twin_primes			LIST( SET( INTEGER NOT NULL ) NOT NULL )
);

The primes and evens columns hold simple collections. The twin_primes column holds a nested collection, a LIST of SETs. (Twin prime numbers are pairs of consecutive prime numbers whose difference is 2, such as 5 and 7, or 11 and 13. The twin_primes column is designed to allow you to enter such pairs.

Some examples in this chapter use the polygons table in Figure 10-62 to illustrate how to manipulate collections. The polygons table contains a collection to represent two-dimensional graphical data. For example, suppose that you define an opaque data type named point that has two double-precision values that represent the x and y coordinates of a two-dimensional point whose coordinates might be represented as '1.0, 3.0'. Using the point data type, you can create a table that contains a set of points that define a polygon, as Figure 10-62 shows.

CREATE OPAQUE TYPE point ( INTERNALLENGTH = 8);


CREATE TABLE polygons
(
	id			INTEGER PRIMARY KEY,
	definition			SET( point NOT NULL )
);

The definition column in the polygons table contains a simple collection, a SET of point values.

Before you can access and handle an individual element of a simple or nested collection, you must follow a basic set of steps:

• Declare a collection variable to hold the collection.
• Declare an element variable to hold an individual element of the collection.
• Select the collection from the database into the collection variable.

After you take these initial steps, you can insert elements into the collection or select and handle elements that are already in the collection.

Each of these steps is explained in the following sections, using the numbers table as an example.

Tip: You can handle collections in any SPL routine.

Declaring a Collection Variable

Before you can retrieve a collection from the database into an SPL routine, you must declare a collection variable. Figure 10-63 shows how to declare a collection variable to retrieve the primes column from the numbers table.

DEFINE p_coll SET( INTEGER NOT NULL );

The DEFINE statement declares a collection variable p_coll, whose type matches the data type of the collection stored in the primes column.

After you declare a collection variable, you declare an element variable to hold individual elements of the collection. The data type of the element variable must match the data type of the collection elements.

For example, to hold an element of the SET in the primes column, use an element variable declaration such as the one that Figure 10-64 shows.

DEFINE p INTEGER;

To declare a variable that holds an element of the twin_primes column, which holds a nested collection, use a variable declaration such as the one that Figure 10-65 shows.

DEFINE s SET( INTEGER NOT NULL );

The variable s holds a SET of integers. Each SET is an element of the LIST stored in twin_primes.

After you declare a collection variable, you can fetch a collection into it. To fetch a collection into a collection variable, enter a SELECT... INTO statement that selects the collection column from the database into the collection variable you have named.

For example, to select the collection stored in one row of the primes column of numbers, add a SELECT statement, such as the one that Figure 10-66 shows, to your SPL routine.

	SELECT primes INTO p_coll FROM numbers
		WHERE id = 220;

The WHERE clause in the SELECT statement specifies that you want to select the collection stored in just one row of numbers. The statement places the collection into the collection variable p_coll, which Figure 10-63 on page 10-50 declares.

The variable p_coll now holds a collection from the primes column, which could contain the value SET {5,7,31,19,13}.

After you retrieve a collection into a collection variable, you can insert a value into the collection variable. The syntax of the INSERT statement varies slightly, depending on the type of the collection to which you want to add values.

To insert into a SET or MULTISET stored in a collection variable, use an INSERT statement with the TABLE keyword followed by the collection variable, as Figure 10-67 shows.

INSERT INTO TABLE(p_coll) VALUES(3);

The TABLE keyword makes the collection variable a collection-derived table. Collection-derived tables are described in the section Handling Collections in SELECT Statements. The collection that Figure 10-67 derives is a virtual table of one column, with each element of the collection representing a row of the table. Before the insert, consider p_coll as a virtual table that contains the rows (elements) that Figure 10-68 shows.

5
7
31
19
13

After the insert, p_coll might look like the virtual table that Figure 10-69 shows.

5
7
31
19
13
3

Because the collection is a SET, the new value is added to the collection, but the position of the new element is undefined. The same principle is true for a MULTISET.

Tip: You can only insert one value at a time into a simple collection.

Inserting into a LIST

If the collection is a LIST, you can add the new element at a specific point in the LIST or at the end of the LIST. As with a SET or MULTISET, you must first define a collection variable and select a collection from the database into the collection variable.

Figure 10-70 shows the statements you need to define a collection variable and select a LIST from the numbers table into the collection variable.

	.
	.
	DEFINE e_coll LIST(INTEGER NOT NULL);


	SELECT evens INTO e_coll FROM numbers
		WHERE id = 99;
	.
	.

At this point, the value of e_coll might be LIST {2,4,6,8,10}. Because e_coll holds a LIST, each element has a numbered position in the list. To add an element at a specific point in a LIST, add an AT position clause to the INSERT statement, as Figure 10-71 shows.

INSERT AT 3 INTO TABLE(e_coll) VALUES(12);

Now the LIST in e_coll has the elements {2,4,12,6,8,10}, in that order.

The value you enter for the position in the AT clause can be a number or a variable, but it must have an INTEGER or SMALLINT data type. You cannot use a letter, floating-point number, decimal value, or expression.

At times you might want to add an element at the end of a LIST. In this case, you can use the CARDINALITY() function to find the number of elements in a LIST and then enter a position that is greater than the value CARDINALITY() returns.

Dynamic Server allows you to use the CARDINALITY() function with a collection that is stored in a column but not with a collection that is stored in a collection variable. In an SPL routine, you can check the cardinality of a collection in a column with a SELECT statement and return the value to a variable.

Suppose that in the numbers table, the evens column of the row whose id column is 99 still contains the collection LIST {2,4,6,8,10}. This time, you want to add the element 12 at the end of the LIST. You can do so with the SPL procedure end_of_list, as Figure 10-72 on page 10-54 shows.

CREATE PROCEDURE end_of_list()


	DEFINE n SMALLINT;
	DEFINE list_var LIST(INTEGER NOT NULL);


	SELECT CARDINALITY(evens) FROM numbers INTO n
		WHERE id = 100;


	LET n = n + 1;


	SELECT evens INTO list_var FROM numbers
		WHERE id = 100;


	INSERT AT n INTO TABLE(list_var) VALUES(12);


END PROCEDURE;

In end_of_list, the variable n holds the value that CARDINALITY() returns, that is, the count of the items in the LIST. The LET statement increments n, so that the INSERT statement can insert a value at the last position of the LIST. The SELECT statement selects the collection from one row of the table into the collection variable list_var. The INSERT statement inserts the element 12 at the end of the list.

The syntax of the VALUES clause is different when you insert into an SPL collection variable than when you insert into a collection column. The syntax rules for inserting literals into collection variables are as follows:

• Use parentheses after the VALUES keyword to enclose the complete list of values.
• If you are inserting into a simple collection, you do not need to use a type constructor or brackets.
• If you are inserting into a nested collection, you need to specify a literal collection.

Suppose you want your SPL routine to select elements from the collection stored in the collection variable, one at time, so that you can handle the elements.

To move through the elements of a collection, you first need to declare a cursor using a FOREACH statement, just as you would declare a cursor to move through a set of rows. Figure 10-73 shows the FOREACH and END FOREACH statements, but with no statements between them yet.

	FOREACH cursor1 FOR
	.
	.
	END FOREACH

The FOREACH statement is described in Using Cursors and the Informix Guide to SQL: Syntax.

The next section, The Collection Query, describes the statements that are omitted between the FOREACH and END FOREACH statements.

The examples in the following sections are based on the polygons table of Figure 10-62 on page 10-49.

After you declare the cursor between the FOREACH and END FOREACH statements, you enter a special, restricted form of the SELECT statement known as a collection query.

A collection query is a SELECT statement that uses the FROM TABLE keywords followed by the name of a collection variable. Figure 10-74 shows this structure, which is known as a collection-derived table.

.
.
	FOREACH cursor1 FOR


		SELECT * INTO pnt FROM TABLE(vertexes)
		.
		.


	END FOREACH

The SELECT statement in Figure 10-74 uses the collection variable vertexes as a collection-derived table. You can think of a collection-derived table as a table of one column, with each element of the collection being a row of the table. For example, you can visualize the SET of four points stored in vertexes as a table with four rows, such as the one that Figure 10-75 shows.

'(3.0,1.0)'

'(8.0,1.0)'

'(3.0,4.0)'

'(8.0,4.0)'

After the first iteration of the FOREACH statement in Figure 10-75, the collection query selects the first element in vertexes and stores it in pnt, so that pnt contains the value '(3.0,1.0)'.

Tip: Because the collection variable vertexes contains a SET, not a LIST, the elements in vertexes have no defined order. In a real database, the value '(3.0,1.0)' might not be the first element in the SET.

Adding the Collection Query to the SPL Routine

Now you can add the cursor defined with FOREACH and the collection query to the SPL routine, as Figure 10-76 shows.

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);
	.
	.
	END FOREACH
.
.
END PROCEDURE;

The statements that Figure 10-76 shows form the framework of an SPL routine that handles the elements of a collection variable. To decompose a collection into its elements, use a collection-derived table. After the collection is decomposed into its elements, the routine can access elements individually as table rows of the collection-derived table. Now that you have selected one element in pnt, you can update or delete that element, as Updating a Collection Element and Deleting a Collection Element describe.

For the complete syntax of the collection query, see the SELECT statement in the Informix Guide to SQL: Syntax. For the syntax of a collection-derived table, see the Collection-Derived Table segment in the Informix Guide to SQL: Syntax.

Tip: If you are selecting from a collection that contains no elements or zero elements, you can use a collection query without declaring a cursor. However, if the collection contains more than one element, and you do not use a cursor, you will receive an error message.

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 10-77 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 10-77, 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 Updating the Collection in the Database.

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

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 10-78 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)'

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

You can update a collection element by accessing the collection within a cursor just as you select or delete an individual element.

If you want to update the collection SET{100, 200, 300, 500} to change the value 500 to 400, retrieve the SET from the database into a collection variable and then declare a cursor to move through the elements in the SET, as Figure 10-80 shows.

.
.
	DEFINE s SET(INTEGER NOT NULL);
	DEFINE n INTEGER;


	SELECT numbers INTO s FROM orders
		WHERE order_num = 10;


	FOREACH cursor1 FOR
		SELECT * INTO n FROM TABLE(s)
		IF ( n == 500 ) THEN
			UPDATE TABLE(s)(x)
				SET x = 400 WHERE CURRENT OF cursor1;
			EXIT FOREACH;
		ELSE
			CONTINUE FOREACH;
		END IF;
	END FOREACH
.
.

The UPDATE statement uses the collection variable s as a collection-derived table. To specify a collection-derived table, use the TABLE keyword. The value (x) that follows (s) in the UPDATE statement is a derived column, a column name you supply because the SET clause requires it, even though the collection-derived table does not have columns.

You can think of the collection-derived table as having one row and looking something like the following example:

100		200		300		500

In this example, x is a fictitious column name for the "column" that contains the value 500. You only specify a derived column if you are updating a collection of built-in, opaque, distinct, or collection type elements. If you are updating a collection of row types, use a field name instead of a derived column, as Updating a Collection of Row Types describes.

You can also update a collection with the value stored in a variable instead of a literal value.

The SPL procedure in Figure 10-81 uses statements that are similar to the ones that Figure 10-80 shows, except that this procedure updates the SET in the direct_reports column of the manager table with a variable, rather than with a literal value. Figure 10-51 on page 10-39 defines the manager table.

CREATE PROCEDURE new_report(mgr VARCHAR(30),
	old VARCHAR(30), new VARCHAR(30) )


	DEFINE s SET (VARCHAR(30) NOT NULL);
	DEFINE n VARCHAR(30);


	SELECT direct_reports INTO s FROM manager
		WHERE mgr_name = mgr;


	FOREACH cursor1 FOR
		SELECT * INTO n FROM TABLE(s)
		IF ( n == old ) THEN
			UPDATE TABLE(s)(x)
				SET x = new WHERE CURRENT OF cursor1;
			EXIT FOREACH;
		ELSE
			CONTINUE FOREACH;
		END IF;
	END FOREACH


	UPDATE manager SET mgr_name = s
		WHERE mgr_name = mgr;


END PROCEDURE;

The UPDATE statement nested in the FOREACH loop uses the collection- derived table s and the derived column x. If the current value of n is the same as old, the UPDATE statement changes it to the value of new. The second UPDATE statement stores the new collection in the manager table.

If you want to update all the elements of a collection to the same value, or if the collection contains only one element, you do not need to use a cursor. The statements in Figure 10-82 show how you can retrieve the collection into a collection variable and then update it with one statement.

.
.
	DEFINE s SET (INTEGER NOT NULL);


	SELECT numbers INTO s FROM orders
		WHERE order_num = 10;


	UPDATE TABLE(s)(x) SET x = 0;


	UPDATE orders SET numbers = s
		WHERE order_num = 10;
.
.

The first UPDATE statement in this example uses a derived column named x with the collection-derived table s and gives all the elements in the collection the value 0. The second UPDATE statement stores the new collection in the database.

To update a collection of row types, you can use the name of the field you want to update in the UPDATE statement, instead of a derived column name.

The manager table in Figure 10-51 on page 10-39 has a column named projects that contains a LIST of row types with the definition that Figure 10-83 shows.

projects			LIST( ROW( pro_name VARCHAR(15),
			pro_members SET(VARCHAR(20) NOT NULL) ) NOT NULL)

To access the individual row types in the LIST, declare a cursor and select the LIST into a collection variable. After you retrieve an individual row type, you can update the pro_name or pro_members fields by supplying a field name and the new data, as Figure 10-84 shows.

CREATE PROCEDURE update_pro( mgr VARCHAR(30),
	pro VARCHAR(15) )


	DEFINE p LIST(ROW(a VARCHAR(15), b SET(VARCHAR(20) 

			 NOT NULL) ) NOT NULL);
	DEFINE r ROW(p_name VARCHAR(15), p_member SET(VARCHAR(20) 
NOT NULL) );
	LET r = ROW("project", "SET{'member'}");


SELECT projects INTO p FROM manager
		WHERE mgr_name = mgr;


	FOREACH cursor1 FOR
		SELECT * INTO r FROM TABLE(p)
		IF (r.p_name == 'Zephyr') THEN
			UPDATE TABLE(p) SET pro_name = pro
				WHERE CURRENT OF cursor1;
			EXIT FOREACH;
		END IF;
	END FOREACH


	UPDATE manager SET projects = p
		WHERE mgr_name = mgr;


END PROCEDURE;

Before you can use a row-type variable in an SPL program, you must initialize the row variable with a LET statement or a SELECT...INTO statement. The UPDATE statement nested in the FOREACH loop of Figure 10-84 sets the pro_name field of the row type to the value supplied in the variable pro.

Tip: To update a value in a SET in the pro_members field of the row type, declare a cursor and use an UPDATE statement with a derived column, as Updating a Collection Element explains.

Updating a Nested Collection

If you want to update a collection of collections, you must declare a cursor to access the outer collection and then declare a nested cursor to access the inner collection.

For example, suppose that the manager table has an additional column, scores, which contains a LIST whose element type is a MULTISET of integers, as Figure 10-85 shows.

scores			LIST(MULTISET(INT NOT NULL) NOT NULL)

To update a value in the MULTISET, declare a cursor that moves through each value in the LIST and a nested cursor that moves through each value in the MULTISET, as Figure 10-86 shows.

CREATE FUNCTION check_scores ( mgr VARCHAR(30) )
	SPECIFIC NAME nested;
	RETURNING INT;


	DEFINE l LIST( MULTISET( INT NOT NULL ) NOT NULL );
	DEFINE m MULTISET( INT NOT NULL );
	DEFINE n INT;
	DEFINE c INT;


	SELECT scores INTO l FROM manager
		WHERE mgr_name = mgr;


	FOREACH list_cursor FOR
		SELECT * FROM TABLE(l) INTO m;


		FOREACH set_cursor FOR
			SELECT * FROM TABLE(m) INTO n;
			IF (n == 0) THEN
				DELETE FROM TABLE(m)
					WHERE CURRENT OF set_cursor;
			ENDIF;
		END FOREACH;
		LET c = CARDINALITY(m);
		RETURN c WITH RESUME;
	END FOREACH


END FUNCTION
	WITH LISTING IN '/tmp/nested.out';

The SPL function in Figure 10-86 selects each MULTISET in the scores column into l, and then each value in the MULTISET into m. If a value in m is 0, the function deletes it from the MULTISET. After the values of 0 are deleted, the function counts the remaining elements in each MULTISET and returns an integer.

Tip: Because this function returns a value for each MULTISET in the LIST, you must use a cursor to enclose the EXECUTE FUNCTION statement when you execute the function.

Inserting into a Collection

You can insert a value into a collection without declaring a cursor. If the collection is a SET or MULTISET, the value is added to the collection but the position of the new element is undefined because the collection has no particular order. If the value is a LIST, you can add the new element at a specific point in the LIST or at the end of the LIST.

In the manager table, the direct_reports column contains collections of SET type, and the projects column contains a LIST. To add a name to the SET in the direct_reports column, use an INSERT statement with a collection-derived table, as Figure 10-87 shows.

CREATE PROCEDURE new_emp( emp VARCHAR(30), mgr VARCHAR(30) )


	DEFINE r SET(VARCHAR(30) NOT NULL);


	SELECT direct_reports INTO r FROM manager
		WHERE mgr_name = mgr;


	INSERT INTO TABLE (r) VALUES(emp);


	UPDATE manager SET direct_reports = r
		WHERE mgr_name = mgr;


END PROCEDURE;

This SPL procedure takes an employee name and a manager name as arguments. The procedure then selects the collection in the direct_reports column for the manager the user has entered, adds the employee name the user has entered, and updates the manager table with the new collection.

The INSERT statement in Figure 10-87 inserts the new employee name that the user supplies into the SET contained in the collection variable r. The UPDATE statement then stores the new collection in the manager table.

Notice the syntax of the VALUES clause. The syntax rules for inserting literal data and variables into collection variables are as follows:

• Use parentheses after the VALUES keyword to enclose the complete list of values.
• If the collection is SET, MULTISET, or LIST, use the type constructor followed by brackets to enclose the list of values to be inserted. In addition, the collection value must be enclosed in quotes.
VALUES( "SET{ 1,4,8,9 }" )
• If the collection contains a row type, use ROW followed by parentheses to enclose the list of values to be inserted:
VALUES( ROW( 'Waters', 'voyager_project' ) )
• If the collection is a nested collection, nest keywords, parentheses, and brackets according to how the data type is defined:
VALUES( "SET{ ROW('Waters', 'voyager_project'),
ROW('Adams', 'horizon_project') }")

For more information on inserting values into collections, see Chapter 6.

If you want to insert into a nested collection, the syntax of the VALUES clause changes. Suppose, for example, that you want to insert a value into the twin_primes column of the numbers table that Figure 10-61 on page 10-48 shows.

With the twin_primes column, you might want to insert a SET into the LIST or an element into the inner SET. The following sections describe each of these tasks.

Inserting a SET into the LIST is similar to inserting a single value into a simple collection.

To insert a SET into the LIST, declare a collection variable to hold the LIST and select the entire collection into it. When you use the collection variable as a collection-derived table, each SET in the LIST becomes a row in the table. You can then insert another SET at the end of the LIST or at a specified point.

For example, the twin_primes column of one row of numbers might contain the following LIST, as Figure 10-88 shows.

LIST( SET{3,5}, SET{5,7}, SET{11,13} )

If you think of the LIST as a collection-derived table, it might look similar to the one that Figure 10-89 shows.

{3,5}
{5,7}
{11,13}

You might want to insert the value "SET{17,19}" as a second item in the LIST. The statements in Figure 10-90 show how to do this.

CREATE PROCEDURE add_set()


	DEFINE l_var LIST( SET( INTEGER NOT NULL ) NOT NULL );


	SELECT twin_primes INTO l_var FROM numbers
		WHERE id = 100;


	INSERT AT 2 INTO TABLE (l_var) VALUES( "SET{17,19}" );


	UPDATE numbers SET twin_primes = l
		WHERE id = 100;


END PROCEDURE;

In the INSERT statement, the VALUES clause inserts the value SET {17,19} at the second position of the LIST. Now the LIST looks like the one that Figure 10-91 shows.

{3,5}
{17,19}
{5,7}
{11,13}

You can perform the same insert by passing a SET to an SPL routine as an argument, as Figure 10-92 shows.

CREATE PROCEDURE add_set( set_var SET(INTEGER NOT NULL), 
		row_id INTEGER );


	DEFINE list_var LIST( SET(INTEGER NOT NULL) NOT NULL );
	DEFINE n SMALLINT;


	SELECT CARDINALITY(twin_primes) INTO n FROM numbers
		WHERE id = row_id;


	LET n = n + 1;


	SELECT twin_primes INTO list_var FROM numbers
		WHERE id = row_id;


	INSERT AT n INTO TABLE( list_var ) VALUES( set_var );


	UPDATE numbers SET twin_primes = list_var
		WHERE id = row_id;


END PROCEDURE;

In add_set(), the user supplies a SET to add to the LIST and an INTEGER value that is the id of the row in which the SET will be inserted.

In an SPL routine, you can also insert a value into the inner collection of a nested collection. In general, to access the inner collection of a nested collection and add a value to it, perform the following steps:

1. Declare a collection variable to hold the entire collection stored in one row of a table.
2. Declare an element variable to hold one element of the outer collection. The element variable is itself a collection variable.
3. Select the entire collection from one row of a table into the collection variable.
4. Declare a cursor so that you can move through the elements of the outer collection.
5. Select one element at a time into the element variable.
6. Use a branch or loop to locate the inner collection you want to update.
7. Insert the new value into the inner collection.
8. Close the cursor.
9. Update the database table with the new collection.

As an example, you can use this process on the twin_primes column of numbers. For example, suppose that twin_primes contains the values that Figure 10-93 shows, and you want to insert the value 18 into the last SET in the LIST.

LIST( SET( {3,5}, {5,7}, {11,13}, {17,19} ) )

Figure 10-94 shows the beginning of a procedure that inserts the value.

CREATE PROCEDURE add_int()


	DEFINE list_var LIST( SET( INTEGER NOT NULL ) NOT NULL );
	DEFINE set_var SET( INTEGER NOT NULL );


		SELECT twin_primes INTO list_var FROM numbers
			WHERE id = 100;
.
.

So far, the add_int procedure has performed steps 1, 2, and 3. The first DEFINE statement declares a collection variable that holds the entire collection stored in one row of numbers.

The second DEFINE statement declares an element variable that holds an element of the collection. In this case, the element variable is itself a collection variable because it holds a SET. The SELECT statement selects the entire collection from one row into the collection variable, list_var.

Figure 10-95 shows how to declare a cursor so that you can move through the elements of the outer collection.

.
.
	FOREACH list_cursor FOR
		SELECT * INTO set_var FROM TABLE( list_var);


		FOREACH element_cursor FOR