Of all the SQL control statements, the compound statement is the easiest to work with and understand. Compound statements are used to group a set of related lines of code. You can declare variables, cursors, and condition handlers, and use flow of control statements within a compound statement. Cursors and condition handlers are discussed in Chapter 5, "Understanding and Using Cursors and Result Sets" and Chapter 6, "Condition Handling," respectively.
BEGIN and END are keywords that define a compound statement. The BEGIN keyword defines the starting line of code for the compound statement, while the END keyword defines the final line of code. Compound statements are used to control variable scoping and for executing more than a single statement where a single statement is expected, such as within a condition handler (this topic is explored more fully in Chapter 6, "Condition Handling").
On LUW and iSeries, for nested compound statements, each compound statement has its own scope. Only variables and the like that have been declared within the same compound statement or within enclosing compound statements can be seen. That is, statements within one compound statement may not be able to refer to variables and values that are declared within another compound statement, even if both compound statements are part of the same SQL procedure body.
Compound statements in DB2 for zSeries cannot be nested.
It is perfectly logical and, in most cases, completely valid to define as many compound statements as needed within an SQL procedure. These compound statements are typically used to introduce scoping and a logical separation of related statements.
There is a specific order for declaring variables, conditions, cursors, and handlers within a compound statement. Specifically, the order of declarations must proceed as follows:
BEGIN variable declarations condition declarations cursor declarations handler declarations assignment, flow of control, SQL statements, and other compound statements END
On iSeries and zSeries, variable and condition declarations can be mixed, but for portability of procedures, you should follow the order specified.
Don't worry if you are not familiar with some of these terms; they are discussed in greater detail later in the book. Also, notice that one or many compound statements can be nested within other compound statements. In such cases, the same order of declarations continues to apply at each level.
It is important to understand the type of variable scoping (or visibility) that occurs when a compound statement has been defined. Specifically:
Scoping is illustrated in Figure 4.1.
Figure 4.1. Variable scoping example for LUW and iSeries.
CREATE PROCEDURE nested_compound () LANGUAGE SQL SPECIFIC nested_compound -- applies to LUW and iSeries BEGIN -- (1) DECLARE v_outer1 INT; DECLARE v_outer2 INT; BEGIN -- (2) DECLARE v_inner1 INT; DECLARE v_inner2 INT; SET v_outer1 = 100; -- (3) SET v_inner1 = 200; -- (4) END; -- (5) SET v_outer2 = 300; -- (6) SET v_inner2 = 400; -- (7) END -- (8)
In the previous figure, Lines (1) and (8) define the outer compound statement, while Lines (2) and (5) define the inner compound statement.
On LUW, all statements, except the statement shown in Line (7), will succeed. This statement fails because an outer compound statement cannot see a variable declared within an inner compound statement. You will receive an SQLSTATE 42703 error with the message 'V_INNER2' is not valid in the context where it is used.
On iSeries, the error is caught at procedure create time, and the CREATE PROCEDURE statement fails with SQLSTATE 42618 with the message Variable V_INNER2 not defined or not usable.
Scoping can be especially useful in the case of looping and exception handling, allowing the program flow to jump from one compound statement to another.
The next few sections describe two distinct types of compound statements, each of which serve a different purpose.
NOT ATOMIC Compound Statement
The previous example illustrated a NOT ATOMIC compound statement and is the default type used in SQL procedures. If an unhandled error (that is, no condition handler has been declared for the SQLSTATE raised) occurs within the compound statement, any work that is completed before the error will not be rolled back, but will not be committed either. The group of statements can only be rolled back if the unit of work is explicitly rolled back using ROLLBACK or ROLLBACK TO SAVEPOINT. You can also COMMIT successful statements if it makes sense to do so.
The syntax for a NOT ATOMIC compound statement is shown in Figure 4.2.
Figure 4.2. The NOT ATOMIC compound statement syntax diagram.
.-NOT ATOMIC--. >>-+---------+--BEGIN----+-------------+------------------------> '-label:--' ... Misc. Statements; ... >-------------------------------------+---END--+--------+------>< '-label--'
The optional label is used to define a name for the code block. The label can be used to qualify SQL variables declared within the compound statement. If the ending label is used, it must be the same as the beginning label. You will learn more about labels in section, "Using Labels," in this chapter.
The use of the NOT ATOMIC keywords is optional, but usually suggested because it reduces ambiguity of the code.
The SQL procedure illustrated in Figure 4.3 demonstrates the non-atomicity of NOT ATOMIC compound statements.
Figure 4.3. An example of a NOT ATOMIC compound statement.
CREATE PROCEDURE not_atomic_proc () LANGUAGE SQL SPECIFIC not_atomic_proc -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries nap: BEGIN NOT ATOMIC -- Procedure logic INSERT INTO cl_sched (class_code, day) VALUES ('R11:TAA', 1); SIGNAL SQLSTATE '70000'; -- (1) INSERT INTO cl_sched (class_code, day) VALUES ('R22:TBB', 2); END nap
Although the SIGNAL statement on Line (1) has not been introduced, it is sufficient to understand that the statement is used to explicitly raise an error. Additionally, because this error is unhandled, the procedure will exit right after the error. More information can be found in Chapter 6, "Condition Handling".
After calling this procedure, you will see that although an error has been raised halfway through execution, the first INSERT successfully inserted a row into the atomic_test table. You need to realize, however, that the procedure itself does not issue either COMMIT or ROLLBACK implicitly.
ATOMIC Compound Statement
ATOMIC compound statements are supported in DB2 for LUW and iSeries only. In DB2 zSeries, the NOT ATOMIC compound statement is the default and the only type used in SQL procedures.
The ATOMIC compound statement, as the name suggests, can be thought of as a singular whole. If any unhandled error conditions arise within it, all statements that have executed up to that point are considered to have failed as well and are therefore rolled back.
On LUW, ATOMIC compound statements cannot be nested inside other ATOMIC compound statements. iSeries does not have this restriction.
In addition, you cannot use SAVEPOINTs or issue explicit COMMITs or ROLLBACKs from within an ATOMIC compound statement.
On LUW, COMMIT, ROLLBACK, SAVEPOINTs, and nested ATOMIC compound statements are not allowed within an ATOMIC compound statement.
On iSeries, COMMIT, ROLLBACK, and SAVEPOINTs are not allowed within an ATOMIC compound statement, although ROLLBACK TO SAVEPOINT may be specified.
The syntax to declare an ATOMIC compound statement is shown in Figure 4.4.
Figure 4.4. ATOMIC compound statement syntax diagram.
>>-+---------+--BEGIN ATOMIC------------------------------------> '-label:--' ... Misc. Statements; ... >-------------------------------------+---END--+--------+------>< '-label--'
A label is used in the same way as with a NOT ATOMIC compound statement.
The example in Figure 4.5 illustrates the behavior of an ATOMIC compound statement. It is quite similar to the NOT ATOMIC example shown in Figure 4.4, and only differs in name and in the fact that it uses an ATOMIC compound statement.
Figure 4.5. ATOMIC compound statement example for LUW and iSeries.
CREATE PROCEDURE atomic_proc () LANGUAGE SQL SPECIFIC atomic_proc -- applies to LUW and iSeries ap: BEGIN ATOMIC -- Procedure logic INSERT INTO cl_sched (class_code, day) VALUES ('R33:TCC', 3); SIGNAL SQLSTATE '70000'; -- (1) INSERT INTO cl_sched (class_code, day) VALUES ('R44:TDD', 4); END ap
When the error condition of SQLSTATE 70000 is raised on Line (1), the unhandled error causes procedure execution to stop. Unlike the NOT ATOMIC example in Figure 4.3, the first INSERT statement will be rolled back, resulting in a table with no inserted rows from this procedure.
Labels can be used to name any executable statement, which includes compound statements and loops. By using labels, you can have the flow of execution either jump out of a compound statement or loop, or jump to the beginning of a compound statement or loop.
Optionally, you may supply a corresponding label for the END of a compound statement. If an ending label is supplied, it must be the same as the label used at its beginning.
Each label must be unique within the body of an SQL procedure.
Labels also help increase the readability of code. Try to label based on purpose of the statement or code block.
From this point on, all examples in the book use labels.
A label can also be used to avoid ambiguity if a variable with the same name has been declared in more than one compound statement of the stored procedure. A label can be used to qualify the name of an SQL variable.
Figure 4.6 shows the use of a label to name a compound statement and also how to avoid ambiguous references to similarly named variables. It uses two variables of the same name (v_ID) defined in two differently labeled compound statements.
Figure 4.6. A labeled compound statement example for LUW and iSeries.
-- Example applies to LUW and iSeries only -- because nested compound statements are used CREATE PROCEDURE show_label (OUT p_WorkerID INT) LANGUAGE SQL SPECIFIC show_label -- applies to LUW and iSeries sl1: BEGIN -- Declare variables DECLARE v_ID INT; -- (1) -- New compound statement sl2: BEGIN -- Declare variables DECLARE v_ID INT; -- (2) -- Procedure logic SET sl1.v_ID = 1; -- (3) SET sl2.v_ID = 2; -- (4) SET v_ID = 3; -- (5) SET p_WorkerID = sl2.v_ID; -- (6) END sl2; END sl1
You can see that, to avoid ambiguity, the two v_ID variables defined in Lines (1) and (2) are qualified with the label of the compound statement in which they were defined at Lines (3), (4), and (6). When qualification is not used, as in Line (5), the variable will be qualified with the label of the compound statement from which it is being referenced. So the value assignment at Line (5) will actually assign a value of 3 to sl2.v_ID, which means that p_WorkerID in Line (6) will also be assigned a value of 3.
It is good programming practice not to declare multiple variables of the same name, regardless of the fact that they can be referenced as label.variable_name.
Additionally, the label of the compound statement or loop can be used with the LEAVE statement to exit the labeled compound statement or loop. Labels can also be used with the ITERATE statement to jump back to the labeled beginning of a LOOP. These SQL PL statements are covered in greater detail later on in this chapter.