Writing the Statement Block
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.
Implicit and Explicit Statement Blocks
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 14-43 shows.
Figure 14-43
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 14-44 has an explicit statement block that redefines a variable defined in the implicit block.
Figure 14-44
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.
Using Cursors
A FOREACH loop defines a cursor, a specific identifier that points to one item in a group.
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. Once 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. Once 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.
An SPL routine that returns more than one row or a collection 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, a row type, or any other value that does not require a cursor is called a noncursor routine.
The FOREACH Loop
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 14-45 show the structure of FOREACH loops.
Figure 14-45
The semicolon is placed after each statement within the FOREACH loop and after END FOREACH.
Figure 14-46 creates a routine that uses a FOREACH loop to operate on the employee table. Figure 14-23 defines the employee table.
Figure 14-46
The routine performs the following tasks within the FOREACH loop:
The SELECT statement is placed within a cursor because it returns all of 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 14-47 shows.
Figure 14-47
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.
Using an IF - ELIF - ELSE Structure
The SPL routine in Figure 14-48 uses an IF - ELIF - ELSE structure to compare the two arguments that the routine accepts.
Figure 14-48
Suppose you define a table named manager with the columns that Figure 14-49 shows.
The SPL routine in Figure 14-48 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.
Figure 14-50
The CARDINALITY() function counts the number of elements that a collection contains. For a description of the CARDINALITY() function, see "Using the CARDINALITY() Function to Count the Elements in a Collection".
An IF - ELIF - ELSE structure in an SPL routine has up to four parts:
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 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.
The routine executes the statements in the ELSE block if the IF condition and all of the ELIF conditions are false.
The END IF statement ends the statement block.
Expressions in an IF Statement
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 Chapter 2 of the Informix Guide to SQL: Syntax.
Adding WHILE and FOR Loops
Both the WHILE and FOR statements create execution loops in SPL routines. A WHILE loop starts with WHILE condition, executes a block of statements as long as the condition is true, and ends with END WHILE.
Figure 14-51 shows a valid WHILE condition. The routine executes the WHILE loop as long as the condition specified in the WHILE statement is true.
Figure 14-51
The SPL procedure in Figure 14-51 accepts an integer as an argument and then inserts an integer value in to 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 14-52 shows.
Figure 14-52
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 14-53 shows several ways to define the iterations in the FOR loop.
Figure 14-53
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.
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 14-54 shows examples of CONTINUE and EXIT within a FOR loop.
Figure 14-54
|
