Hierarchikus (rekurzív) lekérdezések  (START WITH, CONNECT BY)
------------------------------------

SELECT ... FROM ... WHERE ...
START WITH feltétel
CONNECT BY feltétel

Ez a lehetőség arra való, hogy hierarchikus szerkezetű adatokat is le tudjunk kérdezni 
egy SELECT utasítás segítségével. A hierarchia gyökerét (gyökereit) a START WITH -del 
kell megadni. A szülő és gyermek sorok közti kapcsolatot a CONNECT BY feltétel résszel. 
Ebben használnunk kell a PRIOR kulcsszót, amelyik a szülő sorra utal (lásd alább). 
Az ilyen jellegű lekérdezések esetén a kiértékelés sorrendje a következő:

1. A START WITH alapján kiválasztja a gyökér sorokat.

2. A CONNECT BY alapján a gyökerek gyerekeit választja ki.

3. A további leszármazottakat választja ki mélységi keresési stratégiával.

4. A WHERE alapján kiszűri a sorokat, de az eldobott sorok leszármazottait nem dobja el. 
   Vagyis a feltételt minden egyes sorra külön-külön értékeli ki.

5. A sorokat a preorder bejárás szerinti sorrendben adja vissza.

Ha egy SELECT hierarchikus struktúrát kérdez le, akkor korlátozottan használhat join műveletet.

A CONNECT BY után akár több feltételt is használhatunk. De ha kört talál a struktúrában akkor hibát ad.
pl.  CONNECT BY PRIOR dkod = fonoke AND fizetes > jutalek
Itt a dkod a szülő sorra vonatkozik, a többi a gyerek sorra.

A PRIOR unáris operátor
-----------------------
Amikor egy szülő-gyerek csomópont párt (sor párt) vizsgál a bejárás során, a PRIOR-ral hivatkozott 
oszlopok a szülő csomópontra vonatkoznak. A CONNECT BY feltételben kötelező használnunk legalább
egy helyen a PRIOR kulcsszót, egyébként nem tudnánk a szülő-gyerek viszonyt kifejezni a csomópontok
között.

LEVEL pszeudo oszlop
--------------------
Hierarchikus (CONNECT BY) lekérdezésekben használhatjuk a LEVEL pszeudo oszlopot, 
amelyik a gyökér sorokra egyet ad vissza, a többiekre, pedig a megfelelő mélységet.

Példa:
------
SELECT   LPAD(' ', 2*(LEVEL-1)) || dnev, dkod, fonoke, foglalkozas, LEVEL
FROM nikovits.dolgozo
START WITH   foglalkozas='PRESIDENT'
CONNECT BY  PRIOR dkod = fonoke;

Testvérek rendezése:
ORDER BY  -> SIBLINGS [NULLS FIRST | NULLS LAST] 

Példa:
------
SELECT   LPAD(' ', 2*(LEVEL-1)) || dnev, dkod, fonoke, foglalkozas, LEVEL
FROM nikovits.dolgozo
START WITH   foglalkozas='PRESIDENT'
CONNECT BY  PRIOR dkod = fonoke
ORDER SIBLINGS BY dnev;  --  [NULLS FIRST | LAST]


Adjuk meg a NIKOVITS.VAGYONOK tábla alapján KAIN unokáinak az össz-vagyonát.

SELECT   SUM(vagyon) -- LPAD(' ', 2*(LEVEL-1)) || nev, apja, vagyon
FROM nikovits.vagyonok
WHERE LEVEL = 3
START WITH  nev='KAIN'
CONNECT BY  PRIOR nev = apja;


Hasonlítsuk össze az alábbi lekérdezéseket és figyeljük meg a PRIOR szerepét!

-- ABEL összes leszármazottja
SELECT    LPAD(' ', 4*(LEVEL-1)) || nev, apja, vagyon
FROM nikovits.vagyonok
WHERE nev like '%%'
START WITH  nev='ABEL'
CONNECT BY  PRIOR nev = apja;

-- ABEL azon leszármazottai, akik felmenőinek nevében van 'A' betű
-- mielőtt egy újabb csomópontot bejár, megnézi, hogy az új csomópont szülőjében van-e 'A' betű.
SELECT    LPAD(' ', 4*(LEVEL-1)) || nev, apja, vagyon
FROM nikovits.vagyonok
WHERE nev like '%%'
START WITH  nev='ABEL'
CONNECT BY  PRIOR nev = apja and prior nev like '%A%';

-- Akik nevében és összes felmenőjének nevében van 'A' betű
-- mielőtt egy újabb csomópontot bejár, megnézi, hogy az új csomópontban van-e 'A' betű.
SELECT    LPAD(' ', 4*(LEVEL-1)) || nev, apja, vagyon
FROM nikovits.vagyonok
WHERE nev like '%%'
START WITH  nev='ABEL'
CONNECT BY  PRIOR nev = apja and nev like '%A%';


A CONNECT_BY_ROOT operátor
--------------------------
A bejárás során bármelyik szinten hivatkozhatunk vele a gyökér csomópont (sor) egy oszlopára.

Példa:
------
Azokat kérdezzük le a hierarchiában, akik városa azonos ADAM városával.

SELECT  LPAD(' ', 2*(LEVEL-1)) || nev, apja, varos, CONNECT_BY_ROOT varos
FROM nikovits.vagyonok
WHERE varos = CONNECT_BY_ROOT varos
START WITH  nev='ADAM'
CONNECT BY  PRIOR nev = apja;


A SYS_CONNECT_BY_PATH függvény: SYS_CONNECT_BY_PATH(oszlop, char)
----------------------------------------------------------------
A SYS_CONNECT_BY_PATH összefűzi a csomópontok adott oszlopát a hierarchia mentén
a gyökértől az adott csomópontig. Elválasztó karakterláncként a második paraméter szolgál.

Példa:
------
SELECT  nev, SYS_CONNECT_BY_PATH(nev,'.'), SYS_CONNECT_BY_PATH(varos,'.')
FROM nikovits.vagyonok
START WITH  nev='ADAM'
CONNECT BY  PRIOR nev = apja;


Az eddigi (fenti) rekurziv lekérdezések csak akkor működnek, ha nincs kör a gráfban,
vagyis az fa szerkezetű. (összefüggő, körmentes gráf -> fa)
Ha kör van, akkor hibaüzenetet kapunk.

Ezt elkerülhetjük ha a lekérdezésbe beleírjuk a NOCYCLE kulcsszót (lásd alább).


Egy újabb példa.
----------------

CREATE TABLE jaratok(legitarsasag VARCHAR2(10), honnan VARCHAR2(15), hova VARCHAR2(15), koltseg NUMBER);

INSERT INTO jaratok VALUES('Lufthansa', 'San Francisco', 'Denver', 1000);
INSERT INTO jaratok VALUES('Lufthansa', 'San Francisco', 'Dallas', 10000);
INSERT INTO jaratok VALUES('Lufthansa', 'Denver', 'Dallas', 500);
INSERT INTO jaratok VALUES('Lufthansa', 'Denver', 'Chicago', 2000);
INSERT INTO jaratok VALUES('Lufthansa', 'Dallas', 'Chicago', 600);
INSERT INTO jaratok VALUES('Lufthansa', 'Dallas', 'New York', 2000);
INSERT INTO jaratok VALUES('Lufthansa', 'Chicago', 'New York', 3000);
INSERT INTO jaratok VALUES('Lufthansa', 'Chicago', 'Denver', 2000);

-- Az alábbi törlés megszünteti a kört a gráfban, azután már működik 
-- a korábbi START WITH, CONNECT BY típusú lekérdezés.

delete from jaratok where honnan='Chicago' and hova='Denver';

Példa:
------
Adjuk meg, hogy mely városokba lehet eljutni San Franciscoból.

SELECT LPAD(' ', 4*level) ||honnan, hova, level-1 Atszallasok
FROM jaratok 
START WITH honnan = 'San Francisco'
CONNECT BY PRIOR hova = honnan;

Ha azonban az utolsó sort nem töröljük ki, akkor kör van a gráfban, és ekkor 
bele kell írnunk a NOCYCLE kulcsszót a lekérdezésbe.

SELECT LPAD(' ', 2*level) ||honnan, hova, level-1 Atszallasok
FROM jaratok 
START WITH honnan = 'San Francisco'
CONNECT BY NOCYCLE PRIOR hova = honnan;

További pszeudo oszlopok:

CONNECT_BY_ISCYCLE: 1-et ad vissza az aktuális sorra, ha annak van olyan gyermeke, aki őse is.
CONNECT_BY_ISLEAF:  1-et ad vissza az aktuális sorra, ha annak nincs gyermeke (levél a fában),
                    illetve csak olyan gyermeke van, amit már korábban bejárt.
Példa:
------
SELECT LPAD(' ', 2*level) ||honnan, hova, level-1 Atszallasok,
  CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF
FROM jaratok 
START WITH honnan = 'San Francisco'
CONNECT BY NOCYCLE PRIOR hova = honnan;


Példa:
------
Listázzuk ki a teljes útvonalakat San Franciscoból.
A csomópontok honnan oszlopait fűztük össze a gyökértől végig a csomópontig, és az utolsó 
csomópont hova oszlopát is a végéhez fűztük.

SELECT CONNECT_BY_ROOT honnan AS indulas, hova AS cel, LEVEL, 
       SYS_CONNECT_BY_PATH(honnan, '->')||'->'||hova "Utvonal"
FROM jaratok
START WITH honnan='San Francisco'
CONNECT BY NOCYCLE PRIOR hova = honnan
ORDER BY LEVEL;


Egy érdekes példa az alábbi:
----------------------------
SELECT LTRIM(SYS_CONNECT_BY_PATH (r2,','),',')  -- az első vesszőt levágjuk
FROM (SELECT ROWNUM r, dnev r2 FROM dolgozo)
WHERE CONNECT_BY_ISLEAF = 1                     -- csak a levelek maradnak meg
START WITH r = 1 CONNECT BY r = PRIOR r + 1;


DATALOG programok
-----------------
A Datalog programok segítségével rekurzív jellegű lekérdezéseket is ki tudunk fejezni.
Először nézzünk meg néhány egyszerű DATALOG lekérdezést a korábbi táblákra.

Írjunk fel néhány egyszerű lekérdezést Datalog program segítségével a
Szeret(név, gyümölcs) relációra:

Kik szeretik az almát? 
  Almaszereto(X) <- Szeret(X, 'alma')   (vagy  Almaszereto(X) <- Szeret(X, Y) AND Y='alma')

Kik azok akik nem szeretik az almát? (de valami mást igen)
  Almat_NemSz(X) <- Szeret(X, Y) AND NOT Szeret(X, 'alma')

Kik szeretik az almát és a körtét?
  AlmaKorte(X) <- Szeret(X, 'alma') AND Szeret(X, 'körte')

Kik szeretik az almát vagy a körtét?
  AlmavagyKorte(X) <- Szeret(X, 'alma')
  AlmavagyKorte(X) <- Szeret(X, 'körte')

Kik azok akik szeretik az almát, de a körtét nem szeretik?
  Alma_KörteNSz(X) <- Szeret(X, 'alma') AND NOT Szeret(X, 'körte')

Kik szeretnek legalább kétféle gyümölcsöt?
  KettotSzeret(X) <- Szeret(X, Y) AND Szeret(X, Z) AND Y<>Z


Hozzuk létre az alábbi táblát, ami már nem csak a közvetlen járatokat fogja tartalmazni.
----------------------------------------------------------------------------------------
CREATE TABLE eljut(honnan VARCHAR2(15), hova VARCHAR2(15), koltseg NUMBER);

Azt, hogy mely városokból hova lehet eljutni, az alábbi Datalog lekérdezéssel
tudnánk megválaszolni:

Eljut(X,Y)  <--  Jaratok(X,Y,_)
Eljut(X,Y)  <--  Jaratok(X,Z,_) AND Eljut(Z,Y) AND X <> Y

A fenti rekurzív Datalog programot írjuk át az SQL3 szabványban 
szereplő WITH RECURSIVE utasítással! (Oracle nem támogatja ezt a szintaxist, de PostgreSQL igen). 
Az alábbi lekérdezés szerkezete hasonlít a fenti DATALOG program szerkezetére.

WITH RECURSIVE eljut(honnan, hova) AS 
(SELECT honnan, hova FROM jaratok
  UNION
 SELECT jaratok.honnan, eljut.hova FROM jaratok, eljut
 WHERE jaratok.hova = eljut.honnan AND jaratok.honnan <> eljut.hova
)
SELECT  honnan, hova FROM eljut order by 1;


A fenti típusú lekérdezést Oracle-ben egy kicsit eltérő szintaxissal
de hasonló szerkezetű lekérdezéssel valósíthatjuk meg:

WITH  eljut(honnan, hova) AS 
 (
  SELECT honnan, hova FROM jaratok
   UNION ALL
  SELECT jaratok.honnan, eljut.hova FROM jaratok, eljut
  WHERE jaratok.hova = eljut.honnan AND jaratok.honnan <> eljut.hova
  )
  CYCLE honnan SET van_kor TO 'I' DEFAULT 'N'   -- a megadott oszlop alapján dönti el, hogy van-e kör
SELECT  distinct honnan, hova FROM eljut order by 1;


Az ELJUT táblát előállíthatjuk a korábban látott CONNECT BY rekurzív lekérdezéssel is:

SELECT distinct CONNECT_BY_ROOT honnan AS indulas, hova AS cel
FROM jaratok
WHERE CONNECT_BY_ROOT honnan <> hova       -- a végpont ne legyen azonos a kezdőponttal
START WITH 1=1                             -- minden csomópontból elindítjuk a bejárást
CONNECT BY NOCYCLE PRIOR hova = honnan
ORDER BY indulas, cel;