Oracle PL/SQL - Cursor within package

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

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  w w  . j  a  v  a 2s.c  om

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>--  ww  w  . ja  va  2  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