General rules

Routine control SQL statements can be specified in a routine definition SQL procedure statement.

Table 7-1 shows the types and functions of routine control SQL.

Table 7-1 Types and functions of routine control SQL

TypeFunction
Assignment statement (Assign value)Assigns a value to an SQL variable or an SQL parameter.*
Compound statement (Execute multiple statements)Executes several SQL statements as a group, treating them as a single SQL statement.
IF statement (Execute by conditional branching)Executes SQL statements that meet a given set of conditions.
LEAVE statement(Exit statement)Exits from a compound statement or the WHILE statement and terminates the execution of the statement.
RETURN statement (Return function return value)Returns a return value from a function.
FOR statement (Repeat a statement on rows)Repeatedly executes the SQL statement on rows in the table.
WHILE statement (Repeat statements)Executes repeatedly a set of SQL statements.
WRITE LINE statement (Output character string to a file)Outputs a character string to a file.
SIGNAL statement (Signal an error)Signals an error.
RESIGNAL statement (Resignal an error)Resignals an error.

* For details about the assignment statement in an SQL procedure statement, see Assignment statement Format 1 (Assign a value to an SQL variable or SQL parameter).

In addition to the routine control SQL statements shown in Table 7-1, the following SQL statements, which cannot be specified in a function body, can be specified as SQL procedure statements in a routine: