包含用户定义的类型的程序包 (PL/SQL)

在程序包中，可以声明和引用用户定义的类型。

以下示例说明 EMP_RPT 程序包的程序包规范。此定义包含下列声明：

可公开访问的记录类型 EMPREC_TYP
可公开访问的弱类型 REF CURSOR 类型 EMP_REFCUR
可公开访问的子类型 DEPT_NUM 仅限于值范围 1 到 99
两个函数 GET_DEPT_NAME 和 OPEN_EMP_BY_DEPT；这两个函数都具有子类型为 DEPT_NUM 的输入参数；后一个函数将返回 REF CURSOR 类型 EMP_REFCUR
两个过程 FETCH_EMP 和 CLOSE_REFCUR；这两个过程都声明了一个弱类型 REF CURSOR 类型作为形参

CREATE OR REPLACE PACKAGE emp_rpt
IS
    TYPE emprec_typ IS RECORD (
        empno       NUMBER(4),
        ename       VARCHAR(10)
    );
    TYPE emp_refcur IS REF CURSOR;
    SUBTYPE dept_num IS dept.deptno%TYPE RANGE 1..99;

    FUNCTION get_dept_name (
        p_deptno    IN dept_num
    ) RETURN VARCHAR2;
    FUNCTION open_emp_by_dept (
        p_deptno    IN dept_num
    ) RETURN EMP_REFCUR;
    PROCEDURE fetch_emp (
        p_refcur    IN OUT SYS_REFCURSOR
    );
    PROCEDURE close_refcur (
        p_refcur    IN OUT SYS_REFCURSOR
    );
END emp_rpt;

相关联程序包主体的定义包含下列私有变量声明：

静态游标 DEPT_CUR
关联数组类型 DEPTTAB_TYP
关联数组变量 T_DEPT
整数变量 T_DEPT_MAX
记录变量 R_EMP

CREATE OR REPLACE PACKAGE BODY emp_rpt
IS
    CURSOR dept_cur IS SELECT * FROM dept;
    TYPE depttab_typ IS TABLE of dept%ROWTYPE
        INDEX BY BINARY_INTEGER;
    t_dept          DEPTTAB_TYP;
    t_dept_max      INTEGER := 1;
    r_emp           EMPREC_TYP;

    FUNCTION get_dept_name (
        p_deptno    IN dept_num
    ) RETURN VARCHAR2
    IS
    BEGIN      
        FOR i IN 1..t_dept_max LOOP
            IF p_deptno = t_dept(i).deptno THEN
                RETURN t_dept(i).dname;
            END IF;      
        END LOOP;
        RETURN 'Unknown';
    END;

    FUNCTION open_emp_by_dept(
        p_deptno    IN dept_num
    ) RETURN EMP_REFCUR
    IS
        emp_by_dept EMP_REFCUR;
    BEGIN      
        OPEN emp_by_dept FOR SELECT empno, ename FROM emp
            WHERE deptno = p_deptno;
        RETURN emp_by_dept;
    END;

    PROCEDURE fetch_emp (
        p_refcur    IN OUT SYS_REFCURSOR
    )
    IS
    BEGIN      
        DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
        DBMS_OUTPUT.PUT_LINE('-----    -------');
        LOOP
            FETCH p_refcur INTO r_emp;
            EXIT WHEN p_refcur%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(r_emp.empno || '     ' || r_emp.ename);
        END LOOP;
    END;

    PROCEDURE close_refcur (
        p_refcur    IN OUT SYS_REFCURSOR
    )
    IS
    BEGIN      
        CLOSE p_refcur;
    END;
BEGIN      
    OPEN dept_cur;
    LOOP
        FETCH dept_cur INTO t_dept(t_dept_max);
        EXIT WHEN dept_cur%NOTFOUND;
        t_dept_max := t_dept_max + 1;
    END LOOP;
    CLOSE dept_cur;
    t_dept_max := t_dept_max - 1;
END emp_rpt;

此程序包包含一个初始化节，该节使用私有静态游标 DEPT_CUR 来装入私有关联数组变量 T_DEPT。在函数 GET_DEPT_NAME 中，将 T_DEPT 用作部门名称查找表。函数 OPEN_EMP_BY_DEPT 返回一个 REF CURSOR 变量，此变量是给定部门的职员编号和姓名的结果集。然后，可以将这个 REF CURSOR 变量传递给过程 FETCH_EMP，以便检索和列示该结果集的各行。最后，可以使用过程 CLOSE_REFCUR 来关闭与此结果集相关联的 REF CURSOR 变量。

以下匿名块运行程序包函数和过程。在声明节中，使用公用 SUBTYPE DEPT_NUM 来声明标量变量 V_DEPTNO，以及使用公用 REF CURSOR 类型 EMP_REFCUR 来声明游标变量 V_EMP_CUR。V_EMP_CUR 包含一个指针，该指针指向在程序包函数和过程之间传递的结果集。

DECLARE
    v_deptno        emp_rpt.DEPT_NUM DEFAULT 30;
    v_emp_cur       emp_rpt.EMP_REFCUR;
BEGIN      
    v_emp_cur := emp_rpt.open_emp_by_dept(v_deptno);
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #' || v_deptno ||
        ': ' || emp_rpt.get_dept_name(v_deptno));
    emp_rpt.fetch_emp(v_emp_cur);
    DBMS_OUTPUT.PUT_LINE('**********************');
    DBMS_OUTPUT.PUT_LINE(v_emp_cur%ROWCOUNT || ' rows were retrieved');
    emp_rpt.close_refcur(v_emp_cur);
END;

此匿名块生成以下样本输出：

EMPLOYEES IN DEPT #30: SALES
EMPNO    ENAME
-----    -------
7499     ALLEN
7521     WARD
7654     MARTIN
7698     BLAKE
7844     TURNER
7900     JAMES
**********************
6 rows were retrieved

以下匿名块说明另一种实现同一结果的方法。即，将逻辑直接编码到匿名块中，而不是使用程序包过程 FETCH_EMP 和 CLOSE_REFCUR。注意，这里使用公用记录类型 EMPREC_TYP 来声明记录变量 R_EMP。

DECLARE
    v_deptno        emp_rpt.DEPT_NUM DEFAULT 30;
    v_emp_cur       emp_rpt.EMP_REFCUR;
    r_emp           emp_rpt.EMPREC_TYP;
BEGIN      
    v_emp_cur := emp_rpt.open_emp_by_dept(v_deptno);
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #' || v_deptno ||
        ': ' || emp_rpt.get_dept_name(v_deptno));
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH v_emp_cur INTO r_emp;
        EXIT WHEN v_emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(r_emp.empno || '     ' ||
            r_emp.ename);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('**********************');
    DBMS_OUTPUT.PUT_LINE(v_emp_cur%ROWCOUNT || ' rows were retrieved');
    CLOSE v_emp_cur;
END;

此匿名块生成以下样本输出：

EMPLOYEES IN DEPT #30: SALES
EMPNO    ENAME
-----    -------
7499     ALLEN
7521     WARD
7654     MARTIN
7698     BLAKE
7844     TURNER
7900     JAMES
**********************
6 rows were retrieved