SPL Statements
In This Chapter
You can use Stored Procedure Language (SPL) statements to write routines, and you can store these SPL routines in the database. These SPL routines are effective tools for controlling SQL activity.
This chapter contains descriptions of the SPL statements. The description of each statement includes the following information:
If a statement is composed of multiple clauses, the statement description provides the same set of information for each clause.
For task-oriented information about using SPL routines, see Chapter 14 of the Informix Guide to SQL: Tutorial.
CALL
Use the CALL statement to execute a routine from within an SPL routine.
Syntax
Usage
The CALL statement invokes one of the following:
The CALL statement is identical in behavior to the EXECUTE PROCEDURE and EXECUTE FUNCTION statements, but you can only use CALL from within an SPL routine. You can use CALL in an ESQL/C program or with DB-Access, but only if you place the statement within an SPL routine executed by the program or DB-Access.
If you use CALL with a procedure name, you cannot specify a RETURNING clause. If you use CALL with a function name, you must specify a RETURNING clause.
Specifying Arguments
Specifying arguments is optional, whether you use CALL with a procedure or a function.
When you use CALL to execute a routine, you have the option of specifying parameter names for the arguments you pass to the routine. For example, each of the following examples is valid for a routine that has three parameters of VARCHAR type, named t, n, and d, in that order:
The syntax of specifying arguments is described in more detail in the Argument segment on page 1-828.
Receiving Input from the Called Routine
The RETURNING clause, used only with functions, specifies the data variable that receives the values the function returns. If you are calling a procedure, do not use the RETURNING clause.
The following example shows two routine calls.
One routine call is to a procedure (no_args), which expects no returned values. The second routine call is to a function (yes_args), which expects three returned values. The not_much() procedure declares three integer variables (i, j, and k) to receive the returned values from yes_args.
CONTINUE
Use the CONTINUE statement to start the next iteration of the innermost FOR, WHILE, or FOREACH loop.
Syntax
Usage
When you encounter a CONTINUE statement, the routine skips the rest of the statements in the innermost loop of the indicated type. Execution continues at the top of the loop with the next iteration. In the following example, the SPL function loop_skip inserts values 3 through 15 into the table testtable. The function also returns values 3 through 9 and 13 through 15 in the process. The function does not return the value 11, because it encounters the CONTINUE FOR statement. The CONTINUE FOR statement causes the function to skip the RETURN i WITH RESUME statement.
The CONTINUE statement generates errors if it cannot find the identified loop.
DEFINE
Use the DEFINE statement to declare variables that an SPL routine uses and to assign them data types.
Syntax
Complex Data Type (Subset)
Default Value Clause
Usage
The DEFINE statement is not an executable statement. It must appear after the routine header and before any other statements.
If you define a local variable (by using DEFINE without the GLOBAL keyword), the scope of the variable is the statement block in which it is defined. You can use the variable anywhere within the statement block. You can also use the same variable name outside the statement block with a different definition.
If you define a variable with the GLOBAL keyword, the variable is global in scope and is available outside the statement block and to other routines.
SQL Data Type Subset
In defining variables, the SQL data type subset includes all the SQL data types except SERIAL, SERIAL8, TEXT, BYTE, CLOB, and BLOB.
Variables of INT data type hold data from SERIAL columns, and variables of INT8 data type hold data from SERIAL8 columns.
Referencing TEXT and BYTE Variables
The REFERENCES keyword lets you use TEXT and BYTE variables. TEXT and BYTE variables do not contain the actual data but are simply pointers to the data. The REFERENCES keyword is a reminder that the SPL variable is just a pointer. Use an SPL variables that references a TEXT or BYTE data type exactly as you would any other variable.
You cannot define a variable that references a CLOB or BLOB data type.
Declaring GLOBAL Variables
The GLOBAL keyword indicates that the variables that follow are available to other routines through the global environment. The global environment is the memory that is used by all the routines that run within a given DB-Access or SQL API session.
Routines that are running in the current session share global variables. Because the database server does not save global variables in the database, the global variables do not remain when the current session closes. The data types of global variables you use in your SPL routine must match the data types of variables in the global environment.
Databases do not share global variables. The database server does not share global variables with application development tools.
The first declaration of a global variable establishes the variable in the global environment. Subsequent global declarations simply bind the variable to the global environment and establish the value of the variable at that point. The following example shows two SPL procedures, proc1 and proc2. Each procedure has defined the global variable gl_out:
If proc1 is called first, gl_out is set to 13 and then incremented to 14. If proc2 is then called, it sees that the value of gl_out is already defined, so the default value of 23 is not applied. Then, proc2 assigns the existing value of 14 to tmp. If proc2 had been called first, gl_out would have been set to 23, and 23 would have been assigned to tmp. Later calls to proc1 would not apply the default of 13.
Providing Default Values
You can provide a literal value or a null value as the default for a global variable. You can also use a call to an SQL function to provide the default value. The following example uses the SITENAME function to provide a default value. It also defines a global BYTE variable.
SITENAME or DBSERVERNAME
If you use the value returned by SITENAME or DBSERVERNAME as the default, the variable must be a CHAR, VARCHAR, NCHAR, or NVARCHAR value of at least 18 characters.
USER
If you use USER as the default, the variable must be a CHAR, VARCHAR, NCHAR, or NVARCHAR value of at least 8 characters.
CURRENT
If you use CURRENT as the default, the variable must be a DATETIME value. If the YEAR TO FRACTION keyword has qualified your variable, you can use CURRENT without qualifiers. If your variable uses another set of qualifiers, you must provide the same qualifiers when you use CURRENT as the default value. The following example defines a DATETIME variable with qualifiers and uses CURRENT with matching qualifiers:
TODAY
If you use TODAY as the default, the variable must be a DATE value.
TEXT and BYTE
The only default value possible for a TEXT or BYTE variable is null. The following example defines a TEXT global variable that is called l_blob:
Declaring Local Variables
Most local variables (that is, nonglobal variables) do not allow defaults. The following example shows typical definitions of local variables:
Declaring Collection Variables
A variable of type COLLECTION, SET, MULTISET, and LIST is a collection variable and holds a collection fetched from the database. You cannot define a collection variable as global (with the GLOBAL keyword) or with a default value.
A variable defined with the type COLLECTION is an untyped collection variable. An untyped collection variable is generic and can hold a collection of any type.
A variable defined with the type SET, MULTISET, or LIST is a typed collection variable. A typed collection variable can hold only a collection of its type.
You must define the elements of a typed collection variable as NOT NULL, as in the following examples:
Note that with variable c, both the INTEGER values in the SET and the SET values in the LIST are defined as NOT NULL.
You can define collection variables with nested complex types to hold matching nested complex type data. Any type or depth of nesting is allowed. You can nest row types within collection types, collection types within row types, collection types within collection types, s within collection and row types, and so on.
If you define a variable of COLLECTION type, the variable acquires varying type assignments if it is reused within the same statement block, as in the following example:
In this example, varA is a generic collection variable that changes its data type to the data type of the currently assigned collection. The first LET statement makes varA a SET variable. The second LET statement makes varA a LIST variable.
Declaring Row Variables
Row variables hold data from named or unnamed row types. You can define a generic row variable, a named row variable, or an unnamed row variable.
A generic row variable, defined with the ROW keyword, can hold data from any row type. A named row variable holds data from the specific named row type specified in the variable definition. The following statements show examples of generic row variables and named row variables:
A named row variable holds named row types of the same type in the variable definition.
To define a variable that will hold data stored in an unnamed row type, use the ROW keyword followed by the fields of the row type, as in:
Unnamed row types are type-checked only by structural equivalence. Two unnamed row types are considered equivalent if they have the same number of fields, and if the fields have the same type definitions. Therefore, you could fetch either of the following row types into the variable area defined above:
Row variables can have fields, just as row types have fields. To assign a value to a field of a row variable, use the SQL dot notation variableName.fieldName, followed by an expression, as in the following example:
When you assign a value to a row variable, you can use any allowed expression described in "Expression".
Declaring Opaque-Type Variables
Opaque type variables hold data retrieved from opaque types, which you create with the CREATE OPAQUE TYPE statement. An opaque type variable can only hold data of the opaque type on which it is defined.
The following example defines a variable of the opaque type point, which holds the x and y coordinates of a two-dimensional point:
Declaring Variables Like Columns
If you use the LIKE clause, the database server gives SPL variable the same data type as a column in a table, synonym, or view.
The data types of variables that are defined as database columns are resolved at runtime. Therefore, column and table do not need to exist at compile time.
Declaring Variables of Type PROCEDURE
The PROCEDURE type indicates that in the current scope, SPL variable is a call to an SPL routine or external routine. In this release of INFORMIX-Universal Server, the DEFINE statement does not have a FUNCTION keyword. Use the PROCEDURE keyword, whether you are calling a procedure or function.
Defining a variable of PROCEDURE type indicates that in the current statement scope, SPL variable is not a call to an SQL function or a system function. For example, the following statement defines length as an SPL routine, not as the SQL LENGTH function:
This definition disables the SQL LENGTH function within the scope of the statement block. You would use such a definition if you had already created an SPL routine or an external routine with the name length.
If you create a routine with the same name as an aggregate function (SUM, MAX, MIN, AVG, COUNT) or with the name extend, you must qualify the routine name with the owner name.
Declaring Variables for BYTE and TEXT Data
The keyword REFERENCES indicates that SPL variable is not a BYTE or TEXT value but a pointer to the BYTE or TEXT value. However, you can use the variable as though it holds the data.
The following example defines a local BYTE variable:
If you pass a variable of TEXT or BYTE data type to an SPL routine, the data is passed to the database server and stored in the root dbspace or dbspaces that the DBSPACETEMP environment variable specifies, if it is set. You do not need to know the location or name of the file that holds the data. BYTE or TEXT manipulation requires only the name of the BYTE or TEXT variable as it is defined in the routine.
You cannot declare a variable to hold or reference a CLOB or BLOB data type.
Redeclaring Variables
If you define the same variable twice within the same statement block, you receive an error. However, you can redefine a variable within a nested block, in which case it temporarily hides the outer declaration. The following example produces an error:
The database server allows the redeclaration in the following example. Within the nested statement block, n is a character variable. Outside the block, n is an integer variable.
EXIT
Use the EXIT statement to stop the execution of a FOR, WHILE, or FOREACH loop.
Syntax
Usage
The EXIT statement causes the innermost loop of the indicated type (FOR, WHILE, or FOREACH) to terminate. Execution resumes at the first statement outside the loop.
If the EXIT statement cannot find the identified loop, it fails.
If the EXIT statement is used outside all loops, it generates errors.
The following example uses an EXIT FOR statement. In the FOR loop, when j becomes 6, the IF condition i = 5 in the WHILE loop is true. The FOR loop stops executing, and the procedure continues at the next statement outside the FOR loop (in this case, the END PROCEDURE statement). In this example, the procedure ends when j equals 6.
FOR
Use the FOR statement to initiate a controlled (definite) loop when you want to guarantee termination of the loop. The FOR statement uses expressions or range operators to establish a finite number of iterations for a loop.
Syntax
|
Usage
The database server computes all expressions before the FOR statement executes. If one or more of the expressions are variables, and their values change during the loop, the change has no effect on the iterations of the loop.
The FOR loop terminates when variable name takes on the values of each element in the expression list or range in succession or when it encounters an EXIT FOR statement.
The database server generates an error if an assignment within the body of the FOR statement attempts to modify the value of variable name.
Using the TO Keyword to Define a Range
The TO keyword implies a range operator. The range is defined by left expression and right expression, and the STEP increment expression option implicitly sets the number of increments. If you use the TO keyword, variable name must be an INT or SMALLINT data type. The following example shows two equivalent FOR statements. Each uses the TO keyword to define a range. The first statement uses the IN keyword, and the second statement uses an equal sign (=). Each statement causes the loop to execute five times.
If you omit the STEP option, the database server gives increment expression the value of -1 if right expression is less than left expression, or +1 if right expression is more than left expression. If increment expression is specified, it must be negative if right expression is less than left expression, or positive if right expression is more than left expression. The two statements in the following example are equivalent. In the first statement, the STEP increment is explicit. In the second statement, the STEP increment is implicitly 1.
The database server initializes the value of variable name to the value of left expression. In subsequent iterations, the server adds increment expression to the value of variable name and checks increment expression to determine whether the value of variable name is still between left expression and right expression. If so, the next iteration occurs. Otherwise, an exit from the loop takes place. Or, if you specify another range, the variable takes on the value of the first element in the next range.
Specifying Two or More Ranges in a Single FOR Statement
The following example shows a statement that traverses a loop forward and backward and uses different increment values for each direction:
Using an Expression List as the Range
The database server initializes the value of variable name to the value of the first expression specified. In subsequent iterations, variable name takes on the value of the next expression. When the server has evaluated the last expression in the list and used it, the loop stops.
The expressions in the IN list do not have to be numeric values, as long as you do not use range operators in the IN list. The following example uses a character expression list:
The following FOR statement shows the use of a numeric expression list:
Mixing Range and Expression Lists in the Same FOR Statement
If variable name is an INT or SMALLINT value, you can mix ranges and expression lists in the same FOR statement. The following example shows a mixture that uses an integer variable. Values in the expression list include the value that is returned from a SELECT statement, a sum of an integer variable and a constant, the values that are returned from an SPL procedure named p_get_int, and integer constants.
FOREACH
Use a FOREACH loop to select and handle a set of rows or a collection.
Syntax
Usage
A FOREACH loop is the procedural equivalent of using a cursor. When Universal Server executes a FOREACH statement, it takes the following actions:
1. It declares and implicitly opens a cursor.
2. It obtains the first row, the first collection element, or the first set of return values (depending upon the syntax of your FOREACH statement).
3. It assigns each variable in the variable list 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, the next collection element, or the next set of return values, and it repeats steps 3 and 4.
6. It terminates the loop when it finds no more rows, the end of the collection, or the last set of return values. It closes the implicit cursor when the loop terminates.
Because the statement block can contain additional FOREACH statements, you can nest cursors. 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 routine. An SPL routine that returns only one row or value is a noncursor routine.
The following SPL procedure illustrates the three types of FOREACH statements: with a SELECT...INTO clause, with an explicitly named cursor, and with a procedure call:
A select cursor is closed when any of the following situations occur:
Using a SELECT...INTO Statement
With an ordinary cursor that fetches a set of rows or values, you can use a SELECT ... INTO statement. With an ordinary cursor, SELECT ... INTO can also include the UNION and ORDER BY clauses, but it cannot include the INTO TEMP clause. The syntax of a SELECT statement is shown on page 1-596.
The type and count of each variable in the variable list must match each value that the SELECT...INTO statement returns.
Using Hold Cursors
The WITH HOLD keyword specifies that the cursor should remain open when a transaction closes (that is, is committed or rolled back).
Updating or Deleting Rows Identified by Cursor Name
Use the WHERE CURRENT OF cursor name clause to update or delete the current row of cursor name.
Using Collection Variables
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.
If you are using a collection cursor to fetch individual elements from a collection variable, you must use a restricted form of the SELECT statement called a collection query. The collection query 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.
- You must use a collection query within a FOREACH loop.
- You cannot use the WITH HOLD option on the FOREACH statement.
- The data type of the element variable must be the same as the element type of the collection.
- The element variable can have 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 field list must be an asterisk (*).
- If the collection contains row types, the select field list can be a list of one or more field names.
- The select field list cannot contain an expression.
- The collection query cannot specify a WHERE clause, a HAVING clause, a GROUP BY clause, or an ORDER BY clause.
The following excerpt from an SPL routine shows a collection query within a FOREACH loop:
In this example, the SELECT statement within the FOREACH loop is the collection query. The statement selects one element at a time from the collection variable b into the element variable a.
In the collection query, the select field 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 on using a Collection Derived Table, see page 1-831.
The next example shows a collection query that uses a list of row type fields in its select field list.
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. For more information on selecting from a collection variable, see the SELECT statement on page 1-596.
When the cursor is positioned on the element to be updated, you can use the WHERE CURRENT OF clause, as follows:
Calling a Routine in the FOREACH Loop
In general, use the following guidelines for calling another routine from an SPL routine:
If you use EXECUTE PROCEDURE, Universal Server looks first for a procedure of the name you specify. If it finds the procedure, the server executes it. If Universal Server does not find the procedure, it looks for a function of the same name to execute. If the server finds neither a function nor a procedure, it issues an error message.
If you use EXECUTE FUNCTION, Universal Server looks for a function of the name you specify. If it does not find a function of that name, the server issues an error message.
A called function can return zero or more values or rows. The type and count of each variable in the variable list must match each value that the function returns.
IF
Use an IF statement to create a branch within an SPL routine.
Syntax
Usage
The condition that the IF clause states is evaluated. If the result is true, the statements that follow the THEN keyword execute. If the result is false, and an ELIF clause exists, the statements that follow the ELIF clause execute. If no ELIF clause exists, or if the condition in the ELIF clause is not true, the statements that follow the ELIF keyword execute.
In the following example, the SPL function uses an IF statement with both an ELIF clause and an ELSE clause. The IF statement compares two strings and displays a 1 to indicate that the first string comes before the second string alphabetically, or a -1 if the first string comes after the second string alphabetically. If the strings are the same, a 0 is returned.
The ELIF Clause
Use the ELIF clause to specify one or more additional conditions to evaluate.
If you specify an ELIF clause, and the IF condition is false, the ELIF condition is evaluated. If the ELIF condition is true, the statements that follow the ELIF clause execute.
The ELSE Clause
The ELSE clause executes if no true previous condition exists in the IF clause or any of the ELIF clauses.
Conditions in an IF Statement
Conditions in an IF statement are evaluated in the same way as conditions in a WHILE statement.
If any expression that the condition contains evaluates to null, the condition automatically becomes untrue. Consider the following points:
1. If the expression x evaluates to null, then x is not true by definition. Furthermore, not(x) is also not true.
2. IS NULL is the sole operator that can yield true for x. That is, x IS NULL is true, and x IS NOT NULL is not true.
An expression within the condition that has an UNKNOWN value (due to the use of an uninitialized variable) causes an immediate error. The statement terminates and raises an exception.
IF Statement List
Subset of SQL Statements Allowed in an IF Statement
You can use any SQL statement in the statement block except the ones in the following list.
You can use a SELECT statement only if you use the INTO TEMP clause to put the results of the SELECT statement into a temporary table.
LET
Use the LET statement to assign values to variables or to call a function from an SPL routine and assign the return value or values to variables.
Syntax
Usage
If you assign a value to a single variable, you make a simple assignment. If you assign values to two or more variables, you make a compound assignment.
You can also assign the value of an expression to a variable. At runtime, the value of the SPL expression is computed first. The resulting value is cast to the data type of variable, if possible, and the assignment occurs. If conversion is not possible, an error occurs, and the value of variable name is undefined.
A compound assignment assigns multiple expressions to multiple variables. The data types of expressions in the expression list does not need to match the data types of the corresponding variables in the variable list, because the database server automatically converts the data types. (For a detailed discussion of casting, see the Informix Guide to SQL: Reference.)
The following example shows several LET statements that assign values to SPL variables:
You cannot use multiple values to operate on other values. For example, the following statement is illegal:
Using a SELECT Statement in a LET Statement
The examples in this section use a SELECT statement within a LET statement. You can use a SELECT statement to assign values to one or more variables on the left side of the = operator, as the following examples show:
You cannot use a SELECT statement to make multiple values operate on other values. The following example is illegal:
Because a LET statement is equivalent to a SELECT...INTO statement, the two statements in the following example have the same results, a=c and b=d:
If the SELECT statement returns more than one row, you must enclose the SELECT statement in a FOREACH loop For more information on FOREACH, see page 2-27.
Calling a Function in a LET Statement
You can call an SPL function or an external function in a LET statement and assign the returned value or values to variables. You must specify all the necessary arguments to the function unless the function's arguments have default values.
If you name one of the parameters in the called function, with syntax such as name = 'smith', you must name all the parameters.
The named variable receives the returned value from a function call. A function can return more than one value into a list of variable names. However, you must enclose a function that returns more than one value in a FOREACH loop.
The following example shows several LET statements. The first two are valid LET statements that contain function calls. The third LET statement is not legal, because it tries to add the output of two functions and then assign the sum to two variables, a and b. You can easily split this LET statement into two legal LET statements.
A function called in a LET statement can have an argument of COLLECTION, SET, MULTISET, or LIST. COLLECTION is a generic collection data type that includes SET, MULTISET, and LIST collection types.
You can then assign the value returned by the function to a variable, for example:
In the first statement, the SPL function function1 accepts collection1 (that is, any collection data type) as an argument and returns its value to the variable d. In the second statement, the SPL function function2 accepts set1 as an argument and returns a value to the variable a.
ON EXCEPTION
Use the ON EXCEPTION statement to specify the actions that are taken for a particular error or a set of errors.
Syntax
Usage
The ON EXCEPTION statement, together with the RAISE EXCEPTION statement, provides an error-trapping and error-recovery mechanism for SPL. The ON EXCEPTION statement defines a list of errors that are to be trapped as the SPL routine executes and specifies the action (within the statement block) to take when the trap is triggered. If the IN clause is omitted, all errors are trapped.
You can use more than one ON EXCEPTION statement within a given statement block.
The scope of an ON EXCEPTION statement is the statement block that follows the ON EXCEPTION statement, all the statement blocks that are nested within that following statement block, and all the statement blocks that follow the ON EXCEPTION statement.
The exceptions that are trapped can be either system- or user-defined.
When an exception is trapped, the error status is cleared.
If you specify a variable to receive an ISAM error, but no accompanying ISAM error exists, a zero returns to the variable. If you specify a variable to receive the returned error text, but none exists, an empty string goes into the variable.
Placement of the ON EXCEPTION Statement
ON EXCEPTION is a declarative statement, not an executable statement. For this reason, you must use the ON EXCEPTION statement before any executable statement and after any DEFINE statement in an SPL routine.
The following example shows the correct placement of an ON EXCEPTION statement. Use an ON EXCEPTION statement after the DEFINE statement and before the body of the routine.
The following SPL function inserts a set of values into a table. If the table does not exist, it is created, and the values are inserted. The SPL function also returns the total number of rows in the table after the insert occurs.
When an error occurs, the database server searches for the last declaration of the ON EXCEPTION statement, which traps the particular error code. The ON EXCEPTION statement can have the error number in the IN clause or have no IN clause. If the database server finds no pertinent ON EXCEPTION statement, the error code passes back to the caller (the routine, application, or interactive user), and execution aborts.
The following example uses two ON EXCEPTION statements with the same error number so that error code 691 can be trapped in two levels of nesting:
Using the IN Clause to Trap Specific Exceptions
A trap is triggered if either the SQL error code or the ISAM error code matches an exception code in the list of error numbers. The search through the list begins from the left and stops with the first match.
You can use a combination of an ON EXCEPTION statement without an IN clause and one or more ON EXCEPTION statements with an IN clause to set up default trapping. A summary of the sequence of statements in the following example would be: "Test for an error. If error -210, -211, or -212 occurs, take action A. If error -300 occurs, take action B. If any other error occurs, take action C." When an error occurs, the database server searches for the last declaration of the ON EXCEPTION statement that traps the particular error code.
Receiving Error Information in the SET Clause
If you use the SET clause, when an exception occurs, the SQL error code and (optionally) the ISAM error code are inserted into the variables that are specified in the SET clause. If you provided an error data variable, any error text that the database server returns is put into the error data variable. Error text includes information such as the offending table or column name.
Forcing the Routine to Continue
The example on page 2-45 uses the WITH RESUME keyword to indicate that after the statement block in the ON EXCEPTION statement executes, execution is to continue at the LET x = SELECT COUNT(*) FROM emp_list statement, which is the line following the line that raised the error. For this routine, the result is that the count of salespeople names occurs even if the error occurred.
Continuing Execution After an Exception Occurs
If you do not include the WITH RESUME keyword in your ON EXCEPTION statement, the next statement that executes after an exception occurs depends on the placement of the ON EXCEPTION statement, as the following scenarios describe:
Errors Within the ON EXCEPTION Statement Block
To prevent an infinite loop, if an error occurs during execution of the statement block of an error trap, the search for another trap does not include the current trap.
RAISE EXCEPTION
Use the RAISE EXCEPTION statement to simulate the generation of an error.
Syntax
Usage
Use the RAISE EXCEPTION statement to simulate an error. An ON EXCEPTION statement can trap the generated error.
If you omit the ISAM error parameter, the database server sets the ISAM error code to zero when the exception is raised. If you want to use the error text variable parameter but not specify a value for ISAM error, you can specify 0 as the value of ISAM error.
For example, the following statement raises the error number -9999 and returns the stated text:
The statement can raise either system-generated exceptions or user-generated exceptions.
In the following example, a negative value for a raises exception 9999. The code should contain an ON EXCEPTION statement that traps for an exception of 9999.
See the ON EXCEPTION statement for more information about the scope and compatibility of exceptions.
RETURN
Use the RETURN statement to designate the values that the routine returns to the calling module.
Syntax
Usage
The RETURN statement returns values to the calling module. An SPL routine that returns one or more values is called an SPL function.
All the RETURN statements in an SPL function must be consistent with the RETURNING clause of the CREATE FUNCTION statement, which defines the function. The number and data type of values in the RETURN statement, if any, must match in number and data type the data types that are listed in the RETURNING clause of the CREATE FUNCTION statement. You can choose to return no values even if you specify one or more values in the RETURNING clause. If you use a RETURN statement without any expressions, but the calling routine or program expects one or more return values, it is equivalent to returning the expected number of null values to the calling program.
In the following example, the SPL function includes two acceptable RETURN statements. A program that calls this function should check if no values are returned and act accordingly.
A RETURN statement without any expressions exits only if the SPL function is declared not to return values; otherwise it returns nulls.
In an SPL program, you can use an external function as an expression in a RETURN statement provided that the external function is not an iterator function. An iterator function is an external function that returns one or more rows of data and therefore requires a cursor to execute.
The WITH RESUME Keyword
If you use the WITH RESUME keyword after the RETURN statement executes, the next invocation of the SPL function (upon the next FETCH or FOREACH statement) starts from the statement that follows the RETURN statement. If a function executes a RETURN WITH RESUME statement, the calling routine or program must call the function within a FOREACH loop.
If an SPL routine executes a RETURN WITH RESUME statement, a FETCH statement in an application that is written in an SQL API can call it. 
The following example shows a cursor routine that another routine can call. After the RETURN i WITH RESUME statement returns each value to the calling routine or program, the next line of sequence() executes the next time sequence() is called. If backwards equals 0, no value is returned to the calling routine or program, and execution of sequence stops.
SYSTEM
Use the SYSTEM statement to make an operating-system command run from within an SPL routine.
Syntax
Usage
If the supplied expression is not a character expression, expression is converted to a character expression before the operating-system command is made. The complete character expression passes to the operating system and executes as an operating-system command.
The operating-system command that the SYSTEM statement specifies cannot run in the background. The database server waits for the operating system to complete execution of the command before it continues to the next statement in the SPL routine.
Your SPL routine cannot use a value or values that the command returns.
If the operating-system command fails (that is, if the operating system returns a nonzero status for the command), an exception is raised that contains the returned operating-system status as the ISAM error code and an appropriate SQL error code.
In routines that contain SYSTEM statements, the operating-system command runs with the permissions of the user who is executing the routine.
Specifying Environment Variables in SYSTEM Statements
When the operating-system command that SYSTEM specifies is executed, no guarantee exists that the environment variables that the user application set are passed to the operating system. To ensure that the environment variables that the application set are carried forward to the operating system, enter a SYSTEM command that sets the environment variables before you enter the SYSTEM command that causes the operating-system command to execute.
For information on the operating-system commands that set environment variables, see Chapter 3 of the Informix Guide to SQL: Reference.
Examples of the SYSTEM Statement in SPL Routines
The following example shows the use of a SYSTEM statement within an SPL routine. The SYSTEM statement in this example causes the operating system to send a mail message to the system administrator.
You can use a double-pipe symbol (||) to concatenate expressions with a SYSTEM statement, as the following example shows:
TRACE
Use the TRACE statement to control the generation of debugging output.
Syntax
Usage
The TRACE statement generates output that is sent to the file that the SET DEBUG FILE TO statement specifies.
Tracing prints the current values of the following items:
The output of each executed TRACE statement appears on a separate line.
If you use the TRACE statement before you specify a DEBUG file to contain the output, an error is generated.
Any routine the SPL routine calls inherit the trace state. That is, a called routine assumes the same trace state (ON, OFF, or PROCEDURE) as the calling routine. The called routine can set its own trace state, but that state is not passed back to the calling routine.
A routine that is executed on a remote database server does not inherit the trace state.
TRACE ON
If you specify the keyword ON, all statements are traced. The values of variables (in expressions or otherwise) are printed before they are used. To turn tracing ON implies tracing both routine calls and statements in the body of the routine.
TRACE OFF
If you specify the keyword OFF, all tracing is turned off.
TRACE PROCEDURE
If you specify the keyword PROCEDURE, only the routine calls and return values, but not the body of the routine, are traced.
The TRACE statement does not have ROUTINE or FUNCTION keywords. Therefore, use the TRACE PROCEDURE keywords even if the SPL routine you want to trace is a function.
Printing Expressions
You can use the TRACE statement with a quoted string or an expression to display values or comments in the output file. If the expression is not a literal expression, the expression is evaluated before it is written to the output file.
You can use the TRACE statement with an expression even if you used a TRACE OFF statement earlier in a routine. However, you must first use the SET DEBUG statement to establish a trace-output file.
The following example uses a TRACE statement with an expression after it uses a TRACE OFF statement:
The following example shows additional TRACE statements:
Looking at the Traced Output
To see the traced output, use an editor or utility to display or read the contents of the file.
WHILE
Use the WHILE statement to establish an indefinite loop within an SPL routine.
Syntax
Usage
The condition is evaluated once at the beginning of the loop, and subsequently at the beginning of each iteration. The statement block is executed as long as the condition remains true. The loop terminates when the condition evaluates to not true.
If any expression within the condition evaluates to null, the condition automatically becomes not true unless you are explicitly testing for the IS NULL condition.
If an expression within the condition has an unknown value because it references uninitialized SPL variables, an immediate error results. In this case, the loop terminates, and an exception is raised.
|
