III. Témakör: PL/SQL

>> 1. Alapok. Változóhasználat, vezérlési szerkezetek.
>> 2. Hivatkozási és összetett adattípusok, kurzor.
>> 3. Hiba-és kivételkezelés, tárolt alprogramok, csomagok.
>> 4. Triggerek alkalmazása, felépítése, lekérdezése.
_______________________________________________________
8. gyak. (IV.09) - PL/SQL  alapok.
Változóhasználat, vezérlési szerkezetek.   

On-line help: Oracle PL//SQL User's Guide and Ref.10g   HTML     PDF
Segédanyagok: PL/SQL Technology Center (Oracle 11g PL/SQL)
Nikovits Tibor (ELTE) PL/SQL összefoglaló: Plsql.htm

Oracle rendszer alaptáblái dept, emp, salgrade
-- lásd Oracle Database Samples Schemas (10.2)      HTML      PDF  
-- Hozzuk létre a dept, emp, salgrade táblákat az Oracle SCOTT felhasználó
    tábláiból (mivel hallgatói jogosultság nem szól a scott tábláira, előbb áthoztam,
    és a sila.dept, sila.emp illetve sila.salgrade táblákat már látják a hallgatók is),
    CREATE TABLE dept AS SELECT * FROM sila.dept; stb... VAGY 
-- 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 

8.gyakorlat órai mintapéldái PL/SQL alapokra  (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.

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.)

8.gyakorlat feladatai önálló feldolgozásra
h1a. - 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)
h1b. - Írjuk ki KING fizetését! (olvasás táblából változóba)
h1c. - Írjuk ki KING belépési dátumát! (különböző dátum formátumokkal)
h1d. - PL/SQL programmal írassuk ki az emp sorainak számát és az átlagfizetést.
h1e. - Kérjünk be egy (nem túl nagy) egész számot és állapítsuk meg, hogy prím-e.
h1f.  - Kérjünk be két egész számot és írjuk ki a legkisebb közös többszörösét.
_______________________________________________________
9. gyak. (IV.16)  - PL/SQL kurzor.
Hivatkozási és összetett adattípusok, kurzor.

On-line help: Oracle PL//SQL User's Guide and Ref.10g   HTML     PDF
Segédanyagok: PL/SQL Technology Center (Oracle 11g PL/SQL)
Nikovits Tibor (ELTE) PL/SQL összefoglaló: Plsql.htm

9.gyakorlat órai mintapéldái kurzorra (jelszóval nyitható)

2a_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.

2b_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.

2c_expl_kurzor.sql - Explicit kurzor
-    2b feladat másik megoldása

2d_current_of.sql - Explicit kurzor, FOR UPDATE, CURRENT OF 
-    2b, 2c feladat harmadik megoldása

2e_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)

9.gyakorlat feladatai önálló feldolgozásra
h2a. - Írjuk ki a dolgozók nevét és fizetését! (kurzor használata)
h2b. - Í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.
h2c. - Í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. 
h2d. - Í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.
h2e. - Írjuk ki a 3. 5. és 8. legnagyobb fizetésű dolgozó nevét, fizetését!
          (kurzor attribútumok %ROWCOUNT)
h2f. - 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)
_______________________________________________________
10. gyak. (IV.23)  - PL/SQL kivételkezelés, alprogramok.
Hiba-és kivételkezelés, tárolt alprogramok, csomagok.

On-line help: Oracle PL//SQL User's Guide and Ref.10g      HTML     PDF
                      Oracle PL/SQL Packages and Types Ref.10g  HTML     PDF
Segédanyagok: PL/SQL Technology Center (Oracle 11g PL/SQL)
Nikovits Tibor (ELTE) PL/SQL összefoglaló: Plsql.htm

10.gyakorlat órai mintapéldái alprogramokra, csomagokra (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.

10.gyakorlat feladatai önálló feldolgozásra
h3a. - Írjunk meg egy fv-t, ami az empno azonosító alapján visszaadja a nevet!
h3b. - Írjunk egy olyan függvényt, amely kurzor technikával egy EmpnoIn
          változóval megegyező dolgozó nevét betölti egy EnameOut változóba!
h3c. - 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!
h3d. - Í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.
_______________________________________________________
11. gyak. (IV.30)  - PL/SQL triggerek.
Triggerek alkalmazása, felépítése, lekérdezése.

On-line help: Oracle PL//SQL User's Guide and Ref.10g   HTML     PDF
Segédanyagok: PL/SQL Technology Center (Oracle 11g PL/SQL)
Nikovits Tibor (ELTE) PL/SQL összefoglaló: Plsql.htm

11.gyakorlat órai mintapéldái 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)

4c_after_delete.sql - triggerek  
- Adj meg egy olyan triggert, amely törli a hallgató összes adatát az indexk
   táblából miután töröltük a hallgatót a hallgato táblából!

11.gyakorlat feladatai  önálló feldolgozásra
h4a. - Fogalmazzuk át a 4a 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").
h4b. - 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)

h4c. - Í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)