Oracle PL/SQL FOR LOOP with Example

Richard PetersonByRichard Peterson Hours Updated

What is For Loop?

“FOR LOOP” statement is best suitable when you want to execute a code for a known number of times rather than based on some other conditions.

In this loop, the lower limit and the higher limit will be specified and as long as the loop variable is in between this range, the loop will be executed.

The loop variable is self-incremental, so no explicit increment operation is needed in this loop. The loop variable need not to be declared, as it is declared implicitly.

FOR <loop_variable> in <lower_limit> .. <higher_limit> 
LOOP
<execution block starts>
.
.
.
<execution_block_ends>
 END LOOP;

Syntax Explanation:

  • In the above syntax, keyword ‘FOR’ marks beginning of the loop and ‘END LOOP’ marks the end of the loop.
  • Loop variable is evaluated every time before executing the execution part.
  • The execution block contains all the code that needs to be executed. The execution part can contain any execution statement.
  • The loop_variable is declared implicitly during the execution of the entire loop, and the scope of this loop_variable will be only inside this loop.
  • If the loop variable came out of the range, then control will exit from the loop.
  • The loop can be made to work in the reverse order by adding the keyword ‘REVERSE’ before lower_limit.

Example 1: In this example, we are going to print number from 1 to 5 using FOR loop statement. For that, we will execute the following code.

Loops in PL/SQL

BEGIN
dbms Qutput.put linef.Prp.gram started.' );
FOR a IN 1 .. 5
LOOP
dbms_output.put_line(a);
END LOOP:
dbms_output.put_iine('Program completed.'); 
END;
/

Code Explanation:

  • Code line 2: Printing the statement “Program started”.
  • Code line 3: Keyword ‘FOR’ marks the beginning of the loop and loop_variable ‘a’ is declared. It now will have the value starting from 1 to 5
  • Code line 5: Prints the value of ‘a’.
  • Code line 6: Keyword ‘END LOOP’ marks the end of execution block.
  • The code from line 5 will continue to execute till ‘a’ reaches the value 6, as the condition will fail, and the control will EXIT from the loop.
  • Code line 7: Printing the statement “Program completed”

Nested Loops

The loop statements can also be nested. The outer and inner loop can be of different types. In the nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.

Loops in PL/SQL 

LOOP -outer
<execution block starts>
LOOP — inner
<execution_part> 
END LOOP; 
<execution_block_ends> 
END LOOP;

Syntax Explanation:

  • In the above syntax, the outer loop has one more loop inside it.
  • The loops can be of any types and execution functionality part is same.

Example 1: In this example, we are going to print number from 1 to 3 using FOR loop statement. Each number will be printed as many times as its value. For that, we will execute the following code.

Loops in PL/SQL

Loops in PL/SQL

DECLARE 
b NUMBER;
BEGIN
dbms output put line(‘Program started' );
FOR a IN 1..3 
LOOP
b:=1;
WHILE (a>=b)
LOOP
dbms output put line(a);
b:=b+1;
END LOOP;‭	
END LOOP;‭	‬
dbms_output.put_line('Program completed' );
END;
/

Code Explanation:

  • Code line 2: Declaring the variable ‘b’ as ‘NUMBER’ data type.
  • Code line 4: Printing the statement “Program started”.
  • Code line 5: Keyword ‘FOR’ marks the beginning of the loop and loop_variable ‘a’ is declared. It now will have the value starting from 1 to 3
  • Code line 7: Resetting the value of ‘b’ to ‘1’ each time.
  • Code line 8: Inner while loop checks for the condition a>=b.
  • Code line 10: Prints the value of ‘a’ as long as the above condition is satisfied.
  • Code line 14: Printing the statement “Program completed”

Summary

Loop FOR Loop
EXIT Criteria Exit when the counter reaches the limit
Usage Good to use when loop count to be executed is known.

You Might Like: