Dynamic Compound Statement

Inline SQL PL is described as "inline" because the logic is expanded into and executed with the SQL statements that reference them. In this appendix, we look at statements and elements supported in inline SQL PL. For thorough discussions and examples of how it can be used, refer to Chapter 9, "User-Defined Functions and Triggers."

From the syntax diagram presented in Figure B.1, you can see that the dynamic compound statement must be wrapped inside a BEGIN ATOMIC ... END block. This ensures that either all or none of the statements inside the block will be committed to the database. Optionally, a label can be used to name the atomic block.

Figure B.1. Syntax diagram of a dynamic compound statement.

 dynamic-compound-statement >>-+-------------+--BEGIN ATOMIC-------------------------------->    '-label:------' >--+-----------------------------------------+------------------>    | .-------------------------------------. |    | V                                     | |    '---+-| SQL-variable-declaration |-+--;-+-'        '-| condition-declaration |----'    .-,--------------------------.    V                            | >----SQL-procedure-statement--;-+--END--+-------+--------------><                                         '-label-' SQL-variable-declaration:            .-,-----------------.             V                   | |--DECLARE----SQL-variable-name-+--data-type-------------------->    .-DEFAULT NULL------------. >--+-------------------------+----------------------------------|    '-DEFAULT--default-values-' condition-declaration: |--DECLARE--condition-name--CONDITION--FOR---------------------->                .-VALUE-.    .-SQLSTATE--+-------+-. >--+---------------------+--string-constant---------------------| 

In the declaration section, only SQL variable and condition declarations are supported. This means that you cannot declare cursors and condition handlers. You may ask what good is it to declare conditions without the ability to declare handlers. In inline SQL PL, a condition will be useful when you want to raise an error with a SIGNAL statement using a named condition.

As for the SQL procedure statements supported in inline SQL PL, there are few restrictions. Refer to Figure B.2 for a complete list of supported SQL procedure statements.

Figure B.2. SQL procedure statements supported in inline SQL PL.

 CALL                                  IF CASE                                  ITERATE DECLARE <variable>                    LEAVE DECLARE <condition>                   RETURN FOR                                   SET GET DIAGNOSTICS                       SIGNAL GOTO                                  WHILE 

Most of them are fairly straightforward, but a few statements warrant some discussion. The CALL statement enables the ability to CALL stored procedures from within triggers, SQL UDFs, SQL methods, and stand-alone code. This significantly extends the power of these objects and increases the reusability of stored procedures. For example, you can now indirectly handle conditions in an SQL UDF by calling a stored procedure that contains error-handling logic.

Because cursor declaration is not supported in inline SQL PL, cursor manipulations such as positioned updates and deletes are not possible. Rather than using cursors explicitly, you can alternatively use the FOR statement to loop through the result set returned from the specified SELECT statement. Here is an example extracted from Chapter 4, "Using Flow of Control Statements," that uses a FOR loop to iterate through all the rows returned from the SELECT statement. This method gives you the same result as declaring, opening, and fetching a cursor. In fact, a read-only cursor is declared under the cover when the following example is executed:

Figure B.3. An example of a FOR statement extracted from Chapter 4.

 FOR v_row AS     SELECT firstnme, midinit, lastname       FROM employee     DO         SET v_fullname = v_row.lastname || ', ' ||                          v_row.firstnme || ' ' || v_row.midinit;         INSERT INTO tname VALUES (v_fullname); END FOR 

The SET statement support in inline SQL PL is slightly different from what it can do inside an SQL procedure. As you may already know, the SET statement is used to assign a value to a variable such as this:

 SET v1 = 3; 

You can also assign a result value from a SELECT statement to a variable:

 SET v_salary = (SELECT salary FROM employee WHERE empno='000010'); 

Note that the SELECT statement must return only a single row; otherwise, the statement will raise an error. These statements are both valid in SQL procedures and inline SQL PL. In an SQL procedure, if you want to assign values to one or more variables, you must use the SELECT ... INTO ... statementfor example,

 SELECT salary, bonus INTO v_salary, v_bonus FROM employee WHERE empno='000010'; 

However, in inline SQL PL, the SELECT ... INTO ... statement is not supported. You use the following SET statement to reach the same result instead. Remember that the SELECT statement shown previously must return only one row.

 SET v_salary, v_bonus = (SELECT salary, bonus FROM employee WHERE empno='000010'); 

To summarize when to use which statement, refer to Table B.1.

Table B.1. Support SET and SELECT ... INTO ... Statements in SQL PL and Inline SQL PL

Sample SQL Statement	Supported in SQL PL	Supported in Inline SQL PL
SET v1 = 3	Y	Y
SET v1 = (SELECT c1 FROM t1 FETCH 1 ROW ONLY)	Y
SET v1, v2 = (SELECT c1, c2 FROM t1 FETCH 1 ROW ONLY)	N	Y
SELECT c1, c2 INTO v1, v2 FROM t1 FETCH 1 ROW ONLY	Y	N

Besides showing the list of supported statements allowed in inline SQL PL, Figure B.4 calls out the unsupported statements.

Figure B.4. SQL procedure statements not supported in inline SQL PL.

 ALLOCATE CURSOR                  LOOP ASSOCIATE LOCATORS               REPEAT DECLARE <cursor>                 RESIGNAL DECLARE ... HANDLER              COMMIT PREPARE                          ROLLBACK EXECUTE EXECUTE IMMEDIATE 

Looking at Figure B.4, you already know the story about cursors and handlers. Therefore, any statements related to cursors and handlers are not supported.

Because the statements are dynamic, it is rational that the support of the PREPARE, EXECUTE, and EXECUTE IMMEDIATE statements are not needed. As for the LOOP and REPEAT statements, you can use the WHILE loop to implement the same logic. RESIGNAL is not supported because it can only be used within a condition handler for which it is not allowed in inline SQL PL.

Recall that a dynamic compound statement must be atomic so that all or none of the member statements commit successfully. Therefore, it does not make sense to commit or roll back any particular statement inside the block.