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

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

• CALL statement (Procedure call)
• CLOSE statement (Close cursor)
• DECLARE CURSOR (Declare cursor)
• DELETE statement (Delete row)
• FETCH statement (Fetch data)
• INSERT statement (Insert row)
• OPEN statement (Open cursor)
• PURGE TABLE statement (Delete all rows)
• 1-row SELECT statement (Retrieve one row)
• UPDATE statement (Update data)
• COMMIT statement (Normal termination of transaction)
• LOCK statement (Lock table)
• ROLLBACK statement (Cancel transaction)