Nested Loops

Nested Loop is a Loop inside Loop and PL/SQL supports nested loops that allows you to have multiple levels of iteration within a program. This is achieved by placing one or more LOOP statements inside another. Each nested loop has its own set of loop control statements.

Syntax:

— Outer Loop

LOOP

— Code block

— Inner Loop

LOOP

— Inner loop code block

EXIT WHEN inner_condition;

END LOOP;

EXIT WHEN outer_condition;

END LOOP;

Example of PL/SQL Nested FOR Loop Simultaneous Iteration

DECLARE
  outer_counter NUMBER := 1;
  inner_counter NUMBER := 1;
BEGIN
  FOR outer_counter IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('Outer Loop - Iteration ' || outer_counter);
    
    FOR inner_counter IN 1..2 LOOP
      DBMS_OUTPUT.PUT_LINE('Inner Loop - Iteration ' || inner_counter);
    END LOOP;
  END LOOP;
END;
/

In this example,

• There are two nested loops
• The outer loop (FOR outer_counter IN 1..3 LOOP) runs three times.
• Inside the outer loop, there is an inner loop (FOR inner_counter IN 1..2 LOOP) that runs two times for each iteration of the outer loop.
• DBMS_OUTPUT.PUT_LINE statements is used to print output.

Output:

Statement processed.
Outer Loop - Iteration 1
Inner Loop - Iteration 1
Inner Loop - Iteration 2
Outer Loop - Iteration 2
Inner Loop - Iteration 1
Inner Loop - Iteration 2
Outer Loop - Iteration 3
Inner Loop - Iteration 1
Inner Loop - Iteration 2

PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases it extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers. It is a block-structured language that combines SQL with the procedural features of programming languages.

In this article, we will learn about How to use the Loop statement of PL/SQL with all its features like EXIT, EXIT WHEN, and Nested Loop for example.