Statement labels for nested compound statements in native SQL procedures

You can define a label for each compound statement in an SQL procedure. This label enables you to reference this block of statements in other statements such as the GOTO, LEAVE, and ITERATE SQL PL control statements. You can also use the label to qualify a variable when necessary. Labels are not required.

A label name must meet the following criteria:
  • Be unique within the compound statement, including any compound statements that are nested within the compound statement.
  • Not be the same as the name of the SQL procedure.

You can reference a label within the compound statement in which it is defined, including any compound statements that are nested within that compound statement.

Example of statement labels: The following example shows several statement labels and their scope: 
CREATE PROCEDURE P1 ()
         LANGUAGE SQL

 --Outermost compound statement ------------------------
OUTER1: BEGIN  1 

        --Inner compound statement with label INNER1 ---
        INNER1: BEGIN  2 
          IF...
            ABC:  LEAVE INNER1;  3 
          ELSEIF
            XYZ:  LEAVE OUTER1;  4 
          END IF

        END INNER1;
        --End of inner compound statement INNER1 ------

        --Inner compound statement with label INNER2---
        INNER2: BEGIN  5 
          XYZ:...statement  6 
        END INNER2;
        -- End of inner compound statement INNER2 -----

END OUTER1  7 
The preceding example has the following parts:
  1. The beginning of the outermost compound statement, which is labeled OUTER1
  2. The beginning of an inner compound statement that is labeled INNER1
  3. A LEAVE statement that is defined with the label ABC. This LEAVE statement specifies that Db2 is to terminate processing of the compound statement INNER1 and begin processing the next statement, which is INNER2. This LEAVE statement cannot specify INNER2, because that label is not within the scope of the INNER1 compound statement.
  4. A LEAVE statement that is defined with the label XYZ. This LEAVE statement specifies that Db2 is to terminate processing of the compound statement OUTER1 and begin processing the next statement, if one exists. This example does not show the next statement.
  5. The beginning of an inner compound statement that is labeled INNER2.
  6. A statement that is defined with the label XYZ. This label is acceptable even though another statement in this procedure has the same label, because the two labels are in different scopes. Neither label is contained within the scope of the other.
  7. The end of the outermost compound statement that is labeled OUTER1.

The following examples show valid and invalid uses of labels:

Invalid example of labels: 
L1:  BEGIN
     L2: SET A = B;
     L1: GOTO L2:  --This duplicate use of the label L1 causes an error, because
                   --the same label is already used in the same scope.

END L1;
Valid example of labels: 
L1:  BEGIN
  L2:  BEGIN
    L4: BEGIN  --This line contains the first use of the label L4
      DECLARE A CHAR(5);
      SET A = B;
    END L4;
  END L2;

  L3:  BEGIN
    L4:  BEGIN  --This second use of the label L4 is valid, because 
                --it is used in a different scope.
      DECLARE A CHAR(5);
      SET A = B;
    END L4;
  END L3;
END L1;