PL/SQL Best Practices [2] [Cursor Management]- Cursor For Loop

The cursor FOR loop is one of the best examples of integration between procedurality of PL/SQL and set-at-a-time processing of SQL. The cursor FOR loop executes the body of loop for every record identified by the cursor which greatly reduces code volume and the chance for error.

1FOR emp_rec IN emp_cur
2   LOOP
3      give_raise 
4         (emp_rec.empno, 10000);
5   END LOOP;
6

There are substantial code savings with using a cursor FOR loop. For example:

Using a simple loop to process all records in cursor:

 1DECLARE
 2   CURSOR emp_cur IS  ;
 3   emp_rec emp_cur%ROWTYPE;
 4BEGIN
 5   OPEN emp_cur;
 6   LOOP
 7      FETCH emp_cur INTO emp_rec;
 8      EXIT WHEN emp_cur%NOTFOUND;
 9      give_raise (emp_rec.empno, 10000);
10   END LOOP;
11   CLOSE emp_cur;
12END;

Using a cursor FOR loop to do the same:

1DECLARE
2   CURSOR emp_cur IS  ;
3BEGIN
4   FOR emp_rec IN emp_cur
5   LOOP
6      give_raise (emp_rec.empno, 10000);
7   END LOOP;
8END;
9

With the cursor FOR loop, you do not have to explicitly:

Declare a record into which the cursor’s row is fetched.
Open the cursor.
Fetch from the cursor into the record.
Detect last record fetched in cursor.
Close the cursor.
All these steps are performed automatically by PL/SQL which means you write less code and you have fewer opportunities for bugs.
Only use the cursor FOR loop when you want to perform an action for every record fetched. You should not exit prematurely from the loop using conditional logic.