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.
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.
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.
The following is the result of this anonymous block.
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.
The following is the result of this anonymous block.