Compound statements and scope of variables in SQL procedures

SQL procedures can contain one or more compound statements. They can be introduced in serial or can be nested within another compound statement. Each compound statement introduces a new scope in which variables might or might not be available for use.

The use of labels to identify a compound statement is important as the label can be used to qualify and uniquely identify variables declared within the compound statement. This is particularly important when referencing of variables in different compound statements or in nested compound statements.

In the following example there are two declarations of the variable a. One instance of it is declared in the outer compound statement that is labelled by lab1, and the second instance is declared in the inner compound statement labelled by lab2. As it is written, DB2® will presume that the reference to a in the assignment-statement is the one which is in the local scope of the compound block, labelled by lab2. However, if the intended instance of the variable a is the one declared in the compound statement block labeled with lab1, then to correctly reference it in the innermost compound block, the variable should be qualified with the label of that block. That is, it should be qualified as: lab1.a.

  CREATE PROCEDURE P1 ()
  LANGUAGE SQL
    lab1: BEGIN
      DECLARE a INT DEFAULT 100;
      lab2: BEGIN
        DECLARE a INT DEFAULT NULL;

        SET a = a + lab1.a;

        UPDATE T1
         SET T1.b = 5 
          WHERE T1.b = a;  <-- Variable a refers to lab2.a 
                               unless qualified otherwise

      END lab2;
   END lab1

The outermost compound statement in an SQL procedure can be declared to be atomic, by adding the keyword ATOMIC after the BEGIN keyword. If any error occurs in the execution of the statements that comprise the atomic compound statement, then the entire compound statement is rolled back.