Informix Guide to SQL: Tutorial Creating and Using SPL Routines

Every SPL routine has at least one statement block, which is a group of SQL and SPL statements between the CREATE statement and the END statement. You can use any SPL statement or any allowed SQL statement within a statement block. For a list of SQL statements that are not allowed within an SPL statement block, see the description of the Statement Block segment in the Informix Guide to SQL: Syntax.

In an SPL routine, the implicit statement block extends from the end of the CREATE statement to the beginning of the END statement. You can also define an explicit statement block, which starts with a BEGIN statement and ends with an END statement, as Figure 10-44 shows.

BEGIN
	DEFINE distance INT;
	LET distance = 2;
END

The explicit statement block allows you to define variables or processing that are valid only within the statement block. For example, you can define or redefine variables, or handle exceptions differently, for just the scope of the explicit statement block.

The SPL function in Figure 10-45 has an explicit statement block that redefines a variable defined in the implicit block.

CREATE FUNCTION block_demo()
	RETURNING INT;
	

	DEFINE distance INT;
	LET distance = 37;
	BEGIN
		DEFINE distance INT;
		LET distance = 2;
	END
	RETURN distance;


END FUNCTION;

In this example, the implicit statement block defines the variable distance and gives it a value of 37. The explicit statement block defines a different variable named distance and gives it a value of 2. However, the RETURN statement returns the value stored in the first distance variable, or 37.

A FOREACH loop defines a cursor, a specific identifier that points to one item in a group, whether a group of rows or the elements in a collection.

The FOREACH loop declares and opens a cursor, fetches rows from the database, works on each item in the group, and then closes the cursor. You must declare a cursor if a SELECT, EXECUTE PROCEDURE, or EXECUTE FUNCTION statement might return more than one row. After you declare the cursor, you place the SELECT, EXECUTE PROCEDURE, or EXECUTE FUNCTION statement within it.

An SPL routine that returns a group of rows is called a cursor routine because you must use a cursor to access the data it returns. An SPL routine that returns no value, a single value, or any other value that does not require a cursor is called a noncursor routine.The FOREACH loop declares and opens a cursor, fetches rows or a collection from the database, works on each item in the group, and then closes the cursor. You must declare a cursor if a SELECT, EXECUTE PROCEDURE, or EXECUTE FUNCTION statement might return more than one row or a collection. After you declare the cursor, you place the SELECT, EXECUTE PROCEDURE, or EXECUTE FUNCTION statement within it.

In a FOREACH loop, you can use an EXECUTE FUNCTION or SELECT...INTO statement to execute an external function that is an iterator function.

A FOREACH loop begins with the FOREACH keyword and ends with END FOREACH. Between FOREACH and END FOREACH, you can declare a cursor or use EXECUTE PROCEDURE or EXECUTE FUNCTION. The two examples in Figure 10-46 show the structure of FOREACH loops.

FOREACH cursor FOR
	SELECT column FROM table INTO variable;
.
.
.
END FOREACH


FOREACH 
	EXECUTE FUNCTION name() INTO variable;
END FOREACH

The semicolon is placed after each statement within the FOREACH loop and after END FOREACH.

Figure 10-47 creates a routine that uses a FOREACH loop to operate on the employee table.

CREATE_PROCEDURE increase_by_pct( pct INTEGER )


	DEFINE s INTEGER;


	FOREACH sal_cursor FOR


		SELECT salary INTO s FROM employee 
			WHERE salary > 35000


		LET s = s + s * ( pct/100 );


		UPDATE employee SET salary = s
			WHERE CURRENT OF sal_cursor;


	END FOREACH


END PROCEDURE;

The routine in Figure 10-47 performs the following tasks within the FOREACH loop:

• Declares a cursor
• Selects one salary value at a time from employee
• Increases the salary by a percentage
• Updates employee with the new salary
• Fetches the next salary value

The SELECT statement is placed within a cursor because it returns all the salaries in the table greater than 35000.

The WHERE CURRENT OF clause in the UPDATE statement updates only the row on which the cursor is currently positioned. The clause also automatically sets an update cursor on the current row. An update cursor places an update lock on the row so that no other user can update the row until your update occurs.

An SPL routine will set an update cursor automatically if an UPDATE or DELETE statement within the FOREACH loop uses the WHERE CURRENT OF clause. If you use WHERE CURRENT OF, you must explicitly name the cursor in the FOREACH statement.

If you are using an update cursor, you can add a BEGIN WORK statement before the FOREACH statement and a COMMIT WORK statement after END FOREACH, as Figure 10-48 shows.

BEGIN WORK;


	FOREACH sal_cursor FOR
		SELECT salary INTO s FROM employee 
			WHERE salary > 35000;
		LET s = s + s * ( pct/100 );
		UPDATE employee SET salary = s
			WHERE CURRENT OF sal_cursor
	END FOREACH
COMMIT WORK;

For each iteration of the FOREACH loop, the COMMIT WORK statement commits the work done since the BEGIN WORK statement and releases the lock on the updated row.

The SPL routine in Figure 10-49 uses an IF - ELIF - ELSE structure to compare the two arguments that the routine accepts.

CREATE FUNCTION str_compare( str1 CHAR(20), str2 CHAR(20))
	RETURNING INTEGER;
	

	DEFINE result INTEGER;
	

	IF str1 > str2 THEN
		 result = 1;
	ELIF str2 > str1 THEN
		result = -1;
	ELSE
		result = 0;
	END IF
	RETURN result;
END FUNCTION;

Suppose you define a table named manager with the columns that Figure 10-50 shows.

CREATE TABLE manager
(
	mgr_name				VARCHAR(30),
	department				VARCHAR(12),
	dept_no				SMALLINT,
	direct_reports				SET( VARCHAR(30) NOT NULL ),
	projects				LIST( ROW ( pro_name VARCHAR(15),
					pro_members SET( VARCHAR(20) NOT NULL ) )
					NOT NULL),
	salary				INTEGER,
);

The SPL routine in Figure 10-51 uses an IF - ELIF - ELSE structure to check the number of elements in the SET in the direct_reports column and call various external routines based on the results.

CREATE FUNCTION check_set( d SMALLINT )
	RETURNING VARCHAR(30), VARCHAR(12), INTEGER;


	DEFINE name VARCHAR(30);
	DEFINE dept VARCHAR(12);
	DEFINE num INTEGER;


	SELECT mgr_name, department, CARDINALITY(direct_reports) 
		FROM manager INTO name, dept, num
		WHERE dept_no = d;
	IF num > 20 THEN
		EXECUTE FUNCTION add_mgr(dept);
	ELIF num = 0 THEN
		EXECUTE FUNCTION del_mgr(dept);
	ELSE
		RETURN name, dept, num;
	END IF;


END FUNCTION;

The CARDINALITY() function counts the number of elements that a collection contains. For more information, see Cardinality Function.

An IF - ELIF - ELSE structure in an SPL routine has up to the following four parts:

• An IF... THEN condition

If the condition following the IF statement is TRUE, the routine executes the statements in the IF block. If the condition is false, the routine evaluates the ELIF condition.

The expression in an IF statement can be any valid condition, as the Condition segment of the Informix Guide to SQL: Syntax describes. For the complete syntax and a detailed discussion of the IF statement, see the Informix Guide to SQL: Syntax.

• One or more ELIF conditions (optional)

The routine evaluates the ELIF condition only if the IF condition is false. If the ELIF condition is true, the routine executes the statements in the ELIF block. If the ELIF condition is false, the routine either evaluates the next ELIF block or executes the ELSE statement.

• An ELSE condition (optional)

The routine executes the statements in the ELSE block if the IF condition and all of the ELIF conditions are false.

• An END IF statement

The END IF statement ends the statement block.

Both the WHILE and FOR statements create execution loops in SPL routines. A WHILE loop starts with a WHILE condition, executes a block of statements as long as the condition is true, and ends with END WHILE.

Figure 10-52 shows a valid WHILE condition. The routine executes the WHILE loop as long as the condition specified in the WHILE statement is true.

CREATE PROCEDURE test_rows( num INT )


	DEFINE i INTEGER;
	LET i = 1;


	WHILE i < num
		INSERT INTO table1 (numbers) VALUES (i);
		LET i = i + 1;
	END WHILE;


END PROCEDURE;

The SPL routine in Figure 10-52 accepts an integer as an argument and then inserts an integer value into the numbers column of table1 each time it executes the WHILE loop. The values inserted start at 1 and increase to
num - 1.

Be careful that you do not create an endless loop, as Figure 10-53 shows.

CREATE PROCEDURE endless_loop()


	DEFINE i INTEGER;
	LET i = 1;
	WHILE ( 1 = 1 )				-- don't do this!
		LET i = i + 1;
		INSERT INTO table1 VALUES (i);
	END WHILE;


END PROCEDURE;

A FOR loop extends from a FOR statement to an END FOR statement and executes for a specified number of iterations, which are defined in the FOR statement. Figure 10-54 shows several ways to define the iterations in the FOR loop.

FOR i = 1 TO 10
.
.
END FOR;




FOR i = 1 TO 10 STEP 2
.
.
END FOR;




FOR i IN (2,4,8,14,22,32)
.
.
END FOR;




FOR i IN (1 TO 20 STEP 5, 20 to 1 STEP -5, 1,2,3,4,5)
.
.
END FOR:

In the first example, the SPL procedure executes the FOR loop as long as i is between 1 and 10, inclusive. In the second example, i steps from 1 to 3, 5, 7, and so on, but never exceeds 10. The third example checks whether i is within a defined set of values. In the fourth example, the SPL procedure executes the loop when i is 1, 6, 11, 16, 20, 15, 10, 5, 1, 2, 3, 4, or 5-in other words, 11 times, because the list has two duplicate values, 1 and 5.

Tip: The main difference between a WHILE loop and a FOR loop is that a FOR loop is guaranteed to finish, but a WHILE loop is not. The FOR statement specifies the exact number of times the loop executes, unless a statement causes the routine to exit the loop. With WHILE, it is possible to create an endless loop.

Exiting a Loop

In a FOR, FOREACH, or WHILE loop, you can use a CONTINUE or EXIT statement to control the execution of the loop.

CONTINUE causes the routine to skip the statements in the rest of the loop and move to the next iteration of the FOR statement. EXIT ends the loop and causes the routine to continue executing with the first statement following END FOR. Remember that EXIT must be followed by the keyword of the loop the routine is executing-for example, EXIT FOR or EXIT FOREACH.

Figure 10-55 shows examples of CONTINUE and EXIT within a FOR loop.

FOR i = 1 TO 10
	IF i = 5 THEN
		CONTINUE FOR;
.
.
.
	ELIF i = 8 THEN
		EXIT FOR;
	END IF;


END FOR;


Tip: You can use CONTINUE and EXIT to improve the performance of SPL routines so that loops do not execute unnecessarily.