Guru: SQL PL Labels

February 13, 2017 Ted Holt

SQL PL, the powerful, easy-to-learn procedural database language that IBM ships with all versions of DB2, allows you to identify any executable statement or compound statement with a label. I can’t think of a better day than today for us to think about why we might want to do that.

As a minimalist programmer, I avoid useless code, and that includes unnecessary labels. In the following paragraphs, I will list only the cases that require labels.

But first, a few ground rules.

• As with CL, a label name is followed by a colon when used to identify a statement. You may leave blanks before the colon if you wish, but you don’t have to.
• A label name may contain:
  • letters
  • digits
  • the at sign @
  • the dollar sign $
  • the sign #, variously known as number sign, pound sign, hash, tic-tac-toe, or octothorpe
  • the underscore character
• A label name must begin with a letter, at sign, dollar sign, or octothorpe.
• Duplicate label names are allowed if they’re not in the same scope.
• Like everything else except character literals, label names are not case-sensitive.
• You may follow an END xxx statement with a label name, but that label name must match the label name for the corresponding beginning.

Case 1: Exit a compound statement

A compound statement is a group of SQL declarations and statements surrounded by the words BEGIN and END. (For a more detailed explanation of the compound statement, see A Brief Introduction to the SQL Procedures Language.)

It may be that you want to exit a compound statement before you reach the end. If you know RPG, think of the RETURN operation in subprocedures and the LEAVESR operation in subroutines.

You can use the LEAVE statement to do the same in SQL PL. However, there is a big difference between the SQL LEAVE and the RPG op codes: LEAVE requires a label to tell it which block you want to leave, even if there’s only one block from which it is possible to leave.

Here’s a stripped-down example that uses LEAVE:

create or replace procedure MyProcedure
   ( in  pOption  char(1))

Main_routine: begin

   . . . statements . . .
   if pOption = 'X' then
         leave Main_routine;
   end if;

   Posting_routine: begin
      . . . statements . . .
      if pOption = 'Y' then
            leave Posting_routine;
      end if;
      . . . statements . . .
      if pOption = 'Z' then
            leave Main_routine;
      end if;
      . . . statements . . .
   end Posting_routine;

   . . . statements . . .

end Main_routine;

This procedure contains two compound statements, named Main_routine and Posting_routine. Notice that it is permitted to leave Main_routine from either Main_routine or Posting_routine.

The ending labels for Posting_routine and Main_routine aren’t required, but I like to use them because I find the code easier to read.

Case 2: Exit a loop or return to the beginning of a loop

You may label any of the looping structures – WHILE, REPEAT, LOOP, and FOR – in order to use the LEAVE and ITERATE commands. LEAVE exits the loop, and ITERATE branches to the loop test.

Loop1:
   Loop
      fetch c_Bill into v_Company, v_Order, v_Line,
                     v_Item, v_Qty;
      if SqlState = '02000'
         then leave Loop1;
      end if; 

      update SalesOrderDetails as d
         set d.QtyShipped = d.QtyShipped + v_Qty
       where (d.company, d.order, d.line) =
                (v_Company, v_Order, v_Line);

      delete from Shipments
       where current of c_Bill;

   end loop;

The LOOP statement is unimaginatively named Loop1, and it processes a cursor. When the FETCH finds nothing left to fetch, DB2 sets the SQL state to 02000. The IF executes LEAVE to end the loop. Control passes to the statement after the last one shown. To see the complete example, see SQL PL–The LOOP Loop.

Case 3: Qualify a variable name

SQL PL allows you to define two or more variables of the same name if they are not within the same scope. That is, they must be defined within different compound statements. One of those compound statements may be nested within the other.

If you need to reference a variable that is defined outside of the compound statement, you can use a label to qualify. Let me illustrate with some skeletal code.

create or replace procedure MyProcedure
   ( in  pOption  char(1))

Main_routine: begin
   declare v_Code     char(1);
   declare v_Msg      char(24);

   . . . statements . . .

   Preparation: begin
      declare v_Code     char(1);

	  . . . statements . . .

      if v_Code = 'M' then
         . . . statements . . .
      end if;
      if Main_routine.v_Code = 'X' then 
         . . . statements . . .
      end if;

	  . . .  statements . . .

   end Preparation;

   Posting: begin
      declare v_Code     char(1);

      . . . statements . .

      if v_Code = 'M' then
         . . . statements . . .
      end if;
      if Main_routine.v_Code = 'X' then 
         . . . statements . . .
      end if;
      . . . statements . . .

   end Posting;

end Main_routine;

In this example, there are three compound statements. Preparation and Posting are nested within Main_routine. All three declare a variable named v_Code, but no routine can access all three of those variables.

• Main_routine can access the v_Code variable defined in Main_routine.
• Preparation can access the v_Code variables defined in Main_routine and Preparation.
• Posting can access the v_Code variables defined in Main_routine and Posting.

Qualification is not necessary when referencing a variable defined in an outer compound statement if a variable of the same name is not defined in the local compound statement. This procedure also defines a variable called v_Msg. You may refer to this variable within any of the three compound statements without the need to qualify.

If a duplicate variable is not qualified, it is assumed to be the one defined within the local scope. For example, the test for an unqualified v_Code in Preparation refers to the v_Code defined in Preparation.

I have not used this feature, as it’s easy to give each declared variable a unique name. The Yip et al book doesn’t recommend declaring variables of the same name within the same procedure.

Case 4: Branch

The GOTO command causes an immediate command to another part of the procedure, which must be identified with a label. Here’s a hastily contrived example:

create or replace procedure MyProcedure
   ( in  pFlag  char(1)) 

Main_routine : 
begin 
   . . . statements . . . 
   if pFlag = 'X' then 
      goto Skip_1; 
   end if; 
   . . . statements . . .
Skip_1: 
   update . . . 
   . . . statements . . .
end 

Notice that the label Skip_1 does not precede a BEGIN or a loop statement. It’s just a place to branch to, so any statement could follow it.

I have never written a SQL routine that required GOTO. I haven’t needed to.

What’s in a Name?

So, there you have it. You can name a statement. You can name it Chuck or Bubba or BillyBob, but you can’t name it Skip. I don’t know why not.

RELATED STORIES

SQL PL–The LOOP Loop

A Brief Introduction to the SQL Procedures Language

DB2 SQL Procedural Language for Linux, UNIX, and Windows

The rules of minimalist programming