CURSOR FOR Loop in Oracle PLSQL

A CURSOR FOR Loop in Oracle PLSQL is used whenever we want to retrieve and process every record within a cursor.

The CURSOR FOR loop automatically gets terminated as soon as all the records in the cursor are fetched.

The Syntax for the CURSOR FOR LOOP in Oracle PLSQL is:

FOR cursor_records IN cursor_name
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a CURSOR FOR Loop in Oracle PLSQL is:

CREATE OR REPLACE function TotalSalary
   ( emp_id_in IN NUMBER)
   RETURN VARCHAR2
IS
   total_sal NUMBER(6);

   CURSOR cur_salary IS
     SELECT salary
     FROM employee
     WHERE employee_id = emp_id_in;
BEGIN
   total_sal := 0;
   FOR employee_record IN cur_salary
   LOOP
      total_sal := total_sal + employee_record.salary;
   END LOOP;
   RETURN total_sal;
END;

In the above example the CURSOR FOR loop will terminate automatically when all the records have been fetched from the CURSOR ‘cur_salary’.


Tagged , , , , , , , , , , , . Bookmark the permalink.