次の例には、以前のトピックで説明したさまざまなユーザー定義型がパッケージのコンテキスト内に組み込まれています。
emp_rpt のパッケージ仕様は、2 つの関数と 2 つのプロシージャに加え、レコード型 emprec_type および弱く型付けされた REF CURSOR emp_refcur
をパブリックにアクセスできるようにする宣言を示しています。 open_emp_by_dept 関数は、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;
FUNCTION get_dept_name (
p_deptno IN NUMBER
) RETURN VARCHAR2;
FUNCTION open_emp_by_dept (
p_deptno IN emp.deptno%TYPE
) 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 NUMBER
) 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 emp.deptno%TYPE
) 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 をロードする初期化セクションが含まれています。 t_dept プライベートテーブル変数は、get_dept_name 関数の部門名ルックアップテーブルとして機能します。
open_emp_by_dept 関数は、特定の部門の従業員番号と名前の結果セットの REF CURSOR 変数を返します。 次に、この REF CURSOR 変数を fetch_emp プロシージャに渡して、結果セットの個々の行を取得してリストすることができます。 最後に、close_refcur プロシージャを使用して、この結果セットに関連付けられている REF CURSOR 変数を閉じることができます。
次の匿名ブロックは、パッケージ関数とプロシージャを実行します。 匿名ブロックの宣言セクションで、EMP_REFCUR (パッケージのパブリック REF CURSOR
型) を使用する v_emp_cur カーソル変数を宣言していることにご注意ください。v_emp_cur には、パッケージ関数とプロシージャの間で渡される結果セットへのポインタが含まれています。
DECLARE
v_deptno dept.deptno%TYPE 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_TYPE (パッケージのパブリックレコード型) として宣言された r_emp レコード変数を追加していることにご注意ください。
DECLARE
v_deptno dept.deptno%TYPE 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