Inline SQL procedural language (SQL PL)

Inline SQL PL is a subset of SQL PL features that can be used in compound SQL (inlined) statements.

Compound SQL (inlined) statements can be executed independently or can be used to implement the body of a trigger, SQL function, or SQL method. Compound SQL (inlined) statements can be executed independently from the Db2® CLP when it is in interactive mode to provide support for a basic SQL scripting language.

Inline SQL PL is described as "inline", because the logic is expanded into and executed with the SQL statements that reference them.

The following SQL PL statements are considered to be part of the set of inline SQL PL statements:
  • Variable related statements
    • DECLARE <variable>
    • DECLARE <condition>
    • SET statement (assignment statement)
  • Conditional statements
    • IF
    • CASE expression
  • Looping statements
    • FOR
    • WHILE
  • Transfer of control statements
    • GOTO
    • ITERATE
    • LEAVE
    • RETURN
  • Error management statements
    • SIGNAL
    • GET DIAGNOSTICS

Other SQL PL statements that are supported in SQL procedures are not supported in compound SQL (inlined) statements. Cursors and condition handlers are not supported in inline SQL PL and therefore neither is the RESIGNAL statement.

Because inline SQL PL statements must be executed in compound SQL (inlined) statements, there is no support for PREPARE, EXECUTE, or EXECUTE IMMEDIATE statements.

Also, because ATOMIC must be specified in a compound SQL (inlined) statement that is dynamically prepared or executed, all or none of the member statements must commit successfully. Therefore the COMMIT and ROLLBACK statements are not supported either.

As for the LOOP and REPEAT statements, the WHILE statement can be used to implement equivalent logic.

Standalone scripting with inline SQL PL consists of executing a compound SQL (inlined) statement that is dynamically prepared or executed within a Command Line Processor (CLP) script or directly from a CLP prompt. Compound SQL (inlined) statements that are dynamically prepared or executed are bounded by the keywords BEGIN and END and must end with a non-default terminator character. They can contain SQL PL and other SQL statements.

Because inline SQL PL statements are expanded within the SQL statements that reference them rather than being individually compiled, there are some minor performance considerations that should be considered when you are planning on whether to implement your procedural logic in SQL PL in an SQL procedure or with inline SQL PL in a function, trigger, or compound SQL (compiled) statement that is dynamically prepared or executed.