PL/SQL Loop - Basic Loop, FOR Loop, WHILE Loop

PL/SQL Loop Basic Loop, FOR Loop, WHILE Loop repeat a number of block statements in your PL/SQL program. Loop use when we have a block of statements for required to repeatedly certain number of times. PL/SQL loop statements 3 different forms:

1. Basic LOOP
2. WHILE LOOP
3. FOR LOOP

Oracle recommended to write a label when use loop statement. It's benefit to improve readability. label is not compulsory for execute loop. compiler does not check to label defined or not. Define label before LOOP keyword and after END LOOP keyword

Basic LOOP

Basic LOOP write in following syntax format:

[ label_name ] LOOP 
   statement(s);
END LOOP [ label_name ];

Example Code

DECLARE
   no NUMBER := 5;
BEGIN
 LOOP
  DBMS_OUTPUT.PUT_LINE ('Inside value:  no = ' || no);
     no := no -1;
     IF no = 0 THEN
        EXIT;
     END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Outside loop end');   
END;
/

Example Result

Inside value: no = 5
Inside value: no = 4
Inside value: no = 3
Inside value: no = 2
Inside value: no = 1
Outside loop end

PL/SQL procedure successfully completed.

WHILE LOOP

WHILE LOOP write in following syntax format:

[ label_name ] WHILE condition LOOP 
   statement(s);
END LOOP [ label_name ];

Example Code

DECLARE
   no NUMBER := 0;
BEGIN
 WHILE no < 10 LOOP
     no := no + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Sum :' || no);   
END;
/

Example Result

Sum : 10

PL/SQL procedure successfully completed.

FOR LOOP

FOR LOOP write in following syntax format:

[ label_name ] FOR current_value IN [ REVERSE ] lower_value..upper_value LOOP 
   statement(s);
END LOOP [ label_name ];

Example Code

BEGIN
 FOR no IN 1 .. 5 LOOP
     DBMS_OUTPUT.PUT_LINE('Iteration : ' || no);
   END LOOP;
END;
/

Example Result

Iteration : 1
Iteration : 2
Iteration : 3
Iteration : 4
Iteration : 5

PL/SQL procedure successfully completed.

REVERSE FOR Loop

Optional REVERSE keyword introduce to iteration is proceed from upper_value to lower_value range.

Example Code :

BEGIN
 FOR no IN REVERSE 5 .. 1 LOOP
     DBMS_OUTPUT.PUT_LINE('Iteration : ' || no);
   END LOOP;
END;
/

Example Result :

Iteration : 5
Iteration : 4
Iteration : 3
Iteration : 2
Iteration : 1

PL/SQL procedure successfully completed.