SQL control statements for SQL routines

SQL control statements for SQL routines can be used in SQL functions and native SQL procedures. 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------------+   
   +-REPEAT statement----------+   
   +-RESIGNAL statement--------+   
   +-RETURN statement----------+   
   +-SIGNAL statement----------+   
   '-WHILE statement-----------'

Start of change Control statements are supported in SQL functions and SQL procedures. End of change

SQL functions are created by specifying LANGUAGE SQL and an SQL routine body in a CREATE FUNCTION (compiled SQL) statement. An SQL function can be changed. A new SQL routine body can be specified in an ALTER FUNCTION (compiled SQL) statement.
SQL procedures are created by specifying LANGUAGE SQL and an SQL routine body in a CREATE PROCEDURE (native SQL) statement. An SQL procedure can be changed. A new SQL routine body can be specified in an ALTER PROCEDURE (native SQL) statement.

Start of change The SQL routine body is the executable part of the function or procedure and is transformed by DB2® into a program. End of change

Start of change The remainder of the topics about SQL control statements for SQL routines contain information about references to SQL parameters and variables, SQL condition names, SQL cursor names, labels, and reference information for the use of the statements that constitute the SQL routine body. End of change

The two common elements that are used in describing specific SQL control statements are:

SQL control statements as described above
SQL-procedure-statement

References to SQL parameters and SQL variables
SQL parameters and SQL variables can be referenced anywhere in the statement where an expression or a variable can be specified. Host variables cannot be specified in SQL routines. SQL parameters can be referenced anywhere in the routine and can be qualified with the routine name. SQL variables can be referenced anywhere in the compound statement in which they are declared, including any statement that is directly or indirectly nested within that compound statement.
References to SQL condition names
A condition name can only be referenced within the compound statement in which it is declared, including any compound statements that are nested within that compound statement. When there is a reference to a condition name, the condition that is declared in the innermost compound statement is the condition that is used.
References to SQL cursor names
A cursor name can only be referenced within the compound statement in which it is declared, including any compound statements that are nested within that compound statement.
References to labels
Labels can be specified on 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 or SQL procedure.
References to SQL statement names
The name of an SQL statement can be the same as the name of another SQL statement declared in the same routine.
Nested compound statements and scope of names
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 can 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 a SQL parameter or to an SQL variable.
CALL statement
The CALL statement invokes a stored procedure.
CASE statement
The CASE statement selects an execution path based on multiple conditions. A CASE statement operates in the same way as a CASE expression.
compound-statement
A compound statement groups other statements together in an SQL routine. A compound statement allows the declaration of SQL variables, cursors, and condition handlers.
FOR statement
The FOR statement executes a statement for each row of a table. An implicit compound statement is generated to implement the FOR statement.
GET DIAGNOSTICS statement
The GET DIAGNOSTICS statement obtains information about the previous SQL statement that was executed.
GOTO statement
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 labeled loop.
LEAVE statement
The LEAVE statement transfers program control out of a FOR, LOOP, REPEAT, WHILE, or compound statement.
LOOP statement
The LOOP statement executes a statement or group of statements multiple times.
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 resignal the condition that activated the handler, or to 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 is used to return from the routine.
SIGNAL statement
The SIGNAL statement is used to return an exception or warning condition. It causes an error or warning to be returned with the specified SQLSTATE, along with optional message text. The SIGNAL statement places the specified condition information in the cleared diagnostics area.
WHILE statement
The WHILE statement repeats the execution of a statement or group of statements while a specified condition is true.

Parent topic: SQL: The language of DB2