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;