Adatbázis_I

11.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. Tárolt eljárások, függvények
>> 3.5. PL/SQL csomagok, kivételkezelés
   >> 3.6. Triggerek
   >> PL/SQL FELADATOK-2.

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

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:
- 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. Tárolt eljárások, függvények

-- Példák PL/SQL függvények és eljárások használatára plsql_proc_fv.sql
-- PL/SQL Language Reference 11gR2 >> 8.fej. PL/SQL Subprograms

3.5. PL/SQL csomagok, kivételkezelés

-- Példák hiba és kivételkezelésre: plsql_exception.sql
-- Példák csomag használatára: plsql_csomag.sql és plsql_package.sql
-- PL/SQL Language Reference 11gR2 >> 10.fej. PL/SQL Packages
-- PL/SQL Language Reference 11gR2 >> 11.fej. PL/SQL Error Hangling

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

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

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

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

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

További gyakorló feladatok találhatóak az Oracle Példatárban
-- Lásd Feladatok.pdf (10.fejezet feladatai)
-- Ehhez: a táblák létrehozása cr_dept_emp.sql

Vissza az AB1 gyakorlat oldalára

Vissza a Kezdőlapra