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.
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.
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.
- 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 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
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.
- Specifies an SQL statement to be executed within the compound SQL (inlined) statement.
- Declares a variable that is local to the compound SQL (inlined)
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
- 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.
- 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.
- Specifies NULL as the default for the SQL variable.
- Specifies a constant as the default for the SQL variable.
- Declares a condition name and corresponding SQLSTATE value.
- 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'.
- 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
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.
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.
This creates a table called TARGET to contain the cleansed data.
CREATE TABLE TARGET (PK INTEGER NOT NULL PRIMARY KEY, C1 INTEGER)
This creates a table called EXCEPT to contain the exceptions.
CREATE TABLE EXCEPT (PK INTEGER NOT NULL PRIMARY KEY, C1 INTEGER)
This creates a table called SOURCE to hold the data that is to be cleansed.
CREATE TABLE SOURCE (PK INTEGER NOT NULL PRIMARY KEY, C1 INTEGER)
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
INSERT INTO SOURCE (PK, C1) VALUES (1, -5), (2, NULL), (3, 1200), (4, 23), (5, 10), (6, 876)
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
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.
DROP FUNCTION DISCRETIZE DROP TABLE SOURCE DROP TABLE TARGET DROP TABLE EXCEPT