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

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;
/

In this example,

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

Output

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

EXIT WHEN Statement

It is similar to EXIT statement. The EXIT WHEN statement allows you to directly specify condition within the LOOP statement.

Syntax:

LOOP

— Code block

EXIT WHEN condition;

END LOOP;

Example of PL/SQL LOOP with Printing “GeeksForGeeks”

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;
/

In this example,

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

Output:

Statement processed.
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks

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

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.