Wednesday, July 11, 2012

PL/SQL Loop Labels


This was tested on Oracle 11.2. PL/SQL loops can have labels, which must appear just before the LOOP statement. They must be undeclared identifiers enclosed by double angle brackets. If you wish, you can repeat the label at the end of the loop:

SQL> DECLARE
  2   X NUMBER;
  3   Y NUMBER;
  4  BEGIN
  5   X := 1;
  6   <<OUTER_LOOP>>
  7   LOOP
  8    Y := 1;
  9    <<INNER_LOOP>>
 10    LOOP
 11     DBMS_OUTPUT.PUT_LINE('X = '||X||' and Y = '||Y);
 12     Y := Y + 1;
 13     EXIT WHEN Y > 2;
 14    END LOOP INNER_LOOP;
 15    X := X + 1;
 16    EXIT WHEN X > 2;
 17   END LOOP OUTER_LOOP;
 18  END;
 19  /
X = 1 and Y = 1
X = 1 and Y = 2
X = 2 and Y = 1
X = 2 and Y = 2

PL/SQL procedure successfully completed.

SQL> DECLARE
  2   X NUMBER;
  3   Y NUMBER;
  4  BEGIN
  5   X := 1;
  6   <<OUTER_LOOP>>
  7   LOOP
  8    Y := 1;
  9    LOOP
 10     DBMS_OUTPUT.PUT_LINE('X = '||X||' and Y = '||Y);
 11     Y := Y + 1;
 12     EXIT WHEN Y > 2;
 13     EXIT OUTER_LOOP WHEN X + Y > 6;
 14    END LOOP;
 15   X := X + 1;
 16   END LOOP;
 17  END;
 18  /
X = 1 and Y = 1
X = 1 and Y = 2
X = 2 and Y = 1
X = 2 and Y = 2
X = 3 and Y = 1
X = 3 and Y = 2
X = 4 and Y = 1
X = 4 and Y = 2
X = 5 and Y = 1

PL/SQL procedure successfully completed.

SQL>

No comments: