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.

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 –

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 –

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