Skip to main content

​
What is Loops in PL/SQL?

In PL/SQL, a loop is a fundamental control structure that allows you to execute a sequence of statements repeatedly. Often referred to simply as “iterative control” or “looping constructs,” its core purpose is to automate repetitive tasks.
The primary problem loops solve is the need to process multiple items—like records from a database cursor, elements in a collection, or simply to perform an action a specific number of times—without writing redundant code.
This is a direct application of the DRY (Don’t Repeat Yourself) principle.

​
LOOP EXIT WHEN

This is a basic, unconditional loop. The loop structure begins with LOOP and must be explicitly terminated with an EXIT WHEN statement, which provides the condition for exiting the loop.
The EXIT WHEN condition can be placed anywhere within the loop body, providing maximum flexibility.
DECLARE
  l_counter NUMBER := 1;
BEGIN
  LOOP
    -- This is the loop body. It will execute repeatedly...
    DBMS_OUTPUT.PUT_LINE('Current counter value: ' || l_counter);
    
    -- ...until this exit condition is met.
    EXIT WHEN l_counter >= 5;
    
    l_counter := l_counter + 1; -- Increment the counter
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Loop exited.');
END;
/

​
WHILE LOOP

A WHILE LOOP is a pre-test loop, meaning the condition is evaluated before each iteration. If the condition is TRUE, the loop body executes.
Use WHILE LOOP when the number of iterations is not known beforehand and depends on a dynamic condition.
DECLARE
  l_counter NUMBER := 1;
BEGIN
  -- The condition is checked at the beginning of each iteration.
  WHILE l_counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Current counter value: ' || l_counter);
    l_counter := l_counter + 1; -- If we forget this, it creates an infinite loop.
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Loop exited.');
END;
/

​
FOR LOOP

The FOR LOOP (or numeric FOR loop) is a count-controlled loop. You specify a start point, an end point, and an optional increment (default is 1). The loop index is implicitly declared and incremented/decremented automatically.
The exit condition is built into the loop definition, reducing the chance of errors.
BEGIN
  -- The index `i` is implicitly declared. It increments by 1 from 1 to 5.
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Current index value: ' || i);
    -- No need to manually increment 'i'
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('-- Reverse Loop --');
  -- You can also loop in reverse order.
  FOR i IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Current index value: ' || i);
  END LOOP;
END;
/

​
Cursor FOR Loop

This is a powerful variation of the FOR loop designed specifically for iterating over the results of a query. It implicitly opens the cursor, fetches rows, and closes the cursor, handling the entire cursor lifecycle automatically.
Cursor FOR loops greatly simplify code and eliminate common errors like forgetting to close a cursor.
BEGIN
  -- The loop implicitly handles: OPEN, FETCH, and CLOSE.
  -- 'emp_rec' is a record variable implicitly defined with the structure of the SELECT.
  FOR emp_rec IN (SELECT employee_id, last_name FROM employees WHERE department_id = 50) LOOP
    -- Process each row directly.
    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.employee_id || ' - ' || emp_rec.last_name);
  END LOOP; -- The cursor is automatically closed here.
END;
/

​
Nested Loops

You can place one loop (inner loop) inside another loop (outer loop). This is essential for working with multi-dimensional data.
Be careful with nested loops - they can become computationally expensive with large datasets.
BEGIN
  -- Outer loop
  FOR outer_index IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('Outer loop iteration: ' || outer_index);
    
    -- Inner loop
    FOR inner_index IN 1..2 LOOP
      DBMS_OUTPUT.PUT_LINE('  Inner loop iteration: ' || inner_index);
    END LOOP; -- End of inner loop
    
  END LOOP; -- End of outer loop
END;
/

​
Labels for Loops

A loop label is a name you assign to a loop by enclosing it in double angle brackets (<<label_name>>). Labels are primarily used to improve readability and to qualify the EXIT or CONTINUE statement when working with nested loops.
Use labels to exit specific loops in nested structures.
BEGIN
  <<outer_loop>>  -- Label for the outer loop
  FOR i IN 1..2 LOOP
    <<inner_loop>> -- Label for the inner loop
    FOR j IN 1..2 LOOP
      -- Use the label to exit the outer loop from within the inner loop.
      EXIT outer_loop WHEN i * j > 2;
      DBMS_OUTPUT.PUT_LINE('i: ' || i || ', j: ' || j);
    END LOOP inner_loop;
  END LOOP outer_loop;
  DBMS_OUTPUT.PUT_LINE('Exited both loops.');
END;
/

​
CONTINUE Statement

Introduced in Oracle Database 11g, the CONTINUE statement exits the current iteration of a loop and immediately moves to the next one. It’s useful for skipping over specific values or conditions within a loop. 
BEGIN
  FOR i IN 1..5 LOOP
    -- Skip even numbers
    IF MOD(i, 2) = 0 THEN
      CONTINUE; -- Skip the rest of this iteration and start the next one.
    END IF;
    DBMS_OUTPUT.PUT_LINE('Odd number: ' || i);
  END LOOP;
END;
/

​
Why is Loops in PL/SQL Important?

​
Embodies DRY (Don’t Repeat Yourself)

Loops prevent code duplication by allowing a single block of code to process multiple data elements, making code more maintainable and less error-prone.

​
Enables Scalability

By programmatically handling datasets of any size, loops allow your code to scale efficiently from processing a few rows to millions without a change in the core logic.

​
Implements the Iterator Pattern

Loops, especially Cursor FOR Loops, provide a clean and standard way to sequentially access elements of an aggregate object (like a query result set) without exposing its underlying structure.

​
Advanced Nuances

​
Dynamic Exit Conditions

LOOP EXIT WHEN can have multiple exit conditions based on complex business logic that isn’t known until runtime.
Example: “exit when total sales exceed 1 million OR when 1000 products have been processed”.

​
Exiting Multiple Nested Loops

The primary use-case for loop labels is to break out of an outer loop from deep within a nested structure. Without the label, an EXIT statement would only affect the innermost loop.
This is a crucial technique for optimizing search algorithms in PL/SQL.

​
CONTINUE with Labeled Loops

The CONTINUE statement can also be used with a loop label (e.g., CONTINUE outer_loop;). This will skip the rest of the current iteration of the labeled loop, which is useful for skipping an entire outer loop iteration based on a condition found in an inner loop.

​
How This Fits the Roadmap

Within the “PL/SQL Programming” section of the Advanced PL/SQL Mastery roadmap, Loops are a foundational building block for procedural logic.

​
Direct Prerequisites For:

  • Cursor Management - Understanding cursor attributes (%NOTFOUND, %ROWCOUNT) within loops
  • Bulk Processing (BULK COLLECT, FORALL) - Appreciate performance gains over row-by-row loops
  • Complex Data Processing - Iterating through collections (VARRAYs, Nested Tables)

​
Unlocks:

  • Efficient, scalable data processing routines
  • Intelligent data processing
  • JSON/XML data manipulation
  • Advanced PL/SQL applications
A deep understanding of loops unlocks the ability to write efficient, scalable, and intelligent data processing routines, which is the ultimate goal of the “PL/SQL Programming” mastery path.

Build docs developers (and LLMs) love

Get started for freeTalk to us