Programozás | SQL » Oracle PL/SQL programegységek

10.7 PL/SQL program-egységek Egy alkalmazás mindegyik program-egysége blokkot tartalmaz. A program-egységek alprogramok (subprograms), csomagok (packages), és triggerek formákban vannak jelen egy alkalmazásban. Az alprogramok eljárások és függvények lehetnek 10.71 Eljárások Eljárások létrehozása (deklarálása). Az eljárás specifikációja és a törzsének leírása egy közös parancsban történik: CREATE [OR REPLACE] PROCEDURE eljárás név [(paraméter1 [IN | OUT | IN OUT] típus, [paraméter2 [IN | OUT | IN OUT] típus, . )] AS <eljárás törzse>; A paramétereket hivatkozással vagy értékével lehet átadni az alprogramba. Az Oracle a következő alapértelmezést veszi figyelembe: • IN esetén az alprogram a paramétert hivatkozás alapján kapja; • OUT és IN OUT esetén pedig értékkel kapja. Az IN, OUT, IN OUT paraméterátadást rendel a paraméterekhez: • IN – érték szerinti paraméterátadás (alapértelmezés). Ha egy formális

paraméterhez IN záradék tartozik, akkor az alprogram az aktuális paraméter értékét kapja (mint bemenő értéket). Az alprogram végrehajtása alatt a paraméter értéke nem változhat meg. • OUT – eredmény szerinti paraméterátadás. Ha egy formális paraméter OUT záradékkal van jelölve, akkor annak az értéke nem kerül be az alprogramba (rá úgy lehet tekinteni, mintha annak NULL értéke lenne). Az alprogram befejezésekor az aktuális paraméter megkapja a formális paraméter értékét, és azzal tér vissza a hívó programba. • IN OUT– érték-eredmény szerinti paraméterátadás. Ha a formális paraméter IN OUT záradékkal van jelölve, akkor az alprogram az aktuális paraméter értékét kapja, és a paraméter értéke az alprogramban megváltozhat. Az alprogram befejezésekor az aktuális paraméter megkapja a formális paraméter értékét, és azzal tér vissza a hívó programba. Az <eljárás törzse> – egy PL/SQL blokk. Az

eljárás módosítását is a CREATE [OR REPLACE] PROCEDURE paranccsal lehet végrehajtani. Mivel a CREATE parancsok a DDL parancsokhoz tartoznak, ezért a CREATE [OR REPLACE] PROCEDURE parancs előtt és után implicit módon végrehajtódik a COMMIT parancs. A CREATE PROCEDURE parancs DECLARE részt nem tartalmazhat. Ha figyelembe vesszük az <eljárás törzse> struktúráját, akkor az eljárás deklarálását így írhatjuk le: CREATE OR REPLACE PROCEDURE eljárás név [paraméter lista] AS BEGIN <az eljárás végrehajtandó része> [EXCEPTION <a kivétel-kezelő rész>] END [eljárás név]; Az eljárás végén az eljárás név nem kötelező, de megkönnyítheti a programok olvasását. Példa. CREATE OR REPLACE PROCEDURE InsertIntoTemp AS v Num1 NUMBER := 5; v Num2 NUMBER := 6; v String1 VARCHAR2(50) := Hello World!; v String2 VARCHAR2(50) := -- ; v OutputStr VARCHAR2(50); BEGIN INSERT INTO test (num col, char col) VALUES (v Num1, v String1); INSERT

INTO test (num col, char col) VALUES (v Num2, v String2); SELECT char col INTO v OutputStr FROM test WHERE num col = v Num1; DBMS OUTPUT.PUT LINE(v OutputStr); SELECT char col INTO v OutputStr FROM test WHERE num col = v Num2; DBMS OUTPUT.PUT LINE(v OutputStr); END InsertIntoTemp; Eljárás törlése DROP PROCEDURE eljárás név; Példa. DROP PROCEDURE InsertIntoTemp; 10.72 Függvények Függvény létrehozása (deklarálása). A függvény struktúrája hasonló az eljárás struktúrájához, de az alkalmazása különbőzik az eljárás alkalmazásától. A függvényekre a kifejezésekben hivatkozhatunk, az eljárásokra, pedig egy külön parancsból kell hivatkozni. CREATE [OR REPLACE] FUNCTION függvény név [(paraméter1 [IN | OUT | IN OUT] típus, [paraméter2 [IN | OUT | IN OUT] típus, . )] RETURN <az eredmény típusa> AS <függvény törzse>; A < függvény törzse> – egy PL/SQL blokk. Kötelező, hogy a függvény törzse a RETURN parancsot

tartalmazza: RETURN <kifejezés>, ahol a kifejezés az eredményt tartalmazza. Egy függvény több RETURN parancsot is tartalmazhat, de a függvény futtatása alatt mindig csak egy RETURN kerül végrehajtásra. A RETURN parancs a vezérlést a program felsőbb szintjére adja vissza (arra a kifejezésre, ahonnan kapta a függvény a vezérlést). Példa. CREATE OR REPLACE FUNCTION TestReturn (ParIn IN INTEGER) RETURN VARCHAR IS BEGIN END ; IF ParIn< 0 THEN RETURN ‘Kisebb, mint 0’; ELSIF ParIn= 0 THEN RETURN ‘Nullával egyenlő’; ELSIF ParIn= 1 THEN RETURN ‘Eggyel egyenlő’; ELSIF ParIn= 2 THEN RETURN ‘Ketövel egyenlő’; ELSE RETURN ‘Nagyobb, mint 2’; Függvény törlése DROP FUNCTION függvény név; Példa. DROP FUNCTION TestReturn; 10.73 Csomagok (PACKAGE) Egy program logikailag összetartozó elemeit (részeit) célszerű együtt egy csomagban tárolni. Egy csomag eljárásokat, függvényeket, kurzorokat, típusok deklarálását, és

változókat tartalmazhat. A csomag deklarálása két részből áll Deklarálni kell a • csomag fejlécét (package header) és a • csomag törzsét (package body). A csomag fejlécét és törzsét az a felhasználó hozhatja létre, aki CREATE PROCEDURE privilégiummal rendelkezik. A csomag fejlécét a következő paranccsal deklaráljuk: CREATE [OR REPLACE] PACKAGE [felhasználó.]csomag neve AS <csomagspecifikációk> END [csomag neve]; A csomagspecifikációk a következő nyilvános elemeket tartalmazhatják: • típusok; • konstansok; • változók; • eljárások; • függvények; • kivételek; • kurzorok; • kompiler (fordító) direktívák. Az elemek sorrendje a deklarálásban tetszőleges, de azt az elemet, amelyre hivatkozik egy másik elem, előbb kell deklarálni. Példa. CREATE [OR REPLACE] PACKAGE TestPackage AS PROCEDURE TestProc(p ParIn IN INTEGER, p ParOut OUT VARCHAR2); FUNCTION TestFunc(p Par1 IN VARCHAR2) RETURN INTEGER; END TestPackage;

A csomag törzs tartalmazza azoknak az alprogramoknak a kódját, amelyek a csomag fejlécében vannak deklarálva. A csomag törzsét a következő paranccsal lehet megadni: CREATE [OR REPLACE] PACKAGE BODY csomag neve AS [<a törzs globális deklarálásai>] <az alprogramok> END [csomag neve]; A csomag törzse globális deklarálásokat is tartalmazhat, amelyek elérhetők a csomag összes alprogramjaiból, de nem láthatók (nem léteznek) a csomagon kívül. A globális deklarálások típusok és változok deklarálását tartalmazhatnak. Példa. CREATE OR REPLACE PACKAGE BODY TestPackage AS PROCEDURE TestProc(p Par IN INTEGER, p ParOut OUT VARCHAR2) IS BEGIN IF p ParIn>100 THEN p ParOut:=’ InPar>100’; ELSE p ParOut:=’ InPar<=100’; END IF TestProc; END; FUNCTION TestFunc(p Par1 IN VARCHAR2) RETURN INTEGER IS BEGIN RETURN LENGTH(p Par1); END TestFunc; END TestPackage; A csomagnak mindig fejléccel kell rendelkezni, de olyan ritka eset is

elképzelhető, amikor a csomag nem tartalmaz törzset. Ez azt feltételezi, hogy a csomag nem tartalmaz alprogramokat. Ebben az esetben a csomag csak típusok, és változok deklarálását tartalmazza Ez akkor hasznos, ha az alkalmazás globális típusait és változóit egy helyen akarjuk deklarálni (egy csomagban), hogy később aztán azokra bárhonnan könnyen hivatkozhassunk. Azok az objektumok, amelyek a csomag törzsében vannak deklarálva, elérhetők bárhonnak, még a csomagon kívülről is. A csomag objektumára úgy hivatkozhatunk, hogy az objektum neve előtt megadjuk a csomag nevét. Példa. DECLARE OutPar VARCHAR2(20); BEGIN TestPacsage.TestProc(120, OutPar); END; Egy csomagon belül több azonos nevű eljárás (függvény) létezhet, amelyeknek a paraméterei különbözőek lehetnek. Ez akkor lehet hasznos, ha egy a műveletet különböző típusú adatokkal akarunk végrehajtani. Példa. CREATE OR REPLACE PACKAGE TestPackage AS PROCEDURE TestProc(p ParIn

IN INTEGER, p ParOut OUT VARCHAR2); FUNCTION TestFunc(p Par1 IN VARCHAR2) RETURN INTEGER; FUNCTION TestFunc(p Par1 IN INTEGER) RETURN INTEGER; END TestPackage; CREATE OR REPLACE PACKAGE BODY csomag neve AS PROCEDURE TestProc(p Par IN INTEGER, p ParOut OUT VARCHAR2) IS BEGIN IF p ParIn>100 THEN p ParOut:=’ InPar>100’; ELSE p ParOut:=’ InPar<=100’; END IF; END TestProc; FUNCTION TestFunc(p Par1 IN VARCHAR2) RETURN INTEGER IS BEGIN RETURN LENGTH(p Par1); END TestFunc; FUNCTION TestFunc(p Par1 IN VARCHAR2) RETURN INTEGER IS BEGIN RETURN p Par1*p Par1; END TestFunc; END TestPackage; 10.74 Csomagok tulajdonságai A csomagnak az a fontos tulajdonsága és egyben előnye is, hogy a fejléce függetlenül tárolódik a törzsétől. Ha megváltoztatjuk a csomag törzsét, akkor nem kell megváltoztatni a csomag fejlécét, de ha megváltozik a csomag fejléce, akkor a csomag törzse automatikusan megkapja a nem érvényes tulajdonságot. Példa. CREATE TABLE Test Table (Col1

VARCHAR2(10)); CREATE OR REPLACE PACKAGE TestPackage AS PROCEDURE TestProc(ParIn IN VARCHAR2); END TestPackage; CREATE OR REPLACE PACKAGE BODY TestPackage AS PROCEDURE TestProc(ParIn IN VARCHAR2) AS BEGIN INSERT INTO Test Table VALUES(ParIn); END TestProc; END TestPackage; CREATE OR REPLACE PROCEDURE Proc Dep (ParIn IN VARCHAR2) AS BEGIN TestPackage.TestProc (’paméter ’ || ParIn); END Proc Dep; Ellenőrizzük, hogy érvényesek-e (VALID) az objektumok: SELECT Object Name, Object Type, Status FROM User Objects WHERE Object Name IN (’TestPackage’, ’Proc Dep’, ’Test Table’); OBJECT NAME --------------------TEST TABLE PROC DEP TESTPACKAGE TESTPACKAGE OBJECT TYPE STATUS -----------------------------TABLE VALID PROCEDURE VALID PACKAGE VALID PACKAGE BODY VALID Láthatjuk, hogy mindegyik objektum érvényes. Most megváltoztatjuk a csomag törzsét: CREATE OR REPLACE PACKAGE BODY TestPackage AS PROCEDURE TestProc(ParIn IN VARCHAR2) AS BEGIN INSERT INTO Test Table

VALUES(UPPER(ParIn)); END TestProc; END TestPackage; Az objektumok továbbra is érvényes maradnak: SELECT Object Name, Object Type, Status FROM User Objects WHERE Object Name IN (’TestPackage’, ’Proc Dep’, ’Test Table’); OBJECT NAME --------------------TEST TABLE PROC DEP TESTPACKAGE TESTPACKAGE OBJECT TYPE STATUS -----------------------------TABLE VALID PROCEDURE VALID PACKAGE VALID PACKAGE BODY VALID De ha töröljük a táblát: DROP TABLE Test Table; akkor a csomag törzse már nem lesz érvényes: SELECT Object Name, Object Type, Status FROM User Objects WHERE Object Name IN (’TestPackage’, ’Proc Dep’, ’Test Table’); OBJECT NAME --------------------PROC DEP TESTPACKAGE TESTPACKAGE OBJECT TYPE STATUS -----------------------------PROCEDURE VALID PACKAGE VALID PACKAGE BODY INVALID 10.75 Csomagok végrehajtása Példa. CREATE OR REPLACE PACKAGE TestPackage AS FUNCTION TestFunc(ParIn IN VARCHAR2) RETURN NUMBER; END TestPackage; CREATE OR REPLACE PACKAGE

BODY TestPackage AS MyVar NUMERIC :=0; FUNCTION TestFunc(ParIn IN VARCHAR2) RETURN NUMBER AS BEGIN MyVar:=MyVar + ParIn¿ RETURN MyVar; END TestFunc; END TestPackage; A TestFunc ParIn-el növeli a MyVar változó értékét. Mivel a MyVar változó a csomag szintén van deklarálva (nem pedig a függvényben), ezért a függvény következő hívása előtt megmarad a változó értéke. Ezt a tulajdonságot figyelembe kell venni, amikor szerkesztjük a programot. Elemezzük tovább az előbbi példát: DECLARE TempVar NUMBER; B BOOLEAN; BEGIN B:= False; WHILE NOT B LOOP SELECT TestPackage.TesztFunc(1) INTO TempVar FROM Dual; DBMS OUTPUT.PUT LINE(TempVar); IF TempVar>=10 THEN b:=True; END IF; END LOOP; END; Az eredmény – 1 2 3 4 5 6 7 8 9 10 A tulajdonságot meg lehet változtatni, ha a CREATE parancsban a megfelelő pragmát alkalmazunk. Pragma – egy olyan speciális záradék, amely vezérli a fordítóprogram működését, de mögötte nem áll végrehajtható kód. Ha

a csomag fejlécében megadjuk a PRAGMA SERIALLY REUSABLE záradékot, akkor a függvény mindegyik futtatása előtt a globális változó újból megkapja a kezdő értéket. CREATE OR REPLACE PACKAGE TestPackage AS PRAGMA SERIALLY REUSABLE; FUNCTION TestFunc(ParIn IN VARCHAR2) RETURN NUMBER; END TestPackage; Akkor az előző program futtatásának az eredménye: 1111111111 10.76 Kivételkezelés Amikor egy program futtatása alatt bizonyos kivételes helyzet alakul ki, az Oracle ezt észreveszi, és úgy reagál rá, hogy leállítja a program futtatását és angol nyelvű hibajelzéssel jelzi azt. A felhasználó szempontjából ez nem jó megoldás Leggyakrabban a program futtatása szempontjából a kivételek hibát jelentenek. A PL/SQL-ben a program-egységekben lehetőség van a kivételkezelésre. A kivételkezeléssel a blokk EXCEPTION része (szakasza) foglalkozik. Amikor a blokk végrehajtása alatt bekövetkezik egy kivétel-esemény, akkor a blokk végrehajtandó

részének futása félbeszakad, és a vezérlést a blokk EXCEPTION kivételkezelő része kapja. Ez lehetőséget ad a fejlesztőnek, hogy előre figyelembe vehesse a lehetséges eseményeket (hibákat), és az EXCEPTION szakaszban készítsen olyan programkódot, amely azokat feldolgozza. Más és más kivétel eltérő reakciót igényel A PL/SQL felismeri a rendszer (belső) kivételeket, és az EXCEPTION szakaszban azokra hivatkozhatunk. A fontosabb rendszer-kivételek listája: • CURSOR ALREDY OPEN – a program próbálja megnyitni a már megnyitott állományt; • DUP VAL ON INDEX – a program ismétlődő értéket próbálkozik beírni az egyedi index-mezőbe (INSERT vagy UPDATE parancsokkal); • INVALID CURSOR – hibás művelet a kurzorral. Az OPEN parancs nem deklarált kurzort akar megnyitni, a FETCH parancs meg nem nyitott kurzorból próbál olvasni, vagy a CLOSE parancs meg nem nyitott kurzort próbál lezárni; • INVALID NUMBER – nem numerikus értéket

próbál egy numerikus típusú változóba vinni; • LOGIN DENIED – az ORACLE nem fogadja el felhasználói azonosítót vagy jelszót (a szerverhez való csatlakozáskor); • NO DATA FOUND – a SELECT parancs egyetlen sort sem adott vissza; • NOT LOGGED IN – a program AB műveletet akar végrehajtani a szerverhez való csatlakozása nélkül; • PROGRAM ERROR – a PL/SQL belső hiba; • STORAGE ERROR – memória hiba; • TIMEOUT ON RESOURSE – egy erőforrás nem elérhető; • TOO MANY ROWS – a SELECT . INTO – a SELECT parancs eredménye több, mint egy sor; • • VALUE ERROR – aritmetikai hiba ZERO DEVIDE – nullával való osztás. Példa. DECLARE . out status CHAR2(30); return kod INTEGER; BEGIN . EXCEPTION WHEN NO DATA FOUND THEN out status:= ’Az adatok hiányoznak’; return kod:= 5; WHEN TOO MANY ROWS THEN out status:= ’A lekérdezés eredménye – több mint egy sor’; return kod:= 6; END; Az EXCEPTION részben a WHEN-t úgy kell

értelmezni, mint a hagyományos IF konstrukciót. A rendszer-kivételeken kívül a program tartalmazhat felhasználói kivételeket (hibákat) is, amelyeket a programozó hozhat létre deklarációval: <Hiba név> EXCEPTION A felhasználói kivételeket a PL/SQL blokkokban, eljárásokban, függvényekben, és csomagokban hozhatunk létre. A felhasználói kivétel aktivizálása a RAISE <Hiba név> paranccsal történik. Példa. DECLARE . sajat hibam EXCEPTION; BEGIN IF x>9999 THEN RAISE sajat hibam; END IF; EXCEPTION WHEN sajat hibam THEN return code:= 7; Out status :=’Az x változó hibás értéket kapott!’; END; Ha egy kivételt az EXCEPTION WHEN feltételek nem tartalmaznak, akkor azt az Oracle alapértelmezésként fogja kezeli. Mint már említettük, ez nem a legjobb megoldás Ilyen esetekben alkalmazhatunk OTHERS-nevű kivételt, amely összefoglalja az összes olyan kivételt, amelyek nem voltak feldolgozva az előző WHEN záradékokban. Példa. DECLARE

. sajat hibam EXCEPTION; BEGIN IF x>9999 THEN RAISE sajat hibam; END IF; EXCEPTION WHEN sajat hibam THEN return code:= 7; Out status :=’Az x változó hibás értéket kapót!’; WHEN others THEN return code:= 100; Out status :=’Ismeretlen hiba!’; END; 10.8 SELECT parancs eredményének alkalmazása 10.81 SELECT INTO parancs A PL/SQL a SELECT parancsnak olyan változatát tartalmazza, amely nincs a szabványos SQL-ben. Ez a parancs a következő két formában alkalmazható: SELECT . INTO <változó>[,<változó>[,] SELECT . INTO <rekord> A SELECT parancs ennek a változata csak egy sort adhat vissza. Ha a parancs egynél több sort ad vissza, akkor a TOO MANY ROWS kivétel-eseményt vált ki. 10.82 Kurzorok alkalmazása Ha a SELECT parancs eredménye több mint egy sort tartalmaz, akkor kurzort kell szerkeszteni, amely ebben az esetben problémamentesen alkalmazható. A kurzor természetesen akkor is alkalmazható, ha az eredmény csak egy sort

tartalmaz. A kurzort először deklarálni kell: CURSOR curzor név IS SELECT parancs; A kurzor a SELECT parancs eredményét csak a kurzor megnyitása után: OPEN curzor név; kapja. A kurzor alkalmazásánál a következő négy attribútumot kell figyelembe venni, és az értékeit célszerű ellenőrizni a programban. • %NOTFOUND – ellenőrzi, hogy befejeződtek-e a kurzor sorai; • %FOUND – ellenőrzi, hogy létezik-e még kurzor sora; • %ROWCOUNT – megszámolja, hogy hány sora van a kurzornak; • %ISOPEN – ellenőrzi, hogy nyitva van-e a kurzor. Az attribútumok alkalmazása: Kurzor név. %NOTFOUND, Kurzor név %FOUND, Kurzor név %ROWCOUNT, Kurzor név. %ISOPEN A FETCH parancs alkalmazásával a kurzor következő sorát változókba lehet átírni (szekvenciálisan): FETCH kurzor név INTO valt 1, valt 2, . , valt n; A változók számának meg kell egyezni az eredménytábla oszlopainak számával. A kurzor sorai csak szekvenciálisan érhetők el, és a FETCH

parancs mindég a kurzor következő sorát írja át a változókba. Ha a kurzor sok adatot tartalmaz, és azokat a programba hálózaton keresztül kell átadni, akkor a program lényegesen lelassulhat. A gyorsaság szempontjából figyelembe lehet venni a következő tényeket: • Az adatok áthelyezése szempontjából leggyorsabb a SELECT . INTO parancs; • A a gyorsaság szempontjából a PL/SQL blokk végrehajtása a következő; • Leglassúbb a kurzor alkalmazása. 10.9 Triggerek Trigger – egy olyan táblákhoz rendelt tárolt eljárás, amelyet az Oracle automatikusan lefuttat, amikor bekövetkezik a táblát módosító esemény. A triggerek szerkezete hasonlít az alprogram szerkezetére. A triggerek, mint a csomagok is, csak tárolhatók (az AB-ban) lehetnek. A programokból hivatkozni a triggerekre nem lehet, és azok csak implicit módon lesznek végrehajtva, amikor bekövetkeznek a megfelelő események (például, egy UPDATE parancs). Leggyakrabban a

következő DML INSERT, UPDATE, és DELETE parancsokhoz kapcsolják a triggereket. A triggereket a következő esetekben szokták alkalmazni: • ha olyan bonyolult megszorítást kell szerkeszteni, amelyet a tábla deklarálásában a CHECK záradékkal nehéz megadni; • ha a tábla módosításait kell követni, és azokat és a felhasználókat akarjuk regisztrálni, akik végrehajtották a módosításokat; • amikor automatikusan akarjuk közölni a többi programmal, hogy milyen módosítások történtek az adott táblában. A triggereket három csoportra lehet osztani: • DML triggerek; • INSTEAD OF (’helyette’) triggerek; • rendszer triggerek. 10.91 DML triggerek A leggyakrabban alkalmazzák a DML triggerek. Ezeket a triggereket • az INSERT, UPDATE, DELETE parancsokkal aktivizáljuk, • aktivizálhatók a parancsok előtt vagy után, • hatáskörük lehet az egész tábla vagy a tábla egy sora. Egy trigger több parancshoz is kapcsolható. Például, gyakran

egy triggert az INSERT és UPDATE parancsokhoz kapcsolják. 10.92 Triggerek létrehozása és alkalmazása A trigger létrehozásának következő a szintaxisa: CREATE OR REPLACE TRIGGER trigger neve {BEFORE| AFTER | INSTEAD OF} <az aktivizáló esemény> [WHEN aktivizáló feltétel] [FOR EACH ROW] <a trigger törzse >; A DML parancsok esetén az aktivizáló esemény tartalmazza a műveleteket és a táblát, amellyel a műveletek végrehajtásra kerülnek. A WHEN aktivizáló feltétel szűkítheti a sorok halmazát, melyekre kell, hogy hasson a trigger. Ha a FOR EACH ROW záradék meg van adva, akkor a trigger a tábla mindegyik sorára külön-külön fog hatni, ha nincs megadva a záradék – akkor az egész táblára. Összesen 12 trigger-kombinációt lehet szerkeszteni a következő értékek alapján: {INSERT| UPDATE | DELETE}x{BEFORE| AFTER}x{sor| tábla}. Egy táblához több triggert lehet szerkeszteni. A triggerek végrehajtásának sorrendje: 1. Az

egész táblához tartozó BEFORE-trigger aktivizálása 2. A tábla mindegyik sorára végrehajtódnak a soros BEFORE-triggerek 3. Végrehajtódik az adott parancs (például, UPDATE) 4. A tábla mindegyik sorára végrehajtódnak a soros AFTER-triggerek 5. Végrehajtódnak az egész táblához tartozó AFTER-triggerek Konkrét esetekben egy táblához nem mindig tartozik több trigger. Mivel egy trigger a tábla mezőire hivatkozhat a művelet előtt és után, ezért szükséges megkülönböztetni a mezőknek az időbeli az állapotát: :old.mező név, :newmező név Ezeket a neveket csak a soros triggerekben lehet alkalmazni. Példa. CREATE OR REPLACE TRIGGER scott.Trg testtable BEFORE UPDATE ON scott.testtable FOR EACH ROW BEGIN :new.Num col := :newNum col - :oldNum col; END; Ezt a triggert az UPDATE parancs fogja aktivizálni. DELETE FROM testtable; INSERT INTO testtable VALUES (10, ’első sor’); INSERT INTO testtable VALUES (20, ’második sor’); SELECT * FROM

testtable; NUM COL -------------10 20 CHAR COL --------------első sor második sor Végrehajtjuk a következő parancsot: UPDATE testtable SET Num Col=8 WHERE Num Col=20; És ellenőrizzük a Trg testtable trigger működését: SELECT * FROM testtable; NUM COL -------------10 -12 CHAR COL --------------első sor második sor Még egy példa a trigger alkalmazására a SQL*Plus-ban: SQL> CREATE OR REPLACE TRIGGER display new sales 2 BEFORE INSERT OR UPDATE ON customer 3 FOR EACH ROW 4 DECLARE 5 New sales amt number; 6 BEGIN 7 New sales amt := :new.total sales – NVL(:oldtotal sales0); 8 DBMS OUTPUT.PUT LINE(’New sales amount: ’ + new sales amt); 9 END; / Trigger created. :old – a mező régi tartalma, :new – a mező új (a tranzakció sikeres befejezési utáni) tartalma, dbms output.put line – a képernyőn információt jelenít meg SQL> SET SERVEROUTPUT ON 2 INSERT INTO customer 3 VALUES 4 (11, ’a’, ’b’, ’c’, 12); New sales amount: 12 1 row

created. SQL> SET SERVEROUTPUT ON 2 UPDATE customer 3 SET total sales = 55 4 WHERE customer id=11; New sales amount: 67 1 row updated. A következő trigger nem engedi bevinni a tábla mezőjébe a negatív számokat: CREATE OR REPLACE TRIGGER OnlyPositive BEFORE INSERT OR UPDATE OF num col ON test FOR EACH ROW BEGIN IF :new.num col < 0 THEN RAISE APPLICATION ERROR(-20100, Csak pozitív számot vihet be!); END IF; END OnlyPositive; 10.93 INSTEAD OF triggerek Az INSTEAD triggereket csak a nézetekhez lehet létrehozni, a DML parancsok helyet aktivizálódnak, és csak egyes sorokra hatnak. A nézeteket akkor lehet módosítani, ha azok nem tartalmaznak: • halmaz műveleteket (UNION, UNION ALL, MINUS); • agregáló függvényeket (AVG, SUM, .); • GROUP BY, CONNECT BY, START WITH konstrukciókat; • DISTINCT záradékot. Az INSTEAD OF triggereket a nézetekben akkor alkalmazzák, amikor • módosítani akarunk olyan nézetet, amelyet másként nem lehet végrehajtani; • a

nézet tartalmaz beágyazott oszlopot, amelyet módosítani akarjuk. 10.94 Rendszer-triggerek A rendszer-triggerek az Oracle rendszer bizonyos eseményire reagálhatnak. Ilyen esemény lehet, például, az AB elindítása vagy megállítása. A rendszer-triggerek a DDL parancsok és az adatbázis eseményeire reagálnak. A következő táblázat tartalmazza a fontosabb eseményeket. Esemény STARTUP Mikor aktivizálható AFTER SHUTDOWN BEFORE SERVERERROR LOGON AFTER AFTER LOGOFF BEFORE CREATE BEFORE AFTER DROP BEFORE AFTER ALTER BEFORE AFTER Tartalom Aktiválódik az Oracle-példány indításakor Aktiválódik az Oracle-példány megalításakor Aktiválódik hiba esetén Aktiválódik amikor a felhasználó kapcsolatba lép az AB-sal Aktiválódik amikor a felhasználó megszakítja a kapcsolatot az AB-sal Aktiválódik az objektum létrehozásakor Aktiválódik az objektum törlésekor Aktiválódik az objektum módosításakor A STARTUP és a SHUTDOWN

triggerekket csak az AB-sal lehet alkalmazni, és nem a sémával. A rendszer-triggereket egy adatbázishoz (DATABASE) vagy egy sémához (SHEMA) lehet kapcsolni. Példa. Létrehozunk egy rendszer-triggert, és a sémához kapcsoljuk CREATE OR REPLACE TRIGGER Schema Trg AFTER LOGON ON SCHEMA BEGIN INSERT INTO trg temp table VALUES (1, ‘LOGGED IN SCHEMA’); END Schema Trg; Most egy másik triggert kapcsolunk az AB- hoz: CREATE OR REPLACE TRIGGER Database Trg AFTER LOGON ON DATABASE BEGIN INSERT INTO trg temp table VALUES (1, ‘LOGGED IN DATABASE’); END Database Trg; Az a művelet, amelyet a trigger hajt végre, leggyakrabban a COMMIT paranccsal fejeződik be. A trigger törzse egy PL/SQL blokk. Ez azt jelenti, hogy a triggerben minden olyan parancsot alkalmazhat, amely a PL/SQL blokkban alkalmazható. De létezik néhány kivétel is: A trigger nem tartalmazhat tranzakciós parancsokat COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION; A tranzakciós parancsokat nem tartalmazhat

az a függvény (eljárás) sem, melyre a trigger hivatkozik; A LONG és LONG RAW típusú változókat a trigger törzsében nem lehet deklarálni. • • • A CALL parancs által a trigger egy ’külső’ eljárásra hivatkozhat. Ezek az eljárások lehetnek ORACLE-tárolt, vagy Java alprogramok. Példa. CREATE OR REPLACE TRIGGER LogingDisConn AFTER LOGON ON DATABASE CALL TestPkg.LogDsCnt(SYSLOGIN USER); Az TestPkg.LogDsCnt eljárás megkapja a felhasználó nevét SYSLOGIN USER, mint IN paramétert. A triggereket lehet törölni: DROP TRIGGER trigger név; vagy törlés nélkül lehet kikapcsolni: ALTER TRIGGER trigger név DESABLE; és utána visszakapcsolni: ALTER TRIGGER trigger név ENABLE; Lehetőség van egy paranccsal az összes triggert egyszerre be- vagy kikapcsolniALTER TABLE trigger név ENABLE ALL TRIGGERS; ALTER TABLE trigger név DESABLE ALL TRIGGERS; Ezeket a műveleteket az alprogramokkal nem lehet végrehajtani. 10.10 Alprogramok, triggerek és csomagok

alkalmazása A tárolt objektumok az adat-szótárban tárolódnak, és bármelyik PL/SQL blokkból lehet hivatkozni. A triggerek és a csomagok nem lehetnek lokálisak, azok csak tárolt objektumok lehetnek. Az alprogramok (függvények és eljárások) lokálisak, vagy tároltak lehetnek. Egy alprogram akkor lesz tárolt, ha azt a CREATE OR REPLACE paranccsal hoztuk létre. Ha azt akarjuk, hogy az alprogram lokális legyen, azt egy blokkban vagy egy tárolt alprogramban a DECLARE részében a többi deklarálás után kell megadni. Objektumok Függvény Eljárás Trigger Csomag Az objektum lehet tárolt (az AB-ban) + + + + Példa. DECLARE FUNCTION Test2 (InPar IN VARCHAR2) RETURN VARCHAR2 IS Az objektumok lokális lehet + + - BEGIN RETURN InPar || SYSDATE; END Test2; BEGIN DBMS OUTPUT.PUT LINE(Test2 (’A mai dátum-’)); END; Mivel a Test2 függvény lokális, ezért a Test2-re csak az adott blokkban lehet hivatkozni. A tárolt alprogramban is lehet lokális alprogramókat

deklarálni, de természetesen csak az adott tárolt alprogramból lehet rá hivatkozni. Példa. CREATE OR REPLACE PROCEDURE TestStoredProc AS FUNCTION Test3 (InPar IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN ’Át van adva a-’ || InPar; END Test3; BEGIN DBMS OUTPUT.PUT LINE(Test3 (’Szöveg’)); END; Ha egy P1 alprogram hivatkozik egy másikra P2-re, akkor előbb a P2-t kell deklarálni. Abban az esetben, amikor a P1 hivatkozik a P2-re, és fordítva a P2 hivatkozik a P1-re, akkor a második alprogram fejlécét előbb kell deklarálni. Példa. DECLARE TempVal INTEGER := 3; PROCEDURE P2 (C IN OUT INTEGER); PROCEDURE P1 (C IN OUT INTEGER) IS BEGIN DBMS OUTPUT.PUT LINE(’P1’ || C || ‘)’ ); IF C > 0 THEN P2( C ); C := C-1; END IF; END P1; PROCEDURE P2 (C IN OUT INTEGER) IS BEGIN DBMS OUTPUT.PUT LINE(’P2’ || C || ‘)’ ); C := C-1; P1( C ); END P2; BEGIN P2 (TempVal); END; A blokk futtatásának eredménye: P2(3) P1(2) P2(2) P1(1) P2(1) P1(0) Az adat-szótár

nézetei alapján az alprogramokról különböző információt lehet kapni. Például, a USERS OBJECTS nézetből megtudhatjuk a tulajdonos (felhasználó) összes objektumának paramétereit, és köztük az alprogramokkét is. Többek között meg lehet tudni, az objektum típusát, azt, hogy az objektum mikor volt létrehozva, mikor volt az utolsó módosítás, érvényes-e az objektum. Ha például, egy eljárás hibát tartalmaz, akkor az nincs érvényes állapotban, továbbra is tárolódhat, de azt nem lehet végrehajtani. Az érvényes állapotot az Oracle VALID-ként jelzi, a nem érvényes, pedig INVALID tulajdonságot kap. A USERS SOURSE nézetből meg lehet tudni az objektum program kódját, a USERS ERRORS nézet tartalmazza a forditási (compile) hibákat. Az SQL*Plus-ban könnyen lelehet lekérdezni a USERS ERRORS állapotát, és a SHOW ERRORS paranccsal megjeleníteni a hibákat. Tárolt és lokális alprogramok összehasonlítása Tárolt alprogram Lefordított

formában tárolódik az ABban Lokális alprogram Lefordítódik, mint a blokk része. Mint névtelen blokk része, mindegyik hivatkozáskor újból fordítódik Bármelyik blokkból lehet rá hivatkozni. Csak abban a blokkban lehet rá A felhasználónak rendelkeznie az hivatkozni, melyben az alprogram EXECUTE privilégiummal volt deklarálva Az alprogram kódja külön tárolódik a Az alprogram és blokk együtt blokk kódjától, amely rá hivatkozik. Ez tárolódnak Ez megnehezíti a megkönnyíti a program értelmezését program értelmezését. Ha a blokk megváltozik, akkor az alprogramot is újból kell fordítani Két azonos nevű alprogram nem Egy blokkban lehet deklarálni két tárolható. Egy csomagon belül ez azonos nevű alprogramot, melyek megengedett. egymástól paraméterekben térnek el