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.