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.

Start of changeSQL 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. End of change

SQL control statements

Start of changeSQL 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.End of change

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

Start of changeControl statements are supported in native SQL procedures, compiled SQL functions, and advanced triggers.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 (SQL - native) statement. An SQL procedure can be changed. A new SQL routine body can be specified in an ALTER PROCEDURE (SQL - native) statement.
  • Start of changeTriggers 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.End of change

Start of changeThe 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.End of change

Start of changeThe 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.End of change

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