Nézettáblák, munkatáblák létrehozása és használata; Rekurzió, with záradék
>> I.RÉSZ - NÉZETTÁBLÁK, MUNKATÁBLÁK LÉTREHOZÁSA ÉS HASZNÁLATA
>> II.RÉSZ - HIERARCHIKUS, GRÁFOS ADATSZERKEZETEK, WITH ZÁRADÉK
I.RÉSZ Nézettáblák [CREATE OR REPLACE | DROP ] VIEW; WITH munkatáblák
1.) Egyszerű/összetett nézettáblák létrehozása, melyik módosítható/melyik nem?
Adatok karbantartása adattáblán illetve nézeten keresztül, és megfigyelni ezek
egymásra hatását: Hogyan hat a nézetek adatainak módosítása az adattáblára,
és a másik irányban, az adatok módosítása hogyan jelenik meg a nézetben?
>> Oracle: SQL Lang.Ref. CREATE VIEW [ kiegészítés: Oracle SQL11_view.pdf ]
2.) Például: Bonyolultabb lekérdezés megoldása nézettáblák segítségével...
Képezzük osztályonként az összfizetést, vegyük ezen számok átlagát, és
adjuk meg, hogy mely osztályokon nagyobb ennél az átlagnál az összfizetés.
CREATE OR REPLACE VIEW osztaly_osszfiz
AS
SELECT onev, SUM(fizetes) ossz_fiz
FROM sila.dolgozo d, sila.osztaly o
WHERE d.oazon = o.oazon
GROUP BY onev;
CREATE OR REPLACE VIEW atlag_koltseg
AS
SELECT SUM(ossz_fiz)/COUNT(*) atlag
FROM osztaly_osszfiz;
SELECT * FROM osztaly_osszfiz
WHERE ossz_fiz > (SELECT atlag FROM atlag_koltseg);
-- -- -- -- --
3.) Példák WITH munkatáblák használatára:
- lásd SQL08_alkerdes2.pdf (16-18.o. példa, uez with-re példa SQL Lang.Ref.)
- Oracle: SQL Language Reference >> innen: Subquery Factoring: Examples
Képezzük osztályonként az összfizetést, vegyük ezen számok átlagát, és
adjuk meg, hogy mely osztályokon nagyobb ennél az átlagnál az összfizetés.
WITH
osztaly_osszfiz AS (
SELECT onev, SUM(fizetes) ossz_fiz
FROM sila.dolgozo d, sila.osztaly o
WHERE d.oazon = o.oazon
GROUP BY onev),
atlag_koltseg AS (
SELECT SUM(ossz_fiz)/COUNT(*) atlag
FROM osztaly_osszfiz)
SELECT * FROM osztaly_osszfiz
WHERE ossz_fiz > (SELECT atlag FROM atlag_koltseg);
-- -- -- -- --
>> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 6.fejezet feladatai
II.RÉSZ: HIERARCHIKUS ADATSZERKEZETEK ÉS GRÁFOK, REKURZIÓ
1.) Hierarchikus adatszerkezetek lekérdezése Oracle-ben: CONNECT BY
>> Oracle: SQL12_connect_by.pdf; -- példákhoz: create_hr_synonym.txt
>> Oracle LiveSQL Tutorials Try it! >> LiveSQL Hierarchical_Queries.html
>> Oracle Példatár 3.fej. Hierarchikus lekérdezések: peldatar_62-65o.pdf
SELECT ... FROM... WHERE ... START WITH ... CONNECT BY PRIOR ...
Példák / Examples: Oracle DB SQL Language Reference 12.2
>> 9.fej. SQL Queries and Subqueries >> Hierarchical Queries html
>> 3.fej. Pseudocolumns -> Hierarchical Query >> LEVEL Pseudocolumn
>> 7.fej. Functions >> Single-Row Functions >> Hierarchical Functions:
>> SYS_CONNECT_BY_PATH (Examples)
-- Az Oracle SQL Lang.Ref. 9.fej. SELECT példáit írjuk át, lépésenként...
select dkod, dnev, fonoke from dolgozo;
select dkod, dnev, fonoke, level
from dolgozo
start with dkod = 7839 -- 'KING'
-- (select dkod from dolgozo where dnev='KING')
connect by prior dkod = fonoke;
-- Ha a fát felülről-lefelé, előrefelé járjuk be:
select dkod, dnev, fonoke, level
from dolgozo
start with dkod = 7566 -- 'JONES'
connect by prior dkod = fonoke;
-- Ha a fát alulról felfelé, visszafelé járjuk be:
select dkod, dnev, fonoke, level
from dolgozo
start with dkod = 7566 -- 'JONES'
connect by prior fonoke = dkod;
-- SQL Lang.Ref. 7.fej. SYS_CONNECT_BY_PATH fv-re való példát is írjuk át:
select dkod, dnev, fonoke, level szint,
LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(dnev, '/') utvonal
from dolgozo
start with dkod = (select dkod
from dolgozo where dnev='KING')
connect by prior dkod = fonoke;
-- -- -- -- --
>> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 3.fejezetben is volt.
-- -- -- -- --
2.) Rekurzív relációk definiálása az SQL-ben / A Tankönyv Járatok példája
Az Eljut-feladat a Tankönyv (Ullman-Widom kék könyv) 10.2 szakaszára épül
Adott Jaratok(legitarsasag, honnan, hova, koltseg, indulas, erkezes) táblában
repülőjáratok adatait tároljuk (honnan-hova várospárok). Azt keressük, hogy
Dallasból mely városokba tudunk eljutni (közvetlenül vagy egy/több átszállással).
>> Ullman-Widom tankönyv 10.2 szakasza Eljut-feladat: korábbiEA/p67_p80.pdf
>> Oracle: SQL Language Reference >> innen: Recursive Subquery Factoring
>> Ezzel a scripttel jaratok_tabla.txt készítsünk saját táblát,
lásd Ullman-Widom Tankönyv 10.2. szakasz (468.oldal tetején)
>> Az Ullman-Widom Tankönyv szerint (SQL szabvány szerint, csak papíron)
(konkrét adatbázis-kezelő rendszerekben eltérő megvalósítások lehetnek)
WITH RECURSIVE Eljut(honnan, hova) AS
(SELECT honnan, hova FROM Jaratok
UNION
SELECT Eljut.honnan, Jaratok.hova
FROM Eljut, Jaratok
WHERE Eljut.hova = Jaratok.honnan)
SELECT hova FROM Eljut WHERE honnan='DAL';
--- Kérdés: itt miért fontos kiemelni az UNION (halmaz) és
UNION ALL (multihalmaz) közötti különbséget?
-- -- -- -- --
3.) Az Eljut feladat megvalósítása Oracle-ben WITH utasítással:
Az előző feladat scripttel jaratok_tabla.txt készítsünk saját táblát.
>> Az SQL-99 szabványhoz képest az eltérések, hogy WITH RECURSIVE helyett
az Oracle-ben csak WITH, és a UNION az Oracle-ben nem megy, hanem csak
UNION ALL működik, és nem-hierarchikus esetben meg kell adni a CYCLE-t is:
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
)
SEARCH DEPTH FIRST BY honnan SET SORTING
CYCLE honnan SET is_cycle TO 1 DEFAULT 0
SELECT distinct honnan, hova
FROM Eljut
ORDER BY honnan;
-- -- -- -- --
4.) Az Eljut feladat megvalósítása Oracle-ben CONNECT BY megoldással
Az előző feladat scripttel jaratok_tabla.txt készítsünk saját táblát.
Amíg nincs kör, a fenti 1.f. szerinti hierarchikus lekérdezéssel működik!
>> Ha kör van a gráfban, akkor a NOCYCLE kiegészítéssel is már működik!
SELECT LPAD(' ', 4*level) || honnan, hova,
level-1 atszallasok,
sys_connect_by_path(honnan||'->'||hova, '/') utvonal
FROM Jaratok
START WITH honnan = 'SF'
CONNECT BY NOCYCLE PRIOR hova = honnan;
-- -- -- -- --
5.) További lehetőségek, érdekességek (nem-tananyag, opcionális)
>> Reguláris kifejezések: SQL_regularis_kif.txt; SQL_regular_exp.pdf
>> Analitikus függvények: Oracle Példatár 13.fej.; Tutorial: Analytic Funct.
-- -- -- -- --
>> KÖV.TÉMA JÖN 9.-11.gyak. PL/SQL - SQL progr.nyelvi környezetben használata