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).
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 .
- Making up records in SQL Queries – Table Functions and 10g Model clause
- Using .NET Stored Procedures in Oracle 10gR2
- Dynamic and Conditional grouping in SQL queries – for flexible results from single query (oh and a useful case for the CUBE operator)
- Oracle Database Cross Session Data Cache – Introducing the Globally Accessible Database Context
- The Pro Active Database – Advanced Application Development with the Oracle Database
- Steven Feuerstein Master Class. Anti-Pattern PL/SQL Programming + 12c New PL/SQL Features, December 12 and 13 2013
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- On the integrity of data in Java applications – presentation from JFall 2013
- WebLogic & FMW Provisioning update
- The road ahead for WebLogic 12c
- Enriching XMLType data using relational data – XQuery and fn:collection in action
- Java 8 – Collection enhancements leveraging Lambda Expressions – or: How Java emulates SQL
- OOW13: summarizing one week and 2000 sessions in 3 hours and a bit – the yearly AMIS OOW Review session – 10th October
- OOW13 and JavaOne 13: Notes from a Conference – Part Two
- OOW13: First glimpses of the new SOA Suite 12c