Using CURSOR FOR Loop in Oracle

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 favor to fetch and procedure each report in a cursor. The CURSOR FOR LOOP will terminate when all of the files 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 title of the cursor that you wish to fetch records from. statements The statements of code to execute each pass by via the CURSOR FOR LOOP.

Example

Here is an instance of a function that makes use of 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 have created a cursor referred to as c1. The CURSOR FOR Loop will terminate after all documents have been fetched from the cursor c1.