EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL exit
Secondary Sidebar
Install Skype on Ubuntu

Java Books

Ubuntu Budgie

Address Binding in an Operating System

Normal and Trace of a Matrix in Java

Preemptive vs Non-Preemptive Scheduling

PL/SQL exit

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.

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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Syntax:

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

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,754 ratings)

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

This is a guide to PL/SQL exit. Here we discuss the Introduction, syntax, use of conditional statements in PL/ SQL program with Examples and code implementation. You may also have a look at the following articles to learn more –

  1. PL/SQL Cursor Loop
  2. PL/SQL TRIM
  3. PL/SQL Date Functions
  4. SQLite enum
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more