Adatbázisok szerveroldali programozása Labor, 2006.04.04.

Ez az oldal a korábbi SCH wikiről lett áthozva.

Ha úgy érzed, hogy bármilyen formázási vagy tartalmi probléma van vele, akkor, kérlek, javíts rajta egy rövid szerkesztéssel!

Ha nem tudod, hogyan indulj el, olvasd el a migrálási útmutatót.

<style> code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px; } li { line-height: 18px; } </style>

<< Labor, 2006.04.04. >>

PL/SQL Csomagok

1. Készíts el egy csomagot a Forma-1-es adatbázis különböző műveleteinek egységbe zárására! Készíts eljárásokat, amiknek segítségével új csapatot, új szezont és új pilótát lehet felvenni. Definiálj globális kivételeket a különböző, az eljárások során fellépő kivételekhez (Hibás bemeneti paraméter, már létező csapat/szezon/pilóta stb.)! Csomag deklaráció CREATE OR REPLACE PACKAGE forma1_insert is csapatletezik EXCEPTION; csapatnevhianyzik EXCEPTION; PROCEDURE ujcsapat(csnev VARCHAR2, csszekhely VARCHAR2); END forma1_insert; Csomag törzs CREATE OR REPLACE PACKAGE BODY forma1_insert IS PROCEDURE ujcsapat(csnev VARCHAR2, csszekhely VARCHAR2) IS id INT; db INT; BEGIN IF csnev IS NULL THEN RAISE csapatnevhianyzik; END IF; SELECT count(csapatid) INTO db FROM csapat WHERE nev=csnev OR szekhely=csszekhely; IF db>0 THEN RAISE csapatletezik; END IF; SELECT NVL(MAX(csapatid),0)+1 INTO id FROM csapat; INSERT INTO csapat VALUES(id, csnev, csszekhely); END; END forma1_insert;
2. Írj egy blokkot, ami kipróbálja a csomag egyes eljárásait! A blokkon belül kapd el az esetlegesen fellépő csomagbeli kivételeket, és értesítsd az eseményről a kliens oldalt (alkalmazás hiba dobása megfelelő hibaüzenettel). BEGIN forma1_insert.ujcsapat('1', 'x'); EXCEPTION WHEN forma1_insert.csapatletezik THEN DBMS_OUTPUT.put_line('Csapatnév már létezik'); WHEN forma1_insert.csapatnevhianyzik THEN DBMS_OUTPUT.put_line('Csapatnév nincs megadva'); END;
3. Egészítsd ki a forma1-es csomagot az alábbi funkciókkal. Egy eljárást kell elkészítened, aminek bemenő paramétereként egy csapat összetételét kell megadni (szezon, csapatnév, pilóták neve típussal) az eljáráson belül pedig az adott szezonba az adott csapathoz a megadott pilóták kerülnek felvételre. Amennyiben a csapat az adott szezonban még nem létezik, akkor az eljárás felvesz egy új bejegyzést a CSAPAT_SZEZON táblába, de egy hibaüzenettel jelzi, hogy hiányos adatokkal dolgozott. Hozz létre egy rekord típust, ami az eljárás bemenete lesz, és hozd létre az eljárást is! CREATE OR REPLACE PACKAGE forma1_insert IS csapatletezik EXCEPTION; csapatnevhianyzik EXCEPTION; csapatnemletezik EXCEPTION;   TYPE pilotalista IS TABLE OF csapat.nev%TYPE;   PROCEDURE ujcsapat(csnev VARCHAR2, csszekhely VARCHAR2); PROCEDURE csapatot_feltolt(szezon INT, csnev VARCHAR2, pilota1 VARCHAR2, pilota2 VARCHAR2, pilotat pilotalista); END forma1_insert; CREATE OR REPLACE PACKAGE BODY forma1_insert is FUNCTION get_csapatid(csapatnev VARCHAR2) RETURN INT IS result INT; BEGIN SELECT csapatid INTO result FROM csapat WHERE nev=csapatnev; RETURN result; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END;   -- megkeres vagy beszúr egy pilótát, és visszaadja az id-jét FUNCTION get_pilotaid(pnev VARCHAR2) RETURN INT IS result INT; BEGIN SELECT pilotaid INTO result FROM pilota WHERE nev=pnev; RETURN result; EXCEPTION WHEN NO_DATA_FOUND THEN SELECT NVL(MAX(pilotaid),0)+1 INTO result FROM pilota; INSERT INTO pilota(pilotaid, nev) VALUES(result, pnev); RETURN result; END;   PROCEDURE csapatot_feltolt(szezon INT, csnev VARCHAR2, pilota1 VARCHAR2, pilota2 VARCHAR2, pilotat pilotalista) IS csid INT; csszid INT; pid INT; i INT; BEGIN csid := get_csapatid(csnev); SELECT NVL(MAX(csapatszezonid),0)+1 INTO csszid FROM csapat_szezon; INSERT INTO csapat_szezon(csapatszezonid, csapatid, ev) VALUES(csid, csszid, szezon);   pid := get_pilotaid(pilota1); INSERT INTO csapat_pilota VALUES(csszid, pid, '1'); pid := get_pilotaid(pilota2); INSERT INTO csapat_pilota VALUES(csszid, pid, '2');   i := pilotat.FIRST; WHILE i IS NOT NULL LOOP pid := get_pilotaid(pilotat(i)); INSERT INTO csapat_pilota VALUES(csszid, pid, '2'); i := pilotat.NEXT(i); END LOOP;   IF csid IS NULL THEN RAISE csapatnemletezik; END IF; END; END forma1_insert;
4. Készíts egy blokkot, ami létrehoz egy csapat összeállítást, ás az előző feladatban létrehozott eljárást hívja meg. Amennyiben az eljárás hiányos adat hibával tér vissza, a csapat szezonális adatait is vidd be a blokkon belül!
5. Egészítsd ki a forma1-es csomagot egy eljárással, aminek két bemenő paramétere egy csapat neve és egy szezon, egy kimenő paramétere pedig egy REF CURSOR típusú változó, amibe az adott csapat adott évben futott versenyzőinek eredményét tartalmazza.
6. Készíts blokkot, ami meghívja az előző feladatban megírt eljárást, és a kimenetre (DBMS_OUTPUT.PUT_LINE) kiírja az eredméyt.
7. Készíts el egy csomagot a cukrász adatbázisbeli műveletek egységbe zárása céljából! A csomag tartalmazzon két eljárást. Az egyik eljárás tegye lehetővé különböző alapanyagok, félkészek vagy termékek felvételét az adatbázisba. Az eljárás paraméterei közt szerepeljen az adat típusa, és ezek alapján az eljárás szúrja be a megfelelő táblába az adatokat. Az azonosítót szekvenciával generáljad! A második eljárás tegye lehetővé félkész termékek receptjét összeállítani, magyarul a két bemeneti paramétere egy anyag és egy félkész neve legyen, valamint az anyag mennyisége. Próbáld ki a csomagot egy teszt blokk segítségével. Ellenőrizd, hogy jól működnek-e az eljárások.
8. Egészítsd ki a cukrász csomagot a következők szerint. A csomag tartalmazzon egy eljárást, aminek egy kimenő paramétere van, ami a még nem teljesített rendeléseket tartalmazza. (Feltételezzük, hogy van egy rendelés tábla, ha nincs, hozd azt létre!) A kimenet egy rekordokból álló tömb legyen, amely tartalmazza a rendelés azonosítóját, az áru nevét, típusát, mennyiségét, a rendelés dátumát valamint a forintban és euróban vett árát. Az euró árfolyamát a csomagon belül egy változóban tároljuk, és az első használatkor kerül meghatározásra.
   • Tervezd meg a csomag részeit (globális és lokális függvények, eljárások, globális és lokális típusok, inicializáló paraméter.) Alapértelmezésben az euró árfolyamát konstans értékre állítsuk be!
   • Valósítsd meg az egyes eljárásokat és függvényeket, és egy teszt blokk segítségével próbáld is ki a csomagot!
   • Készíts egy függvényt, ami véletlen módon határozza meg az euró árfolyamát, ahol az euró alap árfolyama legyen 240,15 Ft, és a véletlen ingadozás legyen 0-20 között.
9. Az előadáson ismertetett jogosultság ellenőrző rendszert valósítsd meg. A feladat a következő volt: A tanár-diák adatbázisban a diákok nyilvántartására nem célszerű létrehozni annyi Oracle felhasználót, amennyi diák van. Helyette a STUDENT táblában tároljuk el az egyes diákok azonosítóit és jelszavait, és mindenki egységesen ugyanazzal az Oracle felhasználóval léphet be. Létre kell ezért hozni egy mechanizmust, ami biztosítja, hogy egy diák csak akkor tudjon különböző műveleteket végrehajtani az adatbázisban, ha valóban belépett. Készíts egy csomagot, aminek változójában el tudjuk tárolni, hogy egy adott diák belépett-e vagy sem! Írd meg a belépő eljárást, és a belépést ellenőrző függvényt! Írj triggert, ami csak akkor enged adatmódosító utasítást lefuttatni a vizsga jelentkezés táblán, ha a diák belépett a rendszerbe.
10. Módosítsd az előző feladatban megírt csomagot úgy, hogy a tanárok jogosultságait is tudja kezelni. Egy vizsgát csak egy tanári jogosultsággal rendelkező felhasználó írhasson ki, míg egy vizsgára diák is vagy akár tanár is jelentkezhessen. A diák adatait pl. E-mail cím vagy jelszó, csak az adott diák tudja módosítani! Írd meg hozzá a szükséges triggereket vagy tárol eljárásokat!

-- Peti - 2006.04.04.