Oracle PL/SQL - Passing parameters to cursors


Cursors are the primary method of retrieving information from the database.

To dynamically control the cursor when the program is running, use the parameter.

Basic Syntax for Passing Parameters in a Cursor

drop table emp;
create table emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0)

insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
insert into emp values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);

     cursor c_emp (cin_deptNo NUMBER) is                                    
        select count(*)
           from emp
           where deptNo = cin_deptNo;
     v_deptNo dept.deptNo%type:=10;
     v_countEmp NUMBER;
     open c_emp (v_deptNo);                                                
     fetch c_emp into v_countEmp;                                          
     close c_emp;                                                          

Related Topic