Oracle PL/SQL - EXIT WHEN Statement in Inner FOR LOOP Statement

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. FOR LOOP Statement

Introduction

To exit the inner loop early and complete the current iteration of the outer loop, label the outer loop and specify its name in the CONTINUE WHEN statement.

Demo

SQL>
SQL> drop TABLE emp;

Table dropped.-- from w w w . ja v  a2s  . c  o  m

SQL>
SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King');
SQL> INSERT INTO emp VALUES( 101, 'Mary', 'Smith');
SQL>
SQL> DECLARE
  2    v_emp emp%ROWTYPE;
  3    CURSOR c1 is SELECT * FROM emp;
  4  BEGIN
  5    OPEN c1;
  6
  7    -- Fetch entire row into v_emp record:
  8    <<outer_loop>>
  9    FOR i IN 1..10 LOOP
 10      -- Process data here
 11      FOR j IN 1..10 LOOP
 12        FETCH c1 INTO v_emp;
 13        EXIT outer_loop WHEN c1%NOTFOUND;
 14        -- Process data here
 15      END LOOP;
 16    END LOOP outer_loop;
 17
 18    CLOSE c1;
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL>

Related Topic