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 Loops
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.