SQL Statements Valid in SPL Statement Blocks

The diagram for the Statement Block refers to this section. Most SQL statements are valid in SPL statement blocks, except for the statements that are listed below. The following SQL statements are not valid in an SPL statement block:
  • CLOSE DATABASE
  • CONNECT
  • CREATE DATABASE
  • CREATE FUNCTION
  • CREATE FUNCTION FROM
  • CREATE PROCEDURE
  • CREATE PROCEDURE FROM
  • CREATE ROUTINE FROM
  • DATABASE
  • DISCONNECT
  • DROP DATABASE
  • EXECUTE
  • FLUSH
  • INFO
  • LOAD
  • OUTPUT
  • PUT
  • RENAME DATABASE
  • SET AUTOFREE
  • SET CONNECTION
  • UNLOAD
  • UPDATE STATISTICS
For example, you cannot close the current database or connect to a new database within an SPL routine. Similarly, you cannot drop the current SPL routine within the same routine. You can, however, drop another SPL routine.
Only two forms of the SELECT statement are valid in queries within SPL routines:
  • You can use the INTO TEMP clause to put the result 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.

When you include the ORDER BY clause in the SELECT ... INTO TEMP or the SELECT ... INTO variable statement, you imply that the query returns more than one row. The database server issues an error if you specify the ORDER BY clause without a FOREACH loop to process the returned rows individually within the SPL routine.

If an SPL routine is called as part of a data-manipulation language (DML) statement, additional restrictions exist. For more information, see Restrictions on SPL Routines in Data-Manipulation Statements.