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
Read syntax diagramSkip visual syntax diagramassignment-statementCALL-statementCASE-statementcompound-statementFOR-statementGET DIAGNOSTICS-statementGOTO-statementIF-statementITERATE-statementLEAVE-statementLOOP-statementPIPE-statementREPEAT-statementRESIGNAL-statementRETURN-statementSIGNAL-statementWHILE-statement

Control statements are supported in SQL procedures, SQL functions, SQL triggers, Start of changeand compound (dynamic) statementsEnd of change.

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. Start of changeA compound (dynamic) statement is defined by specifying an SQL routine body on the compound (dynamic) statement.End of change

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, trigger, or Start of changeglobal variableEnd of change is created, SQL creates temporary source files (QTEMP/QSQLSRC and QTEMP/QSQLT00000) that will contain C source code with embedded SQL statements. Start of changeWhen SQL creates these temporary source files, a record length of 160 is used and the CCSID value of the source statement is set as the CCSID value for the new file.End of change If either of these source files exist, they will be modified if needed to have the same CCSID as the source. Start of changeThe record length of these source files should be 160 or unexpected results may occur. The name of the source file member is the same as the system name of the routine, trigger, or global variable.End of change 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 DIAGNOSTICS 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.