SELECT INTO
2. példa (A FAKTORIALIS függvény determinisztikus megadása) CREATE OR REPLACE FUNCTION faktorialis(
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
Tárolt alprogramok
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
Tárolt alprogramok
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
Tárolt alprogramok
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
Tárolt alprogramok
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)