PL/SQL User's Guide and Reference

EXIT Statement

Description

You use the EXIT statement to exit a loop. The EXIT statement has two forms: the unconditional EXIT and the conditional EXIT WHEN. With either form, you can name the loop to be exited. For more information, see "Iterative Control"

Syntax

exit_statement ::=

EXIT [label_name] [WHEN boolean_expression];

Keyword and Parameter Description

EXIT

An unconditional EXIT statement (that is, one without a WHEN clause) exits the current loop immediately. Execution resumes with the statement following the loop.

label_name

This identifies the loop to be exited. You can exit not only the current loop but any enclosing labeled loop.

boolean_expression

This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is evaluated with each iteration of the loop in which the EXIT WHEN statement appears. If the expression yields TRUE, the current loop (or the loop labeled by label_name) is exited immediately. For the syntax of boolean_expression, see "Expressions"

Usage Notes

The EXIT statement can be used only inside a loop.

PL/SQL allows you to code an infinite loop. For example, the following loop will never terminate in the normal way:

WHILE TRUE LOOP
   ...
END LOOP;

In such cases, you must use an EXIT statement to exit the loop.

If you use an EXIT statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.

Examples

The EXIT statement in the following example is illegal because you cannot exit from a block directly; you can exit only from a loop:

DECLARE
   amount  NUMBER;
   maximum NUMBER;
BEGIN
   ...
   BEGIN
      ...
      IF amount >= maximum THEN
         EXIT;  -- illegal
      END IF;
   END;
   ...
END;

The following loop normally executes ten times, but it will exit prematurely if there are less than ten rows to fetch:

FOR i IN 1..10
   FETCH c1 INTO emp_rec;
   EXIT WHEN c1%NOTFOUND;
   total_comm := total_comm + emp_rec.comm;
END LOOP;

The following example illustrates the use of loop labels:

<<outer>>
FOR i IN 1..10 LOOP
   ...
   <<inner>>
   FOR j IN 1..100 LOOP
      ...
      EXIT outer WHEN ...  -- exits both loops
   END LOOP inner;
END LOOP outer;