27. März 2007

Table Functions

Eine Funktion selektieren wie eine Tabelle? Table Functions machen es möglich. Im Gegensatz zu einer normalen Funktion liefert eine Table Function eine ganze Ergebnismenge zurück. Und diese Ergebnismenge kann selektiert werden wie eine normale Tabelle.
Was damit möglich ist, zeigt das folgende Beispiel. Anhand der üblichen Angaben wie Darlehenssumme, Zinssatz, Tilgung und Zahlweise wird der Tilgungsplan eines Darlehens berechnet und wie eine Tabelle zurückgegeben. Das Ergebnis lässt sich bspw. mit Oracle Application Express oder anderen Report-Frameworks leicht ins Web bringen.
drop type tilgungsplan_ct
/
drop type tilgungsplan_t
/

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
/

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;
/
sho err

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

Keine Kommentare:

Beliebte Postings