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))
/

19. März 2007

SUM(VARCHAR2)? User Defined Aggregates!

GROUP BY wird zusammen mit Aggregatsfunktionen wie SUM oder AVG alltäglich eingesetzt. Manchmal möchte man jedoch auch Zeichenketten aggregieren ... ein Beispiel wäre die Zusammenfassung der Email-Adressen einer Person; durch Kommata getrennt.
    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK, KING, MILLER
        20 SMITH, FORD, ADAMS, SCOTT, JONES
        30 ALLEN, BLAKE, MARTIN, TURNER, JAMES, WARD 
Eine eingebaute Funktion für ein solches VARCHAR2-Aggregat gibt es (noch) nicht. Die gute Nachricht ist jedoch: Die Oracle-Datenbank bietet die Möglichkeit, eine solche Funktion selbst zu bauen. Wie? Mit User-Defined-Aggregates. Das folgende Beispiel zeigt dies anhand der Tabellen EMP und DEPT. Zu jeder Abteilung (DEPT) sollen die jeweiligen Angestellten durch Kommata getrennt zurückgegeben werden.
drop function list;
drop type list_t;

create or replace package list_const_p
is
  list_sep varchar2(10) := ',';
end list_const_p;
/
sho err

create type list_t as object(
  v_liststring varchar2(32767),

  static function ODCIAggregateInitialize(
    sctx IN OUT list_t
  ) return number,
  member function ODCIAggregateIterate(
    self IN OUT list_t, value IN varchar2
  ) return number,
  member function ODCIAggregateTerminate(
    self IN list_t, returnValue OUT varchar2, flags IN number
  ) return number,
  member function ODCIAggregateMerge(
    self IN OUT list_t, ctx2 IN list_t
  ) return number
 );
/
sho err

create or replace type body list_t is

static function ODCIAggregateInitialize(sctx IN OUT list_t)
return number is
begin
  sctx := list_t('');
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
  self IN OUT list_t, 
  value IN varchar2
) return number is
begin
  if (self.v_liststring is null) then
    self.v_liststring:=self.v_liststring || value;
  else
    self.v_liststring:=self.v_liststring || list_const_p.list_sep || value;
  end if;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self IN list_t, 
  returnValue OUT varchar2, 
  flags IN number
) return number is
begin
  returnValue := self.v_liststring;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT list_t, ctx2 IN list_t) return number is
begin
  if ctx2.v_liststring is null and self.v_liststring is not null then
    null;
  elsif ctx2.v_liststring is not null and self.v_liststring is not null then
    self.v_liststring := self.v_liststring || list_const_p.list_sep ||ctx2.v_liststring;
  elsif ctx2.v_liststring is not null and self.v_liststring is null then
    self.v_liststring := ctx2.v_liststring;
  elsif ctx2.v_liststring is null and self.v_liststring is null then
    null;
  end if;
  return ODCIConst.Success;
end;
end;
/
sho err

CREATE or replace FUNCTION list (input varchar2) RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING list_t;
/
sho err
Und so nutzt man die neue Funktion:
exec list_const_p.list_sep := ';';

select deptno, list(ename) from emp
group by deptno
/

exec list_const_p.list_sep := ', ';

select deptno, list(ename) from emp
group by deptno
/

Beliebte Postings