Oracle PL/SQL Tutorial - PL/SQL FOR LOOP

There are two kinds of PL/SQL FOR loops: the numeric FOR loop and the cursor FOR loop.

The numeric FOR loop is the traditional. The number of iterations of the FOR loop is known when the loop starts.

Here is the general syntax of the numeric FOR loop:


     FOR loop index IN [REVERSE]  lowest number .. highest number 
     LOOP 
        executable statement(s) 
     END LOOP;

You must have at least one executable statement between the LOOP and END LOOP keywords.

Example

The following loop executes 10 times; loop_counter starts at 1 and ends at 10:


          FOR loop_counter IN 1 .. 10 
          LOOP 
             --executable statements ... 
          END LOOP;

The following loop executes 10 times; loop_counter starts at 10 and ends at 1:


          FOR loop_counter IN REVERSE 1 .. 10 
          LOOP 
             ---executable statements ... 
          END LOOP;

Here is a loop that doesn't execute even once.


          FOR loop_counter IN REVERSE 10 .. 1 
          LOOP 
             ---executable statements ... 
          END LOOP;

The following loop executes for a range determined by the values in the variable and expression:


FOR calc_index IN start_period_number .. 
           LEAST (end_period_number, current_period) 
LOOP 
   --executable statements ... 
END LOOP;

Example 2

The bounds of a loop range can be either literals, variables, or expressions, but they must evaluate to numbers.


DECLARE --  w ww . j  av a  2s .  c o  m
  first  INTEGER := 1; 
  last   INTEGER := 10; 
  high   INTEGER := 100; 
  low    INTEGER := 12; 
BEGIN 
  -- Bounds are numeric literals: 
 
  FOR j IN -5..5 LOOP 
    NULL; 
  END LOOP; 
 
  -- Bounds are numeric variables: 
 
  FOR k IN REVERSE first..last LOOP 
    NULL; 
  END LOOP; 
 
  FOR step IN 0..(TRUNC(high/low) * 2) LOOP 
    NULL; 
  END LOOP; 
END; 
/

Example 3

Using the Increment of the Counter in a FOR-LOOP Statement


DECLARE 
  TYPE DateList IS TABLE OF DATE INDEX BY PLS_INTEGER; 
  dates DateList; 
BEGIN 
  FOR j IN 1..3 LOOP 
    dates(j*5) := SYSDATE; 
  END LOOP; 
END; 
/

Dynamic Ranges for Loop Bounds

PL/SQL lets you specify the loop range at run time by using variables for bounds.

   
CREATE TABLE temp ( 
  emp_no NUMBER, 
  email_addr VARCHAR2(50) 
); --   www  .ja  v  a 2  s  .  com

DECLARE 
  emp_count NUMBER := 4; 
BEGIN 
  
 
  FOR i IN 1..emp_count LOOP 
    INSERT INTO temp 
      VALUES(i, 'to be added later'); 
  END LOOP; 
END; 
/ 

select * from temp;

drop table temp;

The code above generates the following result.

Note

If the lower bound of a loop range is larger than the upper bound, the loop body is not executed.

Using Existing Variable as Loop Variable

   
DECLARE -- from   w w w . j av  a2 s  .  co  m
  i NUMBER := 5; 
BEGIN 
  FOR i IN 1..3 LOOP 
    DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i)); 
  END LOOP; 
 
  DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i)); 
END; 
/

The code above generates the following result.

Referencing Outer Counter with Same Name as Inner Counter


BEGIN -- www. j  av a2s.c o  m
<<outer_loop>> 
  FOR i IN 1..3 LOOP 
    <<inner_loop>> 
    FOR i IN 1..3 LOOP 
      IF outer_loop.i = 2 THEN 
        DBMS_OUTPUT.PUT_LINE 
          ( 'outer: ' || TO_CHAR(outer_loop.i) || ' inner: ' 
            || TO_CHAR(inner_loop.i)); 
      END IF; 
    END LOOP inner_loop; 
  END LOOP outer_loop; 
END; 
/

The code above generates the following result.

Example for EXIT

You can give a name to a loop by using a label. A loop label in PL/SQL has the following format:


<<label_name>>

where label_name is the name of the label, and that loop label appears immediately before the LOOP statement:


     <<all_emps>> 
     FOR emp_rec IN emp_cur 
     LOOP 
        ... 
     END LOOP;

The EXIT statement lets a FOR loop complete early.

  
DECLARE -- ww  w  .  j  av  a  2  s  . c  o  m
   v_employees employees%ROWTYPE; 
   CURSOR c1 is SELECT * FROM employees; 
BEGIN 
  OPEN c1; 
  -- Fetch entire row into v_employees record: 
  FOR i IN 1..10 LOOP 
    FETCH c1 INTO v_employees; 
    EXIT WHEN c1%NOTFOUND; 
    -- Process data here 
  END LOOP; 
  CLOSE c1; 
END; 
/

EXIT with a Label in a FOR LOOP


DECLARE -- from  w w w .j ava2  s  .  co  m
   v_employees employees%ROWTYPE; 
   CURSOR c1 is SELECT * FROM employees; 
BEGIN 
  OPEN c1; 
 
  <<outer_loop>> 
  FOR i IN 1..10 LOOP 
    -- Process data here 
    FOR j IN 1..10 LOOP 
      FETCH c1 INTO v_employees; 
      EXIT outer_loop WHEN c1%NOTFOUND; 
      -- Process data here 
    END LOOP; 
  END LOOP outer_loop; 
 
  CLOSE c1; 
END; 
/

Cursor FOR Loop

A cursor FOR loop is a loop that is associated with an explicit cursor.

Here is the basic syntax of a cursor FOR loop:


     FOR record IN { cursor_name | (explicit SELECT statement) } 
     LOOP 
        executable statement(s) 
     END LOOP;

where record is a record declared implicitly by PL/SQL with the %ROWTYPE attribute against the cursor specified by cursor_name.

Example of Cursor FOR Loops


DECLARE --   w  ww.java2  s  . c o m
   CURSOR c_my IS SELECT id, room_number FROM occupancy; 
   my_rec c_my%ROWTYPE; 
BEGIN 
   OPEN c_my; 
   LOOP 
      FETCH c_my INTO my_rec; 
      EXIT WHEN c_my%NOTFOUND; 
      update_bill(my_rec.id, my_rec.room_number); 
    END LOOP; 
    CLOSE c_my; 
END;