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
prog/sql_aggregatfunktion_erstellen.txt · Zuletzt geändert: 2015/03/28 17:54 von gpipperr