Aug
9
Written by:
StevenFeuersteinTW
Tuesday, August 09, 2011 6:56 AM
I received this request from a reader in July:
"Could you please explain why Oracle recommends that we not use OUT/IN-OUT parameters in functions?"
Actually, it's not just Oracle. Most "gurus" in the software world make the same suggestion. There are two key reasons for the recommendation that you should only return data through the RETURN clause of a function, not the parameter list. I sum up these answers here, and then I offer a section from
Oracle PL/SQL Best Practices, 2nd edition, that explores the same topic using the "story" style of that book.
Two key reasons to use only IN parameters with functions:
-
The whole point of a function is to return a value (whether it’s a single, scalar value or a composite, such as a record or a collection). If you also return data back through the parameter list with OUT or IN OUT arguments, the purpose and usage of the function will be obscured.
-
A function that contains an OUT or IN OUT parameter cannot be called from within a SQL statement. Here's an example:
Perhaps you don't often run across the need to call your own application-specific functions inside SQL. I take advantage of this feature frequently in the backend and APEX code of the
PL/SQL Challenge, a site that offers quizzes on PL/SQL, SQL and APEX.
So remember: just because Oracle let's you do something (like define an OUT parameter in a function's parameter list), doesn't mean that you should do it!
Where'd that data come from?
AKA: Functions should return data only through the RETURN clause.
Problem: Jasper returns data in a very confusing manner.
Jasper needs a program to retrieve several pieces of information about an excuse: the title, the author and the word count. In just a few moments he builds the following function:
FUNCTION excuse_title (
excuse_id_in IN mfe_excuse.isbn%TYPE
, author_out OUT mfe_excuse.author%TYPE
, word_count_out OUT mfe_excuse.word_count%TYPE)
RETURN mfe_excuse.title%TYPE
IS BEGIN
... implementation unimportant! ...
END excuse_title;
And then he puts this function to use as follows:
PROCEDURE process_excuse (excuse_id_in IN mfe_excuse.isbn%TYPE)
IS
l_title mfe_excuse.title%TYPE;
l_author mfe_excuse.author%TYPE;
l_word_count mfe_excuse.word_count%TYPE;
BEGIN
l_title := excuse_title (l_id, l_author, l_word_count);
...
Jasper is proud that he took the time to put this retrieval logic into its own program. Now it can be used in many places in the application. He shows everyone excuse_title at the weekly code review session. Imagine his dismay when Delaware snorts:
Humph. Well, Jasper, it's absolutely peachy keen that you wrote a reusable function. But that is not a program I would ever want to use. The name says you are giving me a title, and you are, but then you are also passing back all that other stuff. It's self-contradictory and I have to declare a bunch of individual variables to use the darned thing!
Jasper pouts; he was so looking forward to a pat or two on the back. Sunita gives Delaware a dirty look and shakes her head. "Jasper," she says, drawing his attention away from Delaware's gloomy outlook, "hiding the lookup was an excellent move. You just need to take a step or two further, and make sure there are no mixed messages. Let's redesign excuse_title together."
Solution: Return multiple values through a single, composite structure or with a procedure.
Here are the steps that Sunita takes to revamp the lookup function and make it more useful:
-
Make sure the name of the program reflects what it does: In this case, the program doesn't return just the title; it returns several pieces of information about an excuse. Let's call it the "excuse_info" function.
-
Pass everything in the RETURN clause: Rather than returning one value through the RETURN clause and another through the parameter list, let's pass everything back in the RETURN clause, using a composite structure—in this case, a record.
Here, then, is the new header of the program:
FUNCTION excuse_info (id_in IN mfe_excuse.id%TYPE)
RETURN mfe_excuse%ROWTYPE
And here is the revised usage of this function:
PROCEDURE process_excuse (excuse_id_in IN mfe_excuse.isbn%TYPE)
IS
l_excuse mfe_excuse%ROWTYPE;
BEGIN
l_excuse := excuse_info (l_id);
Now the code is leaner and cleaner. Everything that is returned by the function is deposited into a single record. And if you are concerned about returning all the data in a table's row, when you need only a small subset of its columns, you can always create your own user-defined record, as shown here:
PACKAGE excuse_pkg
IS
TYPE key_info IS RECORD (
title mfe_excuse.title%TYPE
, author mfe_excuse.author%TYPE
, word_count mfe_excuse.word_count%TYPE
);
FUNCTION excuse_info (id_in IN mfe_excuse.id%TYPE)
RETURN key_info;
END excuse_pkg;
To sum up: the whole point of a function is to return a value (whether it’s a single, scalar value or a composite, such as a record or a collection). If you also return data back through the parameter list with OUT or IN OUT arguments, the purpose and usage of the function will be obscured.
If you need to return multiple pieces of information, take one of the following approaches:
-
Return a record or collection of values
Make sure to publish the structure of your record or collection (the TYPE statement) in a package specification so that developers can understand and use the function more easily.
-
Break up a single function into multiple functions, all returning scalar values
With this approach, you can call the functions from within SQL statements.
-
Change a function into a procedure
Unless you need to call a function to return this information, just change it to a procedure returning multiple pieces of information through the OUT arguments in the parameter list
If you follow these guidelines, your subprograms will be more likely to be used and reused, because they will be defined in ways that make them easy to understand and apply in your own code.