The following example incorporates the various user-defined types discussed in earlier chapters within the context of a package.
The package specification of emp_rpt shows the declaration of a record type, emprec_typ, and a weakly-typed
REF CURSOR, emp_refcur, as publicly accessible along with two functions and two procedures. Function,
open_emp_by_dept, returns the REF CURSOR type, EMP_REFCUR. Procedures, fetch_emp and
close_refcur, both declare a weakly-typed REF CURSOR as a formal parameter. See Record Declaration
and REF CURSORS and Cursor Variables for information on record types and REF CURSORs, respectively.
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;
The package body shows the declaration of several private variables - a static cursor, dept_cur, a table type,
depttab_typ, a table variable, t_dept, an integer variable, t_dept_max, and a record variable,
r_emp. See Static Cursors, Table Declaration, and
Record Declaration for information on static cursors, table types and variables, and record variables,
respectively.
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;
This package contains an initialization section that loads the private table variable, t_dept, using the private
static cursor, dept_cur. t_dept serves as a department name lookup table in function, get_dept_name.
Function, open_emp_by_dept returns a REF CURSOR variable for a 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 associated with this result set.
The following anonymous block runs the package function and procedures. In the anonymous block's declaration section, note the
declaration of cursor variable, v_emp_cur, using the package's public REF CURSOR type, EMP_REFCUR.
v_emp_cur contains the pointer to the result set that is passed between the package function and procedures.
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;
The following is the result of this anonymous block.
INFO: EMPLOYEES IN DEPT #30: SALES
INFO: EMPNO ENAME
INFO: ----- -------
INFO: 7499 ALLEN
INFO: 7521 WARD
INFO: 7654 MARTIN
INFO: 7698 BLAKE
INFO: 7844 TURNER
INFO: 7900 JAMES
INFO: **********************
INFO: 6 rows were retrieved
The following anonymous block illustrates another means of achieving the same result. Instead of using the package procedures,
fetch_emp and close_refcur, the logic of these programs is coded directly into the anonymous block.
In the anonymous block's declaration section, note the addition of record variable, r_emp, declared using the
package's public record type, 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;
The following is the result of this anonymous block.
INFO: EMPLOYEES IN DEPT #30: SALES
INFO: EMPNO ENAME
INFO: ----- -------
INFO: 7499 ALLEN
INFO: 7521 WARD
INFO: 7654 MARTIN
INFO: 7698 BLAKE
INFO: 7844 TURNER
INFO: 7900 JAMES
INFO: **********************
INFO: 6 rows were retrieved