Informix Guide to SQL: Syntax SPL Statements

Use a FOREACH loop to select and manipulate more than one row.

A FOREACH loop is the procedural equivalent of using a cursor. When a FOREACH statement executes, the database server takes the following actions:

1. It declares and implicitly opens a cursor.
2. It obtains the first row from the query that is contained within the FOREACH loop, or it obtains the first set of values from the called routine.
3. It assigns to each variable in the variable list the value of the corresponding value from the active set that the SELECT statement or the called routine creates.
4. It executes the statement block.
5. It fetches the next row from the SELECT statement or called routine on each iteration, and it repeats steps 3 and 4.
6. It terminates the loop when it finds no more rows that satisfy the SELECT statement or called routine. It closes the implicit cursor when the loop terminates.

Because the statement block can contain additional FOREACH statements, cursors can be nested. No limit exists to the number of cursors that can be nested.

An SPL routine that returns more than one row, collection element, or set of values is called a cursor function. An SPL routine that returns only one row or value is a noncursor function.

The following SPL procedure illustrates FOREACH statements with a SELECT...INTO clause, with an explicitly named cursor, and with a procedure call:

CREATE PROCEDURE foreach_ex()

	DEFINE i, j INT;



	FOREACH SELECT c1 INTO i FROM tab ORDER BY 1

		INSERT INTO tab2 VALUES (i);

	END FOREACH



	FOREACH cur1 FOR SELECT c2, c3 INTO i, j FROM tab

		IF j > 100 THEN

			DELETE FROM tab WHERE CURRENT OF cur1;

			CONTINUE FOREACH;

		END IF

		UPDATE tab SET c2 = c2 + 10 WHERE CURRENT OF cur1;

	END FOREACH



	FOREACH EXECUTE PROCEDURE bar(10,20) INTO i

		INSERT INTO tab2 VALUES (i);

	END FOREACH

END PROCEDURE; -- foreach_ex

A select cursor is closed when any of the following situations occur:

• The cursor returns no further rows.
• The cursor is a select cursor without a HOLD specification, and a transaction completes using COMMIT or ROLLBACK statements.
• An EXIT statement executes, which transfers control out of the FOREACH statement.
• An exception occurs that is not trapped inside the body of the FOREACH statement. (See ON EXCEPTION.)
• A cursor in the calling routine that is executing this cursor routine (within a FOREACH loop) closes for any reason.

As indicated in the diagram for FOREACH, not all clauses and options of the SELECT statement are available for you to use in a FOREACH statement.

The SELECT statement in the FOREACH statement must include the INTO clause. It can also include UNION and ORDER BY clauses, but it cannot use the INTO TEMP clause. For a complete description of SELECT syntax and usage, see SELECT.

The type and count of each variable in the variable list must match each value that the SELECT...INTO statement returns.

The WITH HOLD keyword specifies that the cursor should remain open when a transaction closes (is committed or rolled back).

Use the WHERE CURRENT OF cursor clause to update or delete the current row of cursor.

The FOREACH statement allows you to declare a cursor for an SPL collection variable. Such a cursor is called a collection cursor. You use a collection variable to access the elements of a collection (SET, MULTISET, LIST) column. Use a cursor when you want to access one or more elements in a collection variable.

When you use a collection cursor to fetch individual elements from a collection variable the FOREACH statement has the following restrictions:

• It cannot contain the WITH HOLD keywords.
• It must contain a restricted select statement within the FOREACH loop.

In addition, the SELECT statement that you associate with the collection cursor has the following restrictions:

• Its general structure is SELECT INTO FROM TABLE. The statement selects one element at a time from a collection variable named after the TABLE keyword into another variable called an element variable.
• It cannot contain an expression in the select list.
• It cannot include the following clauses or options: WHERE, GROUP BY, ORDER BY, HAVING, INTO TEMP, and WITH REOPTIMIZATION.
• The data type of the element variable must be the same as the element type of the collection.
• The data type of the element variable can be any opaque, distinct, or collection data type, or any built-in data type except SERIAL, SERIAL8, TEXT, BYTE, CLOB or BLOB.
• If the collection contains opaque, distinct, built-in, or collection types, the select list must be an asterisk (*).
• If the collection contains row types, the select list can be a list of one or more field names.

The following excerpt from an SPL routine shows how to fill a collection variable and then how to use a cursor to access individual elements:

	DEFINE a SMALLINT;
	DEFINE b SET(SMALLINT NOT NULL);

	SELECT numbers INTO b FROM table1
		WHERE id = 207;

	FOREACH cursor1 FOR
		SELECT * INTO a FROM TABLE(b);
	.
	.
	.
	END FOREACH;

In this example, the SELECT statement selects one element at a time from the collection variable b into the element variable a. The select list is an asterisk, because the collection variable b contains a collection of built-in types. The variable b is used with the TABLE keyword as a Collection Derived Table. For more information, see Collection Derived Table.

The next example also shows how to fill a collection variable and then how to use a cursor to access individual elements. This example, however, uses a list of row type fields in its select list.

	.
	.
	.
	DEFINE employees employee_t;
	DEFINE n VARCHAR(30);
	DEFINE s INTEGER;

	SELECT emp_list into employees FROM dept_table
		WHERE dept_no = 1057;

	FOREACH cursor1 FOR
		SELECT name,salary 

			INTO n,s FROM TABLE( employees ) AS e;
	.
	.
	.
	END FOREACH;
	.
	.
	.

In this example, the collection variable employees contains a collection of row types. Each row type contains the fields name and salary. The collection query selects one name and salary combination at a time, placing name into n and salary into s. The AS keyword names e as an alias for the collection derived table employees. The alias exists as long as the SELECT statement executes.

To update an element of a collection, you must first declare a cursor with the FOREACH statement. Then, within the FOREACH loop, select elements one at a time from the collection variable, using the collection variable as a collection derived table in a SELECT query.

When the cursor is positioned on the element to be updated, you can use the WHERE CURRENT OF clause, as follows:

• The UPDATE statement with the WHERE CURRENT OF clause updates the value in the current element of the collection variable.
• The DELETE statement with the WHERE CURRENT OF clause deletes the current element from the collection variable.

In general, use the following guidelines for calling another UDR from an SPL routine:

• To call a user-defined procedure, use EXECUTE PROCEDURE procedure name.
• To call a user-defined function, use EXECUTE FUNCTION function name (or EXECUTE PROCEDURE function name if the user-defined function was created with the CREATE PROCEDURE statement).

In Enterprise Decision Server, you must use EXECUTE PROCEDURE. Enterprise Decision Server does not support the EXECUTE FUNCTION statement.

In Dynamic Server, if you use EXECUTE PROCEDURE, the database server looks first for a user-defined procedure of the name you specify. If it finds the procedure, the server executes it. If it does not find the procedure, it looks for a user-defined function of the same name to execute. If the database server finds neither a function nor a procedure, it issues an error message.

If you use EXECUTE FUNCTION, the database server looks for a user-defined function of the name you specify. If it does not find a function of that name, the server issues an error message.

A called SPL function can return zero (0) or more values or rows.

The type and count of each variable in the variable list must match each value that the function returns.