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

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

Introduction

In the following code, the FOR LOOP statement executes 10 times unless the FETCH statement inside it fails to return a row, in which case it ends immediately.

Demo

SQL>
SQL> drop TABLE emp;
SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),-- w ww . j a v  a  2 s . c  o m
  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    -- Fetch entire row into v_emp record:
  7    FOR i IN 1..10 LOOP
  8      FETCH c1 INTO v_emp;
  9      EXIT WHEN c1%NOTFOUND;
 10      -- Process data here
 11    END LOOP;
 12    CLOSE c1;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>

Related Topic