9.GYAKORLAT (ADATBÁZISOK)
      
   A 9.-10.-11.gyakorlatok témaköre az SQL/PSM (adatbázisban tárolt eljárások)
   a gyakorlatban: az 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 csak átismételjük a programozási alapismereteket.
   
 - 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:
   >> SQL/PSM, PL/SQL: 08.adatb.ea.pdf (korábbi BSc-2008 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, pl. tárolt fv legyen,
    csak IN módú paraméterei legyenek, stb, lásd később pl-proc-fv.txt
*/
    
 
 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. BLOKK-1 DEKLARÁCIÓS RÉSZ-1: VÁLTOZÓK DEKLARÁLÁSA
       BLOKK-2 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! 
   
 
 1.4. BLOKK-1 DEKLARÁCIÓS RÉSZ-2: ALPROGRAMOK DEKLARÁLÁSA
       BLOKK-2 VÉGREHAJTHATÓ RÉSZ-2: VEZÉRLŐ UTASÍTÁSOK
    
   Feltételes utasítások, ciklusok >> 4.fej. PL/SQL vezérlő 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!
   >> 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
   
   
 1.5. 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  
   
   Később még lesz a 11.gyakorlaton az Oracle Példatár 10.fej.PSM.pdf
   de már most is kezdjünk el tárolt eljárást, illetve tárolt függvényt írni!
   PL/SQL eljárások és függvények: 8.fej. PL/SQL Alprogramok
   >> 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
   
   
   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;
   
 
GYAKORLÁS:
  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!]