PL/SQL 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

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;
/

Result

WHILE LOOP

WHILE LOOP write in following syntax format:

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

Example

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

Result

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

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

Result

• « Previous
• Next »