10-8. Returning a Collection As a Parameter
Problem
Retrieving a collection of data is a common need. For example, you need a function that returns all employee data and is easily called from any procedure.
Solution
Write a function that returns a complete collection of employee data. In this example, a package is used to globally define a collection of employee records and return all employee data as a collection.
CREATE OR REPLACE PACKAGE empData AS
type emps_type is table of employees%ROWTYPE INDEX BY BINARY_INTEGER;
FUNCTION get_emp_data RETURN emps_type;
END empData;
CREATE OR REPLACE PACKAGE BODY empData as
FUNCTION get_emp_data RETURN emps_type is
cursor driver is
select *
from employees
order by last_name, first_name;
recs emps_type;
begin
open driver;
FETCH driver BULK COLLECT INTO recs;
close driver;
return recs;
end get_emp_data;
end empData;
declare
emp_recs empData.emps_type;
begin
emp_recs := empData.get_emp_data;
dbms_output.put_line ('Employee Records: ' || emp_recs.COUNT);
END;
Executing this block of code produces the following results.
Employee Records: 103
How It Works
By defining a PACKAGE, the data structure emps_type is available for use by any package, procedure, or function that has access rights to it.1 The function get_emp_data within the common package contains all the code necessary to fetch and return the employee data. This common routine can be used by multiple applications that require the employee data for processing. This is a much more efficient method than coding the same select statement in multiple applications.
It is not uncommon to include business rules in this type of function; for example, the routine may fetch only active employees. If the definition of an active employee changes, you need to update only one routine to fix all the applications that use it.
__________
1 To grant access rights, enter the following command: grant execute on empDate to SCHEMA, where SCHEMA is the user that requires access. To grant access to every user in the database, use grant execute on empData to PUBLIC;.