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 QueriesHierarchical 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.txt02_tipusok.pdf03_dml.pdf05_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 QueriesHierarchical 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)