Cursor For Loop

What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.


Example :- Display  list  of an employees their last name & Salary.

DECLARE
  CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;
BEGIN
  FOR EMPREC IN EMPCUR LOOP
     DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
     EXIT WHEN EMPCUR%ROWCOUNT>=10;
  END LOOP;
END;

We can use Cursor for loop when we want to fetch & precess every record  in a cursor.Cursor for loop will terminate when  all of the records in the cursor have been fetched.

Syntax:-
For record_index in cursor_name
loop
{Statements...}
End loop;


Record Index is is the name of record.

Cursor Name is the name of cursor that you wish to fetch records from it.Statements are the

statements of code to execute through the cursor for loop.

Here is an example of Cursor for loop.


DECLARE
  CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;
BEGIN
  FOR EMPREC IN EMPCUR LOOP
     DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' Earning '||EMPREC.SALARY);
  END LOOP;
END;
/
In this example we have created a cursor Empcur & the Cursor will terminate after all records

have been fetched.
sql> set serveroutput on

Output:- OConnell Earning 2600
Grant Earning 2600
Whalen Earning 4400
Hartstein Earning 15000
Fay Earning 8000
Mavris Earning 6500
Baer Earning 10000
Higgins Earning 12000
Gietz Earning 8300
King Earning 24000
Kochhar Earning 17000
De Haan Earning 17000
Hunold Earning 9000
Ernst Earning 6000
Austin Earning 4800
Pataballa Earning 4800
Lorentz Earning 4200
Greenberg Earning 12000
Faviet Earning 9000
Chen Earning 8200
Sciarra Earning 7700
Urman Earning 7800
Popp Earning 6900
Raphaely Earning 11000
Khoo Earning 3100
Baida Earning 2900
Tobias Earning 2800
Himuro Earning 2600
Colmenares Earning 2500
Weiss Earning 8000
Fripp Earning 8200
Kaufling Earning 7900
Vollman Earning 6500
Mourgos Earning 5800
Nayer Earning 3200
Mikkilineni Earning 2700
Landry Earning 2400
Markle Earning 2200
Bissot Earning 3300
Atkinson Earning 2800
Marlow Earning 2500
Olson Earning 2100
Mallin Earning 3300
Rogers Earning 2900
Gee Earning 2400
Philtanker Earning 2200
Ladwig Earning 3600
Stiles Earning 3200
Seo Earning 2700
Patel Earning 2500
Rajs Earning 3500
Davies Earning 3100
Matos Earning 2600
Vargas Earning 2500
Russell Earning 14000
Partners Earning 13500
Errazuriz Earning 12000