SQL control statements
Control statements are SQL statements that allow SQL to be used in a manner similar to writing a program in a structured programming language. SQL control statements provide the capability to control the logic flow, declare and set variables, and handle warnings and exceptions. Some SQL control statements include other nested SQL statements.
SQL-control-statement |--+-assignment-statement------+--------------------------------| +-CALL-statement------------+ +-CASE-statement------------+ +-compound-statement--------+ +-FOR-statement-------------+ +-GET DIAGNOSTICS-statement-+ +-GOTO-statement------------+ +-IF-statement--------------+ +-ITERATE-statement---------+ +-LEAVE-statement-----------+ +-LOOP-statement------------+ +-PIPE-statement------------+ +-REPEAT-statement----------+ +-RESIGNAL-statement--------+ +-RETURN-statement----------+ +-SIGNAL-statement----------+ '-WHILE-statement-----------'
Control statements are supported in SQL procedures,
SQL functions, SQL triggers 
and compound (dynamic)
statements
.
SQL procedures are created by specifying an SQL routine body on
the CREATE PROCEDURE statement. SQL functions are created by specifying
an SQL routine body on the CREATE FUNCTION statement. SQL routines
are SQL procedures or SQL functions. SQL triggers are created by specifying
an SQL routine body on the CREATE TRIGGER statement. A
compound (dynamic) statement is defined by specifying an SQL routine
body on the compound (dynamic) statement.
An SQL procedure can also be altered. A new SQL routine body can be specified on the ALTER PROCEDURE statement. An SQL function can also be altered. A new SQL routine body can be specified on the ALTER FUNCTION statement.
An SQL routine body must be a single SQL statement which may be an SQL control statement.
The SQL routine body is the executable part of the procedure, function, or trigger that is transformed by the database manager into a program or service program. When an SQL routine or trigger is created, SQL creates a temporary source file (QTEMP/QSQLSRC and QTEMP/QSQLT00000) that will contain C source code with embedded SQL statements. If either of these source files exist, they will be modified if needed to have the same CCSID as the source. If DBGVIEW(*SOURCE) is specified, SQL creates the root source for the routine or trigger in source file QSQDSRC in the same library as the procedure, function or trigger.
An SQL procedure or SQL trigger is created as a program (*PGM) object using the CRTPGM command. An SQL function is created as a service program (*SRVPGM) object using the CRTSRVPGM command. The program or service program is created in the library that is the implicit or explicit qualifier of the procedure, function, or trigger name.
When the program or service program is created, the SQL statements other than certain control statements become embedded SQL statements in the program or service program. The CALL, SIGNAL, RESIGNAL, and GET DIAGNOSTIC control statements also become embedded SQL statements in the program or service program.
The specified procedure or function is registered in the SYSROUTINES and SYSPARMS catalog tables, and an internal link is created from SYSROUTINES to the program. When the procedure is called using the SQL CALL statement or when the function is invoked in an SQL statement, the program associated with the routine is called. The specified SQL trigger is registered in the SYSTRIGGER catalog table.
- References to SQL parameters and SQL variables
SQL parameters and SQL variables can be referenced anywhere in an SQL procedure statement where an expression or variable can be specified. - References to SQL condition names
The name of an SQL condition can be the same as the name of another SQL condition declared in the same routine. This can occur when the two SQL conditions are declared in different compound-statements. - References to SQL cursor names
The name of an SQL cursor can be the same as the name of another SQL cursor declared in the same routine. This can occur when the two SQL cursors are declared in different compound-statements. The cursor name specified in a FOR statement can be the same as the name of another SQL cursor declared in the same compound-statement. - References to SQL labels
Labels can be specified at the beginning of most SQL procedure statements. If a label is specified on an SQL procedure statement, it must be unique from other labels within the same scope. A label must not be the same as any other label within the same compound statement, must not be the same as a label specified on the compound statement itself, and if the compound statement is nested within another compound statement, the label must not be the same as the label specified on any higher level compound statement. The label must not be the same as the name of the SQL function, SQL procedure, or SQL trigger that contains the SQL procedure statement. - Summary of ′name′ scoping in nested compound statements
Nested compound statements can be used within an SQL routine to define the scope of SQL variable declarations, cursors, condition names, and condition handlers. - SQL-procedure-statement
An SQL control statement may allow multiple SQL statements to be specified within the SQL control statement. These statements are defined as SQL procedure statements. - assignment-statement
The assignment-statement assigns a value to an SQL parameter or SQL variable. - CALL statement
The CALL statement invokes a procedure. The syntax of CALL in an SQL function, SQL procedure, or SQL trigger is a subset of what is supported as a CALL statement in other contexts. - CASE statement
The CASE statement selects an execution path based on multiple conditions. - compound-statement
A compound statement groups other statements together in an SQL procedure. A compound statement allows the declaration of SQL variables, cursors, and condition handlers. - FOR statement
The FOR statement executes a statement or group of statements for each row of a table. - GET DIAGNOSTICS statement
The GET DIAGNOSTICS statement obtains information about the previous SQL statement that was executed. The syntax of GET DIAGNOSTICS in an SQL function, SQL procedure, or SQL trigger is a subset of what is supported as a GET DIAGNOSTICS statement in other contexts. - GOTO statement
The GOTO statement branches to a user-defined label within an SQL function, SQL procedure, or SQL trigger. - IF statement
The IF statement executes different sets of SQL statements based on the result of search conditions. - ITERATE statement
The ITERATE statement causes the flow of control to return to the beginning of a labelled loop. - LEAVE statement
The LEAVE statement continues execution by leaving a block or loop. - LOOP statement
The LOOP statement repeats the execution of a statement or a group of statements. - PIPE statement
The PIPE statement returns one row from a table function. An SQL table function that uses a PIPE statement is referred to as a pipelined function. - REPEAT statement
The REPEAT statement executes a statement or group of statements until a search condition is true. - RESIGNAL statement
The RESIGNAL statement is used within a condition handler to re-raise the current condition, or raise an alternate condition so that it can be processed at a higher level. It causes an exception, warning, or not found condition to be returned, along with optional message text. - RETURN statement
The RETURN statement returns from a routine. For SQL functions, it returns the result of the function. For SQL non-pipelined
table functions, it returns a table as the result of the function.
For SQL pipelined table functions, it indicates the end of the result
table has been reached. For an SQL procedure, it optionally returns
an integer status value. - SIGNAL statement
The SIGNAL statement signals an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE and optional condition-information-items. The syntax of SIGNAL in an SQL function, SQL procedure, or SQL trigger is a similar to what is supported as a SIGNAL statement in other contexts. - WHILE statement
The WHILE statement repeats the execution of a statement while a specified condition is true.