使用具有用户定义类型的包
以下示例在包上下文中包括了前几章中讨论的各种用户定义类型。
包规格 emp_rpt 显示了记录类型 emprec_typ 的声明,以及弱类型 REFCURSOR、emp_refcur 的声明,可随两个函数和两个存储过程公开访问。函数 open_emp_by_dept 返回 REFCURSOR 类型 EMP_REFCUR。过程 fetch_emp 和 close_refcur 均声明弱类型 REFCURSOR 作为形参。
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;
此包中包含加载私有表变量 t_dept 的初始化部分,并使用私有静态游标 dept_cur。t_dept 在函数 get_dept_name 中用作部门名称查找表。
函数 open_emp_by_dept 为给定部门的员工编号和姓名结果集返回 REF CURSOR 变量。此 REF CURSOR 变量随后可传递到过程 fetch_emp,用于检索和列出结果集中的单独行。最后,过程 close_refcur 可用于关闭与此结果集关联的 REF CURSOR 变量。
以下匿名块运行包函数和存储过程。在匿名块的声明部分,记录游标变量 v_emp_cur 的声明,使用包的公有 REFCURSOR 类型 EMP_REFCUR。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,其逻辑是直接编码到匿名块中。在匿名块的声明部分,注意添加的记录变量 r_emp,使用包的公有记录类型 EMPREC_TYP 声明。
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
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论