9.GYAKORLAT (ADATBÁZISOK)
   
   Témakör: Oracle PL/SQL témakör első gyakorlatán az ún. "Eljut feladatra" 
   gráfos adatok lekérdezését PL/SQL programmal is megvalósítjuk. Ehhez:
   Először a PL/SQL alapokat tekinjük át (változók, vezérlési szerk., ciklusok,
   DML utasítások, egy sort visszaadó SELECT INTO használata programban. 
   Viszont a több sort visszaadó SELECT-re a kurzorok használatát köv.órán, 
   a PL/SQL haladó témaköreit pedig majd két hét múlva tanuljuk, lépésenként.
   
   A MAI ÓRÁN ELSŐSORBAN A REKURZÍV "ELJUT-FELADATTAL" FOGLALKOZUNK!   
   
   > Eljut feladat: REL5.pdf (9.ea: Datalog), SQL5.pdf (10.ea végén: Rekurzió)
   > Erről rövid összefoglaló a gyakorlatra: Datalog.pdf
   
   > Emlékeztető az Oracle PL/SQL-ről AB1EA : SQL4.pdf (8.előadás)
   > Oracle PL/SQL: Oracle Junior EA >> Oracle PL/SQL.pdf 
   > 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.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.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.rész:  Az "Eljut feladat" megvalósítása Oracle SQL-ben és PL/SQL-ben
    -- REL5.pdf (9.előadás: Datalog), SQL5.pdf (10.előadás végén: Rekurzió) 
    -- 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). 
     
      3A.rész:  Az "Eljut feladat" megvalósítása Datalogban és Oracle SQL-ben
    -- Datalogról és az Oracle SQL-ben a with-ről röviden a gyakorlatra: Datalog.pdf

   >> 3.3.1. 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.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.
 
    Ha megfigyeljük a járatok táblában, hogy honnan hova tudunk eljutni és
    ezt felrajzoljuk az első hét soránál nincs kör, ha az utolsó sort elhagyjuk,
    azzal egy olyan jaratok_fa táblát készíthetünk, amelyben nincs kör:
    --- Előkészítés jaratok helyett jaratok_fa egyszerűbb adatokra:
   
   drop table jaratok_fa;
   create table jaratok_fa as
   (select * from jaratok
    fetch first 7 rows only);  --- FIGYELEM! FETCH FIRST Oracle 12c ÚJDONSÁG !
   select * from jaratok_fa;
 
   --- Erre a jaratok_fa táblára az alábbi kódrészlet hibátlanul lefut:
   --- with utasítás az Oracle-ben hierarchikus fa-struktúrájú táblákra:

   WITH eljut(honnan, hova) AS
       (SELECT honnan, hova FROM jaratok_fa jaratok
         UNION ALL
      SELECT jaratok.honnan, eljut.hova
      FROM jaratok_fa jaratok, eljut
      WHERE jaratok.hova = eljut.honnan AND jaratok.honnan <> eljut.hova
      )
    SELECT DISTINCT honnan, hova FROM eljut ORDER BY honnan;
   
   --- Ez a fenti utasítás rendben lefut az Oracle-ben, a 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
   
  --- Viszont ha a fenti WITH utasítást az eredeti jaratok táblára alkalmazzuk,
      ahol a városok közötti utak kört is tartalmazta (nem fa-struktúrájú gráf)
   
   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 AND jaratok.honnan <> eljut.hova
     )
   SELECT DISTINCT honnan, hova FROM eljut ORDER BY honnan;
   --- ami hibát ad: "ORA-32044: A rekurzív WITH lekérdezésben ciklus van."
       32044. 00000 -  "cycle detected while executing recursive WITH query"
       és csak az alábbi kiegészítéssel működik, 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 ...
       
  -- -- --  --
      3B.rész:  Az "Eljut feladat" megvalósítása Oracle PL/SQL-ben
  >> 3.3.4. Nézzük meg hogyan tudjuk a rekurziót 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)
      
   > Rek3.feladat: Tegyük fel, hogy nemcsak az érdekel, hogy el tudunk-e jutni az
      egyik városból a másikba, hanem az is, hogy utazásunk során az átszállások is
      ésszerűek legyenek, ez azt jelenti, hogy ha több járattal utazunk, akkor nézni
      kell átszálláskor az érkező járatnak legalább egy órával a rákövetkező indulás
      előtt meg kell érkeznie. (Tegyük fel, hogy nincs egy napnál hosszabb utazás!)
     
   > Rek4.feladat: A fenti feladatokat oldjuk meg PL/SQL-ben úgy is, hogy ne csak
      a várospárokat, hanem a teljes útvonalat is listázzuk ki.
     
 -- -- --
 Ţovábbi gyakorló feladatok az Oracle Példatárból
   > Önálló gyakorlás: Oracle Példatár Feladatok.pdf 8.fejezet 8.1-8.9.feladatok
   > és célszerű készülni a következő gyakorlatra is átnézni: feladat9_plsql.txt 
     

---

>>> Vissza a főoldalra >>> Hajas Csilla © ELTE IK Információs Rendszerek Tanszék