PL/SQL by Example / FOR and WHILE loop

For cyclical repetition of a code block, PL/SQL provides the FOR and WHILE constructs. For the sake of completeness, I also mention the general LOOP at the end of the article, which I do not recommend using.

FOR

Basic loop with a fixed range. As in other languages, it is not necessary to explicitly declare the iteration variable; its scope is therefore limited only to the given loop. Incrementing happens automatically.

BEGIN
  FOR i IN 1..5 LOOP
    dbms_output.put(i);
  END LOOP;
  dbms_output.new_line; -- 12345
END;

Very often, the FOR loop is used to iterate through an array with a finite number of elements. In the following example, the keywords REVERSE, which reverses the direction of iteration, and EXIT WHEN, used for premature loop termination, are worth noting.

DECLARE
  TYPE Number_TT IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  lt_Employee_ID Number_TT;
BEGIN
  SELECT employee_id
    BULK COLLECT INTO
         lt_Employee_ID
    FROM employees
    WHERE (ROWNUM<11) -- limit to 10 rows
  ;
  FOR i IN REVERSE 1..lt_Employee_ID.COUNT LOOP
    dbms_output.put(lt_Employee_ID(i)||' ');
    EXIT WHEN lt_Employee_ID(i)=105;
  END LOOP;
  dbms_output.new_line; -- 109 108 107 106 105
END;

WHILE

The WHILE loop does not have a specified fixed range; it is limited only by the condition at the beginning. The example shows one way to iterate through an interval of days.

DECLARE
  -- interval boundaries
  L_START CONSTANT DATE:=TO_DATE('4.1.2016', 'DD.MM.YYYY');
  L_END CONSTANT DATE:=TO_DATE('7.1.2016', 'DD.MM.YYYY');
  -- day serving as a counter
  l_Day DATE;
BEGIN
  l_Day:=L_START;
  WHILE (l_Day<=L_END) LOOP
    dbms_output.put(TO_CHAR(l_Day, 'DD')||' ');
    l_Day:=l_Day+1;
  END LOOP;
  dbms_output.new_line; -- 04 05 06 07
END;

In certain situations, typically with multiple levels of nesting, naming the loop can be useful. Interruption is again triggered using the EXIT command followed by the label name.

DECLARE
  -- it is suitable to use an integer type from PL/SQL as a counter
  -- and the "safe" NOT NULL clause
  i PLS_INTEGER NOT NULL:=1;
  j PLS_INTEGER NOT NULL:=1;
BEGIN
  <<outer_loop>>
  WHILE (i<11) LOOP
    WHILE (j<11) LOOP
      dbms_output.put(i||j||' ');
      EXIT outer_loop WHEN (j>2);
      j:=j+1;
    END LOOP;
    i:=i+1;
  END LOOP;
  dbms_output.new_line; -- 11 12 13
END;

LOOP

General “infinite” loop without specification of either condition or range. Since Oracle 11g, PL/SQL has added support for CONTINUE and CONTINUE WHEN to proceed to the next iteration.

DECLARE
  i PLS_INTEGER NOT NULL:=0;
BEGIN
  LOOP
    i:=i+1;
    CONTINUE WHEN (i<3);
    dbms_output.put(i||' ');
    EXIT WHEN (i>4);
  END LOOP;
  dbms_output.new_line; -- 3 4 5
END;