How to Use the LOOP Statement in Oracle PL/SQL?

Using Cursor FOR LOOP Statement

You would use a CURSOR FOR Loop when you want to fetch and process every record in a cursor. With PL/SQL, it is very simple to issue a query, retrieve each row of the result into a %ROWTYPE record, and process each row in a loop:

• You include the text of the query directly in the FOR loop.
• PL/SQL creates a record variable with fields corresponding to the columns of the result set.
• You refer to the fields of this record variable inside the loop. You can perform tests and calculations, display output, or store the results somewhere else.

The CURSOR FOR Loop will terminate when all of the records in the cursor have been fetched.

The syntax for the CURSOR FOR Loop is:

FOR record_index in cursor_name
LOOP
      sequence_of_statements
END LOOP;

Here is an example that you can run in SQL*Plus. It does a query to get the name and job Id of employees with manager Ids greater than 120.

BEGIN
  FOR item IN
  ( SELECT last_name, job_id
     FROM employees
     WHERE job_id LIKE '%CLERK%'
     AND manager_id > 120 )
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;

Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record. The sequence of statements inside the loop is executed once for each row that satisfies the query. When you leave the loop, the cursor is closed automatically. The cursor is closed even if you use an EXIT or GOTO statement to leave the loop before all rows are fetched, or an exception is raised inside the loop.

If you need to reference the same query from different parts of the same subprogram, you can declare a cursor that specifies the query, and process the results using a FOR loop.

DECLARE
 CURSOR c1 IS SELECT last_name, job_id FROM employees
                WHERE job_id LIKE '%CLERK%' AND manager_id > 120;
BEGIN
  FOR item IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;

If you don't want to use cursor for loop, you fetch rows one at a time from the cursor c1 into the user-defined record emp_rec using OPEN, FETCH, and CLOSE statements:

DECLARE

 CURSOR c1 IS SELECT last_name, job_id FROM employees
                WHERE job_id LIKE '%CLERK%' AND manager_id > 120;
   emp_rec c1%rowtype;
   ...
BEGIN

  OPEN c1;

  LOOP

     FETCH c1 INTO emp_rec;

     DBMS_OUTPUT.PUT_LINE
      ('Name = ' || emp_rec.last_name || ', Job = ' || emp_rec.job_id);

     ...

     EXIT WHEN c1%NOTFOUND;

  END LOOP;

  CLOSE c1;

END;

Passing Parameters to a Cursor FOR Loop

You can pass parameters to the cursor 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/or commissions larger than their salaries.

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;

END;