Iterative statements/Loops in PL/SQL

By Manish Sharma

Numeric FOR Loop In PL/SQL

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. [Click Here To Tweet This]

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.


 FOR loop_counter IN [REVERSE] lower limit.. upper_limit LOOP
  Statement 2;
  Statement 3;
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

  FOR v_counter IN 1..10 LOOP

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.

  FOR v_counter IN REVERSE 1..10 LOOP
This code will give you counting from 1 to 10 in reverse order on execution.

Example 3: Multiplication Table using Numeric FOR loop

  v_result NUMBER;
  FOR v_counter IN 1..10 LOOP
   v_result:= 19*v_counter;

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!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn