Such-Klausel - Suchstrategien in hierarchischen Daten für rekursive Anfragen

Die optionale Such-Klausel kann für rekursive, temporäre Hilfssichten in der WITH-Klausel einer SELECT-Anfrage angegeben werden und spezifiziert, ob der Datenbaum mittels Breiten- oder Tiefensuche durchsucht werden soll. Diese Suchstrategie erzeugt eine Ordnung, mit der die Datensätze ausgegeben werden. Bei der Breitensuche werden erst alle Geschwister gesucht, bevor Kinder gesucht werden, während es bei der Tiefensuche genau anders herum läuft nämlich erst die Kinder und Enkel und deren Kinder und Enkel u.s.w. bevor die Geschwister gesucht werden.

Syntax Such-Klausel

  <Such Klausel>   ::=    SEARCH  [ DEPTH | BREADTH ]  FIRST BY   <Sortierspaltenliste> SET  <Ordnungsspaltenname>

  <Sortierspaltenliste>   ::=
          { <Spaltenname>   [ DESC | ASC ]    [ NULLS FIRST | NULLS LAST ]
            [, <Spaltenname>   [ DESC | ASC ]    [ NULLS FIRST | NULLS LAST ] ]... }
  • Die Option DEPTH lässt eine Tiefensuche durchführen und BREADTH eine Bereitensuche.
  • Gemäß den in der Sortierspaltenliste angeführten Spalten werden die Geschwisterdatensätze einer Ebene sortiert. Analog zur ORDER BY-Klausel kann nach verschiedenen Spalten sortiert werden. Zwei Unterschiede: Hier sind nur Spaltennamen zugelassen und keine Audrücke und die Spaltennamen hier müssen aus der SELECT-Klausel der Anfrage stammen, während beim ORDER BY die Spalten nur in den Tabellen vorkommen müssen.
  • Der Ordnungspaltenname bezeichnet eine Spalte, nach der in der übergeordneten Anfrage der Baum in einer ORDER-BY-Klausel sortiert werden kann. Und damit dies funktionieren kann, wird diese Spalte automatisch der SELECT-Klausel der Anfrage angefügt.
  • Im Vergleich zum SQL-Standard ergeben sich bei dieser Oracle-Syntax nur einige gerinfgügige Unterschiede:
    • Die Ordnungspaltenliste ist ein Oracle-Spezifikum.
    • Die Restriktion, dass für die Suche nur Spaltennamen, aber keine Ausdrücke angegeben werden können gilt für den Standard nicht.

Beispiele

Stückliste
Stückliste


Datenmodell wie Testdaten für diese Beispiele sind bei der WITH-Klausel zu finden.

-- Zeigen Sie für alle Oberteile deren zugehörige Unterteile an.
-- 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 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 )
        SEARCH BREADTH FIRST BY OberTeilenr SET Sortier_OT          
   SELECT lpad(' ',2*Ebenenzaehler)||OberTeilenr AS OberTeileNr, UnterTeilenr, Ebene, Menge, Ebenenzaehler    
     FROM Stueckliste
    ORDER BY Sortier_OT;

  Ergebnismenge:
  OberTeilnr  UnterTeilnr  Ebene  Menge  Ebenenzaehler
  60              62         1                1
  60              41         1                1
  60               2         1                1
    41            42         2      3         2
    41            43         2      7         2
    41            44         2      1         2
    41            45         2      2         2
    62            40         2      1         2
    62            49         2      5         2
      49           3         3      4         3
      49          34         3      1         3

-- Der erste SELECT der rekursiven Hilfssicht Stueckliste ist die sog. Ankeranfrage und ermittelt die Basisdatensätze,
-- auf denen dann der zweite SELECT, eine rekursive Anfrage, ausgewertet wird,
-- die dann die Datenmenge mit jeder Rekursionsschleife um neue abgeleitete Datensätze ergänzt.
-- Die LPAD-Funktion fügt in diesem Beispiel Leerzeichen links vor die Oberteilenummer.
-- und sorgt somit für die optische Einrückung je nach Auswertungsebene.

-- Zeigen Sie für alle Oberteile deren zugehörige Unterteile an. \\ -- mit Tiefensuche

        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 )
             SEARCH DEPTH FIRST BY OberTeilenr SET Sortier_OT          
   SELECT lpad(' ',2*Ebenenzaehler)||OberTeilenr AS OberTeileNr, UnterTeilenr, Ebene, Menge, Ebenenzaehler    
     FROM Stueckliste
     ORDER BY Sortier_OT;

  Ergebnismenge:
  OberTeilnr  UnterTeilnr  Ebene  Menge  Ebenenzaehler
  60               2         1                 1
  60              41         1                 1
    41            42         2      3          2
    41            43         2      7          2
    41            44         2      1          2
    41            45         2      2          2
  60              62         1                 1
    62            40         2      1          2
    62            49         2      5          2
      49           3         3      4          3
      49          34         3      1          3

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, S