Using functions in WITH clause in Oracle 12c

The WITH Clause, or Subquery Refactoring

If you haven’t heard of the WITH clause, let me take a moment to explain what it is.

The WITH clause is a clause in SQL that allows you to define a query before your SELECT statement, and use that query definition inside your main SELECT statement.

It’s helpful for several reasons:

• Improves readability for large queries
• Can be easier to maintain
• May improve performance

The WITH clause is an implementation of a concept called “subquery refactoring”, because the WITH clause is often used to define subqueries that are later used in the SELECT query.

WITH Clause Example

Let’s take a look at an example of the WITH clause.

First, let’s look at a query before the WITH clause is used:

1
2
3
4
5
6

SELECT first_name, last_name  
FROM student  
WHERE enrolment_date =   
(SELECT MAX(enrolment_date)  
FROM student  
)

This query finds all students that were enrolled on the last day that any student enrolled.

It uses a subquery to find the MAX of the enrolment_date column.

Let’s change this into a WITH clause.

1
2
3
4
5
6

WITH maxEnrolDate AS  
SELECT MAX(enrolment_date)  
FROM student  
SELECT first_name, last_name  
FROM student  
WHERE enrolment_date = maxEnrolDate;  

You can see that the maxEnrolDate has been defined in the WITH clause, then referred to in the query.

This is a simple example, but it can improve your query for more complex queries.

Using Functions

What if your query had to use some logic that couldn’t be done using standard SQL?

You could use a function to get the value you need and call the function from the SELECT statement, but then you would have to:

• create a separate function object in the database
• assign privileges to it
• maintain it in the future

Another way to do this would be this new feature in Oracle, which allows you to define and use functions in the WITH clause.

Functions in the WITH Clause

Defining functions in the WITH clause is now possible in Oracle 12c. It’s one of the many new features in this version that are useful for developers.

You can define these functions which are just used by your query. They don’t take up permanent storage space in your database, and you don’t need to apply security to them to ensure that only your query accesses it.

The function is compiled when you run your query.

Functions in the WITH clause are defined as:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

WITH  
FUNCTION findLatestEnrolmentDate IS  
BEGIN  
SELECT MAX(enrolment_date)  
FROM student;  
END;  
SELECT first_name, last_name  
FROM student  
WHERE enrolment_date = findLatestEnrolmentDate;  
 

This is a simple example, and you may not need a function for this exact logic. But, if you have more complicated logic and rules within your SELECT query, you may want to consider using a function inside your WITH clause, as it can improve your query.

So, using functions in the WITH clause is a handy feature of Oracle 12c. It has the possibiity of improving your queries in certain situations.

Ref: https://community.oracle.com/blogs/bbrumm/2016/09/19/using-functions-in-with-clause-in-oracle-12c