You will find below an excerpt from my latest publication: the 2
nd edition of
Oracle PL/SQL Best Practices. This edition is a complete rewrite of the 1
st edition. I decided that since software is still, for the most part, written by humans, I would create a cast of characters who write the software I reference and critique in this book.
They work for a company named My Flimsy Excuse, Inc (MFE) and their web site is myflimsyexcuse.com. The mission statement of My Flimsy Excuse is simple and powerful:
Provide a wide array of excuses (flimsy and otherwise) to people in need of a way to explain away questionable behavior.
The MFE founders love technology and don't want to have any flimsy excuses for failure, so they have chosen Oracle as their database technology. They have several different development teams working on various aspects of the web-deployed business plan. The team, which has graciously volunteered to share their experiences with my readers, is made up of the following individuals:
Sunita
The team leader, a very smart woman with a fast, razor-sharp mind. She is always busy, always constructive, and somewhat intimidating. She was a programmer in years past, mostly trained in Fortran and then some C+. She isn't sure if that makes her a better manager or a bigger danger to the team, but she still likes to get her hands "dirty," now and then, writing code.
Delaware
A classic anarchistic, anti-standards, big ego kind of programmer. He has a hard time learning from anyone else, and writes code that is hard to understand....but he is very productive and very bright. If you need a job done overnight (literally), Delaware is the guy to do it. He claims to read up on all the latest features of PL/SQL, but he generally programs in a rut – relying on techniques learned years past in Oracle7. He keeps his thinning hair neatly trimmed, with just a hint of a comb-over, and favors three-piece suits from the Men's Wearhouse.
Lizbeth
The anchor of the team. She used to write in Cobol and is continually shocked at the lack of strong process in the PL/SQL world. She can't understand why programmers today make fun of the Cobol programmers of the past. Didn't they write the software that made the first phase of the Information Revolution a broad success? Lizbeth is methodical and careful, but not, on the whole, the best problem solver—she too easily falls into the trap of seeing things only from her own perspective.
Jasper
The junior member of the team. He is new to PL/SQL and new to MFE. Jasper is eager to learn from anyone and everyone and has nice thick skin, but he is not very creative (not ready to take risks in his code). He always wears jeans, preferably of the distressed variety, complemented by polo shirts with the logos of animals on them. Lizbeth thinks of him as the son she never had, and Delaware treats him like the mascot of the team.
And here is my recommended best practice and explanation for this first blog entry of the new year:
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.
Your function may also then be callable from within a SQL statement, which encourages even wider use of this program. Note, though, that there are restrictions on function calls from SQL. You may not call a function with an OUT argument from within SQL, You also may not call a function that returns a record (the datatypes of all parameters must be SQL-compatible).