PLSQL Parameterized Cursor

Parameterized Cursor

Parameterized Cursor:- This cursor is helpful when we need to execute the same query for different value of parameter 

Example:- 
declare
cursor c1(dno number) is select ename  from emp where deptno=dno;
v emp.ename%type;
begin
open c1(10);
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/




declare
cursor c1(dno number) is select ename  from emp where deptno=dno;
v emp.ename%type;
begin
open c1(&dpt);
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

declare
cursor c1(dno emp.deptno%type,vjob emp.job%type) is select ename  from emp
where deptno=dno and job=vjob;
v emp.ename%type;
begin
open c1(&dpt,&ajob);
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

declare
cursor c1(dno emp.deptno%type,vjob emp.job%type) is select *  from emp
where deptno=dno and job=vjob;
v emp%rowtype;
begin
open c1(&dpt,&ajob);
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v.ename||','||v.empno||','||v.deptno);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

declare
cursor c1(dno emp.deptno%type,vjob emp.job%type) is
select empno,ename,job  from emp
where deptno=dno and job=vjob;
v emp%rowtype;
begin
open c1(&dpt,&ajob);
loop
fetch c1 into v.empno,v.ename,v.job;
exit when c1%notfound;
dbms_output.put_line(v.ename||','||v.empno||','||v.job);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/