CONNECT-BY-Klausel (Auszug: Oracle)

Die Connect-By-Klausel ist eine Oracle-spezifische Sonderform der SELECT-Anfrage, die es ermöglicht rekursive (synonym: hierarchische) Abfragen an eine Datenbasis zustellen und damit Baumstrukturen beliebiger Tiefe abzubilden. Im SQL-Standard ist dazu die WITH-Klausel vorgesehen, die mittlerweile auch ORACLE-SQL vollständig umgesetzt hat.

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 Manamgent 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 verändernden Tiefen anzupassen.

Syntax der CONNECT-BY-Klausel (Oracle)

 CONNECT-BY-Klausel ::=
   START WITH <Spaltenname1 = Konstante>
   CONNECT BY [NOCYCLE] <Spaltenname1> = PRIOR <Spaltenname2> <Bedingung>
   ORDER SIBLINGS BY <Spaltenname1>

Die START-WITH-Komponente gibt den Startpunkt der rekursiven Abfrage an. Das PRIOR in der CONNECT-BY-Klausel kann auch auf der anderen Seite des Gleichheitszeichens stehen und bewirkt eine rekursive Verknüpfung über die Spaltennamen, wobei die PRIOR-Spalte die untergeordnete Spalte ist. Die <Bedingung> kann genutzt werden, um Äste der Abfrage auszuschließen (siehe 3.Beispiel). ORDER BY SIBLINGS bewirkt eine Sortierung über Tupel auf der gleichen Hierarchiestufe (LEVEL). Level ist eine Pseudospalte, die die Hierarchiestufe abbildet. Die Bedingung kann noch eine zusätzlicher Einschränkung der Spalten enthalten, über die rekursiv verknüpft werden soll (siehe Beispiel 3). NOCYLE schließt zyklische, nicht abbrechende Abfragen aus.

Beispiele

Stückliste

Im Datenbankschema beschriebt die Teile-Tabelle die Knoten mit dem Primärschlüssel Teilenr und die Strukturtabelle die Kanten mit dem Primärschlüssel OTeil und UTeil. Stücklisten werden z.B. im Stücklisten-Tool der E-Learning-Plattform veranschaulicht.

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

 CREATE TABLE STRUKTUR 
     (OTEIL      NUMBER (38) NOT NULL,
      UTEIL      NUMBER (38) NOT NULL,
      POSITION   NUMBER (38) NOT NULL,
      MENGE      NUMBER NOT NULL);

 ALTER TABLE STRUKTUR ADD (
  PRIMARY KEY (OTEIL, UTEIL, POSITION));

 ALTER TABLE STRUKTUR ADD (
  FOREIGN KEY (UTEIL)REFERENCES TEILE1 (TNR),
  FOREIGN KEY (OTEIL)REFERENCES TEILE1 (TNR));

1.Beispiel: Ausgabe der CONNECT-BY-Klausel:

 SELECT OTeil, UTEIL, level
 FROM   Struktur
 START  WITH OTeil = 60
 CONNECT BY OTeil = PRIOR UTeil;

gibt alle dem Teil 60 untergeordneten Teile aus mit dem LEVEL, also der Stufe:

OTEIL	UTEIL	LEVEL
60	2	1
2	3	2
2	4	2
60	41	1
41	42	2
41	43	2
41	44	2
41	45	2
60	62	1
62	49	2
62	40	2
40	34	3
40	3	3

Durch den Tausch des Wortes PRIOR wird aus der Stückliste ein Verwendungsnachweis, in dem nachgesehen wird, in welchen Teilen ein Unterteil verwendet wird.

2.Beispiel: Ausgabe der CONNECT-BY-Klausel mit vertauschtem PRIOR

 SELECT OTeil, UTEIL, level
 FROM   Struktur
 START  WITH UTeil = 2
 CONNECT BY PRIOR OTeil = UTeil;

3.Beispiel: Ausgabe der CONNECT-BY-Klausel mit vertauschtem PRIOR

 SELECT OTeil, UTEIL, level
 FROM   Struktur
 START  WITH UTeil = 2
 CONNECT BY PRIOR OTeil = UTeil AND OTEIL < 60;

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