PLSQL Language Referenc-PL/SQL静态SQL-光标变量-光标变量作为子程序参数

--创建包

CREATE OR REPLACE PACKAGE emp_data AS

    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);

END emp_data;

/

CREATE OR REPLACE PACKAGE BODY emp_data AS

    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS

    BEGIN

        --打开光标

        OPEN emp_cv FOR SELECT * FROM employees;

    END open_emp_cv;

END emp_data;

--为选择的语句打开光标变量（具有相同的返回类型）

CREATE OR REPLACE PACKAGE emp_data AS

    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT);

END emp_data;

/

CREATE OR REPLACE PACKAGE BODY emp_data AS

    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS

    BEGIN

        IF choice = 1 THEN

            OPEN emp_cv FOR SELECT * FROM employees

                WHERE commission_pct IS NOT NULL;

        ELSIF choice = 2 THEN

            OPEN emp_cv FOR SELECT * FROM employees

                WHERE salary > 2500;

        ELSIF choice = 3 THEN

            OPEN emp_cv FOR SELECT * FROM employees

                WHERE department_id = 100;

        END IF;

    END;

END emp_data;

--为选择的查询打开光标（具有不同的返回类型）

CREATE OR REPLACE PACKAGE admin_data AS

    TYPE gencurtyp IS REF CURSOR; --弱类型

    PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);

END admin_data;

/

CREATE OR REPLACE PACKAGE BODY admin_data AS

    PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS

    BEGIN

        IF choice = 1 THEN

            OPEN generic_cv FOR SELECT * FROM employees;

        ELSIF choice = 2 THEN

            OPEN generic_cv FOR SELECT * FROM departments;

        ELSIF choice = 3 THEN

            OPEN generic_cv FOR SELECT * FROM jobs;

        END IF;

    END;

END admin_data;