Friday, 11 November 2016

About functions, records and result cache

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.

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!

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!