The Cursor FOR Loop

View All Scripts Login to Run Script

Script Name The Cursor FOR Loop
Description An exploration into the very useful and elegant cursor FOR loop, in which we declaratively tell the PL/SQL engine "I want to do X for each row fetched by the cursor." But we leave it up to that fine engine to do all the administrative work for us (open, fetch, close) - PLUS this loop is automatically optimized to return 100 rows with each fetch!
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday May 23, 2016

Statement 1

You can embed the SELECT directly inside the FOR loop. That's easiest, but that also means you cannot reuse the SELECT in another FOR loop, if you have that need.

Simplest Form - SELECT inside the loop

BEGIN 
   FOR rec IN (SELECT * FROM hr.employees) 
   LOOP 
      DBMS_OUTPUT.put_line (rec.last_name); 
   END LOOP; 
END;

King
Kochhar
De Haan
Hunold
Ernst
Austin
Pataballa
Lorentz
Greenberg
Faviet
Chen
Sciarra
Urman
Popp
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
Rajs
Davies
Matos
Vargas
Russell
Partners
Errazuriz
Cambrault
Zlotkey
Tucker
Bernstein
Hall
Olsen
Cambrault
Tuvault
King
Sully
McEwen
Smith
Doran
Sewall
Vishney
Greene
Marvins
Lee
Ande
Banda
Ozer
Bloom
Fox
Smith
Bates
Kumar
Abel
Hutton
Taylor
Livingston
Grant
Johnson
Taylor
Fleaur
Sullivan
Geoni
Sarchand
Bull
Dellinger
Cabrio
Chung
Dilly
Gates
Perkins
Bell
Everett
McCain
Jones
Walsh
Feeney
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
Baer
Higgins
Gietz

Statement 2

You can also declare the cursor explicitly and then reference that in the FOR loop. You can then use that same cursor in another context, such as another FOR loop.

Cursor FOR Loop with Explicit Cursor

DECLARE 
   CURSOR emps_cur 
   IS 
      SELECT * FROM hr.employees; 
BEGIN 
   FOR rec IN emps_cur 
   LOOP 
      DBMS_OUTPUT.put_line (rec.last_name); 
   END LOOP; 
 
   FOR rec IN emps_cur 
   LOOP 
      DBMS_OUTPUT.put_line (rec.salary); 
   END LOOP; 
END;

King
Kochhar
De Haan
Hunold
Ernst
Austin
Pataballa
Lorentz
Greenberg
Faviet
Chen
Sciarra
Urman
Popp
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
Rajs
Davies
Matos
Vargas
Russell
Partners
Errazuriz
Cambrault
Zlotkey
Tucker
Bernstein
Hall
Olsen
Cambrault
Tuvault
King
Sully
McEwen
Smith
Doran
Sewall
Vishney
Greene
Marvins
Lee
Ande
Banda
Ozer
Bloom
Fox
Smith
Bates
Kumar
Abel
Hutton
Taylor
Livingston
Grant
Johnson
Taylor
Fleaur
Sullivan
Geoni
Sarchand
Bull
Dellinger
Cabrio
Chung
Dilly
Gates
Perkins
Bell
Everett
McCain
Jones
Walsh
Feeney
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
Baer
Higgins
Gietz
24000
17000
17000
9000
6000
4800
4800
4200
12008
9000
8200
7700
7800
6900
11000
3100
2900
2800
2600
2500
8000
8200
7900
6500
5800
3200
2700
2400
2200
3300
2800
2500
2100
3300
2900
2400
2200
3600
3200
2700
2500
3500
3100
2600
2500
14000
13500
12000
11000
10500
10000
9500
9000
8000
7500
7000
10000
9500
9000
8000
7500
7000
10500
9500
7200
6800
6400
6200
11500
10000
9600
7400
7300
6100
11000
8800
8600
8400
7000
6200
3200
3100
2500
2800
4200
4100
3400
3000
3800
3600
2900
2500
4000
3900
3200
2800
3100
3000
2600
2600
4400
13000
6000
6500
10000
12008
8300

Statement 3

You can add a parameter list to a cursor, just like you can a function. You can only have IN parameters. Well, that makes sense, right?

Parameterized Cursor FOR Loop

DECLARE 
   CURSOR emps_cur (department_id_in IN INTEGER) 
   IS 
      SELECT * FROM hr.employees 
       WHERE department_id = department_id_in; 
BEGIN 
   FOR rec IN emps_cur (1700) 
   LOOP 
      DBMS_OUTPUT.put_line (rec.last_name); 
   END LOOP; 
 
   FOR rec IN emps_cur (50) 
   LOOP 
      DBMS_OUTPUT.put_line (rec.salary); 
   END LOOP; 
END;

Statement 4
You can declare a cursor at the package level and then reference that cursor in multiple program units. Remember, though, that if you explicitly open a packaged cursor (as in OPEN emus_pkg.emps_cur), it will stay open until you close it explicitly (or disconnect).
Package Based Cursor
```
CREATE OR REPLACE PACKAGE emps_pkg 
IS 
   CURSOR emps_cur 
   IS 
      SELECT * FROM hr.employees; 
END;
```
```
Package created.
```

Statement 5

Using a Package-based Cursor

BEGIN 
   FOR rec IN emps_pkg.emps_cur 
   LOOP 
      DBMS_OUTPUT.put_line (rec.last_name); 
   END LOOP; 
END;

King
Kochhar
De Haan
Hunold
Ernst
Austin
Pataballa
Lorentz
Greenberg
Faviet
Chen
Sciarra
Urman
Popp
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
Rajs
Davies
Matos
Vargas
Russell
Partners
Errazuriz
Cambrault
Zlotkey
Tucker
Bernstein
Hall
Olsen
Cambrault
Tuvault
King
Sully
McEwen
Smith
Doran
Sewall
Vishney
Greene
Marvins
Lee
Ande
Banda
Ozer
Bloom
Fox
Smith
Bates
Kumar
Abel
Hutton
Taylor
Livingston
Grant
Johnson
Taylor
Fleaur
Sullivan
Geoni
Sarchand
Bull
Dellinger
Cabrio
Chung
Dilly
Gates
Perkins
Bell
Everett
McCain
Jones
Walsh
Feeney
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
Baer
Higgins
Gietz

Statement 6
You can even hide the SELECT inside the package body in case you don't want developers to know the details of the query.
Hide SELECT in Package-based Cursor
```
CREATE OR REPLACE PACKAGE emps_pkg 
IS 
   CURSOR emps_cur 
      RETURN hr.employees%ROWTYPE; 
END;
```
```
Package created.
```

Statement 7

CREATE OR REPLACE PACKAGE BODY emps_pkg 
IS 
   CURSOR emps_cur RETURN hr.employees%ROWTYPE 
   IS 
      SELECT * FROM hr.employees; 
END;

Package Body created.

Statement 8

BEGIN
   FOR rec IN emps_pkg.emps_cur
   LOOP
      DBMS_OUTPUT.put_line (rec.last_name);
   END LOOP;
END;

King
Kochhar
De Haan
Hunold
Ernst
Austin
Pataballa
Lorentz
Greenberg
Faviet
Chen
Sciarra
Urman
Popp
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
Rajs
Davies
Matos
Vargas
Russell
Partners
Errazuriz
Cambrault
Zlotkey
Tucker
Bernstein
Hall
Olsen
Cambrault
Tuvault
King
Sully
McEwen
Smith
Doran
Sewall
Vishney
Greene
Marvins
Lee
Ande
Banda
Ozer
Bloom
Fox
Smith
Bates
Kumar
Abel
Hutton
Taylor
Livingston
Grant
Johnson
Taylor
Fleaur
Sullivan
Geoni
Sarchand
Bull
Dellinger
Cabrio
Chung
Dilly
Gates
Perkins
Bell
Everett
McCain
Jones
Walsh
Feeney
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
Baer
Higgins
Gietz

Additional Information

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