Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_aggregatfunktion_erstellen

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:

-- als sys
GRANT CREATE TYPE TO scott;
GRANT CREATE PROCEDURE TO scott;

Passendes Objekt anlegen:

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

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

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

Beispiel:

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

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 !):

Quellen

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
prog/sql_aggregatfunktion_erstellen.txt · Zuletzt geändert: 2015/03/28 17:54 von gpipperr