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 “w3wiki”

The purpose of this example is to show how to print “w3wiki” 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 "w3wiki" five times
   LOOP
      DBMS_OUTPUT.PUT_LINE('w3wiki');

      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 “w3wiki”.
• 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.
w3wiki
w3wiki
w3wiki
w3wiki
w3wiki

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.