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

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.