Informix Guide to SQL: Syntax Segments

Use a statement block to specify the operations to take place when the SPL routine is called. Use the statement block segment whenever you see a reference to statement block in a syntax diagram.

If the statement block portion of the statement is empty, no operation takes place when you call the SPL routine.

Warning: When you specify a date value in an expression in any statement in the statement block, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on how the database server interprets the date value. When you specify a 2-digit year, the DBCENTURY environment variable can affect how the database server interprets the date value, so the routine might produce unpredictable results. For more information on the DBCENTURY environment variable, see the Informix Guide to SQL: Reference.

SQL Statements Not Allowed in the Statement Block

The diagram for the Statement Block refers to this section.

The following table lists the SQL statements that are not allowed in an SPL routine.

For example, you cannot close the current database or select a new database within an SPL routine. Likewise you cannot drop the current SPL routine within the routine. You can, however, drop another SPL routine.

You can use a SELECT statement in only two cases:

• You can use the INTO TEMP clause to put the results of the SELECT statement into a temporary table.
• You can use the SELECT... INTO form of the SELECT statement to put the resulting values into SPL variables.

If an SPL routine is later to be called as part of a data manipulation statement, additional restrictions exist. For more information, see Restrictions on an SPL Routine Called in a Data Manipulation Statement.

The diagram for the Statement Block refers to this section.

You can use any of the following SPL statements in the statement block:

You can use the BEGIN-END keywords to limit the scope of SPL variables and exception handlers. Variable declarations and exception handlers defined inside a BEGIN-END statement block are local to that statement block and are not accessible from outside the statement block.

The following code sample demonstrates the use of a BEGIN-END statement block to define the scope of a variable:

CREATE DATABASE demo;

CREATE TABLE tracker (
	who_submitted CHAR(80), 
-- Show which part of code was running.
	value INT, 
-- Show value of the variable.
    sequential_order SERIAL 
-- Show order in which statements were executed.
    );

CREATE PROCEDURE demo_local_var()
DEFINE var1 INT;
DEFINE var2 INT;
   LET var1 = 1;
   LET var2 = 2;
   INSERT INTO tracker (who_submitted, value) 
    VALUES ('var1 param before sub-block', var1);

BEGIN
	DEFINE var1 INT;   -- same name as global parameter.
	LET var1 = var2;
	INSERT INTO tracker (who_submitted, value) 
	VALUES ('var1 var defined inside the "IF/BEGIN".', var1);
END

INSERT INTO tracker (who_submitted, value) 
	VALUES ('var1 param after sub-block (unchanged!)', var1);
END PROCEDURE;

EXECUTE PROCEDURE demo_local_var();

SELECT sequential_order, who_submitted, value 
 FROM tracker 
 ORDER BY sequential_order;

This example defines three independent variables, two of which are named var1. (The example uses two different variables with the same name to illustrate how a statement block limits the scope of a variable. In general, using the same name for different variables is not a good idea.)

Because of the statement block, only one var1 variable is in scope at a time.

The var1 variable that is defined inside the statement block is the only var1 variable that can be referenced from within the statement block.

The var1 variable that is defined outside the statement block can not be referenced from within the statement block. Because this variable is out of scope, it is unaffected by the change in value to the var1 variable that takes place inside the BEGIN-END statement block. After all the statements run, the outer var1 still has a value of 1.

The var2 variable shows that an inner statement block does not lose access to outer variables that were not explicitly superseded. The outer variable var2 is still in scope from within the statement block because it was not superseded by a block-specific variable.

If an SPL routine is called as part of an INSERT, UPDATE, DELETE, or SELECT statement, the routine cannot execute any statement in the following list.

If an SPL routine is called as part of an INSERT, UPDATE, DELETE, or SELECT statement, the routine can execute only the following statements:

However, if the SPL routine is called within a statement that is not a data manipulation statement (namely EXECUTE FUNCTION or EXECUTE PROCEDURE), the routine can execute any statement that does not appear in the list that appears in SQL Statements Not Allowed in the Statement Block.

You can use the BEGIN WORK and COMMIT WORK statements in SPL routines. You can start a transaction, finish a transaction, or start and finish a transaction in an SPL routine. If you start a transaction in a routine that is executed remotely, you must finish the transaction before the routine exits.

You can use roles with SPL routines. You can execute role-related statements (CREATE ROLE, DROP ROLE, and SET ROLE) and SET SESSION AUTHORIZATION statements within an SPL routine. You can also grant privileges to roles with the GRANT statement within an SPL routine. Privileges that a user has acquired through enabling a role or by a SET SESSION AUTHORIZATION statement are not relinquished when an SPL routine is executed.

For further information about roles, see the CREATE ROLE, DROP ROLE, GRANT, REVOKE, and SET ROLE statements.