Oracle Named Loops

When using nested loops in your PL/SQL code it is beneficial to use  Oracle named loops to make the code easier to read.

To name a loop you simply add <<LOOP_NAME>> before the LOOP keyword, then add LOOP_NAME at the end of the END LOOP keyword as shown below

<<MAIN_LOOP>>
LOOP
......
END LOOP MAIN_LOOP;

 

Example using Oracle Named Loops

The code below finds all prime numbers less than 50 while taking advantage of Oracle named loops.

DECLARE

   a number(3);
   b number(3);
BEGIN
   a := 2;
   <<MAIN_LOOP>>
   LOOP
      b:= 2;
      <<SUB_LOOP>>
      LOOP
         exit WHEN ((mod(a, b) = 0) or (b = a));
         b := b +1;
      END LOOP SUB_LOOP;
   IF (b = a ) THEN
      dbms_output.put_line(a || ' is prime');
   END IF;
   a := a + 1;
   exit WHEN a = 50;
   END LOOP MAIN_LOOP;
END;

As you can see the code is much easier to read when using Oracle named loops. I find it easier to keep track of the begin of end of loops when using nested loops. Other developers that read through your code will thank you too.

For more info on loops you can visit this link

Loops