prog:sql_liste_erstellen
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
prog:sql_liste_erstellen [2019/12/13 11:22] – [11g Lösung mit listagg] gpipperr | prog:sql_liste_erstellen [2019/12/20 10:29] (aktuell) – [Ab Oracle 19- LISTAGG distinct] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ===== Ein Listen von Werten in SQL erstellen ===== | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | Aufgabe: Ein separierte Liste soll aus den Werten einer SQL Abfrage erstellt werden. | ||
+ | |||
+ | |||
+ | ==== 11g Lösung mit listagg ==== | ||
+ | |||
+ | Ab 11g kann das gut mit einer [[prog: | ||
+ | <code sql> | ||
+ | SQL> | ||
+ | , listagg(ename,':' | ||
+ | from emp group by deptno | ||
+ | / | ||
+ | |||
+ | DEPTNO | ||
+ | ---------- | ||
+ | 10 CLARK: | ||
+ | 20 ADAMS:JONES | ||
+ | 30 ALLEN: | ||
+ | |||
+ | </ | ||
+ | |||
+ | Mit listagg kann eine Liste bis maximal 4000 Zeichen aggregiert werden. | ||
+ | |||
+ | |||
+ | Quelle, siehe http:// | ||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Ab Oracle 12r2 - LISTAGG overflow ==== | ||
+ | |||
+ | |||
+ | Ab der Version 12c R2 kann auf den ärgerlichen " | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | SQL> | ||
+ | , listagg(ename,':' | ||
+ | from emp group by deptno | ||
+ | / | ||
+ | |||
+ | DEPTNO | ||
+ | ---------- | ||
+ | 10 CLARK: | ||
+ | 20 ADAMS:JONES | ||
+ | 30 ALLEN: | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | Doku => https:// | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Ab Oracle 19- LISTAGG distinct ==== | ||
+ | |||
+ | Ab 19c können auch Duplicate in der Liste aussortiert werden! | ||
+ | |||
+ | |||
+ | siehe auch => https:// | ||
+ | ---- | ||
+ | |||
+ | |||
+ | ==== Eigene Aggregat Funktion verwenden um einen ORA-01489 zu vermeiden ==== | ||
+ | |||
+ | Eine Alternative Lösung ist eine eigene Aggregate Funktion. | ||
+ | |||
+ | siehe dazu => [[prog: | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== SYS.STRAGG verwenden ==== | ||
+ | |||
+ | Die SYS Funktion **STRAGG** kann auch verwendet werden, allerdings wird hier der zuvor ermittelte Wert aller Zielen jeweils pro Zeile mit ausgegeben. | ||
+ | |||
+ | Die Funktion scheint all Input Variable einfach zuvor zu summieren, unabhängig von der aktuellen Zeile. | ||
+ | |||
+ | <code sql> | ||
+ | SELECT | ||
+ | , sys.stragg(ename||';' | ||
+ | FROM scott.emp | ||
+ | where deptno=10 | ||
+ | GROUP BY deptno | ||
+ | / | ||
+ | |||
+ | DEPTNO EMP_LIST | ||
+ | ------------ -------------------------------------------------- | ||
+ | 10 CLARK; | ||
+ | | ||
+ | |||
+ | -- Bei mehr als einer Treffer Zeile wird das Ergebnis mehrdeutig! | ||
+ | SELECT | ||
+ | , sys.stragg(ename||';' | ||
+ | FROM scott.emp | ||
+ | where deptno in (10, | ||
+ | GROUP BY deptno | ||
+ | / | ||
+ | |||
+ | | ||
+ | ------------ -------------------------------------------------- | ||
+ | 10 CLARK; | ||
+ | 20 CLARK; | ||
+ | |||
+ | </ | ||
+ | |||
+ | D.h. ein Einsatz ist eher zu vermeiden, zumal das keine offizielle Funktion ist. | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Die Collect Methode ==== | ||
+ | |||
+ | Collect Methode: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | SQL> | ||
+ | , collect(ename) as emp_list | ||
+ | from emp | ||
+ | group by deptno | ||
+ | / | ||
+ | |||
+ | |||
+ | DEPTNO EMP_LIST | ||
+ | ------ ---------------------------------------------------------------------- | ||
+ | 10 SYSTPFOeGQC1UQMGz4nUc6QoSaw==(' | ||
+ | 20 SYSTPFOeGQC1UQMGz4nUc6QoSaw==(' | ||
+ | 30 SYSTPFOeGQC1UQMGz4nUc6QoSaw==(' | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Die wmsys.wm_concat Funktion ==== | ||
+ | |||
+ | Die wmsys.wm_concat: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | SQL> | ||
+ | |||
+ | DEPTNO EMP_LIST | ||
+ | ------ ---------------------------------------------------------------------- | ||
+ | 10 CLARK, | ||
+ | 20 JONES,ADAMS | ||
+ | 30 ALLEN, | ||
+ | |||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ==== XML verwenden ==== | ||
+ | |||
+ | Daten in XML verwandeln und wieder in Text zerlegen: | ||
+ | <code sql> | ||
+ | |||
+ | SQL> | ||
+ | , rtrim ( xmlagg (xmlelement (c, ename || ',' | ||
+ | from emp | ||
+ | group by deptno; | ||
+ | |||
+ | DEPTNO EMP_LIST | ||
+ | ------ ---------------------------------------------------------------------- | ||
+ | 10 CLARK, | ||
+ | 20 JONES,ADAMS | ||
+ | 30 ALLEN, | ||
+ | |||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== in PL/SQL eine Liste wieder zerlegen ==== | ||
+ | |||
+ | Mit Hilfe von **dbms_utility.comma_to_table** lässt sich eine Liste in ein Array transformieren. | ||
+ | |||
+ | Beispiel: | ||
+ | <code SQL> | ||
+ | |||
+ | declare | ||
+ | |||
+ | v_tab_length | ||
+ | v_s_array | ||
+ | | ||
+ | begin | ||
+ | |||
+ | v_list: | ||
+ | |||
+ | -- split servicenames in a table | ||
+ | |||
+ | dbms_utility.comma_to_table( list => v_list | ||
+ | , tablen => v_tab_length | ||
+ | , tab => v_s_array); | ||
+ | |||
+ | .... | ||
+ | |||
+ | end; | ||
+ | / | ||
+ | |||
+ | </ | ||
+ | |||
+ | siehe auch: [[prog: | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Eine Liste in Ergebniszeilen einer Abfrage umwandeln ==== | ||
+ | |||
+ | Mit** sys.dbms_debug_vc2coll** (siehe [[http:// | ||
+ | |||
+ | Beispiel: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select * from table(sys.dbms_debug_vc2coll(' | ||
+ | |||
+ | COLUMN_VALUE | ||
+ | -------------- | ||
+ | a | ||
+ | b | ||
+ | c | ||
+ | d | ||
+ | e | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | Kann zum Beispiel für eine Pivot Abfrage verwendet werden. | ||
+ | |||
+ | |||
+ | Gleiches kann auch mit XMLTABLE und einem XMLTYPE erreicht werden: | ||
+ | <code sql> | ||
+ | |||
+ | select * | ||
+ | from xmltable ('/ | ||
+ | passing xmltype ('< | ||
+ | | ||
+ | ); | ||
+ | |||
+ | ----- | ||
+ | 12345@12345.ru | ||
+ | 6789@67890.com | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ===== SYS.ODCINUMBERLIST ==== | ||
+ | |||
+ | |||
+ | Min seit 10g siehe => | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | select * | ||
+ | from TABLE | ||
+ | ( | ||
+ | SYS.ODCINUMBERLIST(1, | ||
+ | ) T ; | ||
+ | |||
+ | |||
+ | COLUMN_VALUE | ||
+ | ------------ | ||
+ | 1 | ||
+ | 2 | ||
+ | 3 | ||
+ | 4 | ||
+ | | ||
+ | |||
+ | </ | ||
+ | |||
+ | Alternativ ODCIDATELIST, | ||
+ | |||
+ | Nach möglichen Kollections suchen: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | SQL> desc sys.odcinumberlist | ||
+ | |||
+ | | ||
+ | |||
+ | SQL> desc sys.odcivarchar2list | ||
+ | |||
+ | | ||
+ | |||
+ | SQL> desc sys.ODCIDATELIST | ||
+ | |||
+ | | ||
+ | |||
+ | SQL> desc sys.dbms_debug_vc2coll | ||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | select type_name | ||
+ | , owner | ||
+ | from all_types | ||
+ | where typecode = ' | ||
+ | and owner != user | ||
+ | / | ||
+ | |||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | Oracle SQL Dokumenation | ||
+ | |||
+ | Web | ||
+ | |||
+ | * http:// | ||
+ | * http:// | ||
+ | * https:// | ||
+ | |||
prog/sql_liste_erstellen.txt · Zuletzt geändert: 2019/12/20 10:29 von gpipperr