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.
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 (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,
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,
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
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)
CREATE OR REPLACE FUNCTION faktorialis(n NUMBER) RETURN NUMBER
DETERMINISTIC
AS
rv NUMBER;
BEGIN
IF n < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Hibás paraméter!');
END IF;
rv := 1;
FOR i IN 1..n LOOP
rv := rv * i;
END LOOP;
RETURN rv;
END faktorialis;
/
show errors
A következő két tárolt függvény nem a paramétereitől függ, hanem az adatbázis tartalmától:
CREATE OR REPLACE FUNCTION aktiv_kolcsonzo RETURN NUMBER AS
/* Megadja azon ügyfelek számát, akik jelenleg kölcsönöznek könyvet. */
rv NUMBER;
BEGIN
SELECT COUNT(1)
INTO rv
FROM (SELECT 1 FROM kolcsonzes GROUP BY kolcsonzo);
RETURN rv;
END aktiv_kolcsonzo;
/
show errors
CREATE OR REPLACE FUNCTION osszes_kolcsonzo RETURN NUMBER AS
/* Megadja a beíratkozott ügyfelek számát. */
rv NUMBER;
BEGIN
SELECT COUNT(1)
INTO rv
FROM ugyfel;
RETURN rv;
END osszes_kolcsonzo;
/
show errors
Egy tárolt függvényt újrafordítani az
ALTER FUNCTION függvénynév COMPILE [DEBUG];
paranccsal, törölni a
DROP FUNCTION függvénynév;
paranccsal lehet.
A tárolt alprogramok SQL-ben a következő paranccsal hívhatók meg:
CALL alprogram_név([aktuális_paraméter_lista])
[INTO gazdaváltozó];
A gazdaváltozó a visszatérési értéket tárolja, ha az alprogram függvény.
3. példa
/* Gazdaváltozók az SQL*Plus futtató környezetben. */
VARIABLE v_Osszes NUMBER;
VARIABLE v_Aktiv NUMBER;
/* Tárolt alprogramok hívása */
CALL osszes_kolcsonzo() INTO :v_Osszes;
CALL aktiv_kolcsonzo() INTO :v_Aktiv;
/* Arány számolása és kiíratása */
PROMPT A jelenleg aktív ügyfelek aránya a kölcsönzők körében:
SELECT TO_CHAR(:v_Aktiv*100/:v_Osszes, '999.99') || '%' AS "Arány"
FROM dual;
Amikor egy tárolt alprogram lefordításra kerül, akkor az adatszótárban bejegyzés készül
az összes olyan adatbázis-objektumról, amelyre az alprogram hivatkozik. Az alprogram függ ezektől az objektumoktól. Ha az objektumok valamelyike megváltozik (végrehajtódik rajta egy DDL utasítás), akkor az alprogram érvénytelenné válik. Ha egy érvénytelen alprogramot hívunk meg, akkor a PL/SQL motor automatikusan újrafordítja azt futási időben. Ezt kerülhetjük el a tárolt alprogram explicit újrafordításával, az ALTER parancs alkalmazásával.
4. példa
CREATE OR REPLACE FUNCTION kombinacio(
n NUMBER,
m NUMBER
)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
RETURN faktorialis(n)/faktorialis(n-m)/faktorialis(m);
END kombinacio;
/
show errors
A KOMBINACIO tárolt függvény használja, és ezért függ az előzőleg már definiált FAKTORIALIS függvénytől. Ha a FAKTORIALIS függvényt lecseréljük egy másik implementációra, akkor a függőség miatt vagy nekünk, vagy a PL/SQL motornak újra kell fordítania a KOMBINACIO függvényt:
CREATE OR REPLACE FUNCTION faktorialis(
n NUMBER
)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
IF n = 0 THEN
RETURN 1;
ELSIF n < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Hibás paraméter!');
ELSE
RETURN n*faktorialis(n-1);
END IF;
END faktorialis;
/
show errors
ALTER FUNCTION kombinacio COMPILE;
Ha az alprogram és az objektum, amelytől függ, ugyanazon adatbázisban van, akkor az objektum módosítása az alprogram azonnali érvénytelenítését jelenti. Ha azonban az objektum egy távoli adatbázis eleme, akkor ez nem így történik. Távoli adatbázisban elhelyezett objektum érvényességének ellenőrzése futási időben, a hivatkozásnál történik meg. Az Oracle két különböző modellt alkalmaz ebben a helyzetben.
Időbélyegmodell
Ekkor az objektum és az alprogram utolsó módosításának időbélyegét hasonlítja össze. Ezeket az időbélyegeket a USER_OBJECTS adatszótárnézet LAST_DDL_TIME mezője tartalmazza. Ha a hivatkozott objektum időbélyege későbbi, akkor az alprogramot újrafordítja. Ez az alapértelmezett mód.
Szignatúramodell
Amikor létrehozunk egy tárolt alprogramot, az adatszótárban p-kódban tárolódik annak szignatúrája. Ez a formális paraméterek sorrendjét és típusát tartalmazza. Ez a modell alprogramok egymás közötti függőségeinek kezelésénél használható. Ha ugyanis egy P1 alprogram meghív egy P2 alprogramot, akkor a P2 szignatúrája a P1 első fordításánál elhelyeződik a P1 információi között. P1-et újrafordítani ezek után csak akkor kell, ha P2 szignatúrája megváltozik.
Ezen modell használatához a REMOTE_DEPENDENCIES_MODE rendszerparamétert SIGNATURE értékre kell állítani.
Az SQL utasításokban meghívott tárolt függvények nem módosíthatják a lekérdezés vagy módosítás alatt álló táblákat (lásd 13.6. alfejezet).
Egy függvény hozzáférési szintje azt adja meg, hogy a függvény mely adatokat olvashatja vagy módosíthatja. A PL/SQL négy hozzáférési szintet értelmez, ezek a következők:
WNDS (Writes No Database State). A függvény nem módosíthatja az adatbázis tábláit (nem használhat módosító utasítást).
RNDS (Reads No Database State). A függvény nem olvashatja az adatbázis tábláit (nem használhat SELECT utasítást).
WNPS (Writes No Package State). A függvény nem módosíthat csomagbeli változót (a változó nem szerepelhet értékadó utasítás bal oldalán vagy FETCH utasításban).
RNPS (Reads No Package State). A függvény nem használhatja fel a csomagbeli változó értékét (a változó nem szerepelhet kifejezésben).
Egy függvény hozzáférési szintje a fordító által ellenőrizhető, ha megadjuk a következő
pragmát:
PRAGMA RESTRICT_REFERENCES({függvénynév|DEFAULT},
{RNDS|WNDS|RNPS|WNPS|TRUST}
[, {RNDS|WNDS|RNPS|WNPS|TRUST}]…);
A DEFAULT egy csomag vagy egy objektumtípus minden függvényéhez a megadott hozzáférési szintet rendeli. A függvénynév viszont csak az adott függvény hozzáférési szintjét határozza meg. Túlterhelt függvénynevek esetén a kódban a pragmát megelőző, a pragmához legközelebb eső deklarációra vonatkozik. A TRUST olyan függvények esetén használható, melyek implementációja nem PL/SQL nyelven történt (hanem például Java vagy C nyelven).
5. példa (Létrehozunk egy tárolt függvényt és azt SELECT utasításban használjuk)
CREATE OR REPLACE FUNCTION hatralevo_napok(
p_Kolcsonzo kolcsonzes.kolcsonzo%TYPE,
p_Konyv kolcsonzes.konyv%TYPE
) RETURN INTEGER
AS
/* Megadja egy kölcsönzött könyv hátralevő kölcsönzési idejét. */
v_Datum kolcsonzes.datum%TYPE;
v_Most DATE;
v_Hosszabbitva kolcsonzes.hosszabbitva%TYPE;
BEGIN
SELECT datum, hosszabbitva
INTO v_Datum, v_Hosszabbitva
FROM kolcsonzes
WHERE kolcsonzo = p_Kolcsonzo
AND konyv = p_Konyv;
/* Levágjuk a dátumokból az óra részt. */
v_Datum := TRUNC(v_Datum, 'DD');
v_Most := TRUNC(SYSDATE, 'DD');
/* Visszaadjuk a különbséget. */
RETURN (v_Hosszabbitva + 1) * 30 + v_Datum - v_Most;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END hatralevo_napok;
/
show errors
SELECT ugyfel, konyv, hatralevo_napok(ugyfel_id, konyv_id) AS hatra
FROM ugyfel_konyv
WHERE ugyfel_id = 10 -- Szabó Máté István
/
/* Eredmény: (A példa futtatásakor a dátum: 2002. május 9.)
UGYFEL KONYV HATRA
----------------- ----------------------------------------------------- -----
Szabó Máté István Matematikai Kézikönyv 12
Szabó Máté István Matematikai zseblexikon 12
Szabó Máté István SQL:1999 Understanding Relational Language Components 12
Megjegyzés:
~~~~~~~~~~~
Adatbázispéldány szinten lehetőség van a SYSDATE értékét
rögzíteni a FIXED_DATE inicializációs paraméterrel
(ehhez ALTER SYSYTEM jogosultság szükséges):
ALTER SYSTEM SET FIXED_DATE='2002-MÁJ. -09' SCOPE=MEMORY;
ALTER SYSTEM SET FIXED_DATE=NONE SCOPE=MEMORY;
*/