Oracle PL/SQL - Passing parameters to cursors

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Cursor Variables

Introduction

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);


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

Related Topic