9.GYAKORLAT (ADATBÁZISOK)
   
   A 9.-10.-11.gyakorlatok témaköre: SQL/PSM (adatbázisban tárolt eljárások)
   a gyakorlatban: Oracle PL/SQL, ami az Oracle által az SQL kiterjesztéseként
   kifejlesztett procedurális programozási nyelv. Az Oracle PL/SQL gyakorlatokon
   feltételezzük, hogy rendelkezik az alábbi alapvető informatikai alapismeretekkel:
   - az előző 1.-8.gyakorlatok anyaga, az SQL szabvány és az Oracle SQL ismerete,
   - a különböző programozási nyelvek alapeszközeinek fogalmi szintű ismerete,  
   - és programozási gyakorlat valamely magasszintű programozási nyelven.
 
   Témakör: Oracle PL/SQL 1.rész: programegységek, vezérlési szerkezetek
   Az első PL/SQL gyakorlaton az ún. "Eljut"-feladatra, gráfok lekérdezésre írunk
   PL/SQL programot. Ehhez az alábbi PL/SQL alapokra lesz szükségünk: 
   - Változóhasználat, Vezérlési szerkezetek: feltételes utasítások, ciklusok,
   - DML utasítások (insert, delete, update, merge) használata programban,
   - egy sort visszaadó SELECT INTO használata programban. 
   Megj.: A több sort visszaadó SELECT-re és a kurzorok használata köv.órán, 
   a PL/SQL haladó témaköreit pedig majd két hét múlva tanuljuk, lépésenként.
 
 - Oracle PL/SQL doksi: Oracle Database 12.2 - PL/SQL Language Reference
   (Ez az egyetlen megengedett segédlet, amit a ZH írás alatt használhatunk) 
   >> a doksiból nézünk példákat: List of Examples (PL/SQL Lang.Ref.12.2) 
   >> 9.gyakorlaton az 1-5.fejezet lényegi részét és a példáit dolgozzuk fel,
         főként a 2.fejezet PL/SQL alapjait és a 4.fejezet vezérő utasításokat.
    
 - További segédanyagok a PL/SQL feldolgozásához:
   >> PL/SQL-I.rész (alapok az Eljut-feladathoz) 08.ab1ea.pdf (AB1ea/HajasCs.)
   >> PL/SQL-II.rész (kurzorok, alprogramok) 09.ab1ea.pdf (AB1ea/HajasCs.)
   >> Oracle PL/SQL Oracle Junior Program korábbi ea >>  
   >> További anyagok eduPLSQL mappában >> Tankönyvtár: plsql_progr.pdf
    
 
  1.rész: Oracle PL/SQL programegységek, vezérlési szerkezetek
  1.1. PROGRAMEGYSÉGEK: BLOKK, [TÁROLT] ALPROGRAMOK 
  - blokk, alprogram (eljárás, függvény)
  - tárolt eljárás, tárolt függvény
  - csomagok
-- -- -- -- -- --
-- PL/SQL blokk felépítése 
[<< label >> -- (címke, opcionális) ]
[DECLARE    -- (BLOKK-1 Deklarációs rész, opcionális)
  -- változók, alprogramok deklarálása ]

BEGIN      -- (BLOKK-2 Végrehajtható rész, kötelező)
  -- végrehajtható utasítások, például van NULL;

[EXCEPTION -- (BLOKK-3 Kivételkezelő rész, opcionális
  -- hiba- és kivételkezelés (lásd később 11.gyak) ]
END;
/

-- Blokk típusok 
a.) Névtelen blokk:
[DECLARE]
BEGIN   
  -- végrehajtható utasítások;
[EXCEPTION]
END;
/

   /* Első példa: PL/SQL blokkra, BEGIN .. END nem maradhat üresen, 
       ezért a végrehajtható utasítások között van egy olyan, hogy NULL,
       aminek a programok fejlesztése-tesztelése során hasznát vesszük.
   */

BEGIN   
  NULL;
END;
/
   
b.) Procedure/Eljárás specifikáció:
[CREATE OR REPLACE]
PROCEDURE név [(formális_paraméter[, formális paraméter] ...)]
IS
BEGIN   
  -- végrehajtható utasítások;
[EXCEPTION]
END;

/* A formális paraméter neve után
   a paraméterátadás módját lehet megadni:
   IN esetén érték szerinti,
   OUT esetén eredmény szerinti,
   IN OUT esetén érték-eredmény szerinti a paraméterátadás.
   Ha nem adjuk meg, akkor az IN alapértelmezett.
*/

c.) Function/Függvény specifikáció: 
[CREATE OR REPLACE]
FUNCTION név [(formális_paraméter[, formális paraméter] ...)]
RETURN adattípus
IS
BEGIN   
  -- végrehajtható utasítások;
   RETURN érték;
[EXCEPTION]
END;
/

/* Ahhoz, hogy egy függvényt SQL utasításban is használhassunk, 
    néhány megszorításnak még eleget kell tennie, mint például
    tárolt fv legyen, csak IN módú paraméterei legyenek, stb,
    lásd később 11.gyak.pl-proc-fv.txt
*/
 
 Folyt.köv. ALPROGRAMOK később 11.gyak: Oracle Példatár 10.fej.PSM.pdf
   PL/SQL eljárások és függvények: 8.fej. PL/SQL Alprogramok folyt.11.gyak:  
   >> 8.1. Alprogramokról áttekintés: Miért van szükség alprogramokra, előnyök
   -- Példák procedurákra/függvényekre: pl-proc-fv.txt további példák később...
   >> példa2-17 Változók hatásköre, láthatósága, élettartalma (2.fej. Alapok)
   >> példa4-1 Megnézzük az IF-THEN utasításnál az eljárást (4.fej. Utasítások)
   >> 8.5.fej. Példák példa8-1 PL/SQL eljárásra és példa8-2 PL/SQL függvényre
   >> 8.7.fej. Paraméterátadás; 8.9.fej.Túlterhelés példa8-26 túlterhelésre példa 
    
 
 1.2.  PROGRAMOK TESZTELÉSE, ELJÁRÁSHÍVÁS, FÜGGVÉNYHÍVÁS
 
 a.) ELJÁRÁSHÍVÁS:  
      I/O: DBMS_OUTPUT CSOMAG PUT_LINE ELJÁRÁS HASZNÁLATA

 - Input/Output PL/SQL-ben nincs I/O utasítás, általában a be- és kimenet (I/O)
   SQL utasításokkal történik, az adatokat az adatbázis tábláiban tároljuk, és
   ott módosítjuk vagy onnan lekérdezzük ezeket a táblákat.
 - Minden más PL/SQL I/O pl. képernyőre való kíiírás az Oracle Database által
   biztosított PL/SQL csomagokkal történik.
 - DBMS_OUTPUT csomag link: DBMS_OUTPUT.html (PL/SQL Packages)
   DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2) eljárásra példa:

-- Tesztelés:
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE ('Szia Világ!');
END;
/
 
 - PL/SQL feladat: Írjuk át a fenti programot: Szia ....! (nevet bekérni) ehhez:  
 - Ismétlés: Felhasználói változó, Példatár 4.fej.pdf (Interaktív környezet)
   >> innen felhasználói (helyettesítő változók használata SQL utasításokban
   volt 7.gyak. INSERT 2.feladatát, de most bekérjük az új sorhoz az értékeket,
   &dkod (numerikus), '&dnev' (karaktersorozat) típusú helyettesítő változókba.
 
 b.) FÜGGVÉNYHÍVÁS:
      DBMS_RANDOM CSOMAG VALUE FÜGGVÉNY HASZNÁLATA
 
 - DBMS_RANDOM csomag link: DBMS_RANDOM.html (PL/SQL Packages)
   DBMS_RANDOM.VALUE(AlsóHatár, FelsőHatár) függvény használata.
 
-- Tesztelés:
SELECT ROUND(DBMS_RANDOM.VALUE(0, 10000), -2) from dual; 
 
 - Ismétlés: Ez is volt SQL DDL feladatban, olyan nézettáblát készítsünk
   a dolgozo tábla (dkod, dnev, fizetes, jutalek) oszlopaihoz vegyünk fel
   egy új nyeremény nevű oszlopot, amit úgy töltsünk fel adatokkal, hogy
   0 és 10000 közötti véletlen egész számot százasokra kerekítve adjunk
   hozzá a jutalékhoz, ha a jutalék ismeretlen, akkor tekintsük 0 értéknek.
      
 
 1.3. DEKLARÁCIÓS RÉSZ-1: VÁLTOZÓK DEKLARÁLÁSA
        VÉGREHAJTHATÓ RÉSZ-1: ÉRTÉKADÁS, SELECT INTO 
   
   PL/SQL alapok, PL/SQL blokk >> 2.fej. PL/SQL alapok -> 2.6.rész
   [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, típusok, %TYPE, %ROWTYPE
        -- Az itt szereplő példák kipróbálásához hozzunk létre szinonimákat:
        create or replace synonym departments for hr.departments;
        create or replace synonym employees for hr.employees;
        -- Egészítsük ki: például legyen DATE típusú változó is, illetve
        nézzünk %TYPE és %ROWTYPE használatára további példákat!
        [ opcionális, részletek: 3.fej. PL/SQL adattípusok, 5.fej. Kollekciók ]
   
   >> példa-2-25 Értékadás változónak a SELECT INTO utasítással
        /* Itt a 2-25 példa futtatásához át kell tenni az end;-et a legvégére!
            Ez a példa abból a szempontból is tanulságos, hogy önállóságra
            biztassa a halllgatókat, hogy írják át a példát, emloyees -> dolgozo
            táblára salary -> fizetes, employee_id -> dkod vagy helyette dnev. 
            Kérem a hallgatókat, hogy ha az önálló gyakorlás során találnak
            az Oracle PL/SQL Lang.Ref. doksiban érdekes példákat, írják meg!
        */
             
   >> SELECT INTO-t akkor használjuk, ha a lekérdezés pontosan egy sort
        ad! Ha a lekérdezés nem ad vissza sort vagy több sorral tér vissza, 
        akkor kurzort kell használni, ez lesz a köv.10.gyakorlat tananyaga,   
        illetve a 11.gyakorlaton a hiba- és kivételkezelésnél is visszatérünk! 
   
  Kieg.Példatárból: KÜLSŐ ÉS BELSŐ VÁLTOZÓK HASZNÁLATA 
  >>  Változóhasználat, vezérlési szerkezetek: Oracle Példatár 8.fej.pdf
         innen egy példa:  1a_valtozok_pelda_Oracle_Peldatar_8fej.txt  
   
 
 1.4. VÉGREHAJTHATÓ RÉSZ-2: VEZÉRLŐ UTASÍTÁSOK
    
   VEZÉRLŐ UTASÍTÁSOK  >> 4.fej. PL/SQL vezérlő utasítások
   Feltételes utasítások:
   >> példa-4-4 if-then-elsif utasítás
        !!! Ebben a példában figyeljük meg a deklarációs részben az eljárást,
        írjuk át az itt szereplő eljárást CREATE OR REPLACE PROCEDURE-re!
   >> példa-4-6 egyszerű case utasítás, és itt írjuk át grade := 'B' -> '&B'
        írjuk át helyettesítési változóra: a felhasználó adja meg az értékét!
   Ciklusok:
   >> példa-4-10 alap LOOP ciklus utasítás EXIT WHEN kilépés a ciklusból
   >> példa-4-15 FOR ciklus utasítás
   >> példa-4-28 WHILE ciklus utasítás
   
   SQL UTASÍTÁSOK A PROGRAMBAN >> 6.fej.elején (folyt.később 10.gyak)
    >> példa6-1 DML utasítások a programban (Eljut feladathoz csak INSERT)
   FOLYT.KÖV. 10.gyak: DML utasítások, implicit kurzor, kurzorattribútumok (később)
 
           
   AZ  ELSŐ NAGYOBB PL/SQL FELADAT: Az "Eljut-feladat" PL/SQL-ben
   SQL WITH  RECURSIVE utasítást, az Eljut feladatot lásd a 08.gyakorlaton
   Adott Járatok tábla alapján programmal INSERT INTO Eljut (SFW);
    
   Rekurzió: SQL-1999 szabvány WITH RECURSIVE utasítása helyett itt programmal
   > Eljut feladat, gráf adatszerkezet lekérdezésére írjunk programot PL/SQL-ben!
   > Ullman-Widom tankönyv 10.2 szakasza 08.ab1ea.pdf (korábbi AB1ea/HajasCs.) 

   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). 
       -- Ezzel a scripttel jaratok_tabla.txt készítsük el a repülőgépjáratokról a táblát.
    
   > 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: Ne csak a várospárokat, hanem a teljes útvonalat is listázzuk ki.
    
     
   GYAKORLÓ PL/SQL FELADATSOR --1
 -- A  DBMS_OUTPUT.PUT_LINE() képernyőre való kiíráshoz állítsuk be:
SET SERVEROUTPUT ON
     
-- -- --  --    
 -- 1.)
/* Az első feladat képernyőre kiírás: Írjuk ki PL/SQL blokkból: 'Szia Világ!'  
*/
 
-- -- --  --    
-- 2.)  
/* Írjuk ki KING fizetését és jutalékát (olvasás táblából változókba), ha ismert,
    hogy pontosan egy KING nevű dolgozó szerepel a táblában (SELECT INTO).
    SELECT INTO csak akkor működik, ha a lekérdezés pontosan egy sort ad, 
    erre a feladatra visszatérünk a 11.gyakorlaton a hiba- és kivételkezelésnél.
*/
 
-- -- --  --    
-- 3.)
/* Írjunk meg egy függvényt, amelyik eldönti egy számról, hogy prím-e.
     igen/nem -> 1/0 értékeket adja vissza a függvény
*/
 
CREATE OR REPLACE FUNCTION prim(n integer) RETURN number IS
 
-- Tesztelés:
SELECT prim(101) from dual;
SELECT prim(108) from dual;
SELECT prim(26388279066623) from dual;
   
-- -- --  --    
-- 4.)
/* Írjunk meg egy procedúrát, amelyik kiírja az n-edik Fibonacchi számot
     fib_1 = 0, fib_2 = 1, fib_3 = 1, fib_4 = 2, . . .
     fib_i = a megelőző kettő összege
*/
 
CREATE OR REPLACE PROCEDURE fib(n integer) IS
 
-- Tesztelés:
SET SERVEROUTPUT ON
BEGIN
fib(10);
END;
/
-- vagy lehet így is  
set serveroutput on
execute fib(10);
 
-- -- --  --    
-- 5.)
/* Írjunk meg egy függvényt, amelyik visszaadja két szám legnagyobb közös osztóját
*/
 
CREATE OR REPLACE FUNCTION lnko(p1 integer, p2 integer) RETURN number IS
   
-- Tesztelés:
SELECT lnko(3570,7293) FROM dual;
 
-- -- --  --    
-- 6.)
/* Írjunk meg egy függvényt, amelyik visszaadja n faktoriálisát
*/

CREATE OR REPLACE FUNCTION faktor(n integer) RETURN integer IS
 
-- Tesztelés:
SELECT faktor(10) FROM dual;
 
-- -- --  --    
-- 7.)
/* Írjunk meg egy függvényt, amelyik megadja, hogy hányszor fordul elő 
     egy karakterláncban -> p1 egy másik részkarakterlánc -> p2
*/
 
CREATE OR REPLACE FUNCTION hanyszor(p1 VARCHAR2, p2 VARCHAR2) RETURN integer IS
 
-- Tesztelés:
SELECT hanyszor ('ab c ab ab de ab fg', 'ab') FROM dual;
 
-- -- --  --    
-- 8.)
/* Írjunk meg egy függvényt, amelyik visszaadja a paraméterként szereplő '+'-szal
   elválasztott számok összegét.
*/
 
CREATE OR REPLACE FUNCTION osszeg(p_char VARCHAR2) RETURN number IS
 
-- Tesztelés:
SELECT osszeg('1 + 4 + 13 + -1 + 0') FROM dual;
   
 
  Ţovábbi gyakorló PL/SQL feladatok 
  Fejezze be önállóan a fenti feladatokból a hiányzó feladatokat, és gyakoroljon
  oldja meg önállóan az Oracle Példatár Feladatok.pdf 8.fejezet 8.1-8.9.feladatait!
  [Megjegyzés: Az Oracle Példatár ezeket a táblákat használja: cr_dept_emp.txt
   az emp és dept táblák helyett használjuk itt is a dolgozo, osztaly tábláinkat!]