0
(Noch nicht bewertet)
10. Mai 2016
DataOne Blog

Generische Codegenerierung mit Oracle 2: Konkatenation mit LISTAGG

Die nachfolgende Blogserie ermöglicht Ihnen einen Überblick über verschiedene Möglichkeiten, der Erzeugung generischer Statements mit Oracle. Hierbei werden verschiedene Werkzeuge vorgestellt.


LISTAGG

In unserem letzten Beitrag haben wir uns um WM_CONCAT gekümmert und bereits angekündigt,
dass es dieses Mal mit  LISTAGG weitergehen soll. LISTAGG wurde mit dem Release 11g R2 von Oracle eingeführt.  Bei LISTAGG handelt es sich um eine analytische Funktion (Analytic Function).  Der Aufbau des Befehls ist wie folgt:

LISTAGG (measure_expr*, <delimiter>) WITHIN* GROUP*(order_by_clause)*OVER(query_partition_clause)

Alle Ausdrücke die mit einem (*) markiert sind, sind zwingend zu verwenden. Die measure_expr ist der Wert, der konkateniert werden soll. Der delimiter ist ein frei wählbares Trennzeichen in Form einer Zeichenkette, per Default wird hier ein NULL-Value verwendet. Mittels WITHIN GROUP kann bestimmt werden, wie die konkatenierten Werte sortiert werden sollen. Die Partitionierung mittels OVER ist optional.

Beispiele mit LISTAGG

Folgende Beispiele nutzen das Beispielschema HR, das von Oracle bei der Installation bereitgestellt wird um die Funktionsweise von LISTAGG zu demonstrieren.

LISTAGG ohne Gruppierung

Da LISTAGG eine analytische Funktion ist, können wir LISTAGG auch ohne eine Partitionierung/Gruppierung des Resultsets verwenden, so lange es sich nicht um ein GROUP BY-Statement handelt. In diesem Fall werden alle Werte in dem Resultset, die in der measure_expr stehen, aneinander gehängt wie im folgenden Beispiel:

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 1

Das Ergebnis sieht für die Abfrage wie folgt aus:

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 2

LISTAGG mit OVER-Clause

In diesem Fall wollen wir uns LISTAGG zusammen mit der OVER-Clause ansehen. In unserem Beispiel werden alle Vor- und Nachnamen aus einer bestimmten Jobkategorie zusammengefasst und in einem Feld ausgegeben, dass wir emp_list nennen:

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 3

Das Ergebnis dieser Abfrage sieht in Auszügen wie folgt aus:

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 4

Wie man in unserem Resultset sehen kann, haben wir pro JOB_TITLE genauso viele Zeilen wie Werte in unserem Ergebnisfeld (Employees), was dem zu erwartenden Ergebnis entspricht.

LISTAGG mit GOUP BY

Dieses Beispiel soll zeigen welche Ergebnisse LISTAGG mit der GROUP BY-Clause liefert, im Vergleich zu der Verwendung von OVER. Hierbei handelt es sich um eine Kopie unserer einfachen Konkatenation aus dem zweiten Beispiel.

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 5

Als Ergebnis erhalten wir folgendes Resultset:

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 6

Nun haben wir pro JOB_TITLE eine Zeile in unserer Ergebnismenge und das Feld EMP_LIST enthält erneut alle zugehörigen Mitarbeiter.

CREATE-Statement mit LISTAGG

In unserem letzten Beitrag haben wir uns angesehen wie wir mit WM_CONCAT das CREATE-Statement erzeugen können für eine Tabelle/View. Um einen Vergleich ziehen zu können, werden wir das gleiche nun mit der LISTAGG-Function durchführen. Zum Vergleich wird das Statement aus dem vorhergehenden Beitrag mit angezeigt:

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 7

(LISTAGG)

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 8

(WM_CONCAT)

 

Was im Vergleich auffällt ist, dass wir nun mit der WITHIN GROUP-Clause die Möglichkeit haben die Felder im Create so zu sortieren, wie bei der letzten Erzeugung der Tabelle. Ist eine bestimmte Systematik in der Reihenfolge der Tabellenattribute hinterlegt, wird diese somit beibehalten. Die Unterschiede von WM_CONCAT sind zusammengefasst folgende:

  1. Wollen wir LISTAGG verwenden, müssen wir explizit das Trennzeichen angeben. Sonst wird ein NULL-Value als Trennzeichen benutzt.
  2. LISTAGG ist eine analytische Funktion und keine Aggregatfunktion, möchte man DDL-Statements generieren, ist ein GROUP BY häufig aber trotzdem sinnvoll.
  3. Wir können ein beliebiges Trennzeichen nutzen, die Option fehlte uns bei WM_CONCAT gänzlich.
  4. Die Sortierung der zu konkatenierenden Werte wird nicht wie bei WM_CONCAT in der ORDER BY-Clause, sondern mit dem Statement WITHIN GROUP (ORDER BY <COLUMN>) erzielt.

Das Ergebnis zu der Abfrage sieht auszugsweise wie folgt aus und ist somit mit dem von WM_CONCAT identisch:

Generische Codegenerierung mit Oracle 2 Konkatenation mit LISTAGG 9

Die Spalte STMNT kann markiert und mit Copy and Paste in TOAD oder den SQL-Developer überführt werden und als Skript ausgeführt werden.

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.