Oracle PL/SQL Cursors With Parameters

Cursors With Parameters

We can pass parameters into a cursor and use them in the query.

We can only pass values to the cursor; and cannot pass values out of the cursor through parameters.

Only the datatype of the parameter is defined, not its length.

Optionally, we can also give a default value for the parameter, which will take effect if no value is passed to the cursor.

Cursors With Parameters Example

The following cursor prints department number and name in one line followed by employees working in that department (name and salary) and total salary.

DECLARE
CURSOR cur_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR cur_emp (par_dept VARCHAR2) IS
SELECT ename, salary
FROM emp
WHERE deptno = par_dept
ORDER BY ename;

r_dept DEPT%ROWTYPE;
var_ename EMP.ENAME%TYPE;
var_salary EMP.SALARY%TYPE;
var_tot_salary NUMBER (10,2);

BEGIN
OPEN cur_dept;
LOOP

FETCH cur_dept INTO r_dept;
EXIT WHEN cur_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Department : ' || r_dept.deptno || ' - '|| r_dept.dname);
var_tot_salary := 0;
OPEN cur_emp (r_dept.deptno);
LOOP
FETCH cur_emp INTO var_ename, var_salary;
EXIT WHEN cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Name: ' ||var_ename || ' Salary:'||var_salary);
var_tot_salary := var_tot_salary + var_salary;
END LOOP;
CLOSE cur_emp;
DBMS_OUTPUT.PUT_LINE ('Total Salary for Dept: ' || var_tot_salary);

END LOOP;
CLOSE cur_dept;
END;
/

Important points to keep in mind for parameters in cursors are:

• The mode of the parameters can only be IN.
• Cursor becomes more reusable with Cursor parameters.
• Default values can be assigned to Cursor parameters.
• The scope of the cursor parameters is local to the cursor.