Inline functions in Oracle queries

Defining common table expressions (CTEs) using the WITH clause in SQL queries is a fairly common practice. Oracle has allowed creating inline functions in a query’s WITH clause since version 12. These local functions can make queries more readable and may also lead to more efficient query execution.

Inline functions can be used in a query with or without CTEs. If you do have CTEs in your query, they must be defined after the inline function definition. A query can contain more than one inline function, along with one or more CTEs.

In our first example, we query some dummy data created using a CTE containing a prod_id column and a qty column. Let’s say we need to transform the prod_id column to a category id using the following logic

• For prod_id < 30, category id is the prod_id truncated to the tenths place
  
• For prod_id values between 30 and 89, category id is 80
  
• For prod_id >= 90, category id is the same as the prod_id

In this example, we could just include the logic in the query using a case statement instead of creating an inline function, but assume that for readability we want to separate out the logic. This query shows the use of an inline function to select prod_id, qty and the category_id from our CTE dta. The function get_prod_cat_id is called like any standard function, passing the required parameter (prod_id).

Copycopy code to clipboard
1-----------------------------------------------------------------------
2with
3-----------------------------------------------------------------------
4  -- inline function
5  function get_prod_cat_id(p_prod_id in pls_integer) return pls_integer is
6    -- if prod_id < 30, cat_id = prod_id truncated to the tenths place
7    -- if prod_id between 30 and 89, cat_id = 80
8    -- if prod_id >= 90, cat_id = prod_id
9  begin
10    return (
11    case when p_prod_id < 30
12              then trunc(p_prod_id, -1)    -- truncate to tenths place
13         when p_prod_id < 90
14              then 80
15         else p_prod_id
16    end
17    );
18  end get_prod_cat_id;
19-----------------------------------------------------------------------
20  -- cte
21  dta as (
22    select 11 as prod_id, 100 as qty from dual union all
23    select 15 as prod_id,  68 as qty from dual union all
24    select 29 as prod_id, 222 as qty from dual union all
25    select 34 as prod_id,  35 as qty from dual union all
26    select 39 as prod_id,  23 as qty from dual union all
27    select 94 as prod_id, 876 as qty from dual union all
28    select 99 as prod_id, 555 as qty from dual
29)
30-----------------------------------------------------------------------
31select get_prod_cat_id(prod_id) as cat_id, prod_id, qty
32  from dta
33-----------------------------------------------------------------------
34;

The inline functions can also be referenced in group by or where clauses and they need not be just simple transformations. They can include logic to query other database objects. For our second example, we will look at the employees, departments and locations tables in the Oracle supplied sample schema called hr.

TABLE: EMPLOYEES (selected columns)

TABLE: DEPARTMENTS

TABLE: LOCATIONS

If we need to query employee details from the employees table and show the employee location (city and state) in the output, we could join employees to departments on department_id and then to locations using the location_id. There are some employees who do not have a value in the department_id column, so we will need to do outer joins to departments and locations tables. If we want to display a specific text for such employees that do not have a department id, we will need to check if the department_id is null and assign the text for such rows. Let’s say we want to concatenate the city and state_province columns separated by a comma and a space to show as emp_location in the query output. If a location has no state_province value, we want to show just the value in the city column. Let’s see what the query looks like with an inline function used to generate the emp_location values.

Copycopy code to clipboard
1----------------------------------------------------------------------------------------------------
2with
3----------------------------------------------------------------------------------------------------
4    -- inline function to get employee location
5    function get_emp_loc(p_dept_id in number) return varchar2 as
6      v_location  varchar2(100);
7    begin
8      if p_dept_id is null then -- employees record has null department_id column value
9         return 'Employee is not assigned to a Department';
10      end if;
11      -- return city from locations table; append state_province if there is a value
12      select l.city || nvl2(l.state_province, ', ', '') || l.state_province as location
13        into v_location
14        from hr.departments d
15       inner join hr.locations l using (location_id)
16       where d.department_id = p_dept_id;
17      return v_location;
18    exception
19      when no_data_found then
20        return 'Invalid location id';
21    end get_emp_loc;
22----------------------------------------------------------------------------------------------------
23select employee_id, first_name, last_name, department_id, get_emp_loc(department_id) as emp_location
24  from hr.employees
25 where nvl(department_id,-1) in (-1,10,20,40,70,110)  -- for illustration
26----------------------------------------------------------------------------------------------------
27;

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID	EMP_LOCATION
178	Kimberely	Grant		Employee is not assigned to a Department
200	Jennifer	Whalen	10	Seattle, Washington
201	Michael	Hartstein	20	Toronto, Ontario
202	Pat	Fay	20	Toronto, Ontario
203	Susan	Mavris	40	London
204	Hermann	Baer	70	Munich, Bavaria
205	Shelley	Higgins	110	Seattle, Washington
206	William	Gietz	110	Seattle, Washington

8 rows selected

I filtered on a few departments so we can see the special cases in the output. We capture all the logic for emp_location the inline function and the final select statement fetching data from employees table just needs to call the function get_emp_loc, passing the department_id. I believe PostgreSQL supports inline functions but not BigQuery.

For completeness, this is an equivalent select statement without using an inline function.

Copycopy code to clipboard
1select e.employee_id, e.first_name, e.last_name, e.department_id,
2       case when e.department_id is null then 'Employee is not assigned to a Department'
3            when l.state_province is null then l.city
4            else l.city || ', ' || l.state_province
5       end as emp_location
6  from hr.employees e
7  left join hr.departments d
8       on (d.department_id = e.department_id)
9  left join hr.locations l
10       on (l.location_id = d.location_id)
11 where nvl(e.department_id,-1) in (-1,10,20,40,70,110)
12;

Please note that the performance benefits of inline functions can vary depending on many factors- the complexity of the function, amount of data being queried and database configuration parameters. If the function is complex or is called multiple times from different queries, using a standalone PL/SQL function may be more efficient. The intent here is to introduce the concept of local function definition in WITH clause of Oracle SQL queries.