What’s in a Label

Did you know that any executable statement in your PL/SQL code can be “labeled”. To label a particular section of your code just add <<your_label_name_here>> in front of that section. The label name cannot be more than 30 characters and has to start with a letter. No need to end the label with a semi-colon (;). For example:

SQL> <<check_x>>
  2  declare
  3      x boolean := true;
  4  begin
  5      if x then
  6          dbms_output.put_line('Correct ');
  7      end if;
  8  end;
  9  /
Correct

PL/SQL procedure successfully completed.

If you move the label in front of “x boolean := true;” you will get an error, since “x boolean := true;” is not an executable statement:

SQL> declare
  2      <<check_x>>
  3      x boolean := true;
  4  begin
  5      if x then
  6          dbms_output.put_line('Correct ');
  7      end if;
  8  end;
  9  /
    <<check_x>>
    *
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00103: Encountered the symbol "<" 
when expecting one of the following:...

Labels can be the target of a GOTO:

SQL> <<check_x>>
  2  declare
  3      x boolean := true;
  4  begin
  5      if x then
  6          dbms_output.put_line('Correct ');
  7      else
  8          goto do_nothing;
  9      end if;
 10      <<do_nothing>>
 11      null;
 12  end;
 13  /
Correct

PL/SQL procedure successfully completed.

Labels can be the target of an EXIT:

SQL> begin
  2      <<first_loop>>
  3      for i in 1..4 loop
  4          for j in 1..4 loop
  5              exit first_loop;
  6          end loop;
  7          dbms_output.put_line('Hello');
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Labels can be used to qualify references to variables with the same name:

SQL> <<label1>>
  2  declare
  3      x varchar2(10) := 'hello';
  4  begin
  5      declare
  6           x varchar2(10) := ' there';
  7      begin
  8           dbms_output.put_line(label1.x || x);
  9      end;
 10  end;
 11  /
hello there

PL/SQL procedure successfully completed.

Finally, I have never used PL/SQL labels or known a developer who uses them. So, what’s the purpose of this post? Well, I just want to double check whether there are still developers who use labels, and goto’s (Yak!). So, if you’re one of them, let us know…

Browse the relevant documentation.

Possibly related:

Tagged | Post a Comment