Compound SQL (inlined) statement

A compound SQL (inlined) statement is a compound SQL statement that is inlined at run time within another SQL statement. Compound SQL (inlined) statements have the property of being atomically executed; if the execution of any of the statements raises an error, the full statement is rolled back.

Invocation

This statement can be embedded in a trigger, SQL function, or SQL method, or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the compound statement.

Syntax

Read syntax diagramSkip visual syntax diagramlabel:1BEGIN ATOMICSQL-variable-declarationcondition-declaration;,SQL-statement;END label
SQL-variable-declaration
Read syntax diagramSkip visual syntax diagramDECLARE,SQL-variable-namedata-typeDEFAULT NULLDEFAULTconstant
condition-declaration
Read syntax diagramSkip visual syntax diagramDECLAREcondition-nameCONDITION FOR SQLSTATEVALUE string-constant
SQL-statement
Read syntax diagramSkip visual syntax diagramCALLFORWITH,common-table-expressionfullselectGET DIAGNOSTICSIFINSERTITERATELEAVEMERGE RETURNsearched-deletesearched-updateSET VariableSIGNALWHILE
Notes:
  • 1 A label can only be specified when the statement is in a function, method, or trigger definition.

Description

label
Defines the label for the code block. If the beginning label is specified, it can be used to qualify SQL variables declared in the compound SQL (inlined) statement and can also be specified on a LEAVE statement. If the ending label is specified, it must be the same as the beginning label.
ATOMIC
ATOMIC indicates that, if an error occurs in the compound statement, all SQL statements in the compound statement will be rolled back, and any remaining SQL statements in the compound statement are not processed.

If the ATOMIC keyword is specified in an SQL function in a module or an SQL procedure, the compound statement is processed as a compound SQL (compiled) statement.

SQL-statement
Specifies an SQL statement to be executed within the compound SQL (inlined) statement.
SQL-variable-declaration
Declares a variable that is local to the compound SQL (inlined) statement.
SQL-variable-name
Defines the name of a local variable. SQL variable names are converted to uppercase. The name cannot be the same as:
  • Another SQL variable within the compound statement
  • A parameter name
If an SQL statement contains an identifier with the same name as an SQL variable and a column reference, the identifier is interpreted as a column.
data-type
Specifies the data type of the variable. The XML data type is not supported in a compound SQL (inlined) statement used in a trigger, in a method, or as a stand-alone statement (SQLSTATE 429BB). The XML data type is supported when the compound SQL (inlined) statement is used in an SQL function body.
DEFAULT
Defines the default for the SQL variable. The variable is initialized when the compound SQL (inlined) statement is executed. The default value must be assignment-compatible with the data type of the variable. If a default value is not specified, the default for the SQL variable is initialized to the null value.
NULL
Specifies NULL as the default for the SQL variable.
constant
Specifies a constant as the default for the SQL variable.
condition-declaration
Declares a condition name and corresponding SQLSTATE value.
condition-name
Specifies the name of the condition. The condition name must be unique within the compound statement in which it is declared, excluding any declarations in compound statements that are nested within that compound statement (SQLSTATE 42734). 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 (SQLSTATE 42737).
FOR SQLSTATE string-constant
Specifies the SQLSTATE associated with the condition. The string-constant must be specified as five characters enclosed by single quotation marks, and the SQLSTATE class (the first two characters) must not be '00'.

Notes

  • Compound SQL (inlined) statements are compiled as one single statement. This statement is effective for short scripts involving little control flow logic but significant data flow. For larger constructs with requirements for nested control flow or condition handling, a better choice is to use the compound SQL (compiled) statement or an SQL procedure.
  • A procedure called within a compound statement must not issue a COMMIT or a ROLLBACK statement (SQLSTATE 42985).
  • Table access restrictions: If a procedure is defined as READS SQL DATA or MODIFIES SQL DATA, no statement in the procedure can access a table that is being modified by the compound statement that invoked the procedure (SQLSTATE 57053). If the procedure is defined as MODIFIES SQL DATA, no statement in the procedure can modify a table that is being read or modified by the compound statement that invoked the procedure (SQLSTATE 57053).
  • XML assignments: Assignment to parameters and variables of data type XML is done by reference in SQL function bodies.

    When XML values are passed by reference, any input node trees are used directly. This direct usage preserves all properties, including document order, the original node identities, and all parent properties.

  • Isolation level: If a select-statement, fullselect, or subselect specifies an isolation-clause, the clause is ignored and a warning is returned.

Example

This example illustrates how inline SQL PL can be used in a data warehousing scenario for data cleansing.

The example introduces three tables. The TARGET table contains the cleansed data. The EXCEPT table stores rows that cannot be cleansed (exceptions) and the SOURCE table contains the raw data to be cleansed.

A simple SQL function called DISCRETIZE is used to classify and modify the data. It returns the null value for all bad data. The compound SQL (inlined) statement then cleanses the data. It walks all rows of the SOURCE table in a FOR-loop and decides whether the current row gets inserted into the TARGET or the EXCEPT table, depending on the result of the DISCRETIZE function. More elaborate mechanisms (multistage cleansing) are possible with this technique.

The same code can be written using an SQL Procedure or any other procedure or application in a host language. However, the compound SQL (inlined) statement offers a unique advantage in that the FOR-loop does not open a cursor and the single row inserts are not really single row inserts. In fact, the logic is effectively a multi-table insert from a shared select.

This is achieved by compilation of the compound SQL (inlined) statement as a single statement. Similar to a view whose body is integrated into the query that uses it and then is compiled and optimized as a whole within the query context, the database optimizer compiles and optimizes both the control and data flow together. The whole logic is therefore executed within the runtime environment of the database. No data is moved outside of the core database engine, as would be done for a procedure.

The first step is to create the required tables:
   CREATE TABLE TARGET
     (PK INTEGER NOT NULL
     PRIMARY KEY, C1 INTEGER)
This creates a table called TARGET to contain the cleansed data.
   
   CREATE TABLE EXCEPT
     (PK INTEGER NOT NULL
     PRIMARY KEY, C1 INTEGER)
This creates a table called EXCEPT to contain the exceptions.
   CREATE TABLE SOURCE
     (PK INTEGER NOT NULL
     PRIMARY KEY, C1 INTEGER)
This creates a table called SOURCE to hold the data that is to be cleansed.
Next, a function named DISCRETIZE is created to cleanse the data by throwing out all values outside [0..1000] and aligning them to steps of 10.
   CREATE FUNCTION DISCRETIZE(RAW INTEGER) RETURNS INTEGER
     RETURN CASE
       WHEN RAW < 0 THEN CAST(NULL AS INTEGER)
       WHEN RAW > 1000 THEN NULL
       ELSE ((RAW / 10) * 10) + 5
     END
Then the values are inserted:
   INSERT INTO SOURCE (PK, C1) 
     VALUES (1,   -5),
            (2, NULL),
            (3, 1200),
            (4,   23),
            (5,   10),
            (6,  876)
Invoke the function:
     BEGIN ATOMIC
       FOR ROW AS
         SELECT PK, C1, DISCRETIZE(C1) AS D FROM SOURCE
       DO
         IF ROW.D IS NULL THEN
           INSERT INTO EXCEPT VALUES(ROW.PK, ROW.C1);
         ELSE
           INSERT INTO TARGET VALUES(ROW.PK, ROW.D);
         END IF;
       END FOR;
     END
And test the results:
   
   SELECT * FROM EXCEPT ORDER BY 1
   PK          C1
   ----------- -----------
             1          -5
             2           -
             3        1200
      3 record(s) selected.

   SELECT * FROM TARGET ORDER BY 1
   PK          C1
   ----------- -----------
             4          25
             5          15
             6         875
      3 record(s) selected.
The final step is to clean up:
   DROP FUNCTION DISCRETIZE
   DROP TABLE SOURCE
   DROP TABLE TARGET
   DROP TABLE EXCEPT