Oracle PL/SQL - Defining cursors in the package spec

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

Description

Defining cursors in the package spec 

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

--Here is the package spec where the cursor is declared.

create or replace package pkg_Util is
     cursor c_emp is                                                    
        select * from emp;
     r_emp c_emp%ROWTYPE;
end;

--Here is a different package that references the cursor

create or replace package body pkg_aDifferentUtil is
     procedure p_printEmps is
     begin
          open pkg_Util.c_emp;                                        
          loop
                fetch pkg_Util.c_emp into pkg_Util.r_emp; 
                exit when pkg_Util.c_emp%NOTFOUND;                   
               DBMS_OUTPUT.put_line(pkg_Util.r_emp.eName);
          end loop;
          close pkg_Util.c_emp;                                      
      end;
end;

Related Topic