Oracle SQL: WITH clause

Local functions

with
   function f (a number, b number) return number is
   begin
      return abs(a) + abs(b);
   end f;
   function g (a number, b number) return number is
   begin
      return f(a-5, b+5);
   end g;
   d as (
     select -4 u,  9 v from dual union all
     select  4 u,  9 v from dual union all
     select -2 u, -8 v from dual union all
     select  1 u,  0 v from dual
   )
select
   u,
   v,
   f(u,v) f,
   g(u,v) g
from
   d
;

--
--  Setting a special sql terminator because SQL*Plus is not
--  updated to 12c everywhere...
--
set sqlterminator "!"

create table tq84_inline_function (
  a number,
  b varchar2(10)
)!

insert into tq84_inline_function values ( 5, 'foo')!
insert into tq84_inline_function values ( 7, 'bar')!
insert into tq84_inline_function values ( 2, 'baz')!

with
  function fib(y number) return number as
  begin
     if y<=2 then return 1; end if;
     return fib(y-2) + fib(y-1);
  end fib;
select
  a, fib(a), b
from
  tq84_inline_function
!

drop table tq84_inline_function!

Using the WITH clause together with an INSERT statement

create table tq84_t (col number);

insert into tq84_t
with d as (
   select 42 as num from dual
)
select * from d;

drop table tq84_t;

Combining WITH clause with UNION ALL

with p as (select 'FOO' nam from dual)
   select 'object', owner      from p, dba_objects      where object_name = p.nam union all
   select 'column', table_name from p, dba_tab_columns  where column_name = p.nam;

See also