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:
1 | CREATE OR REPLACE function TotalSalary |
2 | ( emp_id_in IN NUMBER) |
3 | RETURN VARCHAR2 |
4 | IS |
5 | total_sal NUMBER(6); |
6 |
7 | CURSOR cur_salary IS |
8 | SELECT salary |
9 | FROM employee |
10 | WHERE employee_id = emp_id_in; |
11 | BEGIN |
12 | total_sal := 0; |
13 | FOR employee_record IN cur_salary |
14 | LOOP |
15 | total_sal := total_sal + employee_record.salary; |
16 | END LOOP; |
17 | RETURN total_sal; |
18 | END; |
In the above example the CURSOR FOR loop will terminate automatically when all the records have been fetched from the CURSOR ‘cur_salary’.