Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- prog:sql_aggregatfunktion_erstellen [2015/03/25 09:56] – [Eine eigene Aggregat Funktion für eine „group by“ Abfrage erstellen] gpipperr
+++ prog:sql_aggregatfunktion_erstellen [2015/03/28 17:54] (aktuell) – [Eine eigene Aggregat Funktion für eine „group by“ Abfrage erstellen] gpipperr
@@ Zeile 1: / Zeile 1: @@
+====== 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
+------ ----------------------------------------------
+CLARK:MILLER:KING
+JONES:ADAMS
+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