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.

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;
/
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.

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;
/
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).

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.

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:

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:

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.

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:

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:

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;
/
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.

Tags: