"Rekursive" Table Functions: Ohne Objekttypen

English title: Recursive able Functions - without object types!

Erst vor kurzem hatte ich ein wenig was über die neue rekursive WITH-Klausel gepostet. Diese ist ab Version 11.2 verfügbar und erlaubt es, hierarchische Abfragen ohne START WITH - CONNECT BY zu formulieren.

Only a short time ago I posted about the new recursive subqueries, which are available in 11.2 and which allow to do recursive queries without START WITH - CONNECT BY.

Interessant ist, dass diese Technik es auch erlaubt, Table Functions auf eine andere Art zu bauen - man kann sich so eine SQL-Abfrage schreiben, die mit Hilfe der Rekursion zusätzliche Tabellenzeilen generieren kann. Hier ein Beispiel: Den Tilgungsplan, der hier als "klassische" Table Function bereitsteht, kann man auch mit einer rekursiven WITH-Klausel bauen. Das sähe dann so aus ...

The interesting bit is that this kind of query could also be used as a replacement for table functions - the recursion allows to create new, "table-independent" rows in a query result. And here is an example: I've done the table function for the "mortgage plan" as a recursive query. This query looks like this ...

col kapital format 999999990D00
col zinsen format 999999990D00
col tilgung format 999999990D00
col restwert format 999999990D00

with konstanten (datum, kapital, zinssatz, rate, rhythmus) as (
  select 
    trunc(sysdate, 'MONTH') datum,
    100000 kapital,
    5 zinssatz,
    600 rate,
    1 rhythmus
  from dual
), 
tilgungsplan (datum, kapital, zinsen, tilgung, restwert) as (
  select 
    to_char(k.datum) datum,
    k.kapital, 
    k.kapital * k.zinssatz / (100 * (12 / k.rhythmus)) zinsen,
    k.rate - k.kapital * k.zinssatz / (100 * (12 / k.rhythmus)) tilgung,
    k.kapital - ( k.rate - k.kapital * k.zinssatz / (100 * (12 / k.rhythmus))) restwert
  from konstanten k
  union all (
   select 
    to_char(add_months(to_date(s.datum),k.rhythmus)) datum,
    s.restwert kapital,
    s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)) zinsen,
    least(
      k.rate - s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)),
      s.restwert
    ) tilgung,
    s.restwert - ( 
      least(
        k.rate - s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)),
        s.restwert
      )
   ) restwert
   from konstanten k, tilgungsplan s
   where restwert > 0 
  )
)
select * from tilgungsplan
/

Der Vorteil ist, dass man keine Objekttypen zum Beschreiben der Ergebnismenge mehr braucht - man kann eine solche Abfrage mit nichts weiter als einem CREATE SESSION-Privileg ausführen. Allerdings sind die "klassischen" Table Functions (im Moment noch) wesentlich schneller - wer möchte, kann es ja vergleichen. Man kann auf jeden Fall festhalten, dass man mit der rekursiven WITH-Klausel mehr machen kann, als Parent-Child-Beziehungen abzufragen ...

As an advantage you don't need to create object types (which you need for pipelined table functions) - so this query could be executed with just the CREATE SESSION privilege. But (currently) this query is significantly slower than the pipelined function. And the result of all this ...? Using the new recursive WITH clause you can do much more than just querying parent-child relationships.