Oracle PL/SQL: LOOPs Explained

/ Amy Simpson-Grange

I while ago, I wrote, HERE, how I regularly see commonly asked questions appearing in forums relating to the same topic. Like with SELECT INTO from my previous posts or GROUP BY in this post, I also see lots of questions about the correct usage of different LOOPs and so I figured that I would write up an explanation of how to properly use them and then, again, I can reference this post’s URL in response to questions on this topic!

Side Note: For those that frequent this blog.. firstly, Thankyou! and secondly, no I haven’t moved away from Cloud topics. I have lots of ideas for posts over the coming months – I just occasionally enjoy spinning up an oracle database in my free tier account and writing some PL/SQL for a change. Incidentally it happened to serve multiple purposes 🙂

Contents

  1. Oracle PL/SQL Loops
  2. Basic Loop
  3. While Loop
  4. For Loop
  5. Cursor For Loop

Oracle PL/SQL LOOPs

Loops are a fundamental part of PL/SQL, allowing you to execute a block of code multiple times. Oracle provides several different types of loops in PL/SQL and choosing the right one can make your code clearer, more efficient and easier to maintain. In this post, we’ll cover the main types of loops available in PL/SQL, when to use them and some common pitfalls to watch out for.

For the examples in this post, I will use the widely known “hr schema”. The entity-relationship diagram is as below.

LOOP statements, execute sequence of statements multiple times. The LOOP and END LOOP keywords enclose the statements. PL/SQL provides four kinds of LOOP statements:

  1. Basic LOOP – In this loop structure, a sequence of statements is enclosed between the LOOP and END LOOP statements. At each iteration, the sequence of statements is executed and then control resumes at the top of the loop.
  2. WHILE loop – In this loop structure, a statement or group of statements repeat while a given condition remains true. It tests the condition before executing the loop body.
  3. FOR loop – In this loop structure, a sequence of statements is executed multiple times and increments the code that manages the loop variable.
  4. Cursor FOR loop – In this loop structure, you can loop through query results without manually opening, fetching and closing a cursor.

Basic LOOP

Syntax

A basic loop structure encloses a sequence of statements between the LOOP and END LOOP statements. With each iteration, the sequence of statements is executed and then control resumes at the top of the loop. In it’s most simplistic form, a basic LOOP uses the following expected syntax:

LOOP
  -- Code block that you wish to execute a number of times
END LOOP;

An EXIT statement is used to break the loop whether the loop condition has been satisfied or not. This statement is particularly useful when you want to terminate the loop based on a pre-defined condition within the loop block. For instance, if you’re analyzing data in a loop and you encounter an anomaly that requires immediate attention, the EXIT statement allows you to exit the loop gracefully. It looks like this in the most basic syntax form:

LOOP
  -- Code block that you wish to execute a number of times
  IF condition THEN
    EXIT;
  END IF;
END LOOP;

An EXIT WHEN statement can be used to break the loop in a more streamlined way. It looks like this in the most basic syntax form:

DECLARE
  -- Declare a variable and assign the number 1
  variable NUMBER := 1;
BEGIN
  -- Open a loop to iterate a seqence of statements
  LOOP
    -- Code block that you wish to execute a number of times
    variable := variable + 1; -- add 1 to the variable in each loop
    EXIT WHEN variable > 10; -- exit the loop when the variable has a value more than 10 (e.g iterate 10 times only)
  END LOOP;

Example

The following example uses a basic PL/SQL block to loop a counter variable and output the counter value.

DECLARE 
    
    v_counter NUMBER := 1;
BEGIN
   
    LOOP
   
        -- write counter value
        DBMS_OUTPUT.PUT_LINE('Counter value is: ' || v_counter);
        -- increment counter value
        v_counter := v_counter + 1;
        -- exit clause
        EXIT WHEN v_counter > 5;
   
    END LOOP;
END;

An explanation of this example is below:

  • I have declared a variable of type number name v_counter. It is intialised with the value 1
  • I used a basic LOOP statement to open a looped logic. Within each iteration of the loop I:
    • Output the value of the v_counter variable
    • Increment the value of the v_counter variable by 1
  • An EXIT WHEN clause is added to ensure that the loop logic exits at the point in which the v_counter variable is more than 5.

The output is per the below screenshot:

WHILE loop

Syntax

A WHILE loop statement repeatedly executes a set of statements as long as a given condition remains true. It looks like this in the most basic syntax form:

WHILE condition loop
  -- Code block that you wish to execute a number of times
END LOOP;

In a WHILE loop, you do not need to force an EXIT or EXIT WHEN (though you can based on any unhappy path scenarios there may be in your code). This design choice allows for a more streamlined flow of execution, as the loop will naturally continue iterating as long as the specified condition remains true. Such flexibility can be advantageous in situations where the loop is performing complex operations that may encompass various conditions or states, allowing for a more dynamic response to the data being processed.

Example

The following example is very similar to the example used in to show basic loop above. It loops a counter variable and output the counter value, but this time, an EXIT WHEN statement is not necessary as the WHILE loop controls the conditional run.

DECLARE
    v_counter NUMBER := 1;
BEGIN
    
    WHILE v_counter <=5 LOOP
        -- write counter value
        DBMS_OUTPUT.PUT_LINE ('Counter values is: ' || v_counter);
         -- increment counter value
        v_counter := v_counter + 1; 
    
    END LOOP;
END;

An explanation of this example is below:

  • I have declared a variable of type number name v_counter. It is intialised with the value 1
  • I used a WHILE loop statement which has a condition to run only where the value of v_counter is less than or equal to 5 to open a looped logic. Within each iteration of the loop I:
    • Output the value of the v_counter variable
    • Increment the value of the v_counter variable by 1

The output is per the below screenshot:

FOR loop

Syntax

A FOR loop is a repetition control structure that allows you to efficiently write a loop that needs to execute a specific number of times. In it’s most basic syntax form, it looks like this:

FOR counter IN start_value..end_value loop
  -- Code block that you wish to execute a number of times
END LOOP;

Like in a WHILE loop, you do not need to force an EXIT or EXIT WHEN (though you can based on any unhappy path scenarios there may be in your code).

In a FOR loop, the condition start_value..end_value is evaluated. Where it is TRUE, the body of the loop is executed, allowing for repetitive operations based on the specified range. If it is FALSE, the body of the loop does not execute, and the flow of control jumps to the next statement in your PL/SQL logic. Whilst the loop is executing, the value of the counter variable is increased upon each iteration of the loop block, which plays a crucial role in tracking the number of times the loop has run. Each time, the condition will be evaluated again, providing a dynamic check that determines whether the loop should continue or terminate.

There are some special characteristics of a FOR loop:

  • The counter, start_value and end_value can be literals, variables or expressions but must evaluate to numbers otherwise you will be met with a VALUE_ERROR
  • the start_value does not need to be 1, but, the loop counter increment must be 1

It is also possible to use a reverse FOR loop. As mentioned, by default, iteration of the loop proceeds from the initial value to the final value, allowing programmers to traverse elements in a sequential manner. However, in some scenarios, it may be more efficient or logical to traverse elements in the opposite direction. You can reverse this order by using the REVERSE keyword, which effectively alters the flow of the loop. In these cases, iteration of the loop proceeds the other way, starting from the last value and moving down to the first. After each iteration, the loop counter is decremented, which means that the program will continue executing the loop until it reaches the defined starting point. It looks like this:

FOR counter IN REVERSE start_value..end_value LOOP
-- Code block that you wish to execute a number of times
END LOOP;

Example

The following example is again very similar to the example used in to show basic loop and while loop above. It loops a counter variable and output the counter value, but this time, an EXIT WHEN statement is not necessary as the WHILE loop controls the conditional run.

DECLARE 
    v_counter NUMBER := 1;
BEGIN
    FOR i IN 1..5 LOOP 
        DBMS_OUTPUT.PUT_LINE('Counter value is: ' || v_counter);
    
    END LOOP;
END;

An explanation of this example is below:

  • I used a FOR loop statement which has a condition to run only where the value of i is between 1 and 5 to open a looped logic. Within each iteration of the loop I:
    • Output the value of the v_counter variable
    • It is not necessary to increment the value of i manually

The output is per the below screenshot:

cursor FOR loop

Syntax

You would use a CURSOR FOR LOOP when you want to fetch and process every record in a cursor efficiently and systematically. This control structure simplifies the retrieval of rows from a database by automatically handling the iteration through each record. The CURSOR FOR LOOP allows for an organised approach, facilitating operations on each record within its defined scope. The loop will terminate when all of the records have been fetched and processed. It looks like this:

FOR record_index IN cursor_name LOOP
  -- Code block that you wish to execute a number of times
END LOOP;

Though, in reality, it really needs to look like this:

DECLARE
  CURSOR c1 IS
    SELECT row_name
    FROM   table_name
    WHERE  condition;
BEGIN 
  FOR rec in c1 LOOP 
    -- Code block that you wish to execute a number of times
  END LOOP;
END;

Example

The following example uses a cursor to return a SQL query result, loop about those results using a cursor FOR loop and output the Full name of all employees who have a location of Seattle.

DECLARE
    v_employee_count NUMBER;
    CURSOR c1 IS
        -- Example cursor for loop
        SELECT emp.first_name, 
               emp.last_name
        FROM   employees   emp,
               departments dep,
               locations   loc
        WHERE  emp.department_id = dep.department_id
        AND    dep.location_id   = loc.location_id
        AND    loc.city          = 'Seattle';
BEGIN
    
    v_employee_count := 0;
    FOR employees_rec in c1 LOOP
        v_employee_count := v_employee_count + 1;
        DBMS_OUTPUT.PUT_LINE('Full Name: ' || employees_rec.first_name || 
                             ' '           || employees_rec.last_name);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('Total Employees in Seattle: ' || v_employee_count);
END;

An explanation of this example is below:

  • I declared a variable v_employee_count to keep a total of number of employees that are processed in the loop
  • I declared a cursor which returns all employees who are based in Seattle
  • I initialised the v_employee_count variable with the value 0
  • I used a cursor FOR loop statement which iterates all the records returned by the cursor. Within each iteration of the loop I:
    • Increment my variable v_employee_count by 1
    • Output the full name of each employee by concatenating first_name and last_name from the employees_rec record together
  • Finally (outside of the loop), I output the total employees based in Seattle, using my v_employee_count variable

The output is per the below screenshot:

Leave a comment