The FOR and WHILE-LOOP in Oracle PL/SQL - Oracle

As yet another alternative to EXIT, you can place your loop termination condition at the beginning of the loop using this syntax:

WHILE condition LOOP
   statement1;
   statement2;
   . . .
END LOOP;

While this syntax may be more readable, it also has one major distinction compared to the previously discussed loop constructs: if the condition in the WHILE clause is not true the first time through the loop, the statements within the loop are not executed at all. In contrast, all previous versions of the LOOP construct execute the code within the loop at least once. Here is the previous example rewritten to use WHILE :

DECLARE
  countr       NUMBER := 1;
BEGIN
   WHILE countr < 11 LOOP
      dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
      countr := countr + 1;
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;

FOR-LOOP

If your application needs to iterate over a range of integers, you can use the FOR-LOOP construct and simplify your code even more. Here is the syntax:

FOR variable IN startvalue..endvalue LOOP
   statement1;
   statement2;
   . . .
END LOOP;

Within the loop, the variable variable  starts with a value of startvalue  and terminates the loop when the value of variable exceeds endvalue . Rewriting our well-worn example from earlier in the chapter (and while we’re at it, dropping the unnecessary variable declaration) looks like this:

BEGIN
   FOR i IN 1..10 LOOP
      dbms_output.put_line('Square root of ' || i || ' is ' || SQRT(i));
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;

Note that you do not need to include the loop variable in the declaration section. You can, however, explicitly declare your loop variables depending on your programming standards.

In our final loop example, you want to iterate your loop in reverse order and produce the square roots starting with ten and ending at one. As you might expect, all you need to add is the REVERSE keyword to your LOOP clause as follows:

BEGIN
   FOR i IN REVERSE 1..10 LOOP
      dbms_output.put_line('Square root of ' || i || ' is ' || SQRT(i));
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;

This produces the following output, as expected:

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

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

Please check back for the next part of this series.