10-7. Passing a Collection As a Parameter
Problem
You want to pass a collection as a parameter to a procedure or function. For example, you have a data structure that contains employee data, and you need to pass the data to a routine that prints employee paychecks.
Solution
Create a collection of employee records to hold all employee data, and then pass the data to the subroutine to process the paychecks. The TYPE statement defining the data structure must be visible to the called procedure; therefore, it must be defined globally, prior to defining any procedure or function that uses it.
In this example, employee data is fetched from the database into a collection and then passed to a subroutine for processing.
set serverout on size 1000000
DECLARE
CURSOR driver IS
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY last_name, first_name;
TYPE emps_type IS TABLE OF driver%ROWTYPE;
recs emps_type;
PROCEDURE print_paycheck (emp_recs emps_type) IS
BEGIN
FOR i IN 1..emp_recs.COUNT LOOP
DBMS_OUTPUT.PUT ('Pay to the order of: '),
DBMS_OUTPUT.PUT (emp_recs(i).first_name || ' ' || emp_recs(i).last_name);
DBMS_OUTPUT.PUT_LINE (' $' || to_char (emp_recs(i).salary, 'FM999,990.00'));
END LOOP;
END;
BEGIN
OPEN driver;
FETCH driver BULK COLLECT INTO recs;
CLOSE driver;
print_paycheck (recs);
END;
Results
Pay to the order of: Ellen Abel $11,000.00
Pay to the order of: Sundar Ande $6,400.00
Pay to the order of: Mozhe Atkinson $2,800.00
… <<snip>>
Pay to the order of: Alana Walsh $3,100.00
Pay to the order of: Matthew Weiss $8,000.00
Pay to the order of: Eleni Zlotkey $10,500.00
How It Works
TYPE globally defines the data structure as a collection of records for use by the PL/SQL block and the enclosed procedure. This declaration of both the type and the procedure at the same level—inside the same code block—is necessary to allow the data to be passed to the function. The type and the procedure are within the same scope, and thus the procedure can reference the type and accept values of the type.
Defining the recs structure as a collection makes it much easier to pass large amounts of data between routines with a single parameter. The data structure emps_type is defined as a collection of employee records that can be passed to any function or procedure that requires employee data for processing. This recipe is especially useful in that the logic of who receives a paycheck can be removed from the routine that does the printing or the routine that archives the payroll data, for example.