EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL exit
 

PL/SQL exit

Updated April 6, 2023

PL/SQL exit

 

 

Introduction to PL/SQL exit

Pl/SQL exit statement is used for terminating the execution, especially while working with loops and nested loops. In case, if you have a requirement where you need to halt or stop the execution of loop then you can specify the same y making the use of EXIT statement in PL/ SQL program in the LOOP body. There is one more way in which we can use EXIT statement which is along with WHEN statement. The EXIT WHEN statement allows you to specify the condition when you have to exit from that block of code. In this article, we will have a look at the syntax of EXIT and EXIT WHEN, usage of both of them in particular scenarios, and the implementation of these statements along with the help of certain examples.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Use of EXIT

In PL/ SQL, EXIT statement can be used for the following two purposes inside the program –

When working with loops, at the time of recursive execution of loop body, if a certain condition evaluates to true and the flow encounters the EXIT statement then the control is transferred to the statement that is present just below the place where the loop is ending.

When we have nested loops where there is a presence of a loop inside the loop then the use of EXIT statement will make the termination of execution of the innermost loop and the flow of control will be transferred to the end of the block of the innermost loop. The execution will begin from the line after the last line of the innermost loop.

Syntax:

The syntax of the EXIT statement is as shown below in Pl/ SQL –

EXIT;

The use of the above syntax of EXIT is mostly done along with the conditional statements. If the evaluation of the condition inside this statement becomes true then or when it is false in case of ELSE statement, we can make the use of EXIT function inside IF body or ELSE body according to our necessity and requirement.

Example 1

Let us study how we can implement the simple EXIT statement along with the use of conditional statements in PL/ SQL program along with the help of an example –

DECLARE
numberCounter number(2) := 5;
BEGIN
WHILE numberCounter < 15 LOOP
dbms_output.put_line ('Counter value is : ' || numberCounter);
numberCounter := numberCounter + 1;
IF numberCounter > 10 THEN
EXIT;
END IF;
END LOOP;
END;

The output of the above PL/ SQL program is as shown below –

pl exit

In the above example when the value of the number counter becomes more than 10 which is 11 then the condition specified inside the while loop in the if the statement becomes true and execution encounters the EXIT statement which results in the flow of execution being transferred to the statement outside the WHILE loop which stops printing the numbers after 10 in the output.

EXIT WHEN –

There is one more variant of the EXIT statement that comes along with the condition which we can specify there itself when the exit statement should terminate the execution of the block. This variant of EXIT is EXIT WHEN where we can specify the condition which needs to be evaluated. If the condition mentioned after WHEN evaluates to true the transfer of control of execution is made to the statement just below the END LOOP statement. This variant of EXIT is also used mostly when using loops to control the termination of loop execution on a conditional basis.
When the condition mentioned after WHEN statement evaluates to false then the EXIT statement does not terminate the execution and behaves like a NULL statement in that scenario. When the condition becomes true the execution of the loop terminates and the control is transferred to the statement after the END LOOP statement.

Syntax:

The syntax of EXIT WHEN statement in PL/ SQL programming is as shown below:

EXIT WHEN condition to be evaluated

The condition to be evaluated should result in a Boolean value which is either true or false. The use of EXIT THEN statement is helpful as it helps in writing the code for exiting even without using the conditional statements like IF else or IF THEN in PL/ SQL.

Considerations of EXIT WHEN statement –

The EXIT WHEN statement can be used in PL/ SQL considering two main aspects which are listed below –

The value of the condition which is evaluated should be changed by the statements which are present inside the LOOP or else it will be an infinite loop if the condition always evaluates false.

The loop is not terminating till the condition after the WHEN statement evaluates false. The EXIT THEN statement is treated as a NULL statement in that case which just evaluates the condition part.

Example

In order to get clarity in implementation of EXIT WHEN statement in PL/ SQL let us consider one example. Let us try to get the same output as that of the above program but instead of using just EXIT and the conditional IF statement, we will now make the use of EXIT WHEN statement as shown below –

DECLARE
count_variable number(2) := 5;
BEGIN
WHILE count_variable < 15 LOOP
dbms_output.put_line (Counter value is : ' || count_variable);
count_variable := count_variable + 1;
EXIT WHEN count_variable > 10;
END LOOP;
END;

The output of the above code is as shown below which is the same as that of the first example –

pl exit 1

The condition mentioned after the WHEN statement is evaluated for each and every time the WHILE LOOP is traversed. Till the counter variable has a value less than 11 it evaluates false and so the EXIT WHEN statement is considered as the NULL statement. As soon as the value of the counter variable becomes 11 which is greater than 10 the condition becomes true and then the EXIT statement terminates the execution of the while loop and the flow of control is transferred to the statement placed just below the END LOOP statement.

Conclusion – PL/SQL exit

The EXIT and EXIT WHEN statement in PL/ SQL helps to specify the condition in which we can help the termination of the loop on a conditional basis.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL exit” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PL/SQL Cursor Loop
  2. PL/SQL TRIM
  3. PL/SQL Date Functions
  4. SQLite enum

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW