Can you pass a parameter to a cursor?
- PL/SQL also allows you to pass parameters into cursors. It eases your work because:
1. A parameter makes the cursor more reusable.
2. A parameter avoids scoping problems.
- However, you should pass parameters when you are going to use it at more then one place and when there are going to be different values for the same WHERE statement.
Example:Parameterized cursor:
/*Create a table*/
create table Employee
(
ID VARCHAR2(4 BYTE)NOT NULL,
First_Name VARCHAR2(10 BYTE)
);
/*Insert some data*/
Insert into Employee (ID, First_Name) values ('01','Harry');
/*create cursor*/
declare
cursor c_emp(cin_No NUMBER)is select count(*) from employee where id=cin_No;
v_deptNo employee.id%type:=10;
v_countEmp NUMBER;
begin
open c_emp (v_deptNo);
fetch c_emp into v_countEmp;
close c_emp;
end;
/*Using cursor*/
Open c_emp (10);
