Oracle CURSOR FOR LOOP statement.

Preamble

Oracle/PLSQL CURSOR FOR LOOP Statement can be used when you want to upload and process each cursor entry. CURSOR FOR LOOP will end when all records are taken out of the cursor.

Syntax of CURSOR FOR LOOP Statement

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

Statement parameters and arguments

• record_index – record index
• cursor_name – the name of the cursor from which you want to extract the records.
• Cursor_name – code which is executed every time you go through a loop.

Here is an example of a function that uses 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 created a cursor with the name c1. CURSOR FOR LOOP will end after all records from the c1 cursor are processed.

PL/SQL tutorial: Cursor FOR Loop In Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.