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
/

Kommentare:

chris hat gesagt…

Zumindest ab 10g gibt es die Built-In-Funktion "collect", welche Ähnliches leisten kann, jedoch allgemeiner ist (siehe Beispiel auf tahiti).

Mit collect würde ich das Beispiel so programmieren:

select deptno,v2_pkg.join(cast(collect(ename) as v2_tbl),',') from emp
group by deptno
/

Mein v2_tbl ist ein table of varchar2(4000), v2_pkg.join konkateniert dessen Einträge mit dem angegebenen Trennzeichen.

Carsten Czarski hat gesagt…

Die Funktion collect (doku) dient dazu, die selektierten Werte in einer NESTED TABLE oder einem VARRAY zu sammeln (hier v2_tbl). Anschließend wird immer noch PL/SQL-Logik benötigt, die das Array prozedural in ein VARCHAR2 umwandelt (v2_pkg.join)

Das User-Defined-Aggregate benötigt den table-of-Typen und das PL/SQL-Paket nicht - die Logik steht im Code des User Defined Aggregates. Ein wichtiger Aspekt ist der Oracle Optimizer. Beim User-Defined-Aggregate "weiss" der Optimizer, dass es hier ums Aggregieren geht: er kann also den Ausführungsplan entsprechend optimieren (wenn er kann ...)

Anonym hat gesagt…

thanks for sample code, this was the only I found for custom aggregate for strings for oracle. It is like "array_accum" for postgres database.

Beliebte Postings