SQL procedural language (SQL PL)

SQL can be used as a structured programming language to write the body for SQL functions, native SQL procedures, and advanced triggers. This is known as the SQL procedural language (SQL PL). SQL PL includes a set of SQL control statements.

SQL procedures and SQL functions are collectively referred to as SQL routines. 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. Triggers are created by specifying an SQL trigger body on the CREATE TRIGGER statement.

SQL control statements

SQL control statements are SQL statements that allow SQL to be used as 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 statementREPEAT statementRESIGNAL statementRETURN statementSIGNAL statementWHILE statement

Control statements are supported in native SQL procedures, compiled SQL functions, and advanced triggers.

  • 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 (SQL - native) statement. An SQL procedure can be changed. A new SQL routine body can be specified in an ALTER PROCEDURE (SQL - native) statement.
  • Triggers are created by specifying a trigger body on the CREATE TRIGGER (advanced) statement. See CREATE TRIGGER statement (advanced trigger). Advanced triggers can be changed. The body of a trigger can be changed by specifying the OR REPLACE clause on a CREATE TRIGGER (advanced) statement, or with an ALTER TRIGGER (advanced) statement.

The SQL routine body or trigger body is the executable part of the routine or trigger and is transformed by Db2 into a program. The body must be a single SQL statement, which might be an SQL control statement.

The remainder of this section contains a description of the control statements that are supported by SQL routines and advanced triggers, and includes syntax diagrams, semantic descriptions, usage notes, and examples of the use of the statements that constitute the SQL routine body or trigger body. In addition, you can find information about referencing SQL parameters and variables in References to SQL parameters and variables in SQL PL.

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