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.

LOOP Statement in PL/SQL

One of the key features in PL/SQL for controlling program flow is the LOOP statement. The LOOP statement is a feature of PL/SQL that allows you to repeatedly execute a block of code until a specified condition is satisfied.

Procedural Language/Structured Query Language (PL/SQL) provides a robust environment for database programming, allowing developers to create powerful and efficient code for Oracle databases.

Syntax

LOOP
   -- Code block to be executed repeatedly
END LOOP;

EXIT Statement

The 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 certain conditions within the loop block.

Syntax

LOOP
   -- Code block
   IF condition THEN
      EXIT;
   END IF;
END LOOP;

Example of PL/SQL LOOP with Conditional EXIT

In this example, we showcase the application of a PL/SQL LOOP construct with a conditional EXIT statement. The code demonstrates a scenario where a loop iterates a specific block of code, printing iteration numbers, and breaks out of the loop when a predefined condition is met.

DECLARE
  counter NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('This is iteration number ' || counter);
    IF counter = 3 THEN
      EXIT;
    END IF;
    counter := counter + 1;
  END LOOP;
END;
/

Output:

Statement processed.
This is iteration number 1
This is iteration number 2
This is iteration number 3

Explanation:

• Initially counter variable is set to 1.
• The LOOP statement repeatedly executes the code block within it.
• Inside the loop, DBMS_OUTPUT.PUT_LINE is used to print Iteration number (value of counter).
• The counter is incremented by 1 in each iteration.
• IF statement is executed when the value of counter will become 3 and The EXIT statement is executed and loop stops.

EXIT WHEN Statement

The EXIT WHEN statement allows for a more concise way to specify the condition under which a loop should exit. It checks the condition directly within the loop's syntax.

Syntax

LOOP
    -- Code block
    EXIT WHEN condition;
END LOOP;

Example of PL/SQL LOOP with EXIT WHEN

The purpose of this example is to show how to print "GeeksForGeeks" repeatedly using a PL/SQL LOOP construct. With the help of the EXIT WHEN statement, the loop can be controlled to end when a counter variable reaches a predetermined threshold.

DECLARE
   counter NUMBER := 1;  -- Initialization of the counter variable

BEGIN
   -- Loop that prints "GeeksForGeeks" five times
   LOOP
      DBMS_OUTPUT.PUT_LINE('GeeksForGeeks');

      counter := counter + 1;  -- Increment the counter

      EXIT WHEN counter > 5;  -- Exit the loop when counter exceeds 5
   END LOOP;
END;
/

Output:

Statement processed.
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks

Explanation:

• Initially counter variable is set to 1.
• The LOOP statement repeatedly executes the code block within it.
• Inside the loop, DBMS_OUTPUT.PUT_LINE is used to print "GeeksForGeeks".
• The counter is incremented by 1 in each iteration.
• The EXIT WHEN statement is executed when the loop when the counter exceeds 5.

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

In this example, we will create nested FOR loops that iterate over two ranges, demonstrating 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;
/

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

Explanation:

• There are two nested loops
• The outer FOR loop (FOR outer_counter IN 1..3 LOOP) runs three times.
• Inside the outer FOR loop, there is an inner FOR 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.

Conclusion

Pl/SQL is a Procedural Language that is used to write program blocks, procedures, functions, cursors, triggers for databases. It Provides a LOOP statement facility that is used to repeatedly execute a block of code. and It's other feature like EXIT and EXIT WHEN statement are used to stop loop based on specific conditions and It also support Nested loop functionality.