ユーザー定義タイプを含むパッケージ (PL/SQL)

パッケージ内でユーザー定義タイプの宣言および参照が可能です。

以下の例では、EMP_RPT パッケージのパッケージ仕様部を示します。この定義には以下の宣言が含まれます。

パブリックで使用可能なレコード・タイプである、EMPREC_TYP
パブリックで使用可能な、緩やかに型付けされた REF CURSOR タイプである、EMP_REFCUR
公開アクセス可能サブタイプ、DEPT_NUM (1 から 99 までの値の範囲に制限)
2 つの関数 GET_DEPT_NAME および OPEN_EMP_BY_DEPT。どちらの関数も、サブタイプ DEPT_NUM の入力パラメーターを伴います。後者の関数は REF CURSOR タイプ EMP_REFCUR を返します。
2 つのプロシージャー、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;

このパッケージは、プライベート連想配列変数の T_DEPT、プライベート静的カーソルを使った DEPT_CUR の初期化部分を含んでいます。 T_DEPT は、関数 GET_DEPT_NAME において、部門名の参照表として機能します。 OPEN_EMP_BY_DEPT 関数は、指定した部門の従業員番号および従業員名を結果に設定した、REF CURSOR 変数を返します。その後、この REF CURSOR 変数をプロシージャー FETCH_EMP に渡すことにより、結果セットの個々の行を取り出し、リストすることができます。最後に、プロシージャー CLOSE_REFCUR を使用すると、この結果セットに関連付けられた REF CURSOR 変数をクローズできます。

以下の無名ブロックでは、パッケージ関数およびプロシージャーを実行します。宣言セクションには、スカラー変数 V_DEPTNO (公開 SUBTYPE DEPT_NUM 使用) およびカーソル変数 V_EMP_CUR (公開 REF CURSOR タイプ、EMP_REFCUR 使用) の宣言が含まれています。 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 を使用する代わりに、無名ブロック内にロジックが直接コーディングされています。無名ブロックの宣言部分では、レコード変数の R_EMP、パブリックレコードタイプを使用して宣言された EMPREC_TYP の追加に注意してください。

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