prog:sql_aggregatfunktion_erstellen
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
prog:sql_aggregatfunktion_erstellen [2015/03/25 09:55] – 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 " | ||
+ | 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; | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | Passendes Objekt anlegen: | ||
+ | <code sql> | ||
+ | -- als user scott | ||
+ | |||
+ | CREATE OR REPLACE TYPE t_string_agg AS OBJECT | ||
+ | ( | ||
+ | g_string | ||
+ | |||
+ | STATIC FUNCTION ODCIAggregateInitialize(sctx | ||
+ | RETURN NUMBER, | ||
+ | |||
+ | MEMBER FUNCTION ODCIAggregateIterate(self | ||
+ | | ||
+ | | ||
+ | |||
+ | MEMBER FUNCTION ODCIAggregateTerminate(self | ||
+ | | ||
+ | | ||
+ | RETURN NUMBER, | ||
+ | |||
+ | MEMBER FUNCTION ODCIAggregateMerge(self | ||
+ | | ||
+ | RETURN NUMBER | ||
+ | ); | ||
+ | / | ||
+ | |||
+ | SHOW ERRORS | ||
+ | |||
+ | </ | ||
+ | |||
+ | ------------------------------------------------------------------ | ||
+ | |||
+ | <code sql> | ||
+ | CREATE OR REPLACE TYPE BODY t_string_agg IS | ||
+ | STATIC FUNCTION ODCIAggregateInitialize(sctx | ||
+ | RETURN NUMBER IS | ||
+ | BEGIN | ||
+ | sctx := t_string_agg(NULL); | ||
+ | RETURN ODCIConst.Success; | ||
+ | END; | ||
+ | |||
+ | MEMBER FUNCTION ODCIAggregateIterate(self | ||
+ | | ||
+ | RETURN NUMBER IS | ||
+ | BEGIN | ||
+ | SELF.g_string := self.g_string || ':' | ||
+ | RETURN ODCIConst.Success; | ||
+ | END; | ||
+ | |||
+ | MEMBER FUNCTION ODCIAggregateTerminate(self | ||
+ | | ||
+ | | ||
+ | RETURN NUMBER IS | ||
+ | BEGIN | ||
+ | returnValue := RTRIM(LTRIM(SELF.g_string, | ||
+ | RETURN ODCIConst.Success; | ||
+ | END; | ||
+ | |||
+ | MEMBER FUNCTION ODCIAggregateMerge(self | ||
+ | | ||
+ | RETURN NUMBER IS | ||
+ | BEGIN | ||
+ | SELF.g_string := SELF.g_string || ':' | ||
+ | RETURN ODCIConst.Success; | ||
+ | END; | ||
+ | END; | ||
+ | / | ||
+ | SHOW ERRORS | ||
+ | |||
+ | </ | ||
+ | ------------------------------------------------------------------ | ||
+ | |||
+ | <code sql> | ||
+ | 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: | ||
+ | 20 JONES:ADAMS | ||
+ | 30 ALLEN: | ||
+ | |||
+ | </ | ||
+ | |||
+ | 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: | ||
+ | |||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | * http:// | ||
+ | * http:// | ||
prog/sql_aggregatfunktion_erstellen.txt · Zuletzt geändert: 2015/03/28 17:54 von gpipperr