CREATE OR REPLACE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype; --定义TYPE as Strong REF CURSOR
PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT); --根据不同的choice选择不同的CURSOR
PROCEDURE retrieve_data(choice INT); --通过调用procedure open_emp_cv，返回指定的结果集。
END emp_data;

=============================================

CREATE OR REPLACE PACKAGE BODY emp_data AS

--procedure open_emp_cv--------------------------------------------------------

PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS --emp_cv作为传入/传出的CURSOR PARAMETER
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE empno < 7800;
ELSIF choice = 2 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE SAL < 1000;
ELSIF choice = 3 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE ename like 'J%';
END IF;
END;

--procedure retrieve_data------------------------------------------------------------------

PROCEDURE retrieve_data(choice INT) IS
return_cv empcurtyp; --定义传入open_emp_cv的CURSOR变量
return_row emp%ROWTYPE;
invalid_choice EXCEPTION;
BEGIN
open_emp_cv(return_cv, choice); --调用 procedure OPEN_EMP_CV

IF choice = 1 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less than 7800');
ELSIF choice = 2 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');
ELSIF choice = 3 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');
ELSE
RAISE invalid_choice;
END IF;

LOOP
FETCH return_cv
INTO return_row;
EXIT WHEN return_cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||
return_row.sal);
END LOOP;

EXCEPTION
WHEN invalid_choice THEN
DBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');
END;

END emp_data;

=================================================

执行：
SQL> EXEC emp_data.retrieve_data(1);
EMPLOYEES with empno less than 7800
7369--SMITH--800
7499--ALLEN--1600
7521--WARD--1250
7566--JONES--2975
7654--MARTIN--1250
7698--BLAKE--2850
7782--CLARK--2450
7788--SCOTT--3000
PL/SQL procedure successfully completed

SQL> EXEC emp_data.retrieve_data(2);
EMPLOYEES with salary less than 1000
7369--SMITH--800
7900--JAMES--950
PL/SQL procedure successfully completed

SQL> EXEC emp_data.retrieve_data(3);
EMPLOYEES with name starts with 'J'
7566--JONES--2975
7900--JAMES--950
PL/SQL procedure successfully completed

SQL> EXEC emp_data.retrieve_data(34);
The CHOICE should be in one of (1,2,3)!
PL/SQL procedure successfully completed