WITH-Klausel - temporäre (rekursive) Hilfssichten einer SELECT-Anfrage

Die optionale WITH-Klausel bildet einen möglichen Start einer SELECT-Anfrage. Mit ihr können temporäre Hilfssichten definiert werden, auf die dann in der Anfrage selber zugegriffen werden kann. Damit sollen Anfragen übersichtlicher, strukturierter gestaltet werden bzw. rekursive Anfragen können programmiert werden. Eine komplexe Anfrage aus der WITH-Klausel kann im Abfrage-Block mehrfach verwendet werden und daher die Abfrage insgesamt beschleunigen. Wird auf die WITH-Klausel verzichtest, startet die SELECT-Anfrage direkt mit der Syntax einer Unteranfrage?.

Rekursion

Klassische Anwendungsfälle für rekursive Anfragen sind das Vorgesetzten- bzw. das Stücklisten-Problem. Beide Datenbäume zeichnen sich durch die unterschiedlich tiefen Zweige aus. Bei den Vorgesetzen z.B. sind abhängig von der Position eines Mitarbeiters unterschiedlich viele Vorgesetzen-Ebenen gegeben. Während es für einen "normalen" Mitarbeiter vielleicht 5-6 Hierarchieebenen bis zum Chef sind, sind es für das mittlere Management nur 2-3. Ein anderes Charakteristikum dieser Bäume ist, dass die maximale Tiefe in der Regel unbekannt ist bzw. varriieren kann. Rekursive Anfragen zeichnen sich dadurch aus, ohne Programmänderungen in der Lage zu sein, sich unbekannte Tiefen zu erarbeiten.

Obwohl die SELECT-Anfrage an sich nicht rekursiv ist, können im SQL-Standard und bei Oracle solche Anfragen mittels rekursiver, temporärer Hilfssichten der WITH-Klausel prgorammiert werden. Da die rekursive WITH-Klausel bei Oracle recht lange auf sich hat warten lassen, gab es "schon immer" die CONNECT-BY-Klausel, eine absolut Oracle-spezifische Lösung für rekursive Anfragen.

Syntax WITH-Klausel

 <Anfrageausdruck>  ::=  [<WITH Klausel>]  <Unterabfrage?>  [<FOR UPDATE Klausel>]

    <WITH Klausel>  ::=  WITH <WITH Element> [, <WITH Element> ]...

    <WITH Element>  ::=  Anfragenname [ ( Spaltenname [, Spaltenname ]... ) ] 
                                   AS ( <Unterabfrage?> )   [<Such-Klausel>]   [<Zyklus-Klausel>] 
  • Ein WITH-Element stellt eine temporäre Hilfssicht dar, die nur innerhalb dieses Anfrageausdrucks gültig ist. Sie sind damit ein Beispiel für dynamische Sichten.
    • Damit bieten sie eine gute Möglichkeit, den gesamten Anfrageausdruck besser zu strukturieren, lesbarer und damit wartbarer zu programmieren.
    • Da SELECT-Anfragen an sich "rekursionslos" sind, stellen diese Hilfssichten eine echte semantische Erweiterung dar, da es mit ihrer Hilfe möglich wird, rekursive Anfragen zu formulieren. Im SQL-Standard ist dies die Einzige, bei Oracle gibt es seit langem die Eigenentwicklung der CONNECT-BY-Klausel.
  • Die optionale FOR UPDATE Klausel ermöglicht es, die selektierten Datensätze für nachfolgende Änderungen zu sperren hinsichtlich des Zugriffs durch andere Nutzer.
  • Programmierung rekursiver, temporärer Hilfssichten:
    • Die rekursive Anfrage besteht immer aus einem Anfragemengenausdruck mittels UNION ALL. UNION allein reicht nicht.
    • Die Anfrage oberhalb des UNION ALL ist die sog. Anker-/Basisanfrage mit der die Daten der ersten Auswertungsrunde ermittelt werden. Diese stellen dann die Ankerdaten für die weiteren Ableistungsschritte dar.
    • Die Anfrage unterhalb des UNION ALL ist dann die eigentliche rekursive Teilanfrage, die Ebenen 2 bis n auswerten.
    • In der optionalen Such-Klausel kann angegeben werden, ob Breiten- oder Tiefensuche gewünscht wird.
    • Und in der ebenfalls optionalen Zyklus-Klausel kann angegeben werden, wie und wo Zyklen erkannt und markiert werden können.
  • Diese Syntax weist ein paar Oracle-Spezifika auf, die so nicht im SQL-Standard vorgesehen sind:
    • Oracle verzichtet auf das Schlüsselwort RECURSIVE, welches beim Standard optional auf das Schlüsselwort WITH folgen kann. Das Oracle-DBMS erkennt die Rekursivität einer Anfrage an deren Struktur.
    • Bei den Unteranfragen, die als WITH-Elemente formuliert werden, läßt der Standard als Anfragen wiederum Anfrageausdrücke (<Anfrageausdruck>) zu. Dies hat zur Folge, dass innerhalb der WITH-Liste weitere WITH-List-Abfragen mit beliebiger Schachtelungstiefe formuliert werden können. Hierbei stellt sich in der Praxis sicherlich oft das Problem, dass die gewonnene Strukturierung dann doch wieder in Unübersichtlichkeit endet. Vor allem auch, wenn über mehrere Ebenen Rekursion prorammiert wird. Oracle bietet dort "nur" die Möglichkeit einer <Unterabfrage?>, was aber, abgesehen von der über mehrere Ebenen gehenden Rekursion, eher eine syntaktische als eine semantische Einschränkung darstellt.
    • Die FOR UPDATE-Klausel ist im Standard gar nicht vorgesehen.
Stückliste
Stückliste


Beispiele

Beispiele ohne die WITH-Klausel finden sich z.B. auf den Seiten: SELECT, Unterabfrage?, Anfragemengenausdruck. Daher werden hier nun Beispiele mit WITH-Klausel erläutert.

Der Stücklistenbaum aus der nebenstehenden Abbildung wird in Form der Tabellen Teile und Struktur im DBMS gespeichert. Die Konsistenz der Teilenummern in der Teile- und der Struktur-Tabelle wird mittels der beiden Fremdschlüssel sichergestellt. Da ein Teil je Ebene nur einmal als Unterteil auftreten kann, besteht der Primärschlüssel der Struktur-Tabelle aus den Spalten OTEIL, UTEIL, POSITION. Gefüllt werden die Tabellen mit genau diesen Daten für die Teilenummer 60 und all den zugehörigen Unterteilen.

Der Baum wird auch im Stücklisten-Tool von edb edb Logo veranschaulicht.

 CREATE TABLE TEILE 
    (TNR           NUMBER   (18) PRIMARY KEY,
     ME            VARCHAR2 (10),
     BEZEICHNUNG   VARCHAR2 (50));

 CREATE TABLE STRUKTUR 
     (OTEIL        NUMBER (18)   FOREIGN KEY (OTEIL)REFERENCES TEILE (TNR),
      UTEIL        NUMBER (18)   FOREIGN KEY (UTEIL)REFERENCES TEILE (TNR),
      POSITION     NUMBER (18),
      MENGE        NUMBER (6,2)  NOT NULL, 
      PRIMARY KEY (OTEIL, UTEIL, POSITION));


 -- Teile: TNR,ME,BEZEICHNUNG                            -- Struktur: OTEIL,UTEIL,POSITION,MENGE
 INSERT INTO Teile VALUES (60, NULL,'Schraube x');       INSERT INTO Struktur VALUES (60,2,1,4);
 INSERT INTO Teile VALUES (2,NULL,'Schraube xx');        INSERT INTO Struktur VALUES (60,41,1,2);
 INSERT INTO Teile VALUES (41,NULL,'Blech z');           INSERT INTO Struktur VALUES (60,62,1,1);
 INSERT INTO Teile VALUES (62,NULL,'Blech vfv');         INSERT INTO Struktur VALUES (41,42,2,3);
 INSERT INTO Teile VALUES (42,NULL,'Stange gh');         INSERT INTO Struktur VALUES (41,43,2,7);
 INSERT INTO Teile VALUES (43,NULL,'Blech tz');          INSERT INTO Struktur VALUES (41,44,2,1);
 INSERT INTO Teile VALUES (44,NULL,'Schraube tzi');      INSERT INTO Struktur VALUES (41,45,2,2);
 INSERT INTO Teile VALUES (45,NULL,'Schraube ui');       INSERT INTO Struktur VALUES (62,40,2,1);
 INSERT INTO Teile VALUES (40,NULL,'Winkeleisen oi');    INSERT INTO Struktur VALUES (62,49,2,5);
 INSERT INTO Teile VALUES (49,NULL,'Stange io');         INSERT INTO Struktur VALUES (49,3,3,4);
 INSERT INTO Teile VALUES (3,NULL,'Blech pp');           INSERT INTO Struktur VALUES (49,34,3,1);
 INSERT INTO Teile VALUES (34,NULL,'Schraube oo');

"Welche Unterteile hat das Teil 62?"

   WITH Stueckliste (OberTeilenr, Unterteilenr, Ebene, Menge, Ebenenzaehler) 
          -- Basis-/Ankerdatenmenge: Oberteile, die nicht als Unterteile verwendet werden.
        AS (SELECT OTeil, UTeil, Position, NULL, 1
              FROM Struktur
             WHERE OTeil = 62
            UNION  ALL
            -- davon rekursiv abgeleitete Unterteile
            SELECT Struktur.OTeil, Struktur.UTeil, Struktur.Position, Struktur.Menge, Ebenenzaehler+1
              FROM Stueckliste, Struktur
             WHERE Stueckliste.Unterteilenr = Struktur.OTeil )
   SELECT * 
     FROM Stueckliste; 

Die Ergebnismenge umfasst 4 Struktureinträge.
Die Ankerabfrage schränkt auf das Teil 62 ein.
Die rekursive Teilabfrage ermittelt dann in der 1.-2. Ebene: 40, 49, 3 und 34.
Die Spalte Ebenenzaehler ist ein selbstprogrammierter Zähler, der die Ableitungsebenen anzeigt. Er startet mit 1 und endet mit 2.
Die äußere Anfrage "SELECT * FROM Stueckliste;" gibt dann das Ergebnis der rekursiven SELECT-Anweisung aus.


"Welche Unterteile gehören zu den Teilen, die nur als Oberteile sind?"

   WITH Stueckliste (OberTeilenr, Unterteilenr, Ebene, Menge, Ebenenzaehler) 
          -- Basis-/Ankerdatenmenge: Oberteile, die nicht als Unterteile verwendet werden.
        AS (SELECT OTeil, UTeil, Position, NULL, 1
              FROM Struktur
             WHERE OTeil NOT IN (SELECT DISTINCT UTeil FROM Struktur)
            UNION  ALL
            -- davon rekursiv abgeleitete Unterteile
            SELECT Struktur.OTeil, Struktur.UTeil, Struktur.Position, Struktur.Menge, Ebenenzaehler+1
              FROM Stueckliste, Struktur
             WHERE Stueckliste.Unterteilenr = Struktur.OTeil )
   SELECT * 
     FROM Stueckliste; 

Die Ergebnismenge umfasst alle 11 Struktureinträge.
Die Ankerabfrage schränkt auf das Teil 60 ein, weil dies das einzige Teil ist, welches nicht als Unterteil auftritt.
Die rekursive Teilabfrage ermittelt dann in der 2.-3. Ebene: 2, 41, 62, 42, 43, 44, 45, 40, 49, 3 und 34.
Die Spalte Ebenenzaehler ist ein selbstprogrammierter Zähler, der die Ableitungsebenen anzeigt. Er startet mit 1 und endet mit 3.
Die äußere Anfrage "SELECT * FROM Stueckliste;" gibt dann das Ergebnis der rekursiven SELECT-Anweisung aus.


"Welche Unterteile hat das Teil 60? Mal mit Tiefensuche ausgewertet und sortiert, mal mit Breitensuche"

   WITH Stueckliste (OberTeilenr, Unterteilenr, Ebene, Menge, Ebenenzaehler) 
          -- Basis-/Ankerdatenmenge: Oberteile, die nicht als Unterteile verwendet werden.
        AS  (SELECT OTeil, UTeil, Position, NULL, 1
               FROM Struktur
              WHERE OTeil = 60
             UNION  ALL
             -- davon rekursiv abgeleitete Unterteile
             SELECT Struktur.OTeil, Struktur.UTeil, Struktur.Position, Struktur.Menge, Ebenenzaehler+1
               FROM Stueckliste, Struktur
             WHERE Stueckliste.Unterteilenr = Struktur.OTeil ) SEARCH BREADTH FIRST BY Oberteilenr SET SortSpalte
       --    WHERE Stueckliste.Unterteilenr = Struktur.OTeil ) SEARCH DEPTH   FIRST BY Oberteilenr SET SortSpalte
   SELECT * 
     FROM Stueckliste
    ORDER BY SortSpalte ASC; 

Dieses Beispiel zeigt die unterschiedlichen Sortierungen der Ergebnisdatensätze je nach Suchstrategie. Um die Tiefensuche auszuprobieren muss nur der Kommentar umgestzt werden.


siehe auch Beispiele zu zyklischen Ergebnismengen: Zyklus-Klausel

Quellen:

  • Quellen/Standards in http://www.wiscorp.com/SQLStandards.html und http://www.jcc.com/sql.htm
  • INCITS/ISO/IEC 9075-1-2008. Part 1 "SQL/Framework", ISO International Organization for Standardization / INCITS InterNational Committee for Information Technology Standards, 2008
  • INCITS/ISO/IEC 9075-1-2008. Part 2 "SQL/Foundation", ISO International Organization for Standardization / INCITS InterNational Committee for Information Technology Standards, 2008
  • Adams, Ralf: "SQL - Eine Einführung mit vertiefenden Exkursen", Hanser, München, 2012, ISBN 978-3-446-43200-0
  • Elmasri, Ramez/Navathe, Shamkant B.: "Grundlagen von Datenbanksystemen" , Pearson Studium, München, 2002, ISBN 3-8273-7021-3
  • Faeskorn-Woyke, Heide/Bertelsmeier, Birgit/Riemer, Petra/Bauer, Elena: "Datenbanksysteme - Theorie und Praxis mit SQL2003, Oracle und MySQL", Pearson Education, München, 2007, ISBN 978-3-8273-7266-6
  • Kemper, Alfons/Eickler, André: "Datenbanksysteme", Oldenbourg, München, 2009, 978-3-486-59018-0
  • Melton, Jim/Simon, Alan R.: "SQL: 1999 - Understanding Relational Language Components", Morgan Kaufmann, San Francisco, 2001, ISBN 1558604561
  • Oracle® Database SQL Language Reference 11g Release 2 (11.2), E17118-03, August 2010, http://download.oracle.com/docs/cd/E11882_01/server.112/e17118.pdf
  • Saake, Gunter/Sattler, Kai-Uwe/Heuer, Andreas: "Datenbanken - Konzepte und Sprachen", mitp-Verlag, Redline GmbH, Heidelberg, 2007, ISBN 3-8266-1664-2
  • Sieben, Jürgen: "Oracle® SQL - Das umfassende Handbuch", Galileo Press, 2012, ISBN 978-3-8362-1875-7
  • Vossen, Gottfried: "Datenmodelle, Datenbanksprachen und Datenbankmanagementsysteme", Oldenbourg, München, 2008, ISBN 978-3-486-27574-2

Kategorie; SQL, W