Oracle / PLSQL: CURSOR FOR Loop

This Oracle tutorial explains how to use the CURSOR FOR LOOP in Oracle with syntax and examples.

Description

You would use a CURSOR FOR LOOP when you want to fetch and process every record in a cursor. The CURSOR FOR LOOP will terminate when all of the records in the cursor have been fetched.

Syntax

The syntax for the CURSOR FOR LOOP in Oracle/PLSQL is:

FOR record_index in cursor_name
LOOP
   {...statements...}
END LOOP;

Parameters or Arguments

record_index

The index of the record.

cursor_name

The name of the cursor that you wish to fetch records from.

statements

The statements of code to execute each pass through the CURSOR FOR LOOP.

Example

Here is an example of a function that uses a CURSOR FOR LOOP:

CREATE OR REPLACE Function TotalIncome
   ( name_in IN varchar2 )
   RETURN varchar2
IS
   total_val number(6);

   cursor c1 is
     SELECT monthly_income
     FROM employees
     WHERE name = name_in;

BEGIN

   total_val := 0;

   FOR employee_rec in c1
   LOOP
      total_val := total_val + employee_rec.monthly_income;
   END LOOP;

   RETURN total_val;

END;

In this example, we've created a cursor called c1. The CURSOR FOR Loop will terminate after all records have been fetched from the cursor c1.