Packages with user-defined types (PL/SQL)
User-defined types can be declared and referenced in packages.
The following example shows a package specification for the EMP_RPT
package. This definition includes the following declarations:
- A publicly accessible record type, EMPREC_TYP
- A publicly accessible weakly-typed REF CURSOR type, EMP_REFCUR
- A publicly accessible subtype, DEPT_NUM, restricted to the range of values from 1 to 99
- Two functions, GET_DEPT_NAME and OPEN_EMP_BY_DEPT; both functions have an input parameter of the subtype DEPT_NUM; the latter function returns the REF CURSOR type EMP_REFCUR
- Two procedures, FETCH_EMP and CLOSE_REFCUR; both declare a weakly-typed REF CURSOR type as a formal parameter
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;
The definition of the associated package body includes the following
private variable declarations:
- A static cursor, DEPT_CUR
- An associative array type, DEPTTAB_TYP
- An associative array variable, T_DEPT
- An integer variable, T_DEPT_MAX
- A record variable, 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;
This package contains an initialization section that loads the private associative array variable T_DEPT, using the private static cursor DEPT_CUR. T_DEPT serves as a department name lookup table in function GET_DEPT_NAME. The function OPEN_EMP_BY_DEPT returns a REF CURSOR variable for the result set of employee numbers and names for a given department. This REF CURSOR variable can then be passed to procedure FETCH_EMP to retrieve and list the individual rows of the result set. Finally, procedure CLOSE_REFCUR can be used to close the REF CURSOR variable that is associated with this result set.
The following anonymous block runs the package functions and procedures. The declaration section
includes the declaration of a scalar variable V_DEPTNO, using the public
SUBTYPE DEPT_NUM and a cursor variable V_EMP_CUR, using the public REF CURSOR type, EMP_REFCUR.
V_EMP_CUR contains a pointer to the result set that is passed between the package function and
procedures.
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;
This
anonymous block produces the following sample
output:EMPLOYEES IN DEPT #30: SALES
EMPNO ENAME
----- -------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
**********************
6 rows were retrieved
The following anonymous block shows another way of achieving the same result. Instead of using
the package procedures FETCH_EMP and CLOSE_REFCUR, the logic is coded directly into the anonymous
block. Note the declaration of record variable R_EMP, using the public record type
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;
This
anonymous block produces the following sample
output:EMPLOYEES IN DEPT #30: SALES
EMPNO ENAME
----- -------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
**********************
6 rows were retrieved