The simplicity and easy to use behavior of FOR loop has won the hearts of millions and has become the most widely used loop in programming. In PL/SQL we have two types of FOR loops:

1. Numeric FOR loop and
2. Cursor FOR loop.

FOR loop allows you to execute the block of statements repeatedly for a fixed number of time whereas WHILE loop is better suited when the number of iterations are unknown.

This tutorial will concentrate on Numeric “FOR LOOP”. We’ll leave the Cursor FOR loop for the future when we will learn the concepts of Cursor.

Syntax

FOR loop_counter IN [REVERSE] lower limit.. upper_limit LOOP
  Statement 1;
  Statement 2;
  …
  Statement 3;
END LOOP;

For the in-depth explanation of the above syntax please watch my video. There I have explained the same in detail. Now let’s see some examples.

Examples of Numeric FOR Loop In Oracle PL/SQL.

Example 1: FOR loop

SET SERVEROUTPUT ON;
BEGIN
  FOR v_counter IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(v_counter);
  END LOOP;
END;

Here we only have the execution section and inside that we have our FOR loop which will print the value of v_counter variable from 1 to 10.

Have you noticed that we didn’t declare the v_counter variable anywhere in the program? Even we don’t have the declaration section here in this code. This is because variable v_counter is an implicit index integer variable which gets declared automatically with the definition of FOR loop. Moreover the variable v_counter will increment by 1 with each iteration automatically by FOR loop construct thus you do not need to write update statement (v_counter := v_counter +1) explicitly. As a matter of fact if you will try to write the update statement in the “FOR loop” then you will get an error.

Example 2: FOR Loop with IN REVERSE keyword.

Now suppose you want to print the counting, same as we did in the previous example but this time in reverse order. To do so you don’t have to change the loop definition or even you don’t have to add any extra line of codes, PL/SQL block will be same as of the previous example. You just have to add one keyword REVERSE immediately after IN keyword in FOR LOOP definition. Rest of the code will remain the same as that of the previous example.

BEGIN
  FOR v_counter IN REVERSE 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(v_counter);
  END LOOP;
END;
/

This code will give you counting from 1 to 10 in reverse order on execution.

Example 3: Multiplication Table using Numeric FOR loop

DECLARE
  v_result  NUMBER;
BEGIN
  FOR v_counter IN  1..10 LOOP
    v_result:= 19*v_counter;
    DBMS_OUTPUT.PUT_LINE(v_result);
  END LOOP;
END;
/

In this example we need one extra variable to store the result of the multiplication thus we declared a variable v_result with NUMBER data type. In the execution section we have our “FOR loop” and this time inside the loop we have only two statements. First is an arithmetic expression which will perform the multiplication of our table and will store the result in v_result variable. Second is the output statement which will display you the result in a formatted manner.

That is all about Numeric FOR loop in PL/SQL. Hope you enjoyed reading and learnt something. Please do share this tutorial on your social media and with your friends. Also you can send me your feedback on my twitter @RebellionRider. Thanks & have a great day!