Cursor With Parameter In Oracle

-- In PLSQL we can pass the parameters into a cursor and use them in to query.
Pl/sql parameterized cursor define only datatype of parameter and not need to define it's length.

The following example introduce the cursor with parameter.

DECLARE
CURSOR EMPCUR(deptno NUMBER)
IS
SELECT EMPLOYEE_ID,HIRE_DATE,SUM(SALARY) sal
FROM EMPLOYEES
WHERE DEPARTMENT_ID=deptno
GROUP BY EMPLOYEE_ID,HIRE_DATE;
empr1 EMPCUR%rowtype;
BEGIN
OPEN EMPCUR(10);
LOOP
FETCH EMPCUR INTO empr1;
dbms_output.put_line(empr1.employee_id||' '||empr1.hire_date||' '||empr1.sal);
----------------------------
EXIT WHEN EMPCUR%NOTFOUND;
----------------------------
END LOOP;
CLOSE EMPCUR;
OPEN EMPCUR(20);
LOOP
FETCH EMPCUR INTO empr1;
dbms_output.put_line(empr1.employee_id||' '||empr1.hire_date||' '||empr1.sal);
----------------------------
EXIT WHEN EMPCUR%NOTFOUND;
----------------------------
END LOOP;
CLOSE EMPCUR;
END;
/
Output:-
This cursor will display employees information employee_id,hire_date whose department_id is

20.

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
200 17-SEP-87 4400
200 17-SEP-87 4400
202 17-AUG-97 6000
201 17-FEB-96 13000
201 17-FEB-96 13000

PL/SQL procedure successfully completed.