EXPERT RESPONSE
Functions (in any language, not just PL/SQL) return one value. That may be restrictive,
it's one more than procedures return. So what if you want to return a date and a numeric
code that tells how accurate the date is? What if you want to look up an address, including
city, state and ZIP code? Solutions fall into three broad categories:
- Return a composite object
- Use OUT or IN OUT arguments
- Don't return it: store it
Each of the three approaches is useful in different circumstances.
You might want to use all three approaches at the same time.
1. Return a composite object
We're used to seeing functions that return simple, built-in types, like DATE.
But DATEs aren't really simple, are they? An Oracle DATE contains a date (in the non-Oracle
sense) and a time, and these, in turn, are compounds of year, month, minutes, etc.
So the first, obvious trick you can use is to return a single large object (like a long
VARCHAR2) that you can parse after you have it. (Don't laugh: a comma-separated list of
values might be just the thing you need.)
A function can also return an XMLTYPE, a user-defined type, a PL/SQL table, a cursor,
or any kind of data structure you can have in PL/SQL.
2. Use OUT or IN OUT arguments
FUNCTION emp_addr
(
out_street OUT VARCHAR2,
out_city OUT VARCHAR2,
out_state OUT VARCHAR2,
out_zip_cd OUT VARCHAR2,
emp_id IN NUMBER,
max_len IN NUMBER DEFAULT 100
)
RETURN BOOLEAN
The example above "returns" a single true-false value, but it sets four VARCHAR2 variables
along the way. You can (in fact, must) assign values to these variables in the body
of the function. You can not read them. (Declare the arguments as IN OUT if you want
to do both.) When you call the function, you must supply variables (not literals) for
these arguments, and the variables have to be able to contain whatever the function tries
to put in them. That's the purpose of the max_len argument in the example: I can call the
function with tiny little strings, like this:
DECLARE
street_addr VARCHAR2 (20);
city_name VARCHAR2 (20);
...
BEGIN
...
IF emp_addr (street_addr, city_name, state_abbr, zip_cd, current_id, 20)
THEN
...
without risking a run-time error (only loss of data) as long as my function does
something like:
out_street := SUBSTR (emp_addr, 1, max_len);
Remember to think objectively! The OUT (or IN OUT) arguments can be XMLTYPEs,
user-defined types, etc.
3. Don't return it: store it
Store values in
Ordinary tables
You can return hundreds of values at once.
Temporary tables
Each session can have its own copy of the table
Package variables
Like the variable unclaimed_text in my last question
SYS_CONTEXT variables
For example, here's one way to store a value:
...
dbms_session.set_context ('EMP', error_text, NULL);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
dbms_session.set_context ('EMP', error_text,
SQLERRM || ' = error in emp_addr');
RETURN FALSE;
END emp_addr;
Here's how you retrieve it:
SELECT SYS_CONTEXT ('EMP', error_text)
INTO status_text
FROM dual;
|
