Oracle PL/SQL Cursor For Loops

The following procedure is followed in most of the situations in PL/SQL:

1. Open a cursor


2. Start a loop


3. Fetch the cursor


4. Check whether rows are returned


5. Process


6. Close the loop


7. Close the cursor

Cursor FOR loop allows us to simplify this procedure by letting PL/SQL do most of the things for us.

Let us rewrite the previous example (used in Cursors with Parameters) again using Cursor FOR loop

Using a cursor FOR loop

DECLARE

CURSOR cur1_dept IS SELECT deptno, dname

FROM dept ORDER BY deptno;

CURSOR cur1_emp (par1_dept VARCHAR2) IS

SELECT ename, salary

FROM emp

WHERE deptno = par1_dept

ORDER BY ename;

var_total_salary NUMBER (10,2);

BEGIN

FOR r1_dept IN cur1_dept LOOP

DBMS_OUTPUT.PUT_LINE ('Department : ' || r1_dept.deptno|| ' - '|| r1_dept.dname);

var_total_salary := 0;

FOR r1_emp IN cur1_emp (r1_dept.deptno) LOOP

DBMS_OUTPUT.PUT_LINE ('Name: ' ||r1_emp.ename || 'Salary:'||r1_emp.salary);

var_total_salary := var_total_salary + r1_emp.salary;

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Total Salary for Dept: ' || var_total_salary);

END LOOP;

END;

/