In This Chapter

This chapter contains descriptions of the SPL statements. The description of each statement includes the following information:

• A brief introduction that explains the purpose of the statement
• A syntax diagram that shows how to enter the statement correctly
• A syntax table that explains each input parameter in the syntax diagram
• Rules of usage, including examples that illustrate these rules

For task-oriented information about using SPL routines, see Chapter 14 of the Informix Guide to SQL: Tutorial.

CALL

Syntax

Element	Purpose	Restrictions	Syntax
data variable	The name of a variable that receives the value or values a function returns	The data type of data variable must be appropriate for the value the function returns.	Identifier, p. 1-966
routine variable	The name of a variable that is set to the name of an SPL routine or external routine	The routine variable must have the data type CHAR, VARCHAR, NCHAR, or NVARCHAR. The routine name you assign to SPL variable must be non-null and the name of an existing routine.	Identifier, p. 1-966

• A procedure named procedure name
• A function named function name
• Any routine named by routine variable

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

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:

CALL add_col ( t='customer', n = 'newint', d ='integer' );

CALL add_col( 'customer','newint','integer' );

Receiving Input from the Called Routine

The following example shows two routine calls.

CREATE PROCEDURE not_much()


	DEFINE i, j, k INT;

	CALL no_args (10,20);

	CALL yes_args (5) RETURNING i, j, k;


END PROCEDURE 

CONTINUE

Syntax

CREATE FUNCTION loop_skip()

	RETURNING INT;

	DEFINE i INT;

	.

	.

	.

	FOR i IN (3 TO 15 STEP 2)

	INSERT INTO testtable values(i, null, null); 

		IF i = 11

			CONTINUE FOR;

		END IF;

		RETURN i WITH RESUME;

	END FOR;



END FUNCTION;

DEFINE

Syntax

Element	Purpose	Restrictions	Syntax
column name	The name of a column in the table.	The column must exist in the table by the time you run the SPL routine.	Identifier, p. 1-966
distinct type name	The name of a distinct type.	The distinct type must be defined in the database by the time you run the SPL routine.	Identifier, p. 1-966
opaque type name	The name of an opaque type.	The opaque type must be defined in the database by the time you run the SPL routine.	Identifier, p. 1-966
SPL variable	The name of the SPL variable that is being defined.	The name must be unique within the statement block.	Identifier, p. 1-966

Complex Data Type (Subset)

Element	Purpose	Restrictions	Syntax
data type	The data type of the elements of a collection	The data type must match the data type of the elements of the collection the variable will contain. The data type can be any data type except a SERIAL, SERIAL8, TEXT, BYTE, CLOB, or BLOB.	Identifier, p. 1-966

Default Value Clause

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

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

You cannot define a variable that references a CLOB or BLOB data type.

Declaring GLOBAL Variables

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:

CREATE PROCEDURE proc1()

	.

	.

	.

	DEFINE GLOBAL gl_out INT DEFAULT 13;

	.

	.

	.

	LET gl_out = gl_out + 1;

	END PROCEDURE;



CREATE PROCEDURE proc2()

	.

	.

	.

	DEFINE GLOBAL gl_out INT DEFAULT 23;

	DEFINE tmp INT;

	.

	.

	.

	LET tmp = gl_out

	.

	.

	.

END PROCEDURE;

Providing Default Values

CREATE PROCEDURE gl_def()

	DEFINE GLOBAL gl_site CHAR(18) DEFAULT SITENAME;

	DEFINE GLOBAL gl_byte REFERENCES BYTE DEFAULT NULL;

	.

	.

	.

END PROCEDURE

USER

CURRENT

DEFINE GLOBAL d_var DATETIME YEAR TO MONTH 

		DEFAULT CURRENT YEAR TO MONTH;

TEXT and BYTE

CREATE PROCEDURE use_text()

	DEFINE i INT;

	DEFINE GLOBAL l_blob REFERENCES TEXT DEFAULT NULL;

END PROCEDURE 

CREATE PROCEDURE def_ex()

	DEFINE i INT;

	DEFINE word CHAR(15);

	DEFINE b_day DATE;

	DEFINE c_name LIKE customer.fname;

	DEFINE b_text REFERENCES TEXT;

END PROCEDURE;

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:

DEFINE a SET ( INT NOT NULL );


DEFINE b MULTISET ( ROW ( b1 INT,
					        b2 CHAR(50)
					    ) NOT NULL );


DEFINE c LIST( SET( INTEGER NOT NULL ) 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:

DEFINE a COLLECTION;
LET a = setB;
.
.
.
LET a = listC;

Declaring Row Variables

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:

DEFINE d ROW;									-- generic row variable


DEFINE rectv rectangle_t;					-- named row variable

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:

DEFINE area ROW ( x int, y char(10) );

ROW ( a int, b char(10) )
ROW ( area int, name char(10) )

CREATE ROW TYPE rectangle_t (start point_t, length real,
	width real);


DEFINE r rectangle_t;
		-- Define a variable of a named row type
LET r.length = 45.5;
		-- Assign a value to a field of the variable

When you assign a value to a row variable, you can use any allowed expression described in "Expression".

The following example defines a variable of the opaque type point, which holds the x and y coordinates of a two-dimensional point:

DEFINE b point;

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

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:

DEFINE length PROCEDURE;

.

.

.

LET x = length (a,b,c)

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 following example defines a local BYTE variable:

CREATE PROCEDURE use_blob()


	DEFINE i INT;

	DEFINE l_blob REFERENCES BYTE;


END PROCEDURE --use_blob

You cannot declare a variable to hold or reference a CLOB or BLOB data type.

Redeclaring Variables

CREATE PROCEDURE example1() 


	DEFINE n INT; 
	DEFINE j INT;

	DEFINE n CHAR (1);	 -- redefinition produces an error

	.

	.

	.
END PROCEDURE;

CREATE PROCEDURE example2() 


	DEFINE n INT; 
	DEFINE j INT;

	.

	.

	.

	BEGIN

	DEFINE n CHAR (1);	   -- character n masks integer variable

						-- locally

	.

	.

	.

END PROCEDURE;

Syntax

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.

CREATE PROCEDURE ex_cont_ex()


	DEFINE i,s,j, INT;



	FOR j = 1 TO 20

		IF j > 10 THEN 

			CONTINUE FOR;

		END IF



		LET i,s = j,0;

		WHILE i > 0

			LET i = i -1;

			IF i = 5 THEN

				EXIT FOR;

			END IF

		END WHILE

	END FOR


END PROCEDURE;

Syntax

Element	Purpose	Restrictions	Syntax
expression	A numeric or character value against which variable name is compared to determine if the loop should be executed	The data type of expression must match the data type of variable name. You can use the output of a SELECT statement as an expression.	Expression, p. 1-880
increment expression	A positive or negative value by which variable name is incremented. Defaults to +1 or -1 depending on left expression and right expression.	The increment expression cannot evaluate to 0.	Expression, p. 1-880
left expression	The starting expression of a range	The value of left expression must match the data type of variable name. It must be either INT or SMALLINT.	Expression, p. 1-880
right expression	The ending expression in the range. The size of right expression relative to left expression determines if the range is stepped through positively or negatively.	The value of right expression must match the data type of variable name. It must be either INT or SMALLINT.	Expression, p. 1-880
variable name	The value of this variable determines how many times the loop executes.	You must have already defined this variable, and the variable must be valid within this statement block. If you are using variable name with a range of values and the TO keyword, you must define variable name explicitly as either INT or SMALLINT.	Identifier, p. 1-966

Usage

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

FOR index_var IN (12 TO 21 STEP 2)

	-- statement block

END FOR



FOR index_var = 12 TO 21 STEP 2

	-- statement block

END FOR

FOR index IN (12 TO 21 STEP 1)

	-- statement block

END FOR



FOR index = 12 TO 21

	-- statement block

END FOR

Specifying Two or More Ranges in a Single FOR Statement

FOR index_var IN (15 to 21 STEP 2, 21 to 15 STEP -3)

	-- statement body

END FOR

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:

FOR c IN ('hello', (SELECT name FROM t), 'world', v1, v2)

	INSERT INTO t VALUES (c);

	END FOR

FOR index IN (15,16,17,18,19,20,21)

	-- statement block

END FOR

CREATE PROCEDURE for_ex ()

	DEFINE i, j INT;

	LET j = 10;

	FOR i IN (1 TO 20, (SELECT c1 FROM tab WHERE id = 1),

	j+20 to j-20, p_get_int(99),98,90 to 80 step -2)

		INSERT INTO tab VALUES (i);

	END FOR


END PROCEDURE 

Syntax

Element	Purpose	Restrictions	Syntax
cursor name	An identifier that you supply as a name for the FOREACH loop	Each cursor name within a routine must be unique.	Identifier, p. 1-966
data variable	The name of an SPL variable in the calling routine that will receive the value or values the called function returns	The data type of variable name must be appropriate for the value that is being returned.	Identifier, p. 1-966
routine variable	The name of an SPL variable in the calling routine that contains the name of a routine to be executed	The data type of routine variable must be CHAR, VARCHAR, NCHAR, NVARCHAR.	Identifier, p. 1-966

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.

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:

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

• The cursor returns no more values.
• 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 the ON EXCEPTION statement on page 2-43.)
• A cursor in the calling routine that is executing this cursor routine (within a FOREACH loop) closes for any reason.

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

Using Hold Cursors

Updating or Deleting Rows Identified by Cursor Name

Using Collection Variables

Tip: To access only one element of a collection variable, you do not need to declare a cursor. For information on how to select a single element, see "Selecting From a Collection Variable". For information on how to insert a single element, see "Inserting Into 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.

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

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

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:

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

• To call an SPL procedure or external procedure, use
  EXECUTE PROCEDURE procedure name.
• To call an SPL function or external function, use
  EXECUTE FUNCTION function name.
• Do not use EXECUTE FUNCTION procedure name in any case.

Important: Universal Server allows you to use an EXECUTE PROCEDURE statement to execute SPL functions (routines that were formerly called stored procedures that return a value). However, Informix recommends that you use the EXECUTE PROCEDURE statement only with procedures and the EXECUTE FUNCTION statement only with functions.

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

Syntax

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.

CREATE FUNCTION str_compare (str1 CHAR(20), str2 CHAR(20))

	RETURNING INT;


	DEFINE result INT;



	IF str1 > str2 then 

		result =1;

	ELIF str2 > str1 THEN

		result = -1;

	ELSE

		result = 0;

	END IF

	RETURN result;


END FUNCTION -- str_compare

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

Conditions in an IF 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.

IF Statement 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

Syntax

Element	Purpose	Restrictions	Syntax
SPL variable	An SPL variable that receives the value the function returns or is set to the result of the expression	The SPL variable must be defined in the routine and must be valid in the statement block.	Identifier, p. 1-966

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: Syntax.)

The following example shows several LET statements that assign values to SPL variables:

LET a   = c + d ;

LET a,b = c,d ;

LET expire_dt = end_dt + 7 UNITS DAY; 

LET name = 'Brunhilda';

LET sname = DBSERVERNAME;

LET this_day = TODAY;

LET a,b = (c,d) + (10,15); -- ILLEGAL EXPRESSION

LET a,b = (SELECT c1,c2 FROM t WHERE id = 1);	

LET a,b,c = (SELECT c1,c2 FROM t WHERE id = 1), 15;

LET a,b = (SELECT c1,c2 FROM t) + (10,15); -- ILLEGAL CODE

CREATE PROCEDURE proof()


	DEFINE a, b, c, d INT;


	LET a,b = (SELECT c1,c2 FROM t WHERE id = 1);	

	SELECT c1, c2 INTO c, d FROM t WHERE id = 1


END PROCEDURE;

Calling a Function in a LET Statement

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.

LET a, b, c = proc1(name = 'grok', age = 17);

LET a, b, c = 7, proc2('orange', 'green');


LET a, b = proc1() + proc2(); -- ILLEGAL CODE

You can then assign the value returned by the function to a variable, for example:

LET d = function1(collection1);

LET a = function2(set1);

ON EXCEPTION

Syntax

Element	Purpose	Restrictions	Syntax
error data variable	An SPL variable that contains a string returned by an SQL error or a user-defined exception	The variable must be a character data type to receive the error information. It must be valid in the current statement block.	Identifier, p. 1-966
error number	An SQL error number, or an error number created by a RAISE EXCEPTION statement, that is to be trapped	The error number must be of integer data type. It must be valid in the current statement block.	Literal number, p. 1-1001
ISAM error variable	A variable that receives the ISAM error number of the exception raised	The error variable must be of integer data type. It must be valid in the current statement block.	Identifier, p. 1-966
SQL error variable	A variable that receives the SQL error number of the exception raised	The error variable must be a character data type. It must be valid in the current statement block.	Identifier, p. 1-966

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

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.

CREATE FUNCTION add_salesperson(last CHAR(15),

				 first CHAR(15))

	RETURNING INT;


	DEFINE x INT;


	ON EXCEPTION IN (-206) -- If no table was found, create one

		CREATE TABLE emp_list		 	

			 (lname CHAR(15),fname CHAR(15), tele CHAR(12));

		INSERT INTO emp_list VALUES -- and insert values

			 (last, first, '800-555-1234');

	END EXCEPTION WITH RESUME

	INSERT INTO emp_list VALUES (last, first, '800-555-1234')

	LET x = SELECT count(*) FROM emp_list;


	RETURN x;


END FUNCTION;

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:

CREATE PROCEDURE delete_cust (cnum INT)


	ON EXCEPTION  IN (-691)    -- children exist

		BEGIN -- Begin-end is necessary so that other DELETEs

			 -- don't get caught in here.

        	 	 	ON EXCEPTION IN (-691)

            	 	 		DELETE FROM another_child WHERE num = cnum;

    	    	 	 	DELETE FROM orders WHERE customer_num = cnum;

			END EXCEPTION -- for 691



    		DELETE FROM orders WHERE customer_num = cnum;

		END



      DELETE FROM cust_calls WHERE customer_num = cnum;

      DELETE FROM customer WHERE customer_num = cnum;

    END EXCEPTION

       DELETE FROM customer WHERE customer_num = cnum;


END PROCEDURE;

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.

CREATE PROCEDURE ex_test ()

	DEFINE error_num INT;

	.

	.

	.

	ON EXCEPTION

	SET error_num

	-- action C

	END EXCEPTION



	ON EXCEPTION IN (-300)

	-- action B

	END EXCEPTION

	ON EXCEPTION IN (-210, -211, -212)

	SET error_num

	-- action A

	END EXCEPTION

	.

	.

	.

Forcing the Routine to Continue

Continuing Execution After an Exception Occurs

• If the ON EXCEPTION statement is inside a statement block with a BEGIN and an END keyword, execution resumes with the first statement (if any) after that BEGIN...END block. That is, it resumes after the scope of the ON EXCEPTION statement.
• If the ON EXCEPTION statement is inside a loop (FOR, WHILE, FOREACH), the rest of the loop is skipped, and execution resumes with the next iteration of the loop.
• If the ON EXCEPTION statement is not contained within a statement or statement block, the SPL routine executes a RETURN statement with no arguments to terminate. That is, the routine returns a successful status and no values.

RAISE EXCEPTION

Syntax

Element	Purpose	Restrictions	Syntax
error text variable	An SPL variable that contains the error text	The SPL variable must be a character data type and must be valid in the statement block.	Identifier, p. 1-966
ISAM error	A variable or expression that represents an ISAM error number. The default value is 0.	The variable or expression must evaluate to a SMALLINT value. You can place a minus sign before the error number.	Expression, p. 1-880
SQL error	A variable or expression that represents an SQL error number	The variable or expression must evaluate to a SMALLINT value. You can place a minus sign before the error number.	Expression, p. 1-880

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:

 RAISE EXCEPTION -9999, 0, 'You broke the rules';

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.

	FOREACH SELECT c1 INTO a FROM t

	IF a < 0 THEN

	RAISE EXCEPTION 9999-- emergency exit

	END IF

	END FOREACH

RETURN

Syntax

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.

CREATE FUNCTION two_returns (stockno INT)


	RETURNING CHAR (15);

	DEFINE des CHAR(15);

	ON EXCEPTION (-272) 	 
		-- if user doesn't have select privs...

		RETURN;				-
			- return no values.

	END EXCEPTION;

	SELECT DISTINCT descript INTO des FROM stock 

			WHERE stocknum = stockno;

	RETURN des;


END FUNCTION;

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

ESQL

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.

CREATE FUNCTION sequence (limit INT, backwards INT)


	RETURNING INT;

	DEFINE i INT;



	FOR i IN (1 TO limit STEP 1)

		RETURN i WITH RESUME;

	END FOR



	IF backwards = 0 THEN

		RETURN;

	END IF



	FOR i IN (limit TO 1 STEP -1)

		RETURN i WITH RESUME;

	END IF


END FUNCTION; -- sequence

Syntax

Element	Purpose	Restrictions	Syntax
expression	Any expression that is a user-executable operating-system command	You cannot specify that the command run in the background.	Operating-system dependent
SPL variable	An SPL variable that contains a valid operating-system command	The SPL variable must be of CHAR, VARCHAR, NCHAR, NVARCHAR, or CHARACTER VARYING data type,	Identifier, p. 1-966

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

For information on the operating-system commands that set environment variables, see Chapter 3 of the Informix Guide to SQL: Syntax.

Examples of the SYSTEM Statement in SPL Routines

CREATE PROCEDURE sensitive_update()

	.

	.

	.

	LET mailcall = 'mail headhoncho < alert'

	-- code that evaluates if operator tries to execute a

	-- certain command, then sends email to system

	-- administrator

	SYSTEM mailcall

	.

	.

	.

END PROCEDURE; -- sensitive_update

CREATE PROCEDURE sensitive_update2()
	DEFINE user1 char(15);
	DEFINE user2 char(15);
	LET user1 = 'joe';
	LET user2 = 'mary';
	.

	.

	.

	-- code that evaluates if operator tries to execute a

	-- certain command, then sends email to system

	-- administrator
	SYSTEM 'mail -s violation' ||user1 || ' ' || user2
					 || '< violation_file';
	.

	.

	.

END PROCEDURE;

Syntax

Tracing prints the current values of the following items:

• Variables
• Routine arguments
• Return values
• SQL error codes
• ISAM error codes

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

TRACE OFF

TRACE PROCEDURE

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

CREATE PROCEDURE tracing ()

	DEFINE i INT;

BEGIN

	ON EXCEPTION IN (1)

	END EXCEPTION; -- do nothing

	TRACE OFF;

	SET DEBUG FILE TO '/tmp/foo.trace';

	TRACE 'Forloop starts';

	FOR i IN (1 TO 1000)

		BEGIN

			TRACE 'FOREACH starts';

			FOREACH SELECT...INTO a FROM t

				IF <some condition> THEN

					RAISE EXCEPTION 1    -- emergency exit

				END IF

			END FOREACH



			-- return some value

		END

	END FOR

	

	-- do something

END; 

END PROCEDURE

PROCEDURE testproc()

	DEFINE i INT;



	TRACE OFF;

	SET DEBUG FILE TO '/tmp/test.trace';

	TRACE 'Entering foo';



	TRACE PROCEDURE;

	LET i = testtoo();



	TRACE ON;

	LET i = i + 1;



	TRACE OFF;

	TRACE 'i+1 = ' || i+1;

	TRACE 'Exiting testproc';

	

	SET DEBUG FILE TO '/tmp/test2.trace';



END PROCEDURE;

WHILE

Syntax

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.

CREATE PROCEDURE simp_while()

	DEFINE i INT;

	DEFINE pf_name CHAR(15);

	WHILE EXISTS (SELECT fname INTO pf_name FROM customer

		 WHERE customer_num > 400)

		DELETE FROM customer WHERE id_2 = 2;

	END WHILE



	LET i = 1;

	WHILE i < 10

		INSERT INTO tab_2 VALUES (i);

		LET i = i +1;

	END WHILE;

END PROCEDURE;