PL/SQL Iterative Control Statements

PL/SQL Iterative control statements allow a programmer to execute a set of statements multiple times. All iterative statements must have a conditional statement to check whether to continue to execute the block of statement another time.

PL/SQL Iterative control statements

• Simple Loop- LOOP- END LOOP;
• WHILE loop- WHILE-LOOP-END LOOP;
• FOR Loop- FOR-IN-LOOP-END LOOP;

Additional Statements used with PL/SQL Iterative Control Statements

There are certain statements or keywords that are used in Loops for controlling the working of program’s logic.

EXIT

EXIT statement is used in a loop when you want to exit the loop without condition. When PL/SQL compiler encounters an EXIT statement, it terminates the currently executing loop. Control passes to the next statement after the loop. If you want to exit with a condition, you must enclose EXIT statement in an IF-THEN-END IF statement

EXIT WHEN

EXIT WHEN statement is used in a loop when you want to exit the loop with a condition. When PL/SQL compiler encounters an EXIT WHEN statement, it check the condition specified after WHEN. If the condition is true, the currently executing loop is terminated immediately and control passes to the next statement after the loop.  If the WHEN condition is false, next iteration of loop is executed.

CONTINUE

CONTINUE statement is used when you need to return back to start of next iteration of a loop. With CONTINUE the loop terminates the current iteration and control passes to LOOP header to start next iteration.  It can be combined with an IF- THEN-END IF statement to make it conditional.

PL/SQL Iterative control statements

Simple Loop

The simple loop is infinite. The loop keeps on executing the enclosed statements infinitely. Simple loop needs an exit condition and exit statement to terminate.

Syntax

LOOP

            Loop statements

            Conditional exit using EXIT|EXIT WHEN

END LOOP;

The Simple loop begins with LOOP keyword followed by the set of statements to be executed multiple times. The set of statements are defined between LOOP and END LOOP;. To terminate the loop a condition of termination is defined along with EXIT. To use EXIT WHEN the condition must be given after WHEN.

Simple Loop Example with EXIT

DECLARE
intCounter NUMBER:=1;
BEGIN
LOOP
    DBMS_OUTPUT.PUT_LINE('Counter Value='||intCounter); 
    intCounter:=intCounter+1;
    IF intCounter>10 THEN
        EXIT;
    END IF;    
END LOOP;
END;

Simple Loop Example with EXIT WHEN

DECLARE
intCounter NUMBER:=1;
BEGIN
LOOP
    DBMS_OUTPUT.PUT_LINE('Counter Value='||intCounter); 
    intCounter:=intCounter+1;
    EXIT WHEN intCounter>10;    
END LOOP;
END;

WHILE Loop

WHILE loop is not infinite.  The condition of termination is defined after WHILE and before LOOP keywords. Between WHILE LOOP and END LOOP the set of statements can be given. These statements are executed as long as the condition in WHILE is true.

After every iteration or execution of block of statements control returns to WHILE. If the condition is true, next iteration is initiated. If this condition is false, control is passed to statement after END LOOP;

Syntax

WHILE condition expression LOOP

            Loop statements

            [CONTINUE| EXIT|EXIT WHEN with condition]

END LOOP;

WHILE loop can also include conditional exit statements if you don’t want the loop to be executed till the end or you are unsure if the WHILE condition will ever be false.

WHILE Loop Example

DECLARE
intCounter NUMBER:=1;
BEGIN
WHILE intCounter<=10 LOOP
    DBMS_OUTPUT.PUT_LINE('Counter Value='||intCounter); 
    intCounter:=intCounter+1;
END LOOP;
END;

WHILE Loop Example with EXIT

DECLARE
intCounter NUMBER:=1;
BEGIN
WHILE TRUE LOOP
    DBMS_OUTPUT.PUT_LINE('Counter Value='||intCounter); 
    intCounter:=intCounter+1;
    IF intCounter>7 THEN
        EXIT;
    END IF;    
END LOOP;
END;

WHILE Loop Example with EXIT WHEN

DECLARE
intCounter NUMBER:=1;
BEGIN
WHILE TRUE LOOP
    DBMS_OUTPUT.PUT_LINE('Counter Value='||intCounter); 
    intCounter:=intCounter+1;
    EXIT WHEN intCounter>7; 
END LOOP;
END;

WHILE Loop Example with CONTINUE

DECLARE
intCounter NUMBER:=2;
BEGIN
WHILE TRUE LOOP
    DBMS_OUTPUT.PUT_LINE('Before Increment='||intCounter); 
    intCounter:=intCounter+1;
    IF intCounter>10 then
        EXIT;    
    END IF;
    IF mod(intCounter,2)=0 then
        CONTINUE;  
    else     
        intCounter:=intCounter+1;
    END IF;
END LOOP;
END;

For Loop

FOR Loop is not infinite. In the FOR loop header a counter variable is used which is compared between Lower Bound and Upper Bound values. The variable is initialized to the Lower bound value and is incremented with iterations. The statements block is executed as long as variable’s value remains less than or equal to upper bound value.

Syntax

FOR counter-variable IN Lower Bound.. Upper Bound LOOP

            Loop statements

            [CONTINUE| EXIT|EXIT WHEN with condition]

END LOOP;

FOR loop can also include conditional exit statements if you don’t want the loop to be executed till the end or you want to end FOR loop if some special condition arises in the loop while execution.

Note : In FOR Loop the counter used for iterations need not be declared in the DECLARE section of the PL/SQL Block.

FOR Loop Example

DECLARE
n NUMBER :=4;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Table of '||n);
FOR i IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE (n || '*' || i || '=' || n*i );
END LOOP;
END;

FOR Loop Example with CONTINUE

DECLARE
n NUMBER :=4;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Table of '||n);
FOR i IN 1 .. 10 LOOP
IF mod(i,2)<>0 THEN
    CONTINUE;
END IF;    
DBMS_OUTPUT.PUT_LINE (n || '*' || i || '=' || n*i );
END LOOP;
END;

PL/SQL Iterative Control Statements like Simple Loop, While Loop and For Loop gives you flexibility of Procedural Language with SQL.

admin