====== Eine eigene Aggregat Funktion für eine „group by“ Abfrage erstellen ======

Gelegentlich macht es Sinn sich eine eigene Aggregat Funktion zu erstellen. D.h. eine Funktion kann in einem SQL mit einer „group by“ Abfrage so eingesetzt werden, wie die gewohnten Standard Aggregat Funktionen, wie sum, avg etc.

Im folgenden Beispiel geht es darum Strings "aufzusummieren", d.h. nach rechts anzeigen.
Kann praktisch sein, um zum Beispiel für einen Patienten alle Behandlungsarten in einer Spalte zu zeigen.

\\
Rechte:
<code sql>
-- als sys
grant create type to scott;
grant CREATE procedure to scott;

</code>


Passendes Objekt anlegen:
<code sql>
-- als user scott

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/

SHOW ERRORS

</code>

------------------------------------------------------------------

<code sql>
CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ':' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ':'), ':');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ':' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS

</code>
------------------------------------------------------------------

<code sql>
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS
------------------------------------------------------------------

</code>


Beispiel:

<code>

SELECT deptno
    ,  string_agg(ename) AS emp_list
 FROM emp 
GROUP BY deptno
/ 

DEPTNO EMP_LIST
------ ----------------------------------------------
    10 CLARK:MILLER:KING
    20 JONES:ADAMS
    30 ALLEN:TURNER:MARTIN:WARD

</code>

Ab der Version 11g kann das natürlich einfacher mit einer ANA Funktion gelößt werden, aber nur bis zu einen Länge von 4000 Zeichen (da varchar2 !):
  * => [[prog:oracle_analytic_functions#beispiel_6_-_ein_listen_von_werten_in_sql_erstellen|Oracle Analytic Functions im praktischen Einsatz]]


==== Quellen ====

  * http://docs.oracle.com/cd/B13789_01/appdev.101/b10800/dciaggfns.htm#BEJBFAEH
  * http://docs.oracle.com/cd/B13789_01/appdev