LISTAGG w Oracle 10g i 9i, oraz WM_CONCAT i inne metody konkatenacji tekstów w trakcie agregacji

W Oracle 11g pojawiła się funkcja agregująca LISTAGG, która “dodaje” teksty do siebie generując w wyniku wartości z kolumny przekazanej parametrem. Jest to zatem konkatenacja tekstów wywołana w formie funkcji agregującej. Funkcjonalność LISTAGG można uzyskać jednak już w 9i i 10g – i to bez PL/SQL’a, WM_CONCAT i zapytań hierarchicznych. Tylko czysty SQL i standardowo dostępne funkcje agregujące 🙂

Ponieważ, inaczej niż w klasycznym dodawaniu, operacja konkatenacji nie jest przemienna to istotna jest kolejność  w jakiej wiersze są przetwarzane. W 11g do LISTAGG w tym celu dodano składnię WITHIN GROUP (znaną z funkcji hipotetycznych):

SELECT LISTAGG(table_name, ',') WITHIN GROUP (ORDER BY table_name)
FROM user_tables

Można użyć też wywołania analitycznego

SELECT LISTAGG(table_name, ',') WITHIN GROUP (ORDER BY table_name)
FROM user_tables

W Oracle 10g brakuje jednak takiej funkcji. W sieci można znaleźć wskazówki aby w takim razie używać WM_CONCAT:

SELECT WMSYS.WM_CONCAT(table_name) FROM user_tables

Niestety – funkcja ta nie zawsze jest dostępna, jest nie udokumentowana i nie wspierana przez pomoc techniczną Oracle. Nic to jednak nie przeszkadza co sprytniejszym programistom. Można bowiem zrealizować funkcjonalność LISTAGG w wersjach 10g oraz 9i w inny sposób:

• pisząc własna funkcję agregującą w PL/SQL – brak jednak kontroli nad kolejnością przekazywanych rekordów. To da się obejść wywołując własną funkcję agregującą w sposób analityczny
• pisząc funkcję skalarną PL/SQL, która ma zaszyte zapytanie w środku (zapewne z ORDER BY) i pętlę
• pisząc funkcję skalarną PL/SQL, która przyjmuje zapytanie z którego czyta dane w formie tekstu, następnie otwiera dynamiczny kursor i czyta pierwszą kolumnę z zapytania jako tekstową w pętli
• jak wyżej tylko zamiast treści zapytania funkcja otrzymuje ref cursor

Wszystkie te metody mają jedną podstawową wadę – wymagają pisania własnej funkcji. Lenistwo jest motorem nauki i postępu, zatem ja skupię się na tych metodach, które opierają się o czysty SQL.

Pierwsza to skorzystanie z zapytań hierarchicznych i funkcji sys_connect_by_path w połączeniu z ROWNUM/ROW_NUMBER():

WITH dane AS (SELECT table_name, Row_Number() over (ORDER BY table_name) AS rn FROM user_tables) 
SELECT sys_connect_by_path(table_name, ',')
FROM dane
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;
FROM USER_TABLES

I działa, ale tylko w 10g bo w 9i nie ma connect_by_isleaf. Można to ominąć, ale to kolejne linijki kodu, które zupełnie zaciemniają merytoryczną zawartość zapytania:

WITH dane AS (SELECT table_name, Row_Number() over (ORDER BY table_name) AS rn FROM user_tables),
     dane2 AS (
SELECT sys_connect_by_path(table_name, ',') AS txt, Max(LEVEL) over () AS max_level, LEVEL AS lv
FROM dane
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1)
SELECT txt FROM dane2 WHERE max_level = lv;

Można też próbować z klauzulą MODEL (od 10g wzwyż)

WITH dane AS (
SELECT table_name, rn, cnt FROM user_tables
model 
dimension by (Row_Number() over (ORDER BY table_name) AS rn)
measures (CAST(table_name AS VARCHAR2(4000)) AS table_name, Count(*) over () AS cnt)
rules (
  table_name[rn>1] ORDER BY rn = table_name[CV()-1] || ',' || table_name[CV()]
))
SELECT table_name FROM dane WHERE rn = cnt;

I też działa, ale: dopiero od 10g no i czytelne, ani proste to to nie jest.

Czy da się prościej? Da się (chociaż nie tak prosto jak z LISTAGG)! Co prawda brakuje w 9i i 10g LISTAGG, ale za to mamy XMLAGG – funkcję agregującą która generuje XML’a z przekazanego elementu z obsługą sterowania kolejnością rekordów:

SELECT xmlagg(xmlelement("txt", table_name || ',') ORDER BY table_name).getStringVal() 
FROM user_tables;

Funkcja xmlelement wymaga podania w pierwszym argumencie identyfikatora jaki zostanie użyty w tagach budowanego XML’a (podwójny cudzysłów to nie literówka!). Wynik:

<txt>APEX$_ACL,</txt><txt>APEX$_WS_FILES,</txt><txt>APEX$_WS_HISTORY,</txt><txt>APEX$_WS_LINKS,</txt><txt>APEX$_WS_NOTES,</txt><txt>APEX$_WS_ROWS,</txt><txt>APEX$_WS_TAGS,</txt><txt>APEX$_WS_WEBPG_SECTIONS,</txt><txt>APEX$_WS_WEBPG_SECTION_HISTORY,</txt><txt>DUZA,</txt><txt>EXTERNAL_DATA,</txt><txt>KLIENCI,</txt><txt>KONTA,</txt><txt>KREDYTY,</txt><txt>LICZBY,</txt><txt>MALA,</txt><txt>ODDZIALY,</txt><txt>OPERACJE,</txt><txt>OSOBY,</txt><txt>PORECZENIA,</txt><txt>PRODUKTY_FINANSOWE,</txt><txt>PUNKTY_PREMIUM,</txt><txt>PUSTA,</txt><txt>SLOWA,</txt><txt>SYS20367_XMLINDEX_PATH_TABLE,</txt><txt>SYS_NTp6Al6jp/SwaEZY4o+VIkzA==,</txt><txt>TAB,</txt><txt>TAB1,</txt><txt>TAB_CF,</txt><txt>TAB_DUZA,</txt><txt>TEST,</txt><txt>TEST2,</txt><txt>TEST3,</txt><txt>TEST4,</txt><txt>TEST_AUDYT,</txt><txt>TEST_AUDYTU,</txt><txt>TEST_CONST,</txt><txt>TEST_CONST2,</txt><txt>TEST_KOLUMNA,</txt><txt>TEST_TEKST,</txt><txt>TES_SEK,</txt>

No ok – ale ja nie chcę XML’a. Chcę po prostu tekst! Da się zrobić. Zaprzęgamy do roboty extract i XPath:

SELECT extract(xmlagg(xmlelement("txt", table_name || ',') ORDER BY table_name), '/txt/text()').getStringVal() FROM user_tables;

W ten sposób pozbywamy się znaczników XML, które są wygenerowane przy okazji.

Podana metoda implementacji funkcjonalności LISTAGG w wersjach 9i i 10g nie jest najprostsza, ale i tak jest bardziej czytelna niż np. pisanie własnych funkcji agregujących. Ponadto jest zrealizowana wyłącznie przy użyciu w pełni wspieranych przez Oracle funkcji standardowych SQL.