III. Témakör: PL/SQL

9. gyak.
>> Segédanyagok a PL/SQL-hez (és az Oracle mintatáblái)
>> 3.1. PL/SQL alapok. Változóhasználat, vezérlési szerkezetek
 
10. gyak.
>> 3.2. Hivatkozási és összetett adattípusok, kurzor
 
11. gyak.
>> 3.3. Tárolt eljárások, függvények, hiba-és kivételkezelés
 
12. gyak.
>> 3.4. Triggerek alkalmazása, felépítése, lekérdezése
 
13. gyak.
>> 3.5. További feladatok rekurzív lekérdezésekre
    
ORACLE  PL/SQL:
Oracle® Database 10g (10.2)  Documentation Library
- Oracle PL/SQL User's Guide and Ref.10g      HTML   PDF
- Oracle PL/SQL Packages and Types Ref.10g HTML   PDF
 
További segédanyagok a PL/SQL-hez:
- Nikovits Tibor (ELTE) PL/SQL összefoglaló: Plsql.htm
- Előadás >> PLSQL_alapjai.pdf   >> Tk1_94_Plsql.pdf 
- Ullman/Chang (Standford) Using Oracle PL/SQL
- Ullman/Chang (Standford) Constraints and Triggers
- PL/SQL Technology Center (Oracle 11g PL/SQL)
 
Előkészítés: Oracle rendszer alaptáblái dept, emp, salgrade
- A dept és emp táblák közvetlen létrehozása: cr_dept_emp.sql 
- A dept, emp, salgrade, dummy, customer, ord, item, product, price táblák
   és a sales nézettábla létrehozása: cr_dept_stb_sales.sql
 
_______________________________________________________
9. gyak.  - PL/SQL alapok

>> Segédanyagok a PL/SQL-hez (és az Oracle mintatáblái)
>> 3.1. PL/SQL alapok. Változóhasználat, vezérlési szerkezetek
 
3.1. PL/SQL alapok. Változóhasználat, vezérlési szerkezetek

PL/SQL blokk deklarációs és (a kötelező) végrehajtható szegmense.
                         lásd később 3.3-ban a kivételkezelő szegmenst.

Segédanyagok: lásd fent

Előkészítés: Oracle rendszer alaptáblái dept, emp, salgrade
- A dept és emp táblák közvetlen létrehozása: cr_dept_emp.sql 
- A dept, emp, salgrade, dummy, customer, ord, item, product, price táblák
   és a sales nézettábla létrehozása: cr_dept_stb_sales.sql
 
PL/SQL feladatok változóhasználatra (jelszóval nyitható)

1a_valtozok.sql - Változóhasználat (I. az SQL*Plus felhasználói változói,
      II. az SQL*Plus környezeti változói, III. a PL/SQL (belső) változói).
      Az adatok képernyőre való kiíratása (Az Oracle beépített csomagjai,
      DBMS_OUTPUT csomag PUT_LINE eljárása). CONCAT fv. vagy || jel.
--   Írjunk egy PL/SQL blokkot tartalmazó SQL*Plus szkipt programot, amely
      a felhasználótól bekér egy egész számot! Ha ez a szám nagyobb 100-nál,
      akkor a PL/SQL blokkban, egyébként pedig a PL/SQL blokkot követő
      gazdanyelvi (SQL*Plus) környezetben írassa ki

1b_select_into.sql - A SELECT ... INTO utasítás a PL/SQL-ben
--   Határozzuk meg egy PL/SQL program segítségével a felhasználó által
      megadott telephelyen dolgozók béröszegét. A telephelynek a várost (loc)
      adjuk meg (mint például Boston, Chicaco, Dallas vagy New York).
--   Ennek a feladatnak további megoldásaira a következő szakaszban
      visszatérünk (lásd 2a_select_into.sql és 32_01. feladat megoldásait is). 

PL/SQL feladatok vezérlési szerkezetekre (jelszóval nyitható)

1c_if_then.sql - Feltételes utasítás (és a MOD)
--   Kérjünk be két egész számot és döntsük el, hogy az összegük páros-e vagy
      páratlan. Az eredményt a PL/SQL blokkban írjuk ki futás közben.  

1d_loop_ciklus.sql - LOOP ciklus 
--  Állítsuk elő a felhasználó által megadott darabszámig a Fibonacci-sorozat
     elemeit. (A megoldáshoz LOOP ciklust használjunk.)

1e_while_ciklus.sql - WHILE ciklus 
--  Kérjünk be két (nem túl nagy) egész számot és írjuk ki a legnagyobb közös
     osztókat (az Euklideszi algoritmussal és a WHILE ciklust használjunk).

1f_for_ciklus.sql - FOR ciklus 
--  Írjunk SQL*Plus szkript programot, amely egy PL/SQL blokkban kiszámítja
     a felhasználó által megadott A számtól a felhasználó által megadott B számig
     a páratlan számok négyzetösszegét, és ezt az SQL*Plus környezetben írjuk ki.
     (A megoldáshoz FOR ciklust használjunk.)

Feladatok önálló feldolgozásra
31_1. - Hello World program. Kérjük, hogy adja meg a nevét, és íratassuk ki
             a képernyőre, hogy 'Szia <név>!' (képernyőre való kiíratás, lásd 1b_)

31_2. - Írjuk ki KING fizetését! (olvasás táblából változóba)

31_3. - Írjuk ki KING belépési dátumát! (különböző dátum formátumokkal)

31_4. - PL/SQL programmal írassuk ki az emp sorainak számát és az átlagfizetést.

31_5. - Karácsonyi nyereményjátékot tartanak az emp tábla vállalatnál. Minden
             dolgozó 0 és 100 USD közötti jutalmat nyerhet, amit mindjárt hozzá is
             adnak a jutalékához (comm). Készítse el a nyereményjáték programját!  
             -- Segítség: Az Oracle beépített csomagjai jözül a DBMS_RANDOM
             csomag pszeudovéletlen számokat állít elő. A véletlenszám-generátor
             függvény alakja: DBMS_RANDOM.VALUE(AlsóHatár, FelsőHatár)
             ahol az AlsóHatár és FelsőHatár egyaránt Binary_Integer típusú számok.              

______________________________________________________
10. gyak.  - PL/SQL kurzor
 
3.2. Hivatkozási és összetett adattípusok, kurzor

Segédanyagok: lásd fent

PL/SQL feladatok hivatkozási adattípusra (jelszóval nyitható)

2a_select_into.sql - A SELECT ... INTO utasítás a PL/SQL-ben
--   Határozzuk meg egy PL/SQL program segítségével a felhasználó által
      megadott telephelyen dolgozók béröszegét. A telephelynek a várost (loc)
      adjuk meg (mint például Boston, Chicaco, Dallas vagy New York).
--   Lásd a korábbi 1b_select_into feladat I.megoldását is, csak most
      hivatkozási típus deklarálásával oldjuk meg a feladatot! II.megoldás:
      Közvetlen összegzéssel. III.mo: Tagonkénti összesítéssel (nézettáblával).
--   E feladat rejtett kurzorral való megoldására visszatérünk (lásd 32_01.f.)        

PL/SQL feladatok összetett adattípusra (jelszóval nyitható)

2b_gyujtotabla.sql - Összetett típus, rekord, gyűjtőtábla
-    Írjunk PL/SQL programot, amely meghatározza a 7698 azonosítójú
     dolgozó nevét gyűjtőtábla használatával.

PL/SQL feladatok kurzorra (jelszóval nyitható)

2c_kurzor.sql - Kurzor, kurzorattribútumok (%FOUND, %NOTFOUND, stb)
-    Növeljük meg a hivatalnokok (CLERK) fizetését a saját fizetésük 20%-ával!
      (hibas_mo.sql -> tanulságok leszűrése!)

2d_impl_kurzor.sql - Implicit kurzor, kurzorhasználat FOR ciklusban
-    Hozzuk létre a dolgozo táblát az emp táblából, és bővítsük azt egy sorszám
     oszloppal. Ezt töltsük fel 1-től kiindulva egyesével növekvő értékkel minden
     dolgozó esetén a dolgozók nevének ábécé sorrendje szerint.

2e_expl_kurzor.sql - Explicit kurzor
-    2d feladat másik megoldása

2f_current_of.sql - Explicit kurzor, FOR UPDATE, CURRENT OF 
-    2d, 2e feladat harmadik megoldása
 
Feladatok önálló feldolgozásra
32_1. - Az 2a_select_into feladatot oldjuk meg másképp is (rejtett kurzorral).

32_2. - Írjuk ki a dolgozók nevét és fizetését mind a háromféle kurzorhasználattal!

32_3. - Írjunk PL/SQLprogramot, amely (eldob és) létrehoz az emp táblából 
              egy dolgozo táblát, és megnöveli a felhasználó által megadott foglalkozású
              dolgozók fizetését 1000 USD-ral.

32_4. - Írjunk PL/SQLprogramot, amely (eldob és) létrehoz az emp táblából egy
              dolgozo táblát, és megnöveli a felhasználó által megadott százalékértékkel
              minden, az átlagfizetésnél alacsonyabb fizetéssel rendelkező dolgozók fizetését. 

32_5. - Írjunk PL/SQLprogramot, amely (eldob és) létrehoz az emp táblából egy
            dolgozo táblát, és ebben foglalkozásonként megnöveli a legkisebb fizetésű
            dolgozók bérét a foglalkozási csoportjukban legnagyobb fizetés és az ugyanitt
            számított átlagfizetés különbségének 20%-ával.

32_6. - Írjuk ki a 3. 5. és 8. legnagyobb fizetésű dolgozó nevét, fizetését!
            (kurzor attribútumok %ROWCOUNT)

32_7. - Tegyük be a dolgozók nevét egy plsql tömbbe, és írjuk ki az utolsó előtti sort!
             (összetett adattípusok RECORD/rekord TABLE OF/gyűjtőtábla v. plslq tömb)

32_8. - Írjuk ki azon dolgozók nevét és fizetését, akik fizetése nagyobb mint a felhasználó
             által megadott szám.

32_9. - Írjuk ki azon dolgozók nevét, fizetését és osztálykódját, akik a felhasználó által
             megadott osztályon dolgoznak. A felhasználó által megadott szám legyen 1, 2, 3
             a 10, 20, 30-as osztály esetén. (paraméteres kurzor)
 
______________________________________________________
11. gyak.   - PL/SQL kivételkezelés, alprogramok
 
3.3. Tárolt eljárások és függvények, hiba- és kivételkezelés

PL/SQL blokk deklarációs, végrehajtható és kivételkezelő szegmensei.

Segédanyagok: lásd fent

PL/SQL feladatok eljárásokra és függvényekre (jelszóval nyitható)

3a_alprogr.sql - tárolt alprogramok
-    Egyszerű számnövelő példa a PL/SQL függvényekre és eljárásokra.

3b_csomag.sql - csomag
-    Egyszerű példa egy getemp eljárást és avg_salary fv-t tartalmazó csomagra.

3c_hibakez.sql - hiba és kivételkezelés
-    Példa hiba és kivételkezelésre
     Módosítsuk úgy a programot, hogy másik ágra tereljük a hibakezelést! 

3d_kivetelkez.sql - kivételkezelés
-   Írjunk egy olyan eljárást, amely kivételkezelést is tartalmaz és a jutalmat az
    emp táblából létrehozott dolgozo tábla jutalék (comm) értékéhez adja hozzá!
    A jutalom a dolgozó fizetésének 10%-a, feltéve, ha a fizetés 3000 dollár
    alatt van, egyébként csak egy "Boldog Karácsonyt!" üdvözletet kap.


Feladatok önálló feldolgozásra
33_1. - Írjunk meg egy fv-t, ami az empno azonosító alapján visszaadja a nevet!

33_2. - Írjunk egy olyan procedúrát, amely kurzor technikával egy EmpnoIn
            változóval megegyező dolgozó nevét betölti egy EnameOut változóba!

33_3. - Módosítsuk a fizetéseket egy kurzorral végighaladva rajtuk!
            Adjunk hozzá mindenki fizetéséhez n*10 ezret, ahol n a nevében levő
            magánhangzók (vagyis a, e, i, o, u) száma!

33_4. - Írjunk egy csomagot, amelyben eljárásként, függvényként, tárolt eljárásként
            és tárolt függvényként is szerepel az, hogy adott n-re visszaadja n faktoriálist!
            Ha a faktoriális túl nagy szám lenne, akkor a függvény adjon vissza -1-et,
            hiba és kivételkezeléssel megoldva, ekkor egy üzenetet is írjunk ki a hibáról.

33_5. - Írjunk meg egy package-beli függvényt, ami visszaadja a paraméterében levő
             magánhangzók számát. Módosítsuk a fizetéseket ennek a függvénynek a
             segítségével. (UPDATE utasítással, és nem plsql-ben)   (package)

33_6. - Írjunk meg egy package-et, amelyben van egy
                                    TIPUS(input_szoveg IN VARCHAR2)
             nevű függvény, amelyik a paraméterül kapott karakterláncról eldönti, hogy az
             numerikus érték, dátum, vagy egyik sem, azaz csak szövegként értelmezhető.
             A dátumok esetén elég ha a függvény egyetlen dátumformátumot (pl. az alap-
             értelmezettet) felismer. A függvény által visszaadott érték legyen mondjuk:
             1, 2, illetve 3, a parameter típusának megfelelően.
           - A függvényt próbáljuk is ki procedurális utasításokban is és SQL utasításokban is.
              pl.   valtozo := <package_nev>.tipus(parameter);
                     SELECT oszlop1, <package_nev>.tipus(oszlop1) FROM tabla_nev
                     formában.
           - Ehhez tegyük be egy tábla karakteres oszlopába a következő adatokat:
              '1240', '12A3', 'Blabla', '2008-04-19', '12.5', '2008-apr-19'
           - TIPP: Használhatjuk az Oracle konverziós függvényeit plusz a hibakezelést.

33_7. - Írjunk meg egy package-beli procedúrát vagy függvényt, ami különböző típusú
             input paraméterek esetén lényegesen eltérően működik. (túlterhelés)
          - Pl. p(13) -> szám, p('abc') -> szöveg, p('2008-02-02') -> dátum

33_8. - ROWID adattípus formátuma és jelentése
            18 karakteren íródik ki, a következő formában:
            OOOOOOFFFBBBBBBRRR, ahol
            OOOOOO -  az objektum azonosítója
            FFF    -  fájl azonosítója (táblatéren belüli relatív sorszám)
            BBBBBB -  blokk azonosító (a fájlon belüli sorszám)
            RRR    -  sor azonosító (a blokkon belüli sorszám)
            
         - A ROWID megjelenítéskor 64-es alapú kódolásban jelenik meg.
            Az egyes számoknak (0-63) a következő karakterek felelnek meg:
            A-Z -> (0-25), a-z -> (26-51), 0-9 -> (52-61), '+' -> (62), '/' -> (63)
            Pl. 'AAAAAB' -> 000001

         - Írjunk egy olyan  plsql függvényt, amely a ROWID részeit értelmezi,
            vagyis amely a fenti 64-es kódolásnak megfelelő számot adja vissza.
            A függvény paramétere egy karakterlánc, eredménye pedig a kódolt
            numerikus érték legyen. (Elég ha a függvény max 6 hosszú, helyesen
            kódolt karakterláncokra működik, hosszabb karakterláncra vagy
            rosszul kódolt paraméterre adjon vissza -1-et.)

         - Ennek a fv-nek a segítségével adjuk meg egy táblabeli sor pontos
            fizikai elhelyezkedését. (Melyik fájl, melyik blokk, melyik sora)
            Példaul az  emp tábla azon sorára, ahol a dolgozó neve 'KING'.
    
         - A kurzusnak az is az egyik célkitűzése, hogy az Oracle Database
           Online Documentation könyvtárban otthonosan mozogjunk, például:
           PL/SQL Packages and Types Reference  HTML     PDF 
            itt lásd "90 DBMS_ROWID" fejezet eljárásait és függvényeit.
         - Azonban gyakorlásként írjuk meg  fenti plsql függvényt és alkalmazzuk! 

         - A megírt függvényünk működése ellenőrizhető az alábbi függvényekkel:      
         SELECT rowid, substr(rowid, 1, 6),
            DBMS_ROWID.rowid_object(ROWID),
             substr(rowid, 7, 3),
            DBMS_ROWID.rowid_relative_fno(ROWID),
            substr(rowid, 10, 6),
            DBMS_ROWID.rowid_block_number(ROWID),
            substr(rowid, 16, 3),
            DBMS_ROWID.rowid_row_number(ROWID)
     FROM emp
     WHERE ename='KING'
 
_______________________________________________________
12. gyak.   - Triggerek
    
3.4. Triggerek alkalmazása, felépítése, lekérdezése

Segédanyagok: lásd fent

PL/SQL feladatok triggerekre (jelszóval nyitható)

4a_trigger.sql - triggerek
-   Hozzunk létre BEFORE triggert, amely megakadályozza a munkaidőn
     kívüli adatmanipulációkat az emp táblán! Írassuk ki, milyen műveleteket
     kíséreltek meg végrehajtani munkaidőn kívül!

4b_triggerek.sql - triggerek
-  Írjunk triggert (és ellenőrizzük is a működését), amely megakadályozza
    az elnökre (president) vonatkozó törlő, beszúró és adatmódosító
    DML utasítások működését! (a tesztelő script programban a hivatkozási
    megszorítás felfüggesztése illetve a végén az újbóli engedélyeztetése)

Feladatok önálló feldolgozásra
34_1. - Fogalmazzuk át a 4a_trigger példát a következőképpen:
            Hozzunk létre olyan triggereket, melyek segítségével megakadályozzuk
            (letiltjuk) a munkaidőn kívüli beavatkozásokat az emp táblán, tavábbá
            egy feljegyzes nevű adattáblába feljegyezzük a munkaidőn kívül kísérelt,
            és a munkaidőben sikeresen elvégzett (engedélyezett) beavatkozások
            időpontját, jellegét ("Törlés", "Beszúrás", "Módosítás") és minősítését
            ("Letiltott", "Engedélyezett").

34_2. - Adjunk meg egy olyan triggert, amely törli a részlegen dolgozó
            összes dolgozó adatát az emp táblából miután töröltük a részleget
            a dept táblából! (a tesztelés előtt SAVEPOINT A; tesztelés után
            pedig állítsuk vissza az eredeti táblákat ROLLBACK A)

34_3. - Írjunk triggert (ellenőrizzük is a működését), amely megakadályozza,
            hogy a felhasználó olyan dolgozót vigyen fel vagy töröljön ki vagy
            fizetésemeléssel módosítson, akinek a havi bére eléri vagy meghaladja
            a részlegének átlagfizetését! (kivételkezelés is)
 
_______________________________________________________
13. gyak.   - Rekurzió
 
3.5. További feladatok rekurzív lekérdezésekre

Segédanyagok: lásd fent

Feladatok önálló feldolgozásra
   
35_1. - Tranzitív lezárt kifejezése PL/SQL programmal
            Az alábbi feladat a tankönyv (Ullman-Widom kék könyv) 10.2 szakaszára épül.
            Jaratok(legitarsasag, honnan, hova, koltseg, indulas, erkezes)
            táblában repülőjáratok adatait tároljuk.
          - Készítsetek ebből saját példányt: Jaratok_tabla.sql és az alapján dolgozzatok!
          - Mely (x,y) város párokra lehetséges egy vagy több átszállással
             eljutni x városból y városba?
                           1.    Eljut(x,y) <-  Járatok(l, x, y, k, i, e)
                           2.    Eljut(x,y) <- Eljut(x,z) AND Eljut(z,y)
          - Amennyiben azt szeretnénk megtudni, hogy mely városokba lehet eljutni Dallasból
            ezt a következő hierarchikus lekérdezéssel kapjuk meg ('DAL'='Dallas'):
                           SELECT DISTINCT hova
                           FROM jaratok
                           START WITH honnan='DAL'
                           CONNECT BY PRIOR hova=honnan;
          - Átszállásokkal mely városokba lehet eljutni San Franciscoból ('SF'='San Francisco')
                           SELECT LPAD(' ', 4*level) ||honnan, hova, level-1 Atszallasok
                           FROM jaratok
                           START WITH honnan = 'SF'
                           CONNECT BY PRIOR hova = honnan;
          - Ez a lekérdezés addig működik, amíg nincs a gráfban kör. Szúrjatok be a saját
             táblátokba még egy sort, ami után már irányított kör is lesz a táblában:
                           INSERT INTO jaratok VALUES('LH', 'CHI', 'DEN', 2000, 1900, 2100);
         - Mely (x, y) várospárokra lehetséges egy vagy több átszállással eljutni x városból y városba?

35_2. - Hozzatok létre egy alábbi szerkezetű ELJUT táblát:
                           CREATE TABLE eljut
                                     (honnan VARCHAR2(15),
                                      hova VARCHAR2(15),
                                      koltseg NUMBER);
           - Írjunk egy olyan PL/SQL programot, ami feltölti az ELJUT táblát a megfelelő város
             párokkal, ahol az első városból el lehet jutni a másodikba.
             (Önmagával ne szerepeljen egy város sem.)

35_3. - Most úgy töltsük fel az ELJUT táblát, hogy a költség oszlop is legyen kitöltve.
            A és B város között a lehető legolcsóbb út költségét írja be a program.

35_4. - 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, vagyis ha több
             járattal  utazunk és átszállásra van szükségünk, akkor az érkező járatnak legalább egy
            órával a rá következő indulás előtt meg kell érkeznie.
            (Feltehetjük, hogy nincs egy napnál hosszabb utazás)
   

Lap tetejére        Gyak oldalára        Vissza a Kezdőlapra