| Informatikai
képzés: INFORMÁCIÓS
RENDSZEREK 9.GYAKORLAT: Haladó SQL, with recursive, PL/SQL |
|
-- -- --
I.rész: SQL DDL (táblák, megszorítások és nézettáblák létrehozása)
Elméleti összefoglaló: 5.ea.pdf (folyt.34.oldaltól végig) és 6.ea.pdf
> Kieg. Oracle SQL segédanyagok: DML.pdf; DDL.pdf; view.pdf
> További segédletek: create table, típusok, megszorítások (.txt)
> Feladatok: Táblák és megszorítások (constraints) létrehozása.
> Feladatok: Egyszerű és összetett nézettáblák létrehozása,
állapítsuk meg melyik nézettábla módosítható és 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? Eközben mentési pontokat adjunk meg ahova visszalépünk
Tranzakciókezelés alapjai, mentési pontok: SAVEPOINT és
visszagörgetés: ROLLBACK (illetve érvényesítés: COMMIT).
-- -- --
II.rész: WITH utasítás, de előtte még egy példa nézettáblák használatára:
-- 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)
-- Ugyanez WITH-záradékkal, lásd SQL08_subquery2.pdf (16-18.o. WITH záradék)
WITH-záradék, lásd Subquery Factoring: Examples
--- gépes lekérdezési feladatok munkatáblák segítségével, és
--- nézzünk példákat nézettáblák <-> munkatáblák átírásokra
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)
-- -- --
III.rész: Hierarchikus és gráfos adatok lekérdezése, WITH RECURSIVE utasítás
Elméleti összefoglaló: 8.ea.pdf (Eljut feladat: 35-39.o., és 49.o.-tól végig)
Rekurzió: Tankönyv 10.2. (Ullman-Widom kék könyv) Eljut feladat
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 illetve több, akárhány átszállással).
>> 3.1. SQL szabvány szerint (csak papíron)
(konkrét adatbázis-kezelő rendszerekben eltérő megvalósítások)
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.2. Az Eljut feladat gépes megvalósítása Oracle-ben:
-- Ezzel a scripttel jaratok_tabla.txt készítsünk saját táblát.
-- A fenti szabvány SQL megoldás csak egy kis változtatással
fut le gépes környezetben! Például Oracle 11gR2 megoldás:
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;
-- Példák az Oracle SQL Language Reference 11.2.pdf dokumentációban:
>> Rekurzió with-utasítással: Recursive Subquery Factoring: Examples
>> 3.3. Hierarchikus lekérdezésekre Oracle megoldás (8.ea.pdf legvégén)
> SQL Lang.Ref: Hierarchical Queries, Hierarchical Query Examples
SELECT ... FROM... WHERE ... START WITH ... CONNECT BY PRIOR ...
PL/SQL Témakör: Oracle PL/SQL témakör gyakorlatán az Eljut feladatra
gráfos adatok lekérdezését PL/SQL programmal is megvalósítjuk, ehhez:
a PL/SQL alapokat tekinjük át (változók, vezérlési szerkezetek, ciklusok,
DML utasítások, egy sort visszaadó SELECT INTO használata programban,
és a köv.héten folytatjuk a több sort visszaadó SELECT-re kurzorok, stb)
> Adatbázisok-1 EA: 7.előadás.pdf (végén: Rekurzió)
> Adatbázisok-1 EA: 8.előadás.pdf (SQL/PSM, PL/SQL)
> Oracle PL/SQL: Oracle Junior EA >> Oracle PL/SQL.pdf
> SQL/PSM --- Ullman-Widom Tankönyv 9.3-9.4.fejezetei alapján
-- Tk.9.3. SQL/PSM: Osztott változók, lekérdezések és kurzorok
-- Tk.9.4. Tárolt eljárások és függvények, kivételkezelés
> Oracle PL/SQL segédanyagok: ebben az eduPLSQL mappában
-- Tankönyvtár: PL/SQL programozás Oracle 10g-ben (Gábor A.-Juhász I.)
> Oracle PL/SQL doksi: Oracle 11gR2 doc PL/SQL Language Reference
-- -- -- --
1/7.rész: PL/SQL alapjai, PL/SQL blokk szerkezete >> 2.fej. Alapok
[1_deklarációs_rész] 2_végrehajtható_rész [3_kivételkezelő_rész]
SET SERVEROUTPUT ON;
>> példa-2-24 deklarációk-és-értékadás
>> példa-2-25 select-into (itt a 2-25 példában blokk: begin ... end; /
át kell tenni az end;-et a végére!) SELECT INTO-t akkor használjuk,
ha a lekérdezés pontosan egy sort ad, ha a lekérdezés több sorral
tér vissza, akkor kurzort kell használni, lásd köv.gyakorlat anyagát).
>> példa6-1 DML utasítások a programban, implicit kurzor
>> példa-6-4 implicit kurzor attribútumok
> FELADATSOR 5/A --- PL/SQL--1 feladatok:
-- A feladatokat most is a saját Dolgozo-Osztaly táblákra kell megírni, ehhez
a táblákat létrehozó script, mint a DML-hez volt: createDolg (no constraint)
-- A PL/SQL blokk előtt minden alkalommal állítsuk be: SET SERVEROUTPUT ON
1.) Az első feladat: Írjuk ki PL/SQL blokkból: 'Szia Világ!', majd egészítsük ki,
kérjen be egy nevet, számot, dátumot és ezeket is írassuk ki a programból!
2.) Írjuk ki KING fizetését (olvasás táblából változóba), abban az esetben,
ha ismert, hogy pontosan egy KING nevű dolgozó szerepel a táblában,
lásd példa-2-25 select-into (csak ha a lekérdezés pontosan egy sort ad).
3.) Adjuk meg egy főnök azonosítóját, és töröljük a közvetlen beosztottjait, és
írassuk ki hány beosztottja volt, lásd példa-6-4 implicit kurzor attribútumok.
-- -- -- --
2/7.rész: Vezérlési szerkezetek a PL/SQL-ben 4.fej. Control Statements
PL/SQL alapjaihoz kiegészítés: Alprogramok (tárolt eljárások/függvények)
>> példa2-19 Alprogramok (már itt is vannak: 2.fej. Alapok)
>> példa4-1 Alprogramok IF-THEN utasítás példában (4.fej. Utasítások)
Vezérlési szerkezetek: Feltételes utasítások, ciklusok
>> példa-4-5 if-then-elsif utasítás,
>> példa-4-6 egyszerű case utasítás, és itt írjuk át grade := 'B' -> '&B'
helyettesítési változóra, aminek a felhasználó adja meg az értékét!
>> példa-4-10 alap LOOP ciklus utasítás EXIT WHEN kilépés a ciklusból
>> példa 4-14 FOR ciklus utasítás
>> példa 4-27 WHILE ciklus utasítás
-- -- -- --
>> Oracle PL/SQL.pdf (és összefoglalók) plsql_01_bevezetes.pdf,
02_vezerlo_utasitasok.txt, 02_tipusok.pdf, 03_dml.pdf, 05_valtozok.pdf
--- Ez volt az előkészítés az Eljut feladat megoldásához, lásd köv.3/6.részt.
-- -- -- --
3/7.rész: Az "Eljut feladat" megvalósítása PL/SQL-ben
-- Adatbázisok-1 EA: 8.előadás.pdf (Datalog, Rekurzió) -->> 35-62.o.
-- 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).
>> 3.3.1. SQL szabvány szerint (csak papíron)
(konkrét adatbázis-kezelő rendszerekben eltérő megvalósítások)
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.3.2. Az Eljut feladat gépes megvalósítása Oracle-ben:
-- Ezzel a scripttel jaratok_tabla.txt készítsünk saját táblát.
-- A fenti szabvány SQL megoldás csak egy kis változtatással
fut le gépes környezetben! Például Oracle 11gR2 megoldás:
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;
-- Példák az Oracle SQL Language Reference 11.2.pdf dokumentációban:
>> Rekurzió with-utasítással: Recursive Subquery Factoring: Examples
>> 3.3.3. Hierarchikus lekérdezésekre Oracle megoldás (8.ea.pdf legvégén)
> SQL Lang.Ref: Hierarchical Queries, Hierarchical Query Examples
SELECT ... FROM... WHERE ... START WITH ... CONNECT BY PRIOR ...
>> 3.3.4. Nézzük meg hogyan tudjuk PL/SQL-ben megvalósítani:
> Rek1.feladat: Mely (x, y) várospárokra lehetséges egy vagy több átszállással
eljutni x városból y városba? -- Ehhez készítsünk egy Eljut(honnan, hova) táblát,
a sorait a járatok tábla alapján PL/SQL programmal töltsük fel (ciklust szervezni,
az insert 2.alakja: több sor felvitele alkérdéssel/járatok és eljut táblák alapján).
>>> (csak ha kell, egy kis segítség, további ötletek és a megoldás vázlata: itt)
> Rek2.feladat: Mely (x,y) város párokra hány átszállással és milyen költségekkel
lehetséges egy vagy több átszállással eljutni x városból y városba? -- Ehhez is
készítsünk Eljut2(honnan, hova, atszallas, koltseg) táblát, a sorait programmal.
>> Papíron megoldandó feladat: Fejezzük ki az SQL-1999-es szabvány SELECT
WITH RECURSIVE utasítással, hogy mely mely városokba (hova) tudunk eljutni
'DAL' (Dallas)-ból legfeljebb 3 átszállással és legfeljebb 5000 költségből.
>>> (csak ha kell, egy kis segítség WITH RECURSIVE papíros részéhez itt)