Adatbázis_I

9.gyak. PL/SQL programozás II.

> Gépes feladatok: III. témakör: PL/SQL programozás
I. Az előző gyakorlatról ismétlés:
>> 3.1. PL/SQL alapok, változóhasználat, vezérlési szerkezetek
>> 3.2. SELECT INTO, hivatkozási és összetett adattípusok
>> 3.3. PL/SQL kurzor, explicit kurzor, for update záradék
   >> PL/SQL FELADATOK-1.

II. Mai gyakorlat témaköre:
>> 3.4. PL/SQL tárolt eljárások, függvények és csomagok
>> 3.5. PL/SQL hiba- és kivételkezelés
   >> 3.6. Triggerek (ez nem szerepel a számonkérésen!)
   >> PL/SQL FELADATOK-2.

III. Folyt. a köv.gyakorlaton:
>> 3.7. Az "Eljut feladat" PL/SQL-ben (ez vizsga-tananyag is!)

SEGÉDANYAGOK: ZH-n is használható SQL, PL/SQL Ref.
A ZH-n a tűzfalon keresztül is elérhető és használható a dokumentáció:
http://medusa.inf.elte.hu/ tanszéki honlapon alul Oracle linken található
Online documentation for Oracle 11g R2 bal oldali menüből kiválasztva
a harmadik sorban Database Administration és itt a harmadik dobozban:
SQL, PL/SQL, and PL/SQL Packages

SQL Language Reference
SQL Language Quick Reference
PL/SQL Language Reference
PL/SQL Packages and Types Reference

HTML PDF
HTML PDF
HTML PDF
HTML PDF

Itt érdemes átnézni a PL/SQL Lang.Ref. példáit, mint ahogy múlt héten néztük,
például Cursors részben "FOR UPDATE"-re kereséssel az "Example 6-43"-t.

További segédanyagok a PL/SQL-hez:
- Cinkóczki László, Oracle Junior, 2012. márc.8-i ea: PL/SQL
- Nikovits Tibor (ELTE) PL/SQL összefoglaló: NT_Plsql.htm
- Kósa Balázs (ELTE) PL/SQL összefoglaló: KB_Plsql.pdf
- Ullman/Chang (Standford) Using Oracle PL/SQL
- Ullman/Chang (Standford) Using Oracle PL/SQL
- Ullman/Chang (Standford) Constraints and Triggers
- PL/SQL Technology Center (Oracle 11g PL/SQL)

3.4. PL/SQL tárolt eljárások, függvények és csomagok

-- Példák PL/SQL függvények és eljárások használatára plsql_proc_fv.sql
-- Példák csomag használatára: plsql_csomag.sql és plsql_package.sql (N.T.)
-- PL/SQL Language Reference 11gR2 >> 8.fej. PL/SQL Subprograms
-- PL/SQL Language Reference 11gR2 >> 10.fej. PL/SQL Packages

Gyakorló feladatok az Oracle Példatárból -- Ehhez: cr_dept_emp.sql
Lásd Példatár "10.fejezet. Kivételkezelés, alprogramok, triggerek" feladatai

3.5. PL/SQL hiba- és kivételkezelés

-- Példák hiba- és kivételkezelésre: plsql_exception.sql (Nikovits Tibor)
-- PL/SQL Language Reference 11gR2 >> 11.fej. PL/SQL Error Hangling

Gyakorló feladatok az Oracle Példatárból -- Ehhez: cr_dept_emp.sql
Lásd Példatár "10.fejezet. Kivételkezelés, alprogramok, triggerek" feladatai

5a_kivetelkez.sql - Feladat kivételkezelésre
- Í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.

3.6. Triggerek (ez nem szerepel a zh-n a számonkérésen)

A triggerek témakör (Tankönyv 7.5. szakasza) az előadáson szerepelt, illetve
később, az MSc gyakorlaton is foglalkozunk még részletesebben a triggerekkel.
-- PL/SQL Language Reference 11gR2 >> 9.fej. Triggers

Gyakorló feladatok az Oracle Példatárból -- Ehhez: cr_dept_emp.sql
Lásd Példatár "10.fejezet. Kivételkezelés, alprogramok, triggerek" feladatai

6a_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!

6b_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)

PL/SQL FELADATOK-2

-- Táblák és az eddigi sql feladatok: table_dolgozo.txt
-- Ehhez a táblák létrehozása: create_dolgozo.txt

Az előző heti feladatok folytatása, lásd 8.gyak.#plsql_feladatok-1
(aki azt még nem fejezte be, előbb azokat írja meg és utána ezeket).

10.) 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 száma (a, e, i, o, u)!
   (módosítás kurzorral, beépített függvények)

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

12.) Írjunk meg egy csomagot (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átum-
formá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 vigyük fel egy tábla karakteres oszlopába a következő
adatokat: '1240', '12A3', 'Blabla', '2008-04-19', '12.5', '2008-apr-19'
(Használhatjuk az Oracle konverziós függvényeit plusz a hibakezelést.)

13.) Í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)
Például: p(13) -> szám, p('abc') -> szöveg, p('2008-02-02') -> dátum

Szorgalmi feladatok (ebben a félévben a triggerek nem lesznek a számonkérésben)
+1.) Az alábbi feladatokhoz hozzunk létre az emp, dept és salgrade tábláknak
megfelelő magyar nyelvű (de ékezet nélküli) oszlopnevekkel rendelkező
dolgozo, osztaly és fizufokozat táblákat különböző megszorításokkal együtt.
- Az eredeti táblák felhasználásával töltsük fel a táblákat a megszorításoknak
   eleget tevő adatsorokkal (megszorítások ellenőrzése: DBA_CONSTRAINTS)
- Az előző feladatban létrehozott saját DOLGOZO nevű táblához hozzunk
   létre egy TRIGGER_LOG1 nevű táblát, aminek a következő a szerkezete:
     ( idopont     DATE,
     muvelet     VARCHAR2(20),
     esemeny    VARCHAR2(80)
    )
- Hozzunk létre egy (after) triggert, ami akkor aktivizálódik ha a dolgozo tábla
fizetés oszlopát módosítják! A trigger a következő műveleteket végezze el:
- Ha a dolgozo új fizetése nagyobb lesz mint 4000 akkor erről tegyen egy
   bejegyzést a trigger_log táblába. Az esemény oszlopba írja be a régi és
   az új fizetést is.
- Az elnök (foglalkozas = 'PRESIDENT') fizetését ne engedje módosítani.
A módosítás után a fizetés maradjon a régi. Erről is tegyen egy bejegyzést
a trigger_log táblába. Az esemény oszlopba írja be, hogy a fizetés nem változott.

+2.) Hozzunk létre egy TRIGGER_LOG2 nevű táblát is, aminek a szerkezete
a következő:
     ( idopont     DATE,
     muvelet     VARCHAR2(20),
         uj_osszfiz NUMBER
    )
    - Hozzunk létre egy triggert, ami akkor aktivizálódik ha a dolgozo táblára
      valamilyen módosító műveletet (INSERT, DELETE, UPDATE)
   hajtanak végre! A trigger irja be a TRIGGER_LOG2 táblába
a módosítás időpontját, a műveletet és az új összfizetést.
    - Ha az új összfizetés nagyobb lenne mint 40000, akkor a trigger utasítsa
vissza a módosító műveletet, és hibaüzenetként küldje vissza, hogy
'Túl nagy összfizetés'. Ez esetben naplóznia sem kell.

Gyakorló feladatok: Azokat a feladatokat, amelyekre az órán nem maradt idő,
gyakorló feladatként otthon önállóan be lehet fejezni és emailben be lehet küldeni!

Vissza az AB1 gyakorlat oldalára

Vissza a Kezdőlapra