How to Terminate Loop with FETCH BULK COLLECT LIMIT

View All Scripts Login to Run Script

Script Name How to Terminate Loop with FETCH BULK COLLECT LIMIT
Description Many Oracle Database developers are used to executed "EXIT WHEN my_cursor%NOTFOUND" immediately after their fetch inside a loop. That's fine when you are fetching a single row at a time. But why-ever would you be doing that, when you can use BULK COLLECT and fetch 100+ rows at a time, greatly improving performance? So you switch FETCH BULK COLLECT with the LIMIT clause - but then you must be careful about the statement you use to exit the loop. This script shows you the way.
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday February 22, 2016

Statement 1

Create and Populate Table

CREATE TABLE stuff  
(  
   pk            INTEGER PRIMARY KEY,  
   description   VARCHAR2 (100)  
)

Table created.

Statement 2

BEGIN 
   FOR indx IN 1 .. 4 
   LOOP 
      INSERT INTO stuff (pk, description) 
           VALUES (indx, 'Stuff ' || indx); 
   END LOOP; 
 
   COMMIT; 
END;

1 row(s) inserted.

Statement 3
This code was originally in each of the blocks, rather than repeat it, however, I will define it once at the schema level, and then reference elements in the package. This is perfectly fine for constants and types, but THIS IS NOT A BEST PRACTICE for variables like g_stuff! In particular, when you declare a cursor at package level, you must make very sure to close it when done.
Use Package to Reduce Code in Each Test
```
CREATE OR REPLACE PACKAGE pkg  
IS  
   c_limit   CONSTANT PLS_INTEGER := 3;  
  
   CURSOR stuff_cur  
   IS  
      SELECT * FROM stuff;  
  
   TYPE stuff_aat IS TABLE OF stuff_cur%ROWTYPE  
      INDEX BY PLS_INTEGER;  
  
   g_stuff            stuff_aat;  
END; 
```
```
Package created.
```

Statement 4

This is the approach developers will take out of habit, and as you will see when you run the script, it can leave rows unprocessed. That's because the cursor may be (usually will be) exhausted/emptied before the target collection itself is empty.

EXIT WHEN %NOTFOUND Immediately After Fetch

BEGIN  
   DBMS_OUTPUT.put_line ('EXIT WHEN %NOTFOUND right after fetch');  
  
   OPEN pkg.stuff_cur;  
  
   LOOP  
      FETCH pkg.stuff_cur BULK COLLECT INTO pkg.g_stuff LIMIT pkg.c_limit;  
  
      DBMS_OUTPUT.put_line ('  Fetched ' || pkg.g_stuff.COUNT || ' rows.');  
  
      EXIT WHEN pkg.stuff_cur%NOTFOUND;  
  
      FOR indx IN 1 .. pkg.g_stuff.COUNT  
      LOOP  
         DBMS_OUTPUT.put_line ('  Do stuff ' || indx);  
      END LOOP;  
   END LOOP;  
  
   CLOSE pkg.stuff_cur;  
END;

EXIT WHEN %NOTFOUND right after fetch
  Fetched 3 rows.
  Do stuff 1
  Do stuff 2
  Do stuff 3
  Fetched 1 rows.

Statement 5

This works just fine - you stop when the collection is empty. It's my favorite approach: both correct and easy to follow. Some people object because you will sometimes do an extra fetch (the cursor is already empty from the last fetch), but I don't think this is much of a concern.

EXIT WHEN COUNT = 0 Right after Fetch

BEGIN  
   DBMS_OUTPUT.put_line ('EXIT WHEN COUNT = 0 right after fetch');  
  
   OPEN pkg.stuff_cur;  
  
   LOOP  
      FETCH pkg.stuff_cur BULK COLLECT INTO pkg.g_stuff LIMIT pkg.c_limit;  
  
      DBMS_OUTPUT.put_line ('  Fetched ' || pkg.g_stuff.COUNT || ' rows.');  
  
      EXIT WHEN pkg.g_stuff.COUNT = 0;  
  
      FOR indx IN 1 .. pkg.g_stuff.COUNT  
      LOOP  
         DBMS_OUTPUT.put_line ('  Do stuff ' || indx);  
      END LOOP;  
   END LOOP;  
  
   CLOSE pkg.stuff_cur;  
END;

EXIT WHEN COUNT = 0 right after fetch
  Fetched 3 rows.
  Do stuff 1
  Do stuff 2
  Do stuff 3
  Fetched 1 rows.
  Do stuff 1
  Fetched 0 rows.

Statement 6

This works too. The traditional form, but moved to END of loop (after you've gone through the collection).

EXIT WHEN %NOTFOUND at End of Loop

BEGIN  
   DBMS_OUTPUT.put_line ('EXIT WHEN %NOTFOUND at end of loop');  
  
   OPEN pkg.stuff_cur;  
  
   LOOP  
      FETCH pkg.stuff_cur BULK COLLECT INTO pkg.g_stuff LIMIT pkg.c_limit;  
  
      DBMS_OUTPUT.put_line ('  Fetched ' || pkg.g_stuff.COUNT || ' rows.');  
  
      FOR indx IN 1 .. pkg.g_stuff.COUNT  
      LOOP  
         DBMS_OUTPUT.put_line ('  Do stuff ' || indx);  
      END LOOP;  
  
      EXIT WHEN pkg.stuff_cur%NOTFOUND;  
   END LOOP;  
  
   CLOSE pkg.stuff_cur;  
END;

EXIT WHEN %NOTFOUND at end of loop
  Fetched 3 rows.
  Do stuff 1
  Do stuff 2
  Do stuff 3
  Fetched 1 rows.
  Do stuff 1

Statement 7

This works too. If the collection count is less than the limit, it must be done.

EXIT WHEN COUNT < LIMIT at End of Loop

BEGIN  
   DBMS_OUTPUT.put_line ('EXIT WHEN COUNT < limit at end of loop');  
  
   OPEN pkg.stuff_cur;  
  
   LOOP  
      FETCH pkg.stuff_cur BULK COLLECT INTO pkg.g_stuff LIMIT pkg.c_limit;  
  
      DBMS_OUTPUT.put_line ('  Fetched ' || pkg.g_stuff.COUNT || ' rows.');  
  
      FOR indx IN 1 .. pkg.g_stuff.COUNT  
      LOOP  
         DBMS_OUTPUT.put_line ('  Do stuff ' || indx);  
      END LOOP;  
  
      EXIT WHEN pkg.g_stuff.COUNT < pkg.c_limit;  
   END LOOP;  
  
   CLOSE pkg.stuff_cur;  
END;

EXIT WHEN COUNT < limit at end of loop
  Fetched 3 rows.
  Do stuff 1
  Do stuff 2
  Do stuff 3
  Fetched 1 rows.
  Do stuff 1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database