8.GYAKORLAT (ADATBÁZISOK)
Témakör: Oracle
gépes gyakorlat.
I. Nézettáblák:
create view, és Munkatáblák: select
előtt WITH záradék
II. Eljut feladat, gráfok bejárása rekurzívlekérdezések, WITH
RECURSIVE
I.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.
>> a.) Bonyolultabb lekérdezés
megoldása
nézettáblák
segítségével
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)
>> b.) Ugyanez WITH-záradékkal,
lásd SQL08_subquery2.pdf (16-18.o.
WITH)
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)
II.rész:
Hierarchikus és gráfos adatok
lekérdezése, WITH RECURSIVE
utasítás
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).
>> a.) 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?
>> b.) 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
>> c.) Hierarchikus
lekérdezésekre Oracle
megoldás: 2/7.lecke
> SQL Lang.Ref: Hierarchical
Queries, Hierarchical
Query Examples
SELECT ... FROM... WHERE ...
START WITH ... CONNECT BY PRIOR ...