PL/SQL Loops – Fully Explained with Examples

Learn PL/SQL Loops used in Oracle viz Basic Loop or Simple Loop| For Loop | WHILE Loop| Nested loops with examples.

PL/SQL Loops or PL/SQL Iterations

Loops repeats statement or sequence of statements multiple times until evaluation condition is false.The evaluation condition is decision making statement ,where to stop the iteration.

Three forms of loops exists in Oracle PL/SQL.

1. Basic Loop or Simple Loop
2. For Loop
3. WHILE Loop

SIMPLE LOOP

This loop repeats infinite times .
To keep Loop in finite state we can use EXIT statement.

Syntax:

LOOP
   --STATEMENS;
END LOOP;

Syntax

EXIT [WHEN [CONDITION]];

EXIT Statement

• Exit statement used to terminate a LOOP
• Exit statement should inside the LOOP to stop the iterations.

Syntax:

LOOP
----SEQUENCE OF STATEMENTS
EXIT WHEN [CONDITION]
END LOOP;

• when condition evaluates to true then LOOP terminates and cursor will come outside the loop.
• The EXIT WHEN CONDITION is equivalent to
  IF CONDITION THEN
  EXIT
  END IF;
• EXIT CONDITION Can be used as Pretested LOOP or Post Tested LOOP

Example Program to Print the numbers from 0 to N

DECLARE
A NUMBER := &NUM1;
B NUMBER :=0;
BEGIN
dbms_output.put_line('NUMBERS BETWEEN O TO ' || A);
LOOP
dbms_output.put_line(B);
  B:=B+1;
  EXIT WHEN B=A;
END LOOP;
END;

INPUT

NUM1=5

OUTPUT

NUMBERS BETWEEN O TO 5
0
1
2
3
4

NESTED LOOPS

• Loop with in another Loop is called Nested Loop.
• To keep Loop in finite state we have to EXIT condition on Inner and Outer Loops.
• The overall Loop Terminates with Exit condition in outer Loop.

Syntax

LOOP
----SEQUENCE OF STATEMENTS
LOOP
----SEQUENCE OF STATEMENTS
EXIT WHEN [CONDITION]
END LOOP;
EXIT WHEN [CONDITION]

END LOOP;

Example Program to Print the numbers between 2 to 10

DECLARE
    i number(3);
    j number(3);
BEGIN
    i := 2;
  LOOP
    j:= 2;
         LOOP
           EXIT WHEN ((mod(i, j) = 0) or (j = i));
           j := j +1;
         END LOOP;
    IF (j = i ) THEN
      dbms_output.put_line(i || ' is prime');
    END IF;
    i := i + 1;
   EXIT WHEN i = 10;
  END LOOP;
END;

OUTPUT

2 is prime
3 is prime
5 is prime
7 is prime

NESTED LOOPS AND LABELS

• LOOPS cane be labelled by placing the label before the word LOOP with in the label delimiters.
• When the loop is labelled, the label name can optionally included after END LOOP statement for clarity.

Syntax

  <OUTERLOOP>LOOP
   		   ----SEQUENCE OF STATEMENTS
                   <INNERLOOP> LOOP
     		 ----SEQUENCE OF STATEMENTS
    		    EXIT WHEN [CONDITION]
  			   END LOOP INNERLOOP;
        EXIT WHEN [CONDITION]
          
  END LOOP OUTERLOOP;

Example Program to Print the numbers between 2 to 10

DECLARE 
   i number(3); 
   j number(3); 
BEGIN 
   i := 2; 
    <OUTERLOOP> LOOP 
    			  j:= 2; 
     			<INNERLOOP> LOOP 
         				   EXIT WHEN ((mod(i, j) = 0) or (j = i)); 
        				    j := j +1; 
     				          END LOOP INNERLOOP; 
  	                    IF (j = i ) THEN 
                            dbms_output.put_line(i || ' is prime'); 
                            END IF; 
                            i := i + 1; 
                           EXIT WHEN i = 10; 
                       END LOOP  OUTERLOOP; 
END;

OUTPUT
2 is prime
3 is prime
5 is prime
7 is prime

WHILE LOOP

• WHILE LOOP statement includes a condition association with a sequence of statement
• if the condition evaluates to true, then the sequence of statement will be executed, again the control resumes at the beginning of the loop
• if the condition evaluates to FALSE or NULL,Then Terminates from the loop and the control passes to the next statement.

Syntax

WHILE [CONDITION] LOOP
--SEQUENCE OF STATEMENTS

END LOOP;

Example program the Numbers between 1 to N.

DECLARE
   N NUMBER :=&NUM1;
   I NUMBER :=0;

BEGIN

  DBMS_OUTPUT.PUT_LINE('NUMBERS BETWEEN 1 TO '|| N);
  WHILE I < N LOOP

   I:=I+1;
   DBMS_OUTPUT.PUT_LINE(I);

  END LOOP;
END;

INPUT

NUM1=5

OUTPUT

NUMBERS BETWEEN 1 TO 5
1
2
3
4
5

FOR LOOP

• FOR LOOP has the same structure a basic LOOP.
• FOR LOOP contains a control statement at the front of the LOOP keyword, to determine the number of iterations that PL/SQL
  performs.

Syntax:

FOR loop_counter in [REVERSE] LOWERBOUND..UPPERBOUND
LOOP
--SEQUENCE OF STATEMENTS
END LOOP;

COUNTER

• it is implicitly declared INTEGER whose value is automatically increased or decreased by 1 on each iteration of the LOOP until the upper bound or lower bound is reached

REVERSE

• it is a keyword and causes to decrement with each iteration from the upper bound to lower bound
• the loop_counter needs to be declared, as it is implicitly declared as an integer
• the bound of the loop are evaluate once
• Example program to print the numbers to print between 1 to 10 .

DECLARE
BEGIN
  FOR I IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE(I);
  END LOOP;
END;

OUTPUT
1
2
3
4
5
6
7
8
9
10

Example program to print the numbers to print between 10 to 1 .

DECLARE
BEGIN
  FOR I IN REVERSE 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE(I);
  END LOOP;
END;

OUTPUT
10
9
8
7
6
5
4
3
2
1

NESTED FOR LOOPS

• FOR LOOP with in the FOR LOOP is called Nested FOR LOOP
• Inner FOR LOOP will exits when the counter reaches to Lower Bound or Upper Bound

FOR loop_counter in [REVERSE] LOWERBOUND..UPPERBOUND
LOOP
     FOR loop_counter in [REVERSE] LOWERBOUND..UPPERBOUND
     LOOP

     --SEQUENCE OF STATEMENTS
     END LOOP;
--SEQUENCE OF STATEMENTS
END LOOP;