Oracle Database 12c: In Line PL/SQL Functions in SQL queries

At long last it’s here: Oracle Database 12cR1. The download of this fresh software package is hardly done. It is time to start sharing some of our early experiences with this major milestone in the history of Oracle’s flagship product.

The importance of the 12c release lies not necessarily on application development and the further advancement of SQL. Nevertheless, that is where my heart lies and that is where quite interesting new features and functions have been added as well. This article is not about cloud, pluggable databases, information lifecycle management or other administration boons. It is on SQL. Good old – and now good new – SQL.

The first feature I want to introduce is the Inline PL/SQL function.

As an Oracle Database developer you should be aware of the In-Line View – introduced in Oracle 9i, quite some time ago. This In-Line View allows to create Views-on-the-fly inside the scope of a SQL query. The query no longer has to start with SELECT. Instead, it can start with WITH and the definition of one or more views that exist only inside the query and for the duration of the query but otherwise share many of the characteristics of real database views (at least in the eyes of the developer and of the optimizer).

An example of the usage of an In-Line View (one that has been obviated by the TOP-N query feature):

The in-line view ordered_emps is created on the fly. In the main query, we can select from the in-line view as if it were a real database view, making complex queries far easier to compose.

As you probably know – as this TOP-N feature was leaked quite some time ago – in Oracle Database 12c a query to return the top 3 earning employees would be defined like this:

The FETCH FIRST # ROWS option allows us to instruct the database to return only the first # rows from the result of the query.

A more complex example with In-Line Views could look something like:

Here we see one In-Line View referring to another – which is perfectly fine.

Now the world of In-Line stuff has  been extended in Oracle Database 12c. In addition to Views we can now also define PL/SQL functions and procedures as in-line constructs. As before, the query can be written as if it invokes real stored functions; however, these functions do not actually exist as stand-alone objects in the database. The DBA will not have to run DDL scripts for them or worry about their validness. They are created (and destroyed) over the course of the query that has them in-lined.

A simple example of the use of an In-Line Function:

The main query selects inc(sal) from emp. It is obvious that inc must be a function. It is not a standard Oracle Database function, so it has to be a user defined function. However, there is no function inc to be found in ALL_OBJECTS. The function is created in-line, inside the query. It takes a NUMBER as input, it returns a NUMBER and its implementation invokes a procedure to to the actual work. This procedure is also defined in-line.

In addition to the benefits already discussed – no DBA maintenance effort for these in-line functions and procedures and less development struggling because the PL/SQL is readily available (even in queries executed from outside the database such as Forms, Java or .NET) and the obvious drawback that these PL/SQL objects cannot be reused, there is one other tremendous benefit: the context switch that normally happens when PL/SQL is invoked from a SQL query causes a substantial overhead on the query performance in many circumstances. That context switch is removed with the in-line function and procedures. So the use of the PL/SQL logic within the context of the query comes at hardly any price at all.

The In-Line PL/SQL Function (or procedure) can even be a recursive one:

In addition it can do such exotic things such as Dynamic (PL/)SQL and make call outs to real stored procedures (although that of course will have the normal context switch overhead).

Resources

Download Oracle Database 12c at eDelivery or from OTN: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.

Oracle Database 12c Documentation: http://www.oracle.com/pls/db121/homepage. Specific section on Inline PL/SQL Declarations (in SQL Language Reference): http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#BABCGAAJ .