Oracle Iterators: LOOP-END LOOP, EXIT, and EXIT-WHEN - Oracle

Some tasks, such as inserting a number of new rows into a table, require the ability to repeatedly execute over a set of statements. This section introduces the various methods available for iterating and exiting loops.

LOOP

The most basic way to loop through a series of statements is with the LOOP-END LOOP construct using this syntax:

LOOP
   statement1;
   statement2;
   . . .
END LOOP;

The first question that may come to mind is, how useful is this plain LOOP construct if you can’t exit the loop? The DBA will not be too happy if your subprogram runs indefinitely. The EXIT constructs will address this issue in the next section.

EXIT and EXIT-WHEN

The EXIT statement forces a loop to complete unconditionally. As you might expect, you execute the EXIT statement based on a condition in an IF statement. Consider the following example where you display the square roots of the numbers one through ten:

DECLARE
  countr    NUMBER := 1;
BEGIN
   LOOP
      dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
      countr := countr + 1;
      IF countr > 10 THEN
         EXIT;
      END IF;
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;

The counter is initialized in the DECLARE section; within the loop, the counter is incremented. Once the counter reaches the threshold value in the IF statement, the loop terminates and continues execution after the END LOOP statement. The output looks like this:

--------------------------------------------
Square root of 1 is 1
Square root of 2 is 1.41421356237309504880168872420969807857 Square root of 3 is 1.73205080756887729352744634150587236694 Square root of 4 is 2
Square root of 5 is 2.23606797749978969640917366873127623544 Square root of 6 is 2.44948974278317809819728407470589139197 Square root of 7 is 2.64575131106459059050161575363926042571 Square root of 8 is 2.82842712474619009760337744841939615714 Square root of 9 is 3
Square root of 10 is 3.16227766016837933199889354443271853372
End of Calculations.

Statement processed.
--------------------------------------------

You can alternatively use the EXIT-WHEN construct to improve the readability of your code if you only use the IF statement to check for a termination condition. You can rewrite the previous code example as follows:

DECLARE
  countr     NUMBER := 1;
BEGIN
   LOOP
      dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
      countr := countr + 1;
      EXIT WHEN countr > 10;
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;