This article is about functions and how to get more out of them if you combine them with record structures.
Functions are the instrument in the Oracle Database to execute code and return a value. Nothing new here, everybody who develops in PL/SQL on the database knows that.
In case you’re just starting with Oracle and want to know more about procedures and functions, check out this post by David Njoku: “Oracle for Absolute Beginners: Part 6 – Procedures and Functions“.
Functions returning only one value
Let’s start with the easy stuff… the following function will retrieve a record from a table and concatenate the first and last name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE OR REPLACE FUNCTION get_full_name(p_employee_id IN hr.employees.employee_id%TYPE) RETURN VARCHAR2 IS CURSOR c_employees IS SELECT first_name , last_name FROM hr.employees WHERE employee_id = p_employee_id; r_employees c_employees%ROWTYPE; BEGIN OPEN c_employees; FETCH c_employees INTO r_employees; CLOSE c_employees; RETURN r_employees.first_name ||' '||r_employees.last_name; END; / |
|
1 2 3 4 5 6 |
BEGIN dbms_output.put_line(get_full_name(100)); END; / Steven King |
Nothing fancy, just fetching one record from the table and returning the concatenated values. The simplest form of a function.
Functions returning multiple values
But if I need a function that returns multiple values, how can I do that?
You can add an OUT(or IN/OUT) parameter in the functions parameter list.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE OR REPLACE FUNCTION get_full_name( p_employee_id IN hr.employees.employee_id%TYPE , p_first_name OUT hr.employees.first_name%TYPE , p_last_name OUT hr.employees.last_name%TYPE ) RETURN VARCHAR2 IS CURSOR c_employees IS SELECT first_name , last_name FROM hr.employees WHERE employee_id = p_employee_id; r_employees c_employees%ROWTYPE; BEGIN OPEN c_employees; FETCH c_employees INTO r_employees; CLOSE c_employees; p_first_name := r_employees.first_name; p_last_name := r_employees.last_name; RETURN r_employees.first_name ||' '||r_employees.last_name; END; / |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE first_name hr.employees.first_name%TYPE; last_name hr.employees.last_name%TYPE; BEGIN dbms_output.put_line(get_full_name(100, first_name, last_name)); dbms_output.put_line(first_name); dbms_output.put_line(last_name); END; / Steven King Steven King |
This was just an example, you shouldn’t create a function with OUT or IN/OUT parameters!
In the following article Steven Feuerstein gives you some arguments: “Do not put OUT or IN OUT arguments into the parameter list of a function“. I agree on that one. A function is created to RETURN a value, use it that way.
We have to return multiple variables in one RETURN statement. We can achieve this by using record variables.
So… let us agree that we return a record type for returning multiple output parameters instead of using OUT parameters in the function. In this case, to make it easy, I will use a record structure based on the structure of a table (%ROWTYPE).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE OR REPLACE FUNCTION get_employee(p_employee_id IN hr.employees.employee_id%TYPE) RETURN employees%ROWTYPE IS CURSOR c_employees IS SELECT * FROM hr.employees WHERE employee_id = p_employee_id; r_employees c_employees%ROWTYPE; BEGIN OPEN c_employees; FETCH c_employees INTO r_employees; CLOSE c_employees; RETURN r_employees; END; / |
Now, we have all the values we need in different record fields and we can format the way we want to.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE r_employee employees%ROWTYPE; BEGIN r_employee := get_employee(100); dbms_output.put_line(r_employee.last_name ||', '||r_employee.first_name); END; / King, Steven |
Functions returning a record structure, but only needing 1 value
“That record structure is nice, but I only need 1 value”. When I use a record structure, I first need to assign the RETURN value to a local variable and then use the local variable. A little bit of overhead?
What a lot of people don’t know, is that you don’t have to assign the record to a local variable, you can use the fields of the record directly in your function. Say what? Yes, this is possible:
|
1 2 3 4 5 6 |
BEGIN dbms_output.put_line(get_employee(100).last_name); END; / King |
When you only need one variable from a function returning a record, you can use “function_name().field_name”. Interesting, isn’t it?
Of course you can also do this:
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN dbms_output.put_line(get_employee(100).last_name); dbms_output.put_line(get_employee(100).first_name); dbms_output.put_line(get_employee(100).last_name ||', '||get_employee(100).first_name); END; / King Steven King, Steven |
Indeed, no need to declare a variable based on the record structure. But there’s a disadvantage! The function is executed several times in the previous example. We don’t want that!
Functions returning a record structure combined with Result Cache
One of Oracle’s caching mechanisms is “Function Result Cache”. This means that Oracle caches the result of the function and when there’s a new call to the function with the same input, it returns the result that is stored in the cache. Wouldn’t it be cool if we could use that?
A little test case. I’ll write a line using dbms_output.put_line every time I enter the function. Now we can see how many times the function will be executed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE OR REPLACE FUNCTION get_employee(p_employee_id IN hr.employees.employee_id%TYPE) RETURN employees%ROWTYPE IS CURSOR c_employees IS SELECT * FROM hr.employees WHERE employee_id = p_employee_id; r_employees c_employees%ROWTYPE; BEGIN dbms_output.put_line('execute get_employee'); OPEN c_employees; FETCH c_employees INTO r_employees; CLOSE c_employees; RETURN r_employees; END; / |
And execute this block of code again:
|
1 2 3 4 5 6 7 8 9 10 |
BEGIN dbms_output.put_line(get_employee(100).last_name ||', '||get_employee(100).first_name); END; / execute get_employee execute get_employee King, Steven |
As you can see, the function is executed twice.
Now we add the result cache:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE OR REPLACE FUNCTION get_employee(p_employee_id IN hr.employees.employee_id%TYPE) RETURN employees%ROWTYPE RESULT_CACHE IS CURSOR c_employees IS SELECT * FROM hr.employees WHERE employee_id = p_employee_id; r_employees c_employees%ROWTYPE; BEGIN dbms_output.put_line('execute get_employee'); OPEN c_employees; FETCH c_employees INTO r_employees; CLOSE c_employees; RETURN r_employees; END; / |
|
1 2 3 4 5 6 7 8 9 10 |
BEGIN dbms_output.put_line(get_employee(100).last_name ||', '||get_employee(100).first_name); END; / execute get_employee King, Steven |
As you can see the function is executed only once!
Conclusion
We created a nice API to get information from a table that is flexible(we can get one or more fields from a table) and that uses a caching mechanism to gain performance(the query isn’t executed every time I need the same information).
This will certainly contribute to the maintainability of your application.
But…
There’s a little downside in using record structures in a function – you can’t use it in SQL. Not even “get_employee(100).last_name” will work in SQL.
Load comments