Rekurzív WITH utasítás

By 2016.07.28Adatbázisok, sql

Egy korábbi blog bejegyzésben  megismerkedhettek az olvasók a WITH utasítással és annak előnyeivel.

A mostani poszt a WITH utasítás egy másik előnyös tulajdonságára kíván rávilágítani.
Mint ismeretes, a procedurális nyelvek világában (lásd PL/SQL) a rekurzió megszokott dolog.
A deklaratív nyelvek körében (lásd SQL) ez nem szokványos.
A SQL WITH utasításában azonban definiálhatunk rekurzív hívást.
A  jelen bejegyzés erre kíván egy példát mutatni.

Tételezzük fel, hogy egy alkalmazásban táblabeli adatok alapján, útvonal tippeket szeretnénk szolgáltatni az érdeklődőknek.
Jegyezzük meg még egyszer, hogy e cikk szerzője nem akar versenyre kelni a jobbnál jobb GPS rendszerek ilyen irányú szolgáltatásaival  (beleértve a Google kiváló szolgáltatásait).
A cikk abban szeretne segíteni, ötletet adni, hogy egy alkalmazásban miként lehet egy útvonaltervezést használni.

Tételezzük fel, hogy autóval szeretnénk eljutni egy adott kiindulási helyről egy célállomásra.
A mintapéldában egy – nagyon – leegyszerűsített modell szerepel.

A tábla létrehozása és feltöltése az alábbi szkripttel történt:

CREATE TABLE itinerary
(        source VARCHAR2(20),
destination VARCHAR2(20),
distance NUMBER    ) ;

INSERT INTO itinerary (source, destination,distance) VALUES ('Budapest','Warszawa',680);
INSERT INTO itinerary(source, destination,distance) VALUES ('Budapest','Wien',250);
INSERT INTO itinerary( source, destination,distance) VALUES ('Budapest','Beograd',400);
INSERT INTO itinerary( source, destination,distance) VALUES ('Budapest','Bucuresti',830);
INSERT INTO itinerary( source, destination,distance) VALUES ('Wien','Praha',320);
INSERT INTO itinerary (source, destination,distance) VALUES ('Wien','Graz',200);
INSERT INTO itinerary (source, destination,distance) VALUES('Praha','Berlin',340);
INSERT INTO itinerary (source, destination,distance) VALUES ('Warszawa','Berlin',560);
INSERT INTO itinerary (source, destination,distance) VALUES ('Graz','Trieste',290);
INSERT INTO itinerary (source,destination,distance) VALUES ('Zagreb','Trieste',230);
INSERT INTO itinerary (source,destination,distance) VALUES ('Budapest','Zagreb',350);
INSERT INTO itinerary (source,destination,distance) VALUES ('Beograd','Bucuresti',590);
COMMIT;

A tartalmat ellenőrízhetjük az alábbi egyszerű SELECT utasítással:
SELECT * FROM itinerary ;
with1

 

 

 

 

 

Használjuk ki a WITH utasítás azon képességét, hogy rekurzív SQL utasítást engedélyez az UNION ALL operátor használata esetén. (Figyelem az UNION nem elégséges!)

A példánkban látni szeretnénk néhány desztináció elérésére vonatkozó ajánlatokat.
A javaslatok nem térnek ki az időszükségletekre, pusztán távolsági adatokkal  dolgoznak.
Természetesen – az alapadatok bővítésével – ennek sincs akadálya.

A példa az  alábbi WITH … SELECT utasítással dolgozik:

WITH travelling
(SOURCE, destination , total_distance, transfers, route)
AS
(
SELECT SOURCE, destination , distance,  1 transfers,  destination route
FROM itinerary
UNION ALL
SELECT incoming.SOURCE, outgoing.destination,
incoming.total_distance + outgoing.distance, transfers+1 ,  route ||' -> ' || outgoing.destination
FROM travelling incoming, itinerary outgoing
WHERE incoming.destination = outgoing.Source
)
SELECT Source, destination, total_distance, transfers, route
FROM travelling
ORDER BY SOURCE, destination, total_distance ;

Az eredményhalmaz:

with2
Vegyük észre, hogy az SQL utasítás pszeudóoszlopokkal  (számított kifejezésekkel dolgozik): total_distance, transfers,  route.
Megjegyzendő, hogy az eredményhalmaz a köztes csomópontok számáról (transfers) is ad felvilágosítást.
A távolságok egy régi – COLLINS ROAD ATLAS EUROPE 1992 – térkép adatai alapján kerültek felhasználásra, ami vélhetőleg nem rontja a mondanivaló lényegét.

Amennyiben a fenti példák felkeltették érdeklődésedet, keresd fel egyesületünket (PCF – People Come First), mert igény esetén a fenti témákban előadásokat tartunk.
A szerző elérhetősége: laszlo.czinkoczki<a>webvalto.hu

Leave a Reply

− 5 = 1