Beyond the basic select statement: Using the WITH clause inline PL/SQL

For years I used the basic select statement:

SELECT A.C1, A.C2, B.C1, B.C2, <…> FROM TAB1 A, TAB2 B WHERE A.C1 = B.C1;

Sometimes this basic select statement would not answer my question. You can make a select statement quite complicated, but then it gets unreadable and difficult to maintain. The WITH clause helps out quite a bit



FUNCTION monthly_amt(pamt number)
RETURN number IS
x number;
     x := pamt*2;
     RETURN x;
SELECT pay22, monthly_amt(pay22)
  FROM policys;

create or replace function
  monthly_amt (pamt number) return number is
  x number;
  x := pamt*2;
  return x;
select pay22, monthly_amt(pay22)
from policys;

We are going to start this discussion with the assumption that Query A and Query B are equivalent with caveats. The rules associated with using an inline function is the inline function will always take precedence over a stored function.

The results of Query A returned pay22 and the result of the inline function monthly_amt.


Query B Returns pay22 and the results of the stored function monthly_amt.


But what if the stored function returns a different value the argument * 2?

First we will redefine the stored function monthly_amt to return argument * 10. When using WITH to include an inline function, the inline function will always take precedence over a stored function with the same name.


Now we will call the stored function monthly_amt and not making any reference to an inline function. As expected the stored function returns argument * 10.


How would we use this? PL/SQL supports overloading functions; however both the stored function and the inline function have the same name and use the same arguments. I see using the inline function when: A) creating a stored function is not an option. I worked in one shop a while back where it was against policy to store PL/SQL in the database. B) when you have to overload a stored function. C) When you need / want to read the PL/SQL being executed.

This entry was posted in Beyond the basic select statement and tagged , , by rlockard. Bookmark the permalink.

About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Welcome to oraclewizard