PL/SQL User's Guide and Reference

Using Cursor FOR Loops

In most situations that require an explicit cursor, 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 %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

Consider the PL/SQL block below, which computes results from an experiment, then stores the results in a temporary table. The FOR loop index c1_rec is implicitly declared as a record. Its fields store all the column values fetched from the cursor c1. Dot notation is used to reference individual fields.

-- available online in file EXAMP7
DECLARE
   result temp.col1%TYPE;
   CURSOR c1 IS
      SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
   FOR c1_rec IN c1 LOOP
         /* calculate and store the results */
      result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
      INSERT INTO temp VALUES (result, NULL, NULL);
   END LOOP;
   COMMIT;
END;

When the cursor FOR loop is entered, the cursor name cannot belong to a cursor that was already opened by an OPEN statement or by an enclosing cursor FOR loop. Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record, which is equivalent to a record explicitly declared as follows:

c1_rec c1%ROWTYPE;

The record is defined only inside the loop. You cannot refer to its fields outside the loop. For example, the following reference is illegal:

FOR c1_rec IN c1 LOOP
   ...
END LOOP;
result := c1_rec.n2 + 3;  -- illegal

The sequence of statements inside the loop is executed once for each row that satisfies the query associated with the cursor. When you leave the loop, the cursor is closed automatically. This is true even if you use an EXIT or GOTO statement to leave the loop prematurely or if an exception is raised inside the loop.

Using Aliases

Fields in the implicitly declared record hold column values from the most recently fetched row. The fields have the same names as corresponding columns in the query select list. But, what happens if a select item is an expression? Consider the following example:

CURSOR c1 IS
   SELECT empno, sal+NVL(comm,0), job FROM ...

In such cases, you must include an alias for the select-item. In the next example, wages is an alias for the select item sal+NVL(comm,0):

CURSOR c1 IS
   SELECT empno, sal+NVL(comm,0) wages, job FROM ...

To reference the corresponding field, you use the alias instead of a column name, as follows:

IF emp_rec.wages < 1000 THEN ...

Passing Parameters

You can pass parameters to the cursor used in a cursor FOR loop. In the following example, you pass a department number. Then, you compute the total wages paid to employees in that department. Also, you determine how many employees have salaries higher than $2000 and how many have commissions larger than their salaries.

-- available online in file EXAMP8
DECLARE
   CURSOR emp_cursor(dnum NUMBER) IS
      SELECT sal, comm FROM emp WHERE deptno = dnum;
   total_wages NUMBER(11,2) := 0;
   high_paid   NUMBER(4) := 0;
   higher_comm NUMBER(4) := 0;
BEGIN
   /* The number of iterations will equal the number of rows *
    * returned by emp_cursor.                                */
   FOR emp_record IN emp_cursor(20) LOOP
      emp_record.comm := NVL(emp_record.comm, 0);
      total_wages := total_wages + emp_record.sal +
         emp_record.comm;
      IF emp_record.sal > 2000.00 THEN
         high_paid := high_paid + 1;
      END IF;
      IF emp_record.comm > emp_record.sal THEN
         higher_comm := higher_comm + 1;
      END IF;
   END LOOP;
   INSERT INTO temp VALUES (high_paid, higher_comm,
      'Total Wages: ' || TO_CHAR(total_wages));
   COMMIT;
END;