Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_liste_erstellen

Ein Listen von Werten in SQL erstellen

Piombino in der Toskana

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 ANA Funktion gelöst werden.

SQL>SELECT deptno
         , listagg(ename,':') WITHIN GROUP (ORDER BY ename)  AS emp_list
     FROM emp GROUP BY deptno
/     
 
    DEPTNO    EMP_LIST
----------    ---------------------------------
        10    CLARK:KING:MILLER
        20    ADAMS:JONES
        30    ALLEN:MARTIN:TURNER:WARD

Mit listagg kann eine Liste bis maximal 4000 Zeichen aggregiert werden.

Quelle, siehe http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm und http://www.oracle-developer.net/display.php?id=515


Ab Oracle 12r2 - LISTAGG overflow

Ab der Version 12c R2 kann auf den ärgerlichen „ORA-01489: result of string concatenation is too long“ Fehler reagiert werden.

SQL>SELECT deptno
         , listagg(ename,':') WITHIN GROUP (ORDER BY ename) ON overflow TRUNCATE '...' WITH COUNT AS emp_list
     FROM emp GROUP BY deptno
/     
 
    DEPTNO    EMP_LIST
----------    ---------------------------------
        10    CLARK:KING:MILLER
        20    ADAMS:JONES
        30    ALLEN:MARTIN:TURNER:WARDxxxxx  ... (100)

Doku ⇒ https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466


Ab Oracle 19- LISTAGG distinct

Ab 19c können auch Duplicate in der Liste aussortiert werden!

siehe auch ⇒ https://oracle-base.com/articles/19c/listagg-distinct-19c


Eigene Aggregat Funktion verwenden um einen ORA-01489 zu vermeiden

Eine Alternative Lösung ist eine eigene Aggregate Funktion.

siehe dazu ⇒ Eine eigene Aggregat Funktion erstellen


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.

SELECT  deptno
      , sys.stragg(ename||';') AS emp_list
 FROM scott.emp 
WHERE deptno=10      
GROUP BY deptno
/   
 
  DEPTNO EMP_LIST
------------ --------------------------------------------------
          10 CLARK;KING;MILLER;
 
 
-- Bei mehr als einer Treffer Zeile wird das Ergebnis mehrdeutig!          
SELECT  deptno
      , sys.stragg(ename||';') AS emp_list
 FROM scott.emp 
WHERE deptno IN (10,20)      
GROUP BY deptno
/            
 
   DEPTNO EMP_LIST
------------ --------------------------------------------------
          10 CLARK;KING;ADAMS;FORD;MILLER;
          20 CLARK;KING;ADAMS;FORD;MILLER;
 

D.h. ein Einsatz ist eher zu vermeiden, zumal das keine offizielle Funktion ist.


Die Collect Methode

Collect Methode:

SQL>SELECT deptno
         , collect(ename) AS emp_list  
    FROM emp 
   GROUP BY deptno
/
 
 
DEPTNO EMP_LIST
------ ----------------------------------------------------------------------
    10 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('CLARK', 'MILLER', 'KING')
    20 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('JONES', 'ADAMS')
    30 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('ALLEN', 'TURNER', 'MARTIN', 'WARD')

Die wmsys.wm_concat Funktion

Die wmsys.wm_concat:

SQL>SELECT deptno,wmsys.wm_concat(ename) AS emp_list  FROM emp GROUP BY deptno;
 
DEPTNO EMP_LIST
------ ----------------------------------------------------------------------
    10 CLARK,MILLER,KING
    20 JONES,ADAMS
    30 ALLEN,TURNER,MARTIN,WARD

XML verwenden

Daten in XML verwandeln und wieder in Text zerlegen:

SQL>SELECT deptno
         , rtrim ( xmlagg (xmlelement (c, ename || ',') ORDER BY ename).extract ('//text()'), ',' ) AS AS emp_list            
     FROM emp 
    GROUP BY deptno;
 
DEPTNO EMP_LIST
------ ----------------------------------------------------------------------
    10 CLARK,MILLER,KING
    20 JONES,ADAMS
    30 ALLEN,TURNER,MARTIN,WARD

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:

DECLARE
 
  v_tab_length   BINARY_INTEGER;
  v_s_array      DBMS_UTILITY.lname_array;
 
BEGIN
 
v_list:='A,B,C,D,E,F';
 
-- split servicenames in a table
 
dbms_utility.comma_to_table( list   => v_list
			   , tablen => v_tab_length
			   , tab    => v_s_array);
 
....
 
END;
/

siehe auch: Oracle PL/SQL - Strings zerlegen


Eine Liste in Ergebniszeilen einer Abfrage umwandeln

Mit sys.dbms_debug_vc2coll (siehe Das DBMS_DEBUG Package) kann eine Ergebnisliste wieder in Zeilen ausgegeben werden.

Beispiel:

SELECT * FROM TABLE(sys.dbms_debug_vc2coll('a','b','c','d','e'));
 
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:

SELECT *
  FROM xmltable ('/emails/email'
                  passing xmltype ('<emails><email><m>12345@12345.ru</m></email><email><m>6789@67890.com</m></email></emails>')
                   COLUMNS "mail" varchar2 (255) path 'm'
					 );
mail
-----
12345@12345.ru
6789@67890.com

SYS.ODCINUMBERLIST

Min seit 10g siehe ⇒Database Data Cartridge Developer's Guide https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcicnstnts.htm

SELECT * 
FROM TABLE
   (
      SYS.ODCINUMBERLIST(1,2,3,4,5,6,1,2,3,4,5)
   ) T ;
 
 
COLUMN_VALUE
------------
           1
           2
           3
           4
           ....

Alternativ ODCIDATELIST,ODCIVARCHAR2LIST

Nach möglichen Kollections suchen:

SQL> DESC sys.odcinumberlist
 
 sys.odcinumberlist VARRAY(32767) OF NUMBER
 
SQL> DESC sys.odcivarchar2list
 
 sys.odcivarchar2list VARRAY(32767) OF VARCHAR2(4000)
 
SQL> DESC sys.ODCIDATELIST
 
 sys.ODCIDATELIST VARRAY(32767) OF DATE
 
SQL> DESC sys.dbms_debug_vc2coll
 
 sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000)
 
 
SELECT type_name
     , owner
 FROM all_types
WHERE typecode = 'COLLECTION'
  AND owner != USER
/

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
"Autor: Gunther Pipperr"
prog/sql_liste_erstellen.txt · Zuletzt geändert: 2019/12/20 10:29 von gpipperr