Datenbanken Online Lexikon TH Köln, Campus Gummersbach
Aktuelle Änderungen - Suchen:

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