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