Cursor with parameters in Plsql / PL/SQL Parameterized Cursor

Cursor with parameters
 we can declare cursors with parameters.
Parameterized cursors are also saying static cursors that can passed parameter value when cursor are opened.

Syntax
The syntax for a cursor with parameters in Oracle/PLSQL is:

CURSOR cursor_name (parameter_list)
IS
  SELECT_statement;


Example
For example, you could define a cursor called emp_cursor  as below.


Cursor display employee information from emp_information table whose department_id=80 and Job_id='SA_REP'

DECLARE
CURSOR emp_cursor (p_deptno NUMBER, p_job VARCHAR2)
 IS
SELECT *
FROM employees
WHERE department_id = p_deptno
AND job_id = p_job;

emprec employees%rowtype;
BEGIN

OPEN emp_cursor (80, 'SA_REP');
loop
fetch emp_cursor into emprec;
dbms_output.put_line(emprec.employee_id || ' is a '||emprec.job_id ||
' is in Department '||emprec.department_id );

exit when emp_cursor%notfound;
end loop;

CLOSE emp_cursor;
dbms_output.put_line('----------------------------------------------------------------');
OPEN emp_cursor (60, 'IT_PROG');
loop
fetch emp_cursor into emprec;
dbms_output.put_line(emprec.employee_id || ' is a '||emprec.job_id ||
' is in Department '||emprec.department_id );

exit when emp_cursor%notfound;
end loop;

CLOSE emp_cursor;


END;