Oracle PL/SQL FOR LOOP with Example

What is a FOR Loop?

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

In this loop, a lower limit and a higher limit are specified, and as long as the loop variable is between this range, the loop is executed.

The loop variable is self-incremental, so no explicit increment operation is needed. The loop variable need not 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:

• The keyword ‘FOR’ marks the beginning of the loop and ‘END LOOP’ marks the end.
• The loop variable is evaluated every time before executing the execution part.
• The execution block contains all the code that needs to run and can contain any executable statement.
• The loop_variable is declared implicitly during the execution of the loop, and its scope is only inside the loop.
• When the loop variable moves out of range, control exits the loop.
• The loop can be made to work in reverse order by adding the keyword ‘REVERSE’ before the lower_limit.

Example 1: In this example, we print numbers from 1 to 5 using a FOR loop statement.

BEGIN
dbms_output.put_line('Program started.');
FOR a IN 1 .. 5
LOOP
dbms_output.put_line(a);
END LOOP;
dbms_output.put_line('Program completed.');
END;
/

Code Explanation:

• Code line 2: Printing the statement “Program started”.
• Code line 3: The keyword ‘FOR’ marks the beginning of the loop and the loop_variable ‘a’ is declared. It now takes values from 1 to 5.
• Code line 5: Prints the value of ‘a’.
• Code line 6: The keyword ‘END LOOP’ marks the end of the execution block.
• Line 5 continues to execute until ‘a’ reaches the value 6, at which point the condition fails and control exits the loop.
• Code line 7: Printing the statement “Program completed”.

Nested Loops

Loop statements can also be nested. The outer and inner loops can be of different types. In a nested loop, for every iteration of the outer loop, the inner loop executes fully.

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

Syntax Explanation:

• The outer loop has one more loop inside it.
• The loops can be of any type, and the execution functionality is the same.

Example 1: In this example, we print numbers from 1 to 3 using a FOR loop statement. Each number is printed as many times as its value.

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: The keyword ‘FOR’ marks the beginning of the loop and the loop_variable ‘a’ is declared. It now takes values from 1 to 3.
• Code line 7: Resetting the value of ‘b’ to ‘1’ each time.
• Code line 8: The 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”.

FOR Loop and REVERSE FOR Loop

By default a FOR loop counts upward from the lower limit to the higher limit. Adding the REVERSE keyword makes it count downward while keeping the limits in the same order. Both forms are shown together below so the difference is clear.

Note that even with REVERSE, the lower limit is still written before the higher limit; only the direction of iteration changes. For loops whose count is not known in advance, the basic loop is the better choice.

FAQs