Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 14: Creating and Using SPL Routines

Home Contents Index Master Index New Book

Handling Collections

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

A table may 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 types. A nested collection is a collection that contains other collections.

Collection Examples

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 14-59 shows.

Figure 14-59
The numbers Table

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 of Figure 14-60 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 14-60 shows.

Figure 14-60
The polygons Table

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.

The First Steps

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.

Once you have taken 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 either an SPL procedure or an SPL function.

Declaring a Collection Variable

Before you can retrieve a collection from the database into an SPL routine, you must declare a collection variable. You can declare either a typed or untyped collection variable.

If you want to retrieve the primes column from numbers, you can use either of the variable declarations that Figure 14-61 shows:

Figure 14-61

DEFINE p_coll COLLECTION;


DEFINE p_coll SET( INTEGER NOT NULL );

The first DEFINE statement declares an untyped collection variable. The second DEFINE statement declares a typed collection variable, whose type matches the type of the collection stored in the primes column.

Declaring an Element Variable

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 14-62 shows.

Figure 14-62

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 14-63 shows.

Figure 14-63

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.

Selecting a Collection into a Collection Variable

Once 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 14-64 shows, to your SPL routine.

Figure 14-64

	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 14-61 declares.

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

Inserting Elements into a Collection Variable

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

Inserting into a SET or MULTISET

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 14-65 shows.

Figure 14-65

INSERT INTO TABLE(p_coll) VALUES(3);

The TABLE keyword makes the collection variable a collection-derived table, that is, a collection used as a table in an SQL statement. Think of a collection- derived table as a table of one column, with each element of the collection being a row of the table. Before the insert, visualize p_coll as a "table," such as the one that Figure 14-66 shows.

Figure 14-66

After the insert, p_coll might look like the "table" that Figure 14-67 shows.

Figure 14-67

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 14-68 shows the statements you need to define a collection variable and select a LIST from the numbers table into the collection variable.

Figure 14-68

	.
	.
	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 14-69 shows.

Figure 14-69

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.

Tip: Remember that you can only insert one value at a time into a simple collection.

Checking the Cardinality of a LIST Collection

At times you may 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.

In this release of Universal Server, you can 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 14-70 shows.

Figure 14-70

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

Syntax of the VALUES Clause

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.

Selecting Elements from a 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 14-71 shows the FOREACH and END FOREACH statements, but with no statements between them yet.

Figure 14-71

	FOREACH cursor1 FOR
	.
	.
	END FOREACH

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

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

The examples in the following sections are based on the polygons table of Figure 14-60.

The Collection Query

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 14-72 shows this structure, which is known as a collection-derived table.

Figure 14-72

.
.
	FOREACH cursor1 FOR


		SELECT * INTO pnt FROM TABLE(vertexes)
		.
		.


	END FOREACH

The SELECT statement in Figure 14-72 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 14-73 shows.

Figure 14-73

'(3.0,1.0)'

'(8.0,1.0)'

'(3.0,4.0)'

'(8.0,4.0)')

After the first iteration of the FOREACH statement of Figure 14-73, 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 14-74 shows.

Figure 14-74

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 14-74 shows form the framework of an SPL routine that handles the elements of a collection variable. Now that you have selected one element into pnt, you can update or delete that element, as described in "Updating a Collection Element" and "Deleting a Collection Element".

For the complete syntax of the collection query, see the SELECT statement 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

Once you select an individual element from a collection variable into an element variable, you can delete the element from the collection. For example, once 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 14-75 shows a routine that deletes one of the four points in vertexes, so that the polygon becomes a triangle instead of a rectangle.

Figure 14-75

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 14-75, 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.

Updating the Collection in the Database

Once 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 14-76 shows how to update the polygons table to set the definition column to the new collection stored in the collection variable vertexes.

Figure 14-76



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 below:

'(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 14-77 shows the statements that you might use in an SPL routine to delete an entire collection.

Figure 14-77

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

Updating a Collection Element

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 14-78 shows.

Figure 14-78

.
.
	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. 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 this:

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 described in "Updating a Collection of Row Types".

Updating a Collection with a Variable

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

The SPL procedure in Figure 14-79 uses statements that are similar to the ones that Figure 14-78 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 14-49 defines the manager table.

Figure 14-79

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.

Updating the Entire Collection

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 14-80 show how you can retrieve the collection into a collection variable and then update it with one statement.

Figure 14-80

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

Updating a Collection of Row Types

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 of Figure 14-49 has a column named projects that contains a LIST of row types with the definition that Figure 14-81 shows.

Figure 14-81

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. Once 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 14-82 shows.

Figure 14-82

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


	DEFINE p COLLECTION;
	DEFINE r ROW;
	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.pro_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 14-82 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 cursor and use an UPDATE statement with a derived column, as explained in "Updating a Collection Element".

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 14-83 shows.

Figure 14-83

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 14-84 shows.

Figure 14-84

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 14-84 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. Once 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 14-85 shows.

Figure 14-85

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 14-85 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 it 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 12, "Accessing Complex Data Types" in this manual and the Literal Collection segment in the Informix Guide to SQL: Syntax.

Inserting into a Nested Collection

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 14-59 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 Collection into the Outer Collection

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 14-85 shows.

Figure 14-86

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 14-87 shows.

Figure 14-87

{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 14-88 show how to do this.

Figure 14-88

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 14-89 shows.

Figure 14-89

{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 14-90 shows.

Figure 14-90

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.

Inserting a Value into the Inner Collection

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, use 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 14-91 shows, and you want to insert the value 18 into the last SET in the LIST.

Figure 14-91

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

Figure 14-92 shows the beginning of a procedure that inserts the value.

Figure 14-92

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 14-93 shows how to declare a cursor so that you can move through the elements of the outer collection.

Figure 14-93

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


		FOREACH element_cursor FOR

Informix Guide to SQL: TutorialChapter 14: Creating and Using SPL Routines Home Contents Index Master Index New Book

Handling Collections

Selecting Elements from a Collection

Deleting the Entire Collection

Informix Guide to SQL: Tutorial
Chapter 14: Creating and Using SPL Routines

Home Contents Index Master Index New Book