SELECT INTO
9. fejezet - Tárolt alprogramok
A 6.2. alfejezetben megismerkedtünk az alprogramokkal. Az ott tárgyalt alprogramok más programegységek lokális alprogramjai voltak. Lehetőség van azonban arra, hogy az alprogramokat adatbázis-objektumokként kezeljük (ugyanúgy, mint például a táblákat). Ekkor az SQL szokásos létrehozó, módosító és törlő DDL utasításait használhatjuk. A tárolt alprogramok lefordított formában, ún. p-kódban tárolódnak. A PL/SQL fordító a kódból előállít egy belső kódot, amit aztán az adatszótárban tárol. A p-kód a PL/SQL virtuális gép (PVM), az interpreter utasításait tartalmazza. Amikor egy tárolt alprogram kódját végre kell hajtani, a PVM ezt a p-kódot fogja értelmezni úgy, hogy megfelelő alacsony szintű API hívásokat hajt végre.
Tipp
Megjegyzés: Az Oracle 10g az m-kód terminológiát használja, de mi kifejezőbbnek tartjuk a korábbi verziókban használt p-kód elnevezést, így ezt tartottuk meg.
Az Oracle10g lehetőséget ad arra, hogy a PL/SQL fordító natív operációs rendszer kódra fordítson. Ehhez a rendszerben telepíteni kell egy C fordítót és ezután a PL/SQL fordító C kódot generál, amelyből a C fordító által előállított kódot az Oracle háttérfolyamata futtatja majd. Részletesen a 16.5. alfejezetben tárgyaljuk ezt a lehetőséget.
A tárolt alprogramokról információkat elsősorban a következő adatszótárnézetekből nyerhetünk:
USER_OBJECTS, USER_SOURCE (a forráskódot tartalmazza), USER_ERRORS (a fordítási hibákat tartalmazza).
SQL*Plusban a fordítási hibákat a SHOW ERRORS paranccsal is megnézhetjük, illetve meggyőződhetünk arról, hogy nem volt hiba a tárolt alprogram fordítása során.
Tárolt eljárás létrehozása a következő SQL parancs segítségével történik:
CREATE [OR REPLACE] eljárásfej [AUTHID {DEFINER|CURRENT_USER}]
eljárás_törzs
Az eljárás fejére és törzsére a 6.2. alfejezetben leírtak vonatkoznak.
A OR REPLACE újragenerálja az eljárást, ha az már létezik. Lényegében az eljárás definíciójának megváltoztatására szolgál, így az eljáráshoz előzőleg már megadott objektumjogosultságokat nem kell törölni, újra létrehozni és újra adományozni.
Az AUTHID segítségével megadható, hogy az eljárás létrehozójának (DEFINER – ez az alapértelmezés), vagy aktuális hívójának (CURRENT_USER) a jogosultságai érvényesek-e a hívásnál.
1. példa (Tekintsük a következő két eljárást, ahol a VISSZAHOZ eljárás a létrehozó jogosultságaival, a VISSZAHOZ_CURRENT_USER eljárás az aktuális hívó jogosultságaival van definiálva. A két eljárás működése minden másban megegyezik.)
CREATE OR REPLACE PROCEDURE visszahoz(
p_Konyv konyv.id%TYPE,
Tárolt alprogramok
p_Kolcsonzo ugyfel.id%TYPE )
AS
/* Ez az eljárás adminisztrálja egy könyv visszahozatalát.
Azaz törli a rekordot a kölcsönzések közül (ha több egyező is van, akkor egy tetszőlegeset), valamint növeli a könyv szabad példányszámát.
-20020-as számú felhasználói kivétel jelzi, ha nem létezik a kölcsönzési rekord.
*/
v_Datum kolcsonzes.datum%TYPE;
BEGIN
DELETE FROM kolcsonzes WHERE konyv = p_Konyv AND kolcsonzo = p_Kolcsonzo AND ROWNUM = 1
RETURNING datum INTO v_Datum;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20020,
'Nem létezik ilyen kölcsönzési bejegyzés');
END IF;
UPDATE konyv SET szabad = szabad + 1 WHERE id = p_Konyv;
DELETE FROM TABLE(SELECT konyvek FROM ugyfel WHERE id = p_Kolcsonzo) WHERE konyv_id = p_Konyv
AND datum = v_Datum;
END;
/
show errors
CREATE OR REPLACE PROCEDURE visszahoz_current_user(
p_Konyv konyv.id%TYPE, p_Kolcsonzo ugyfel.id%TYPE )
AUTHID CURRENT_USER AS
/* Ez az eljárás adminisztrálja egy könyv visszahozatalát.
Azaz törli a rekordot a kölcsönzések közül (ha több egyező is van,
Tárolt alprogramok
akkor egy tetszőlegeset), valamint növeli a könyv szabad példányszámát.
-20020-as számú felhasználói kivétel jelzi, ha nem létezik a kölcsönzési rekord.
*/
v_Datum kolcsonzes.datum%TYPE;
BEGIN
DELETE FROM kolcsonzes WHERE konyv = p_Konyv AND kolcsonzo = p_Kolcsonzo AND ROWNUM = 1
RETURNING datum INTO v_Datum;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20020,
'Nem létezik ilyen kölcsönzési bejegyzés');
END IF;
UPDATE konyv SET szabad = szabad + 1 WHERE id = p_Konyv;
DELETE FROM TABLE(SELECT konyvek FROM ugyfel WHERE id = p_Kolcsonzo) WHERE konyv_id = p_Konyv
AND datum = v_Datum;
END;
/
show errors
PLSQL nevű felhasználóként hozzuk létre a VISSZAHOZ és a VISSZAHOZ_CURRENT_USER eljárásokat. Ezután hozzunk létre egy új felhasználót PLSQL2 néven. PLSQL felhasználóként mindkét eljárásra adjunk futtatási jogot a PLSQL2 felhasználónak:
GRANT EXECUTE ON visszahoz TO plsql2;
GRANT EXECUTE ON visszahoz_current_user TO plsql2;
Futtassuk az eljárásokat PLSQL2 felhasználóként és vizsgáljuk meg az eredményt:
SQL> CALL plsql.visszahoz(1,1);
CALL plsql.visszahoz(1,1)
*
Hiba a(z) 1. sorban:
ORA-20020: Nem létezik ilyen kölcsönzési bejegyzés
ORA-06512: a(z) "PLSQL.VISSZAHOZ", helyen a(z) 25. sornál
Tárolt alprogramok
SQL> CALL plsql.visszahoz_current_user(1,1);
CALL plsql.visszahoz_current_user(1,1)
*
Hiba a(z) 1. sorban:
ORA-00942: a tábla vagy a nézet nem létezik
ORA-06512: a(z) "PLSQL.VISSZAHOZ_CURRENT_USER", helyen a(z) 18. sornál
Az első hívás során nem létező kölcsönzést adtunk meg, ez okozta, egyébként helyesen, a hibát.
A második hívás során más hibaüzenetet kaptunk, ennek az az oka, hogy a VISSZAHOZ_CURRENT_USER eljárás a hívó jogosultságaival fut. Így a rendszer az eljárásban szereplő táblák nevét a hívó, a PLSQL2 felhasználó nevében és jogosultságaival próbálja meg feloldani, sikertelenül.
Az első hívás során a PLSQL2 felhasználó hozzáfér a megfelelő adatbázis-objektumokhoz, annak ellenére hogy a táblákra semmilyen jogosultsága nincs. Ez a hozzáférés azonban jól definiáltan történik a VISSZAHOZ eljárás hívásával.
Alprogramjainkat a létrehozó jogaival futtatva előírhatjuk az alkalmazások használóinak, hogy csak egy általunk meghatározott, szabályos módon férjenek hozzá az adatbázisban tárolt objektumokhoz.
Az aktuális hívó jogaival definiált alprogramokat használhatjuk általános célú alprogramok készítésére olyan esetekben, amikor a tevékenység a hívó felhasználó objektumaihoz kötődik.
A következő SQL parancs újrafordít egy tárolt eljárást:
ALTER PROCEDURE eljárásnév COMPILE [DEBUG];
A DEBUG megadása azt írja elő, hogy a PL/SQL fordító a p-kód generálásánál használja a nyomkövetőt. A DROP PROCEDURE eljárásnév;
törli az adatbázisból a megadott nevű tárolt eljárást.
Egy tárolt függvényt a következő SQL paranccsal hozhatunk létre.
CREATE [OR REPLACE] függvényfej [AUTHID {DEFINER|CURRENT_USER}]
[DETERMINISTIC]
függvénytörzs
A függvény fejére és törzsére a 6.2. alfejezetben leírtak vonatkoznak.
A DETERMINISTIC egy optimalizálási előírás, amely a redundáns függvényhívások elkerülését szolgálja.
Megadása esetén a függvény visszatérési értékéről másolat készül, és ha a függvényt ugyanazokkal az aktuális paraméterekkel hívjuk meg, az optimalizáló ezt a másolatot fogja használni.
Az egyéb utasításrészek jelentése megegyezik a tárolt eljárásnál ismertetett utasításrészekével.
2. példa (A FAKTORIALIS függvény determinisztikus megadása)