Rekursive Anfragen in SQL

Rekursive Anfragen in SQL
Rupert Schneeberger
26.11.2010
Aufgabenstellung
Beispiel:
• Flugverbindungen
• Anzeige von Strecken mit Umsteigemöglichkeiten
LINIENFLUG
Abflug
Ankunft
Wien
Frankfurt
Frankfurt London
Wien
Budapest
Wien
Venedig
Venedig
Rom
Wien
Zürich
Zürich
Paris
Paris
Madrid
Madrid
Zürich
Relationenalgebra
(SQL-92)
s e l e c t Abflug , Ankunft
from LINIENFLUG
where A b f l u g = ’ Wien ’
union
s e l e c t LF1 . A b f l u g , LF2 . A n k u n f t
from LINIENFLUG LF1 , LINIENFLUG LF2
where LF1 . A b f l u g = ’ Wien ’ and LF1 . A n k u n f t = LF2 . A b f l u g
union
s e l e c t LF1 . A b f l u g , LF3 . A n k u n f t
from LINIENFLUG LF1 , LINIENFLUG LF2 , LINIENFLUG LF3
where LF1 . A b f l u g = ’ Wien ’ and LF1 . A n k u n f t = LF2 . A b f l u g
and LF2 . A n k u n f t = LF3 . A b f l u g
Problem: Abfragetiefe nur fest definierbar!
Rekursionsanfrage
Abhilfe mit with-Klausel:
with r e c u r s i v e r e k u r s i o n s t a b e l l e as (
r e k u r s i v e r −a n f r a g e −a u s d r u c k
)
[ traversierungsklausel ] [ zyklenklausel ]
a n f r a g e −a u s d r u c k
Initialisierung:
select . . .
from t a b e l l e where . . .
union a l l
Rekursionsschritt:
select . . .
from t a b e l l e , r e k u r s i o n s t a b e l l e
where r e k u r s i o n s b e d i n g u n g
(SQL:1999)
Anwendung auf Beispiel
w i t h r e c u r s i v e FLUGSTRECKE( A b f l u g , A n k u n f t ) a s (
s e l e c t Abflug , Ankunft
from LINIENFLUG
where A b f l u g = ’ Wien ’
union a l l
s e l e c t FS . A b f l u g , FL . A n k u n f t
from FLUGSTRECKE FS , LINIENFLUG FL
where FS . A n k u n f t = FL . A b f l u g )
s e l e c t d i s t i n c t ∗ from FLUGSTRECKE
Abflug
Wien
Wien
Wien
Wien
Ankunft
Frankfurt
Budapest
Venedig
Zürich
Abflug
Wien
Wien
Wien
Wien
Wien
Wien
Wien
Ankunft
Frankfurt
Budapest
Venedig
Zürich
London
Rom
Paris
Abflug
Wien
Wien
Wien
Wien
Wien
Wien
Wien
Wien
Ankunft
Frankfurt
Budapest
Venedig
Zürich
London
Rom
Paris
Madrid
Traversierungsreihenfolge
• Manipulation der Reihenfolge
• Traversierungsklausel
• in die Breite
search breadth
f i r s t by s p a l t e s e t pseudo−s p a l t e
• in die Tiefe
s e a r c h d e p t h f i r s t by s p a l t e s e t pseudo−s p a l t e
w i t h r e c u r s i v e FLUGSTRECKE( A b f l u g , Ankunft , S t r e c k e ) a s (
s e l e c t A b f l u g , Ankunft ,
A b f l u g | | ’− ’ | | A n k u n f t a s S t r e c k e
from LINIENFLUG
where A b f l u g = ’ Wien ’
union a l l
s e l e c t FS . A b f l u g , FL . Ankunft ,
S t r e c k e | | ’− ’ | | FL . A n k u n f t a s S t r e c k e
from FLUGSTRECKE FS , LINIENFLUG FL
where FS . A n k u n f t = FL . A b f l u g )
s e a r c h d e p t h f i r s t by A n k u n f t s e t R e i h e n f o l g e
select Strecke
from FLUGSTRECKE o r d e r by R e i h e n f o l g e
Strecke
Wien-Frankfurt
Wien-Frankfurt-London
Wien-Budapest
Wien-Venedig
Wien-Venedig-Rom
Wien-Zürich
Wien-Zürich-Paris
Wien-Zürich-Paris-Madrid
Sicherheit: Beschränkung
w i t h r e c u r s i v e FLUGSTRECKE( A b f l u g , Ankunft , U m s t e i g e n ) a s (
s e l e c t A b f l u g , Ankunft , 0
from LINIENFLUG
where A b f l u g = ’ Wien ’
union a l l
s e l e c t FS . A b f l u g , FL . Ankunft , U m s t e i g e n + 1
from FLUGSTRECKE FS , LINIENFLUG FL
where FS . A n k u n f t = FL . A b f l u g and U m s t e i g e n < 2 )
s e l e c t d i s t i n c t ∗ from FLUGSTRECKE
Sicherheit: Zyklenerkennung
w i t h r e c u r s i v e FLUGSTRECKE( A b f l u g , Ankunft , S t r e c k e ) a s (
s e l e c t A b f l u g , Ankunft ,
A b f l u g | | ’− ’ | | A n k u n f t a s S t r e c k e
from LINIENFLUG
where A b f l u g = ’ Wien ’
union a l l
s e l e c t FS . A b f l u g , FL . Ankunft ,
S t r e c k e | | ’− ’ | | FL . A n k u n f t a s S t r e c k e
from FLUGSTRECKE FS , LINIENFLUG FL
where FS . A n k u n f t = FL . A b f l u g )
c y c l e A n k u n f t s e t Z y k l u s t o ’ ∗ ’ d e f a u l t ’− ’
s e l e c t S t r e c k e , Z y k l u s from FLUGSTRECKE
Strecke
..
.
Zyklus
..
.
Wien-Zürich
Wien-Zürich-Paris
Wien-Zürich-Paris-Madrid
Wien-Zürich-Paris-Madrid-Zürich
*
Unterstützte RDBMS
• IBM DB2 (Windows & Unix), SQL Server 2005
Weglassung des Schlüsselworts recursive
• IBM DB2 (z/OS) gemäß Standard
• Oracle besitzt eigene Befehle für hierarchische Anfragen