Oracle PL/SQL Cursor For Loops

Cursor For Loop

The following procedure is followed in most of the situations in PL/SQL:

1. Open a cursor
2. Start a loop
3. Fetch the cursor
4. Check whether rows are returned
5. Process
6. Close the loop
7. Close the cursor

Cursor For Loop allows us to simplify this procedure by letting PL/SQL do most of the things for us.

You can simplify coding by using a Cursor For Loop instead of the OPEN, FETCH, and CLOSE statements.

A Cursor For Loop implicitly declares its loop index as a record that represents a row fetched from the database.

Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and then closes the cursor when all rows have been processed.

Cursor For Loop Example

DECLARE CURSOR c1 IS
SELECT ename, sal, deptno
FROM emp;
...
BEGIN
FOR emp_rec IN c1 LOOP
...
salary_total := salary_total +
emp_rec.sal;
END LOOP;

Another example of Cursor For Loop



Let us rewrite the example (used in Cursors with Parameters) again using Cursor For Loop