Oracle PL/SQL - Cursor within package

Introduction

The following code defines, in a package, a REF CURSOR type and a procedure that opens a cursor variable parameter of that type.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w ww.j  a  v  a  2  s  .co m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL> --Procedure to Open Cursor Variable for One Query
SQL>
SQL> CREATE OR REPLACE PACKAGE emp_data AS
  2    TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
  3    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);
  4  END emp_data;
  5   /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY emp_data AS
  2    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
  3    BEGIN
  4      OPEN emp_cv FOR SELECT * FROM emp;
  5    END open_emp_cv;
  6  END emp_data;
  7  /

Package body created.

SQL>

In the following code,the stored procedure opens its cursor variable parameter for a chosen query.

The queries have the same return type.

Demo

SQL>
SQL>--   w  w w.  ja  va2  s .c om
SQL> CREATE OR REPLACE PACKAGE emp_data AS
  2    TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
  3    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT);
  4  END emp_data;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY emp_data AS
  2    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS
  3    BEGIN
  4      IF choice = 1 THEN
  5        OPEN emp_cv FOR SELECT *
  6        FROM emp
  7        WHERE commission_pct IS NOT NULL;
  8      ELSIF choice = 2 THEN
  9        OPEN emp_cv FOR SELECT *
 10        FROM emp
 11        WHERE salary > 2500;
 12      ELSIF choice = 3 THEN
 13        OPEN emp_cv FOR SELECT *
 14        FROM emp
 15        WHERE department_id = 100;
 16      END IF;
 17    END;
 18  END emp_data;
 19  /

Package body created.

SQL>

Related Topic