How best to return multiple pieces of data from a subprogram

Question:
I need to call a program and return a number and a date. I usually write a function to return information. Can a function return two different values?
Answer:
A function can return information in several ways:
• Through the RETURN clause of the function: you can return only a single piece of data through the RETURN clause, but it could be a record containing two or more values.
• Through one or more IN OUT or OUT arguments: you can have as many arguments as you want returning information in the parameter list of the function.
• Through database tables and package variables. You can always "return" information from a stored program unit by putting information in a database table, or modify the state of some package by changing the value of a package-level variable.
A procedure, on the other hand, can return one or more values through IN OUT or OUT arguments, but it does not have a RETURN clause.
Given the nature of your question, I will focus my answer on the first two alternatives, demonstrating the techniques and offering my advice on which approach to take.
Let's start with the principles I follow to decide how to design my subprograms:
• When the purpose of the subprogram is to return information, I generally use a function, since by its very structure is designed to return data. And I do not use the "get" prefix on my function names. A function is designed to "get" stuff; it is redundant to include that word in the name.
• When the purpose of the subprogram is to perform one or more operations and then also return information, I will use a procedure with IN OUT or OUT arguments. Or I might even break up this one subprogram into two different subprograms. I address this issue at the very end of my answer.
• If I am returning multiple pieces of information from a function, I will return a non-scalar structure, such as a record, object type instance, or even a collection when I need to return multiple rows of data.
• My functions generally do not contain IN OUT or OUT arguments, only IN arguments. That is, the only way the function returns data is through the RETURN clause.
• If I decide that I need to have one or more IN OUT or OUT arguments, then I will switch from using a function to a procedure.
In your situation, you need to pass back two pieces of information, a number and date. Let's make that a bit more concrete. For a given employee, I need to obtain the salary and hire date of that employee.
The following program headers all will do the trick:
1. A function returning a record containing the salary and hire date.
CREATE OR REPLACE PACKAGE emp_lookups
IS
TYPE sal_and_hd_rt IS RECORD (
salary employees.salary%TYPE
, hire_date employees.hire_date%TYPE
);

FUNCTION emp_sal_and_hd (
employee_id_in IN employees.employee_id%TYPE )
RETURN sal_and_hd_rt;
END emp_lookups;

Here is a procedure that uses this function:
CREATE OR REPLACE PROCEDURE process_employee (
employee_id_in IN employees.employee_id%TYPE
)
IS
l_data emp_lookups.sal_and_hd_rt;
BEGIN
l_data := emp_lookups.emp_sal_and_hd ( employee_id_in );

IF l_data.salary > 10000
THEN
...
END;
2. A function returning an object type instance containing the salary and hire date.
CREATE OR REPLACE TYPE sal_and_hd_ot IS OBJECT (
salary employees.salary%TYPE
, hire_date employees.hire_date%TYPE
);
/

CREATE OR REPLACE PACKAGE emp_lookups
IS
FUNCTION emp_sal_and_hd (
employee_id_in IN employees.employee_id%TYPE )
RETURN sal_and_hd_ot;
END emp_lookups;
/
Here is a procedure that uses this function:
CREATE OR REPLACE PROCEDURE process_employee (
employee_id_in IN employees.employee_id%TYPE
)
IS
l_data sal_and_hd_ot;
BEGIN
l_data := emp_lookups.emp_sal_and_hd ( employee_id_in );

IF l_data.salary > 10000
THEN
...
3. Call a procedure that returns two values in the parameter list:
CREATE OR REPLACE PACKAGE emp_lookups
IS
PROCEDURE get_emp_sal_and_hd (
employee_id_in IN employees.employee_id%TYPE
, salary_out OUT employees.salary%TYPE
, hire_date_out OUT employees.hire_date%TYPE
);
END emp_lookups;
/
Here is an example of a subprogram using this procedure:
CREATE OR REPLACE PROCEDURE process_employee (
employee_id_in IN employees.employee_id%TYPE
)
IS
l_salary employees.salary%type;
l_hire_date employees.hire_date%type;
BEGIN
emp_lookups.get_emp_sal_and_hd (
employee_id_in , l_salary, l_hire_date);

IF l_salary > 10000
THEN
...

4. A function that uses OUT arguments to return the data, and uses the RETURN clause to return a flag showing whether or not a row was found. Notice that I have kept the "get" prefix on this function name. I did that because this function does indeed get that data, but it does not return that data through the RETURN clause. In this sense, it acts more like a procedure and so I will use my procedure naming convention (which is: the name of the procedure should be in the form verb noun, as in we are taking an action on something: "get salary and hire date") for this function's name.
CREATE OR REPLACE PACKAGE emp_lookups
IS
FUNCTION get_emp_sal_and_hd (
employee_id_in IN employees.employee_id%TYPE
, salary_out OUT employees.salary%TYPE
, hire_date_out OUT employees.hire_date%TYPE
)
RETURN BOOLEAN;
END emp_lookups;
/
Here is an example of a subprogram using this procedure:
CREATE OR REPLACE PROCEDURE process_employee (
employee_id_in IN employees.employee_id%TYPE
)
IS
l_salary employees.salary%type;
l_hire_date employees.hire_date%type;
l_rowfound BOOLEAN;
BEGIN
l_rowfound :=
emp_lookups.get_emp_sal_and_hd (
employee_id_in , l_salary, l_hire_date);

IF NOT l_rowfound
THEN
-- Take a different branch of logic.
...
ELSIF l_salary > 10000
THEN
...
Of these different approaches, I will generally use the first: a function that returns a user-defined record whose structure corresponds to the data I need.
CREATE OR REPLACE PACKAGE emp_lookups
IS
TYPE sal_and_hd_rt IS RECORD (
salary employees.salary%TYPE
, hire_date employees.hire_date%TYPE
);

FUNCTION emp_sal_and_hd (
employee_id_in IN employees.employee_id%TYPE )
RETURN sal_and_hd_rt;
END emp_lookups;
This is, I believe, the most straightforward and understandable implementation for returning multiple elements of data that correspond to a single instance of some item (such as a row in a table), through a function.
If I need to return multiple rows of data, I will then return a collection, usually an associative array. Here is an example of the kind of code I might build for this scenario:
CREATE OR REPLACE PACKAGE emp_lookups
IS
TYPE sal_and_hd_rt IS RECORD (
salary employees.salary%TYPE
, hire_date employees.hire_date%TYPE
);

TYPE sal_and_hd_aat IS TABLE OF sal_and_hd_rt
INDEX BY PLS_INTEGER;

FUNCTION emp_sal_and_hd_rows (
employee_id_in IN employees.employee_id%TYPE )
RETURN sal_and_hd_aat;
END emp_lookups;