Berichtsdaten berechnen lassen: Table Functions

Tabelleninhalte werden in einer Application Express-Anwendung bekanntlich mit Berichten dargestellt - Grundlage eines jeden Berichts ist schließlich eine SQL-Abfrage. Es gibt jedoch Fälle, in denen man die tabellarische Ausgabe gut gebrauchen könnte, die Daten aber gar nicht in einer Tabelle liegen. Ein Beispiel wäre der Tilgungsplan für ein Bankdarlehen: Ein solcher bereitet den Verlauf der Zahlungen tabellarisch auf; die Daten liegen jedoch nicht in einer Tabelle, sondern werden aus wenigen Anfangsparametern wie Darlehenssumme, Zinssatz, Rate oder Zahlungsweise berechnet.

In diesen Fällen helfen PL/SQL Table Functions weiter. Eine Table Function ist wie eine normale PL/SQL-Funktion; sie liefert jedoch keinen skalaren Wert zurück, sondern im Gegensatz dazu eine ganze Tabelle, die sogar beliebig groß werden kann. Im Folgenden wird der Einsatz von Table Functions mit Application Express am Beispiel des Bankdarlehens aufgezeigt.

Datenstrukturen für die Table Function erstellen

Die ersten Schritte werden im SQL Workshop durchgeführt. Zunächst muss die Datenbank wissen, wie die Tabelle, welche die Table Function zurückliefert, aussehen soll. Dazu wird ein entsprechender Datentyp TILGUNGSPLAN_T wie folgt definiert:

create type tilgungsplan_t as object(
  datum       date,
  zinsen      number,
  tilgung     number,
  rate        number,
  restbetrag  number
)
/

create type tilgungsplan_ct as table of tilgungsplan_t
/

Der Typ TILGUNGSPLAN_T beschreibt eine Zeile der Tabelle, die später von der Table Function zurückgegeben werden soll. Sie besteht aus dem Datum, zu welchem die jeweilige Zahlung stattfinden soll, den dann anfallenden Zinsen, dem Tilgungsanteil, der gesamten Rate und schließlich der Restschuld zu diesem Termin. Natürlich werden passende Datentypen verwendet, also für die Spalte DATUM ein DATE und für die anderen Spalten NUMBER. Anschließend wird ein Datentyp TILGUNGSPLAN_CT definiert; dieser repräsentiert die ganze Tabelle. Damit sind alle für die Table Function nötigen Strukturen erzeugt. Übrigens: Obwohl TILGUNGSPLAN_CT die Struktur für die gesamte Tabelle ist, wird diese zu keiner Zeit komplett im Hauptspeicher aufgebaut - die Table Function arbeitet, wie Sie weiter hinten noch sehen werden, zeilenweise.

Table Function erstellen

Als nächstes wird die Table Function selbst erstellt. Dazu dient ein "normales" CREATE FUNCTION -Kommando - Setzen Sie vom SQL Workshop aus den folgenden Code ab:

create or replace function tilgungsplan(
  p_kapital   in number,
  p_zinssatz  in number,
  p_annuitaet in number default null,
  p_beginn    in date   default sysdate, 
  p_zahlweise in number default 1
) return tilgungsplan_ct pipelined 
is
  v_restbetrag number;
  v_datum      date;
  v_zinsen     number;
  v_tilgung    number;
  v_rate       number;
  v_zahlweise  interval year to month;
begin
  v_zahlweise := trunc(p_zahlweise / 12)||'-'||remainder(p_zahlweise, 12);
  v_datum := trunc(sysdate + interval '1' month, 'MONTH');

  v_restbetrag := p_kapital;
  pipe row(tilgungsplan_t(v_datum, 0, 0, 0, v_restbetrag));
  while v_restbetrag > 0 loop
    v_zinsen := round(v_restbetrag * p_zinssatz * p_zahlweise / 1200, 2);
    v_tilgung := p_annuitaet - v_zinsen;
    if v_tilgung < 0 then 
      raise_application_error(-20000, 'Rate zu niedrig');
    end if;
    v_restbetrag := v_restbetrag - v_tilgung;
    if v_restbetrag < 0 then
      v_rate := p_annuitaet + v_restbetrag;
      v_tilgung := v_tilgung + v_restbetrag;
      v_restbetrag := 0;
    else 
      v_rate := p_annuitaet;
    end if;
    v_datum := v_datum + v_zahlweise;
    pipe row(tilgungsplan_t(v_datum, v_zinsen, v_tilgung, v_rate, v_restbetrag));
  end loop;
  return;
end tilgungsplan;
/

Die Funktion ist recht einfach. Zunächst werden einige initiale Werte gesetzt, danach läuft eine Schleife, die solange Zinsen berechnet und Tilgungsanteile von der Restschuld abzieht, bis diese gleich Null ist. Achten Sie zu Beginn auf das Schlüsselwort PIPELINED und innerhalb der WHILE-Schleife auf das Kommando PIPE ROW: Bereits hier wird die Zeile an den Aufrufer zurückgegeben - die Table Function belegt also auch, wenn die berechnete Tabelle sehr groß sein sollte, nur wenig Speicherplatz, da immer nur eine Zeile berechnet und diese sofort an den Aufrufer zurückgegeben wird. Aus diesem Grund werden die Table Functions auch Pipelined Table Functions genannt. Damit die Funktion syntaktisch vollständig ist, wird zum Schluß trotzdem noch ein RETURN-Kommando erwartet - es tut allerdings nichts, da die ganze Tabelle zu diesem Zeitpunkt bereits an den Aufrufer zurückgegeben wurde.

Table Function testen

Testen Sie die Funktion nun. Sie nutzen die Table Function ganz einfach mit einer SELECT-Abfrage. Das folgende Kommando berechnet einen Tilgungsplan für ein Darlehen von 5.000 (Euro) mit einem Zinssatz von 6%, einer Rate von 300 Euro, sofortigem Beginn und monatlicher Zahlweise:

select * from table (tilgungsplan(5000, 6, 300, sysdate, 1))

Das Ergebnis sieht dann in etwa wie in Abbildung 1 aus ...

Abbildung 1: Test der Table Function

Table Function in eine Application Express-Anwendung integrieren

Nun können Sie die Funktion ganz einfach als Bericht in Ihre Anwendung aufnehmen. Erzeugen Sie eine Anwendungsseite mit einer HTML-Region und passenden Elementen für jeden der Funktionsparameter, so dass der Anwender die gewünschte Darlehenssumme, die Rate, den Zinssatz und die Zahlweise selbst eingeben kann. Abbildung 2 zeigt ein Beispiel für eine solche Seite.

Abbildung 2: Eingabe der Berechnungsparameter

Fügen Sie der Anwendung nun einen Bericht zu und verwenden Sie die SQL-Abfrage, die Sie bereits zum Testen genutzt haben. Setzen Sie nun allerdings keine festen Were ein, sondern die Namen der jeweiligen Formularelemente (Abbildung 3). Fügen Sie noch eine Bedingung hinzu, wonach der Bericht nur dann angezeigt wird, wenn das Element für die Darlehenssumme (hier :P1_DARLEHEN) nicht NULL ist (Abbildung 4).

Abbildung 3: Erstellen eines Berichts zur Darstellung des Tilgungsplans

Abbildung 4: Einrichten der Bedingung: Bericht nur darstellen, wenn P1_DARLEHEN nicht NULL ist

Starten Sie die Seite, geben Sie einige Werte ein und testen Sie die Table Function. Das Ergebnis sollte in etwa wie in Abbildung 5 aussehen.

Abbildung 5: Das Ergebnis

Table Functions sind sehr mächtig. Natürlich können auch Daten, die in eigentlichen Tabellen liegen, als Grundlage für eine Berechnung dienen. Table Functions erlauben es Ihnen, nahezu alle Informationen als Tabelle darzustellen - und gerade dann bietet Application Express den Vorteil, dass die standardisierte Komponente Bericht die Darstellung der Ergebnisse ganz einfach macht ...

Zurück zur Community-Seite