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;Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
