PL/SQL
Bevezetés
Mint már korábban ismertettük, a relációs adatbáziskezelő rendszerek ma már
egyeduralkodó (és szabvány) adatkezelő nyelve az SQL nyelv. Az SQL nyelv
segítségével hozzáférhetünk az adatbázisban tárolt adatokhoz, metaadatokhoz,
és lekérdezhetjük, módosíthatjuk, bővíthetjük azokat. Az SQL segítségével igen
rugalmasan kezelhetők az adatok, azonban van egy sajátossága, amely miatt
nem tekinthető igazi programozási nyelvnek. Ez pedig az a tény, hogy az SQL
nem procedurális, azaz nem tartalmaz vezérlési elemeket, illetve olyan
utasításokat sem, amelyekkel a nem-adatbázis felé történő adatforgalom
szabályozható. Az SQL csak egy jól behatárolt tevékenységi kört fed le. Emiatt
az SQL önmagában nem használható alkalmazások készítéséhez. Az SQL
megjelenik az alkalmazásokban, amikor az RDBMS-t kell megszólítani, de a
többi vezérlési elem, adatforgalom, ideiglenes változók kezelésére vonatkozó
műveleteknél már nem használható, így ezen feledatok megoldására más
megoldást kell keresni.
A lehetséges megoldások közül már korábban vettük az SQL utasításoknak egy
hagyományos 3GL nyelvbe (mint pl. C, Pascal, Fortran,...) történő beágyazását,
amikor is a gazdanyelv utasításai közé beszúrhatunk SQL szintaktikájú
parancsokat, amelyek a végrehajtás során átadódnak az RDMS-nek és az
RDBMS végzi el a kijelölt műveleteket. E megoldás előnye, hogy több
különböző gazdanyelv is választható, s a programozó támaszkodhat a
gazdanyelv korábban elsajátított ismeretére. A 3GL nyelv általániossága,
rugalmassága biztosítja, hogy igen sok területre készíthessünk így alkalmazást.
Az SQL gazdanyelvbe történő beágyazásának van azonban bizonyos
értelemben egyfajta korlátja is, ami miatt az SQL procedurális elemekkel
történő kibővítése egy másfajta úton is megvalósulhat. A 3GL nyelv ugyanis
bizonyos alkalmazási esetekben túl általános eszközkészletű, túlságosan
független az adatbáziskezelőtől. Azt a tényt, hogy az alkalmazásfejlesztő
eszközök piaca nagyobb lehetőséget biztosít, mint az adatbáziskezelők piaca, az
RDBMS fejlesztő cégek viszonyalga hamar felismerték. Az fejlesztő eszközök
forgalma jóval gyorsabban növekszik az eddigi felméséresk alapján, mint az
adatbázuskezelőké (lsd. ábra).
Az adatbázskezelő rendszerek fejlesztők, nem akarván kimaradni erről a
területről sem, maguk is elkezdtek foglalkozni 4GL rendszerek kidolgozásával.
Mint már korábban, a 4GL rendszerek trgyalásakor említettük, a 4GL
rendszerek nem létezhetnek valamilyen 3GL komponens nélkül, melyekkel
speciális, egyedi vezérlési elemeket definiálhatnak az elkészítendő
alkalmazásba. Ehhez szükséges a közbenső eredmények ideiglenes tárolása,
alapvető vezérlési elemek megvalósítása, s hibakezelés is.
Több fejlesztő választotta azt utat, hogy egy hagyományos, más cég által
kifejlesztett 3GL nyelv helyett, egy saját, egyszerűsített procedurálsi elemekkel
kibővített nyelvet hozott létre. E nyelv saját alkalmazásfejlesztő rendszereiben
kerül felhasználásra. A fejlesztő rendszerek igényei mellett még egy másik érv
is szól a saját SQL kiterjesztés mellett: az adatbáziskezelés fejlődési
tendenciája. A fejlődés ugyanis azt mutatja, hogy egyre nagyobb az igény az
úgynevezett aktív adatbázisok iránt.
Az aktív adatbázisok területén a kutatások már régóta folynak, de eredményeik
csak az elmúlt időszakban jelentek meg a piacon kapható termékek körében is.
Aktív adatbázisok alatt olyan adatbáziskezelő rendszereket értünk, amelyekben
bizonyos tevékenységek a felhasználó közvetlen beavatkozása nélkül
végrehajtódnak. E tevékenységi körök között legismertebb a trigger fogalma. A
triggerek dinamikus integritási feltételt jelent, amelyben adott egy kiváltó
esemény és egy választevékenység. A trigger működése igen egyszerű elven
történik: ha a kiváltó esemény bekövetkezik, akkor végrehajtódik a
választevékenység. A választevékenység több elemi lépésből áll, s e lépések
lehetnek SQL utasítások, de emellett bizonyos vezérlési elemekre is szükség
lehet egy kicsiit is összetetebb választevékenység esetén. E trigger
választevékenység kódolásához tehát szükség lehet az SQL kibővítésére,
amihez megintcsak célszerűbb az RDBMS egy saját nyelvzetét felhasználni,
mint egy külső 3GL nyelvet.
Az adatbázisok aktív elemeinél manapság egyre nagyobb teret hódítanak a
tárolt eljárások is. Ennek lényege, hogy az adatkezeléshez kapcsolódó
többhelyen alkalmazott eljárásokat, függvényeket magában az adatbázisban
helyezhetünk el, mintegy könyvtárszerűen. Ennek előnye, hogy csak egy helyen
szükséges a kódot nyilvántartani, de többen is hozzáférhetnek. Központilag,
gyorsabban módosíthatók a rutinok. A tárolt eljárások alkalmazása esetén
gyorsabb lesz a végrehajtás is, mivel előfeldolgozott formában tárolódik a
művelet, másrészt felesleges részek nem ismétlődnek a memóriában a
konkurens felhasználások során.
Mindezen megfontolások alapján célszerűnek látszik, hogy az RDBMS
fejlesztők is létrehozzanak egy kibővített SQL nyelvet, mely az alapvető
funkcionális elemeket biztosítja a triggerek, a beépített eljárások, az
alkalamazás fejlesztés igényeinek teljesítéséhez. Mivel ezen a téren az elmúlt
időszakban nem létezett még szabvány (a hírek szerint az ANSI most dolgozik
az SQL ilyen értelmű, azaz procedurális kibővítésén), ezért az egyes fejlesztő
cégek saját elképzeléseik alapján hoztak létre ilyen kibővített nyelveket. Mi a
továbbiakban az Oracle cég proceduréális elemekkel kibővített nyelvével, a
PL/SQL nyelvvel fogunk részletesebben megismerkedni.
PL/SQL alapjai
A PL/SQL nyelv az Oracle SQL kibővítése. A kibővítés annyiban pontatlan egy
kicsit, hogy a PL/SQL nem tartalmazza az SQL teljes utasításkészletét, csak
azon utasításokat, melyek az adatkezelő eljárások során nagyobb szereppel
rendelkeznek. Így a PL/SQL nyelvben lehetőség van az SQL adatkezelő
utasításainak, a kurzor szerkezetnek és a tranzakciókezelő utasításoknak a
használatára, de hiányoznak belőle például az adatdefiníciós és a védelmet
szabályzó utasítások.
A PL/SQL nyelv viszont tartalmazza az alapvető vezérlési elemeket, így a
WHILE ciklust és az IF elágazást is. A Pl/SQL-ben lehetőség van saját
memóriaváltozók létrehozására, melyekkel közbenső számítási eredmények
tárolhatók. Igen erős a nyelvhez kapcsolódó hibakezelési komponens, s számos
függvény segíti a rugalmas, hatékony programfejlesztést.
Mivel a PL/SQL alkalmazásával az adatkezelő utasítások nem egyenkénti SQL
utasítások formájában kerül végrehajtásra az adatbáziskezelőhöz, ezért a
végrehajtási sebesség is jelentősen javítható a PL/SQL segítségével. A PL/SQL
eljárások feldolgozzása ugyanis programegységekben, úgynevezett blokkokban
történik. A blokkban helyet foglaló SQL utasítások együttesét hatékonyabban
lehet optimalizálni, mint az egyenkénti SQL utasításokat.
A PL/SQL nagy előnye, hogy igen szorosan kapcsolódik az Oracle rendszerhez,
annak több komponensével is integrálható. A PL/SQL alkalmazható többek
között az SQLPlus, SQLForms és más komponensekben is. E nyelv előnye,
hogy független az alkalmazott konfigurációtól, operációs rendszertől, s csak a
futó adatbáziskezelőtől függ a konkrét felépítése, formátuma.
Összefoglalóan a PL/SQL előnyei az alábbi pontokban adhatók meg:
- procedurális elemek és SQL ötvozése
- hatékonyság
- jobb integráció az adatbáziskezelő rendszerrel
- rugalmasság, hordozhatóság.
A Pl/SQL nyelv alapvető struktúrális egysége a PL/SQL blokk. A blokk durva
közelítéssel megfeleltethető a hagyományos programozási nyelven megírt
eljárásoknak. A legfőbb hasonlósága az eljárással az, hogy a Pl/SQL blokk is
adatdefiníciós, majd azt követő műveleti részből áll, s a végrehajtás egységét
jelenti. A különbség legfontosabb elemi, hogy itt szintaktikailag külön szerepel
egy hibakezelő rész, s a blokkok egymásba is ágyazhatók, ahol a beágyazott
blokk a műveleti vagy hibakezelő részben szerepelhet.
A blokk általános felépítését a következő ábra szemlélteti.
Az ábrában szereplő szavak a bevezető nyelvi utasításokatjelöli. A három
szerkezeti elem közül csak a műveleti rész kötelező, amásik kettő elhagyható.
A deklarációs részben a blokk saját, lokális változóit, memória változóit lehet
megadni. A műveleti részben találhatók az SQL és procedurális utasítások, míg
a hibakezelő rész a felmerülő adatkezelési hibákra adandó válaszokat definiálja.
A blokkok egymásba ágyazása esetén a beágyazott blokk is a BEGIN
kulcsszóval kerül bevezetésre, s az END; utasítás zárja le.
DECLARE Fő-blokk kezdete
... Változók, konstansok, cursor-
szerkezetek és hibakezelési
elemek deklarálása
BEGIN Fő-blokk műveleti rész kezdete
... Utasítások, SQL parancsok,
Vezérlési elemek
DECLARE Al-blokk kezdete
...
BEGIN Al-blokk műveleti része
...
EXCEPTION Al-blokk hibakezelő része
...
END; Al-blokk vége
EXCEPTION Fő-blokk hibakezelő része
... Hibakezelő műveletek
leírása
END; Fő-blokk vége
Egy PL/SQL blokk több másik PL/SQL blokkot is magában foglalhat, s a
blokkok többszörös mélységben is beágyazhatók egymásba. Így az előző
példában megadott al-blokk maga is tartalmazhat további, saját alblokkokat. A
beágyazás gyakorlatilag tetszőleges mélységik folytatható (A PL/SQL 1.0
változatában 200 szintig lehet lemenni).
A hagyományos alprogramok, eljárásokhoz szokott programozóknak feltünhet,
hogy a blokkok deklarálásánál hiányzik egy blokk azonosító. A PL/SQL
rendszerben valóban nincs felhasználó által adott egyedi neve a blokkoknak.
Ebből következően a blokkok önmagukban nem hívhatók meg más blokkokból.
A későbbiekben látható lesz, hogy a PL/SQL blokkok igazából ritkán
használatosak önmagukban, mindíg valamilyen környezetben, más tipusú
objektumok(pl. tárolt eljárások, triggerek) részeként jelenik meg, melyeknek
már van egyedi azonosító neve, így a befoglaló objektumokon keresztül tudunk
a letárolt blokkokra hivatkozni.
A blokkok egymásba ágyazásához kapcsolódóan megemlítjük, hogy a
beágyazott blokk a külső blokk részeként kezelendő. Így például a külső
blokkban deklarált változók a beágyazott blokkban is láthatók lesznek. Az
alblokk függetlensége abban nyilvánul meg, hogy ő maga is hozhat létre saját
változókat, melyek azonos azonosító nevet is kaphatnak, mint a külső blokkban
létrehozott változók. Ez utóbbi esetben a belső blokkban deklarált változó egy
új változó lesz, amely az alblokkban elrejti a kinn deklarált ugyanilyen nevű
változót. A lokálisan létrehozott változónak tehát itt is elsőbbsége van a külső
változókkal szemben.
A következőkben sorra vesszük a PL/SQL utasításait, majd ezt követően a
PL/SQL blokkok alkalmazására, felhasználására térünk ki.
PL/SQL blokk deklarációs része
A változók létrehozását a DECLARE kulcsszót követő részben kell megadni. A
változók deklarációjának formátuma:
változóazonosító tipus(attributum) := kifejezés;
Az attributum egy opcionális tag, amely segítségével már meglévő, az
adatbázisban létező objektumok alapján hozhatunk létre változókat. A
deklarációs sor végén álló kifejezés is opcionális tag. Szerepe a változó
inicializálásban áll.
Adattipusként az alábbi tipusokat lehet felhasználni:
NUMBER numerikus érték, opcionálisan kijelölhető a
teljes ábrázolási hossz és a tizedes jegyek
darabszáma
CHAR szöveges érték, opcionálisan kijelölhető a
karakterek darabszáma
DATE dátum tipus
BOOLEAN logikai adattipus
Az alaptipusok mellett az RDBMS fejlődésével további adatatipusok is
megjelennek, melyekre most nem térünk ki. Az új tipusok formátumát szükség
esetén az aktuális, felhasznált PL/SQL verzió kézikönyvében találhatjuk meg.
Vegyünk néhány példát az alaptipusok használatára:
DECLARE
kor NUMBER(6,2) := 3; -- numerikus érték,
-- 6 számjegy, 2 tizedesjegy
-- induló értéke 3
nev CHAR(25); -- szöveges, 25 karakter
datum DATE := '18-JUL-97'; -- dátum, kezdőértéke
-- 97. július 18.-a
reszvetel BOOLEAN; -- logikai
A megadott példábóllátható, hogy a PL/SQL blokkban a sor végéigterjedő
megjegyzéseket a -- jellel kell bevezetni.
A deklarációs részben a változók mellett konstansokat is megadhatunk. Egy
szimbólum akkor lesz konstans, ha az azonosító név és a tipus közé beírjuk a
CONSTANT kulcsszót. A konstansok esetén kötelező az inicializációs rész. A
példában az árfolyam értéke szerepel konstansként:
ARFOLYAM CONSTANT number(6,1) := 103.2;
Az attributumok segítségével létező adatbázis objektumokhoz kapcsolódó
változókat, konstansokat hozhatunk létre, melynél a létrejövő változó, vagy
konstans ugyanolyan tipusú lesz, mint a kijelölt adatbázisobjektum. Az
adatbázisobjektum lehet mező vagy táblarekord. Ennek megfelelően kétfajta
attributum alkalmazható a deklarációnál, a %TYPE és a %ROWTYPE.
DECLARE
valtozo1 tabla.mezo%TYPE;
valtozo2 valtozo_vagy_konstans%TYPE;
A %TYPE attributumot akkor használjuk, ha egy változó, vagy egy konstans
adattipusát egy már a táblában létező mező adattipusának megfelelően
szeretnénk deklarálni:
NEV EMBER.NEV%TYPE;
A példában a nev változó tipusa megegyezik az ember tábla nev azonosítójú
mezőjének a tipusával. Az %TYPE attributum előnye, hogy nem kell ismerni a
hivatkozott mező (amiből olvasni fogunk, vagy amibe írni fogumk) tipusát, és
ha megváltoztatjuk a mező tipusat, nincs szükség a PL/SQL blokk
módosításáráa, a változó adattipusa a futás során automatikusan illesztődik az
adatbázishoz.
A %ROWTYPE attributumotot akkor használjuk, ha egy rekordváltozót
kívánunk létrehozni. A létrejövő változó egy rekordtipusú változó lesz, amley
több mezőt is tartalmazhat. A mezők nevei és darabszáma megegyezik az
adattábla mezőinek neveivel és darabszámával. A rekordváltozó mezőit a
REKORDNÉV.MEZŐNÉV
hivatkozással érhetjük el. A példában az ember tábla soraihoz hozzunk létre
változót:
EMBER EMBER%ROWTYPE;
A beolvasás után a beolvasott sor nev mezőjének értéke a
EMBER.NEV
hivatkozással érthető el.
A változók, konstansok mellett az adatok adatbázisból történő beolvasásánál
használatos kurzor struktúrát is a deklarációs részben kell meghatározni. A
kurzor működéséről, megadásáról a műveleti rész kapcsán fogunk
részletsebben írni.
PL/SQL műveletei
A PL/SQL blokk a következő utasításokat tartalmazhatja:
:= értékadás
CLOSE kurzor lezárása (SQL utasítás)
COMMIT tranzakció sikeres lezárása (SQL utasítás)
DELETE adatok törlése az adatbázisban (SQL utasítás)
EXIT kilépés a ciklusból
FETCH rekord beolvasás a kurzorból (SQL utasítás)
GOTO ugrás a megadott cimkére
IF feltételes elágazás
INSERT adatok beszúrása az adatbázisba (SQL utasítás)
LOCK TABLE táblák zárolása(SQL utasítás)
LOOP ciklus szervező utasítás
NULL üres utasítás
OPEN kurzor nyitása (SQL utasítás)
RAISE hibajelenség kiváltása
ROLLBACK tranzakció visszagörgetése(SQL utasítás)
SAVEPOINT tranzakció mentési pont(SQL utasítás)
SELECT..INTO lekérdezés az adatbázisból(SQL utasítás)
SET TRANSACTION tranzakció paraméterzés(SQL utasítás)
UPDATE adatok módosítása az adatbázisban(SQL utasítás)
Az egyes műveletek végrehajtását számtalan segédfüggény segíti, melyekkel
többek között numerikus, karakterkezelési, dátumkezelési és konverziós
műveletek végezhetők. Az utasítások felsorolásából is látszik, hogy a PL/SQL a
külvilág felé zárt, csak az adatbázisban tárolt adatokhoz fér hozzá.
Adatértékeket, mint például a változók tartalmát nem lehet vele a képernyőre,
normál állományba kiírni, vagy a képernyőről, normál állományból beolvasni.
Így például a SELECT utasításnak csak ProC-ből megismert SELECT...INTO
formátuma használható. A SELECT eredményeit a blokk változóiban kell
felfogni. Természtesen ezen értékek nem vesznek el örökre előlünk, a
kapcsolatot a PL/SQL blokk és a külvilág között a kapcsolódó
segédprogramok, mint például a SQLForms vagy az SQLPlus biztosítja.
A következőkben részletebben bemutatjuk az egyes PL/SQL utasításokat. Az
utasításokat az SQL-hez hasonlóan itt is pontosvessző határolja.
Értékadás
A legegyszerűbb művelet az értékadás művelete. Az értékadás bal oldalán
változónak kell állnia, jobb oldalán pedig változókból, konstansokból és
beépített függvényeből álló kifejezés szerepel. Az alábbi példában az x változó
értékét eggyel megnöveljük:
x := x + 1;
Vezérlési szerkezetek, elágazás
Az elágazás, a feltételes műveletek kijelölésére az IF szerkezet szolgál,melynek
formátuma:
IF feltétel1 THEN
utasítások1
ELSE IF feltétel2 THEN
utasítások2
ELSE IF feltétel3 THEN
utasítások3
....
ELSE
utasításokN
END IF;
Az utasítások1 rész akkor hajtódik végre, ha a feltétel1 igaz értékre értékelődik
ki. Az utasítások2 részre akkor kerül sor, ha feltétel1 hamis, de feltétel2 igaz
értékű. Az utasítások3 végrehajtásának feltétele, hogy feltétel1, feltétel2 hamis,
míg feltétel3 igaz legyen. A legutólsó utasításokN akkor hajtódik végre, ha az
összes előző feltételek mind hamis értékűek. Az IF szerkezetben az ELSE IF
valamint az ELSE részek a hozzájuk tartozó utasításokkal együtt opcionálisak.
A következőben az x változó értékét y értékének függvényében állítjuk be:
IF x > 15 THEN
y := x - 12;
ELSE
y := x + 12;
END IF;
A feltétel részben a hagyományos operátorok, relációk mellett alkalmazhatók
az SQL-ben megismert speciális operátorok is, mint az
IS [NOT] NULL üres érték ellenőrzése
[NOT] LIKE szövegkeresés minta alapján
[NOT] BETWEEN értéktartományba esés
[NOT] IN halmazban való előfordulás
operátorok.
Vezérlési szerkezet, ciklusok
A PL/SQL többféle iterativ vezérlési szerkezetet is biztosít az ismétlődően
elvégzendő utasítások ciklusokba történő szervezéséhez. A ciklusok alapvetően
azonos formátumban használhatók, azonban konkrét működési módjuknak
megfelelőan az alábbi három cilusfajtát különböztetjük meg:
alap ciklus (LOOP ciklus)
WHILE ciklus
FOR ciklus
Ugyan még a kurzor szerkezetnél alkalmazandó ciklust is külön tipusnak
szokás venni, de mi azt egy csak egy későbbi pontban fogjuk tárgyalni.
Az alapciklus egy végtelen ciklust valósít meg, ezért ritkábban használják
önmagában ezt a ciklusfajtát. A ciklus felépítése:
LOOP
utasitások
END LOOP;
A ciklusmagban megadott utasítások végtelen sokszor ismétlődnek, ha a
vezérlés nem kerül egy ciklusból történő kilépés utasítására. A kilépést az
EXIT;
utasítással lehet elérni. A kilépést rendszerint valamilyen feltétel teljesülése
esetén hívjuk meg, ezért van a kilépésnek a feltételmegadással összekötött
változata is, melynek formátuma:
EXIT WHEN feltétel;
Ebben az esetben a vezérlés csak akkor lép ki a ciklusból, ha a megadott feltétel
igaz értéket ad.
A WHILE ciklus esetében a ciklus fejrészében adjuk meg a kilépés, illetve a
ciklusmag újbóli végrehajtásának feltételét. A ciklus formátuma:
WHILE feltétel LOOP
utasitások
END LOOP;
A WHILE ciklusban a ciklusmagban megadott utasitások sorozata mindaddig
végrehajtodik, amig a WHILE utáni feltétel kiértékelése igazat értéket ad . A
ciklus befejeződik, ha a kifejezés értéke hamis vagy NULL értékű lesz.
A FOR ciklus esetén egy indexváltozót kell kijelölni, amely minden egész
értéket sorra felvesz egy megadott tartományon belül. A ciklus megadása:
FOR index IN induló_egész..záró_egész LOOP
utasitások
END LOOP;
A ciklusban megadott indexváltozót nem szükséges külön deklarálni a
deklarációs részben, mivel a rendszer automatikusan létrehozza NUMBER
tipussúként. A ciklus végrehajtásakor az index előbb az induló_egész értéket
veszi fel, s ezzel végrehajtja a ciklusmagot. Eztkövetően eggyell növelődik az
index értéke, s újból a ciklusmag elvégzése következik. A növelés-végrehajtás
sorozata addig ismétlődik míg az index a záró_egész értékét fel nem dolgozta.
Ha az index értékét nem előre, hanem visszafelé szeretnénk léptetni, azaz
mindíg eggyel csökken a változó értéke, akkor a ciklust az alábbi formátumban
kell megadni:
FOR index IN REVERSE induló_egész..záró_egész LOOP
utasitások
END LOOP;
A ciklus fejrészében megadott index csak a cikluson belül látható és úgy
kezelhető, mint egy konstans: használhatjuk az aktuális ertékét, de közvetlenül
nem változtathatjuk meg.
A következő példában egytől tízig összeadjuk az egész számokat egyx
változóban:
x := 0;
FOR ind IN 1..10 LOOP
x := x + ind;
END LOOP;
Feltétel néküli vezérlésátadás
A nagyobb méretű, kevésbé szabályos vezérlésátadások megvalósítására
rendelkezésre áll a GOTO utasítás, melynek formátuma:
GOTO cimke;
A cimkét a blokkban, a műveleti részben helyezhetjük el. A cimke azonosítót
nem kell előzőleg definiálni. A cimke kijelölésének alakja:
<>
A cimke használatára, a vezérlésátadásra viszonylag több megkötés is
vonatkozik. Elsőként meg kell említeni, hogy cimke csak végrehajtható
utasítások előtt állhat, tehát nem szerepelhet END IF, END LOOP, END
kulcsszavak előtt. E megkötés szerencsére igen könnyen kikerülhető, ha a
kívánt helyre beszúrunk egy üres utasítást (NULL utasítást), amley formailag
végrehajtató utasítás, habár semmi nem történik eredményeképpen.
Így például az alábbi programrészlet szintaktikailag helytelen, mivel a cimke
END LOOP előtt áll:
FOR x IN 1..33 LOOP
...
IF y > 20 THEN
GOTO ujra;
END IF;
...
<>
END LOOP;
A kijavított változatban az END LOOP elé egy üres utasítást teszünk:
FOR x IN 1..33 LOOP
...
IF y > 20 THEN
GOTO ujra;
END IF;
...
<>
NULL;
END LOOP;
További megkötés, hogy a GOTO paranccsal nem ugorhatunk tetszőleges
cimkére, csak olyanra, amely az adott utasításcsoportban, vagy az őt tartalamzó
külső utasításcsoportok valamelyikében helyezkedik el. Utasításcsoport alatt
nemcsak PL/SQL blokkokat, hanem egy vezérlési utasítással (IF, LOOP)
összekötött utasításokat értünk. Igy például az alábbi blokkban helytelen a
vezérlésátadás művelete:
...
GOTO belul;
IF x IS NULL THEN
y := -1;
<>
x := x +1;
END IF
...
A hiba oka, hogy egy tartalmazott utasításcsoportba kell belépni, amit nem
enged meg a PL/SQL. Ha egymással felcseréljük az ugrás és a cimke
pozicióját, akkor viszont helyes elrendezést kapunk, mivel az ugrás a külső, a
GOTO utasítást tartalmazó csoportba történik. Az alábbi példa egy helyes
blokkrészletet mutat.
<>
NULL;
IF x IS NULL THEN
y := -1;
GOTO belul;
x := x +1;
END IF
A hibakezelő részben is szerepelhet GOTO utasítás és cimke is. Azonban itt
olyan megkötés áll fenn, hogy kinntről, a műveleti részből nemlehet beugrani a
hibakezelő részbe, s a hibakezelő részből sem átlépni a saját PL/SQL blokk
műveleti részébe.
A címkék nemcsak végrehajtható utasítások előtt állhatnak, hanem blokkok
előtt is, azaz a bevezető DECLARE vagy BEGIN kulcsszavak előtt. E cimkék
szerepe elsősorban nem a vezérlés átadó ugrásoknál van, hanem a változók
elérésénél. Mint már korábban említettük, egy adott blokkban deklarált változó
elrejti a külső blokkban ugyanolyan néven létrehozott változókat. A cimke
használatával lehetőség van azonban arra, hogy blokkra explicite hivatkozzunk,
így közvetlenül hivatkozhatunk a benne létrehozott változókra is a
blokk_cimke.változó
formátumban. Erre mutat egy példát a következő kódrészlet:
<>
DECLARE
datum DATE;
BEGIN
...
<>
DECLARE
datum DATE;
BEGIN
...
IF datum = kulso_blokk.datum THEN
....
END IF;
END belso_blokk;
....
END kulso_blokk;
A példában a saját és a külső blokkban deklarált ugyanolyan azonosítóval
rendelkező változókat hasonlítjuk össze. Mint a példa is mutatja, ha a blokk
előtt cimkét adunk meg a blokk azonosítására, akkor a blokk végén is
szerepeltetni kell ugyanazt a cimkét. A cimke használata nemcsak a hivatkozási
kört szélesíti, hanem egyben áttekinthetőbbé teszi a program listáját, így
használata feltétlenül javasolt.
Adatbázis adatok kezelése
A PL/SQL blokkok fő funkciója az adatbázisban tárolt információk kezelése.
Ehhez olvasni, módosítani, törölni kell tudni az adatokat, ahol az információk
nemcsak az adatbázisból, hanem a PL/SQL blokk változóiból is jöhetnek. E
terület magába foglalja az ismert adatkezelő SQL utasításokat, és a SELECT
utasítást, pontosabban annak SELECT INTO alakját. A nagyobb tömegű, több
rekordból álló eredményrekordok lekérdezésére, a gazdanyelvi programozásban
már megismert módon, a kurzor segítségével valósítható meg.
Az PL/SQL blokk és az adatbázis közötti adatforgalom megvalósítása a
PL/SQL változókon keresztül történik. A PL/SQL változók ugyanúgy
felhasználahtók az SQL utasításokban, mint a gazdanyelvi programokban, azzal
a formális különbséggel, hogy itt nem áll kettőspont a változóazonosító előtt az
SQL utasításban. A ProC-ben megismert beágyazott SQL-tőlmég abban is
különbözik a PL/SQL SQL kezelése, hogy itt nem áll semmilyen bevezető
kulcsszó az SQL utasítás előtt. A következő példában az x változóba olvassuk
be a legnagyobb életkort, s ezután mósoítjuk az ilyen életkorú személyek
pontszámát:
DECLARE
maxe NUMBER(3);
BEGIN
SELECT MAX(kor) INTO maxe FROM szemelyek;
UPDATE szemelyek SET pont = pont + 1 WHERE
kor = maxe;
END;
Ha az atadbázisban létezik a változónevével megegyező azonosítójú mező,
akkor az adatbázis mezőnek van prioritása az változóval szemben.
Az adatok felvitelére szolgáló INSERT utasítás formátuma megegyezik az SQL
szabványban megismert alakkal. Az új adattábla sor mezőértékeinek
megadásánál hivatkozhatunk PL/SQL változókra, melyek itt is bevezető
kettőspont nélkül szerepeltethetünk a VALUES listában. A következő példa a
PL/SQL változók INSERT utasításban történő felhasználását mutatja be (új
rekord felvitele a tabla táblába, a mező értékek sorra x értéke vagyis 3, az y
értéke vagyis 'ALMA' és 4 lesznek):
x := 3;
y := 'ALMA';
INSERT INTO tabla VALUES (x, y, 4);
Az adatbázis adatok törlésére a DELETE SQL utasítás szolgál, szintaktikája a
megismert formátumnak felel meg. A PL/SQL változók az utasítás WHERE
kulcsszót követő szelekciós részében szerpelhetnek, mint az az alábbi példa is
mutatja (törlés azon rekordoknak, ahol az id mező értéke megegyezik az x
változó tartalmával):
x := 3;
DELETE FROM tabla WHERE id = x;
Az adatok módosítása az UPDATE SQL utasítással valósítható meg. Az
utasítás szintaktikája megegyezik a korábban megismert alakkal. A PL/SQL
változók mind az értékkifejezésben, mind a szelekciós feltételben
szerepelhetnek. A következő példában a tabla tábla azon rekordjaiban, ahol a
kor mező értéke megegyezik a y értékével, módosítjuk az id mező tartalmát az
x változó értékére:
x := 3;
y := 43;
UPDATE tabla SET id = x WHERE kor = y;
Az adatok adatbázisból történő lekérdezésének a SELECT ... INTO utasítás
felel meg. Ez az utasítás ugyanolyan felépítésű, int a ProC-ben megismert
SELECT .. INTO szerkezet. Az INTO kulcsszó után áll egy lista, amely
kijelöli azon PL/SQL változókat, amelyekbe belekerülnek a lekérdezés
eredményei. A lista lehet többelemű, ekkor annyi változót kell tartalmaznia,
ahány mezőt tartalmaz az eredményrekord, a SELECT projekciós része. A lista
állhat egy elemből, ha a változóként nem egy elemi adatipusú elemet adunk
meg, hanem egy rekordtipusú elemet, aminek pontosan annyi és olyan mezője
van, mint az eredményrekordnak. Ez utóbbi előállításához használható fel a
%ROWYPE attributum. Természetesen elemi változók esetén is lehet
egyelemű a lista, ha a projekciós rész csak egy kifejezést tartalmaz. A
következő példa a rekordtipusú változó alkalmazására mutat példát:
DECLARE
egyed ember%ROWTYPE;
nev CHAR(20);
BEGIN
SELECT * INTO egyed FROM ember WHERE id = 2345;
nev := egyed.nev;
END;
A lekérdezés SELECT INTO formátuma azonban csak akkor alkalmazható, ha
a lekérdezés eredménye egyetlen egy rekordból áll. Ha a lekérdezés több
eredmény rekordot is előállítana, akkor a SELECT INTO formátum
végrehajtási hibát eredményez. Több rekordból álló eredmények előállítására, a
ProC-hez hasonlóan itt is kurzor szerkezetet kell létrehozni. A PL/SQL
kurzorszerkezet használata ugyanazon elvi lépésekre épül, mint a beágyazott
SQL esetén, vagyis a
- kurzor deklaráció
- kurzor megnyitás
- rekord beolvasások ciklusa
- kurzor lezárás
lépéseket tartalmazza. Az egyes lépések formátumában azonban már bizonyos
eltéréséket vehetünk észre a két rendszer, a kétféle nyelv között.
A kurzor létrehozása a PL/SQL nyelvben a deklarációs részben, s nem a
műveleti részben történik, mint az a beágyazott SQL esetén történt. A kurzor
deklarációjának formátuma:
DECLARE
...
CURSOR kurzornév (paraméterlista) IS SELECT_utasítás;
Az utasítsásban a zárójelek között megadott paraméterlista opcionális. A
deklarációs utasításban egy azonosító nevet rendelünk a kurzor szerkezethez, s
e név segítségével hajtjuk végre a lekérdezést, s e név segítségével férhetünk
hozzá az eredményhez is. A kurzor a PL/SQL nyelvben paraméteresen is
deklarálható. A paramétereket a kapcsolódó SELECT utasításban kerülnek
felhasználásra. A paraméterlista a paraméter azonosító neve mellett a paraméter
tipusát is tartalmazza.A listaelemek vesszővel vannak elválasztva egymástól. A
PL/SQL szabályai szerint a minden felsorolt paraméternek meg kell jelennie a
kapcsolódó SELECT utasításban is. A SELECT utasításnak itt a normál, INTO
opció nélküli formátuma használható csak. A következő példában egy kurzort
deklarálunk a paraméterként megadott dátum előtt született személyek nevének
és lakcímének a lekérdezésére:
DECLARE
...
CURSOR lista (datum DATE) IS SELECT nev, lakcim
FROM szemelyek WHERE szuldat < datum;
A kurzor megnyitása után a kapott adatokat egyenként lekérdezhetjük, s
módosíthatjuk is. Módosítás esetén azonban a kurzor deklarációjánál jelezni
kell, hogy nemcsak olvasásra hozzuk létre a kurzort. A módosítási igényt a
SELECT utasítás végén álló, már ismert
FOR UPDATE OF mezőlista
opcióval jelezzük. A kurzor által visszaadott rekordot a
CURRENT OF kurzornév
feltétellel jelölhetjük a megfelelő UPDATE utasításban.
A kurzor megnyitása, vagyis kijelölt lekérdezés elvégzése az
OPEN kurzornév (paraméterlista);
utasítással lehetséges, ahol a zárójelek közöttmegadott paraméterlista opcionáis
elem. A paraméterlista most konkrét értékeket tartalmaz, melyek sorra
behelyettesítődnek a deklarációkor kijelölt formális paraméterekbe, s ezen
értékekkel fog a lekérdezés végrehajtódni. Ugyanaz a kurzor többször is
végrehajtható különböző aktuális paraméterértékek mellett.
Az eredményrekordok egyenkénti lekérdezésére a
FETCH kurzornév INTO változólista;
utasítás szolgál. A változólistának vagy annyi elemi változót kell tartalmaznia,
ahány elemű az eredménytáblázat, vagy olyan rekordváltozót ad meg, mely
struktúrája megegyezik az eredménytábla struktúrájával. A vizsgált PL/SQL
változatban a kurzorpointer csak előre léptethető, mégpedig egy rekoddal.
A kurzor felhasználása után célszerű a kurzor által lefoglalt erőforrásokat
felszabadítani. Ehhez ki kell adni a
CLOSE kurzornév;
utasítást.
Az eredménytábla több rekordot is tartalmazhat, ezért a FETCH utasítást
többször egymásután is kell adni a teljes választábla feldolgozásához. Az
eredménytábla végének figyelését, vagyis annak ellenőrzését, hogy az összes
rekordot érintettük-e már, egy kurzor attributum segítségével végezhetjük el.
Az attributum, melynek alakja
kurzornév%NOTFOUND
akkor tartalmaz igaz értéket, ha elértük az eredménytábla végét, s nincs már
továábbi feldolgozásra váró rekord a kurzornál.
A lekérdező ciklus, melynek magjában a megfelelő FETCH utasítás áll, egy
alap LOOP ciklus segítségével is megoldgható, melybe a kilépéshez beteszünk
egy
EXIT WHEN kurzornév%NOTFOUND;
utasítást is.
Hibakezelés
A hibakezelő rutinokat a PL/SQL blokk harmadik komponsnese, az
EXCEPTION kulcsszóval kezdődő rész tartalmazza. A hibakezelő részben
minden egyes felismert hibatipushoz egyedi választevékenység definiálható. A
hibakód és a választevékenység összerendelése a
WHEN hibakód THEN
utasítások;
szerkezettel lehetséges. A hibakód utalhat általános, a rendszer által felismert
hibákra, és saját, egyedi hibatipusokra is. A definált tucatnyi rendszer
hibakódból néhányat mutat be az alábbi felsorolás:
NO_DATA_FOUND A SELECT utaítás vagy a FETCH nem tud
eredményrerekordot visszaadni
ZERO_DIVIDE nullával való osztás
VALUE_ERROR adatkonverziós hiba
A PL/SQL hibakezelési mechanizmusának számos előnye van a hagyományos
módszerrel szemben. A hagyományops eljárásokban a hibakezelés szorosan
összefonódott az utasításokkal: minden művelet után egyedileg kellett megadni
a hibaellenőrzző és lekezelő utasításokat. A PL/SQL ezzel szemben egy
központi helyen tárolja a hibakzelő utasításokat. E módszer pozitív vonása,
hogy
- csak egyszer kell leírni a hibakezelő kódot, áttekinthetőbb forrászöveg
- könnyebb műódosítási lehetőség, csak egy helyen kell módosítani a
hibakezelő rutint.
- nagyobb megbizhatóság, mivel a hibaellenőrzés automatikusan minden
műveletre kiterjed, nem lehet kibújni alóla, hisen automtikusan ,
mindíg érvényesul.
A hiba fellépte esetén a normál vezérlés megszakad, s hibekezelő
outasításcsoport kapja meg vezérlést. A hiba feldolgozása után a blokk
végrehajtása is befejeződik. Egy adott hibatipus esetén, ha az aktuális blokk
nem tartalmazza a mgfellő hibakezelő rutint, akkor a külső blokkok átnézésével
a rendszer megpróbálja a megfelelő hibakezelő rutint megtalálni.
Ha a hibakezelő utasításcsoportot nem csak egyetlen egy hibatipushoz szánjuk
hozzárendelni, akkor lehetőség van a WHEN után több hibatipust is felsorolni,
ahol az egyes tipusokat az OR operátorral kötjük össze. Emellett
alkalmazhatjuka WHEN után az OTHER kulcsszót is, mellyel minden,
explicite ki nem jelölt hibatipus esetén ide kerül a vezérlés.
A rendszer által észlelt hibák mellet a felhasználó maga is kiválthat végrehajtási
hibákat. Itt most nem a véletlen programozási hibákról van szó, hanem arról,
hogy a programozó explicite aktivizálhat hibatipusokat. Egy megadott tipusú
hiba kiváltása a
RAISE hibakód;
utasitással történik. Ekkor a vezérlés a megfelelő WHEN utasításra ugrik. A
saját hibatipusokat, a gyári rendszer hibatipusoktól eltérően deklarálni kell a
blokk deklarációs részében. A hibatipus létrehozása a
hibatipus EXCEPTION;
utasítással lehetséges. Az alábbi programrészlet egy saját hibatipus
használalatát mutatja be:
DECLARE
sajathiba EXCEPTION;
BEGIN
...
IF x < 16 THEN
RAISE sajathiba;
END IF;
...
EXCEPTION
...
WHEN sajathiba THEN
ROLLBACK;
...
END;
A hibatipusok láthatóságára is a változók tárgyalásánál megadott szabályok
érvényesek.
A PL/SQL utasítások áttekintése végén egy komplexebb és teljesebb
mintapéldát mutatunk be a megismert műveletek alkalmazására. A minta
PL/SQL blokk, amelyben az auto táblában a FIAT tipusú autók átlagáránál
drágább OPEL tipusú autók árát 12%-kal növeljük, ha szinkóduk P betűvel
kezdődik és 9%-kal növeljük, ha egyéb színűek.
<>
DECLARE
atlag NUMBER(10); -- átlagár
szin CHAR(3); -- színkód
CURSOR autok (atg NUMBER(10)) IS -- kurzor
SELECT szin FROM auto WHERE ar > atg AND
tip LIKE 'OPEL%' FOR UPDATE OF ar;
BEGIN
BEGIN -- alblokk az atlg kiszámitáshoz
SELECT SUM(ar)/COUNT(ar) INTO minta.atlag FROM auto
WHERE tip LIKE 'FIAT%';
EXCEPTION
-- ha nincs FIAT autó, akkor 100000 lesz az ár
WHEN ZERO_DIVIDE THEN
minta.atlag := 100000;
END;
-- kurzor megnyitása
OPEN autok (minta.atlag);
-- lekérdező ciklus
LOOP
-- rekord beolvasás
FETCH autok INTO minta.szin;
-- kilépés ha nincs több
EXIT WHEN autok%NOTFOUND;
-- módosítás
IF minta.szin LIKE 'P%' THEN
UPDATE auto SET ar = ar * 1.12 WHERE
CURRENT OF autok;
ELSE
UPDATE auto SET ar = ar * 1.09 WHERE
CURRENT OF autok;
END IF;
-- ciklus vége
END LOOP;
-- kurzor lezárása
CLOSE autok;
-- eredmények végelegesítése
COMMIT;
-- blokk vége
END minta;
A PL/SQL blokkok alkalmazása
Az alkalmazások között az egyik legfontosabb szerepet a triggerek játszák. A
triggerek, mint már említettük, az aktív adatbázisok egyik fontos komponensét
jelentik. A trigger koncepció az aktív integritási feltételek közé tartozik. A
trigger két komponensből áll, egy feltétel és egy választevékenység részből. A
trigger működési elve igen egyszerű: ha a feltétel bekövetkezik, akkor
véghrehajtódik a választevékenység. A feltételt valamilyen adatkezelő művelet
formájában adhatjuk meg. Vagyis a triggerrel az figyelhető, hogy végrehajtásra
kerül-e valamilyen kijelölt adatkezelő művelet. A trigger alkalmazásával
számos aktív integritási szabály definiálása az alkalmazásból átkerülhet az
adatbázisba. Ezáltal könnyebbé es gyorsabbá válhat az alkalmazások fejlesztése
is, nem is beszélve a nagyobb biztonságról, hiszen ebben az esetben az
alkalmazói program hibájából, a programozó feledékenysége miatt nem
sérülhet meg az adatbázis integritása, hiszen az ellenőrzés mindíg
végrehajtódik az adatbáziskezelő szoftver által.
A triggereknél a választevékenységet az Oracle RDBMS esetén PL/SQL blokk
formájában lehet megadni. Így választevékenység mindazon tevékenységi körre
kiterjedhet, amik a PL/SQL nyelv keretében leírhatók, sőt a rendszer még
bizonyos extra elemeket is bevezett a trigger koncepcióhoz történő jobb
csatlakozáshoz.
A trigger definiálásának általános formátuma következőképpen írható le:
CREATE TRIGGER triggernév kiváltó_ok PL/SQL_blokk;
ahol a kiváltó_ok a feltételt, míg a PL/SQL_blokk a választevékenységet adja
meg. A kiváltó_ok rész több komponensből áll a feltétel pontos megadására. A
fontosabb komponensek jelentése és formátuma a következő:
előtag művelet ON tábla [FOR EACH ROW[WHEN feltétel]]
Az előtag azt jelöli ki, hogy a választevékenységet a figyelt művelet elvégzése
előtt vagy után kell-e végrehajtani. Az előtagban megadható kulcs-szavak:
BEFORE művelet előtti végrehajtás
AFTER művelet utáni végrehajtás
A művelet a figyelt adatkezelő műveletek körét jelöli ki, így az INSERT,
UPDATE és DELETE utasításokra terjed ki. Egy triggerben egyidejűleg több
tevékenység is figyelhető. A módosításnál a figyelés leszűkíthető az egyes
mezőkre. Az alábbi kulcsszavak szerepelhetnek a művelet részben:
DELETE rekord törlés
INSERT rekord bővítés
UPDATE [OF mezőlista] rekord módosítás
Ha több műveletet is figyelni kívánunk, akkor az egyes műveleteket az OR
kapcsolóval lehet összekötni.
A tábla azt a táblát jelöli ki, melyre a műveletek vonatkoznak. Egy trigger csak
egy táblára vonatkozhat.
Az opcionális FOR EACH ROW tag akkor használatos, ha a megadott
választevékenységet a műveletben érintett minden egyes rekordra külön-külön
végrehajtásra kerülne. Ha ezt a tagok elhagyjuk, akkor a választevékenység az
utasítás szinten hajtódik végre, utasításonként egyszer fut le a PL/SQL blokk.
Így például a
CREATE TRIGGER t1 AFTER DELETE ON auto
BEGIN
INSERT INTO naplo VALUES ('torles', SYSDATE);
END;
triggernél a
DELETE FROM auto WHERE tip LIKE 'Fiat126%';
utasítás hatására egyetlen egyszer hívódik meg a Pl/SQl blokk, azaz egyetlen
egy új rekord fűződik be a napló állományba. Viszont a
CREATE TRIGGER t2 AFTER DELETE ON auto FOR EACH ROW
BEGIN
INSERT INTO naplo VALUES ('torles', SYSDATE);
END;
trigger esetén ugyanazon törlési műveletnél többször is meghívódik a PL/SQL
blokk, mégpedig annyiszor, ahány rekord kerül kitörlésre. Ekkor a napló
táblába annyi új rekord kerül be, ahány rekordot kitöröltek az auto táblából.
A rekordszintű triggerek esetén lehetőség van arra, hogy ne minden érintett
rekordra hívódjon meg a választevékenység, hanem csak azokra, amelyek egy
megadott feltételnek elget tesznek. E szelekciós feltételt
WHEN feltétel
opcionális taggal adhatjuk meg.
A létrehezott triggerek később módosíthatók illetve megszüntethetők az
ALTER TRIGGER és DROP TRIGGER utasításokkal.
A triggerek definiálásánál arra figyelni kell, hogy a triggerek nem izoláltak
egymástól teljesen, mivel egy az egyik triggerhez kötött álasztevékenység
kiválthat egymásik triggert, így kialakulhat egy trigger meghívási láncolat. A
triggerek tervezésénél ügyelni kell e láncolatok kialakulására és hatására is.
A rendszer azt is megengedi, hogy több triggert is definiáljunk ugyanazon
műveletek figyelésérére. Egy művelethez ugyanis létezhet BEFORE művelet
szintű, BEFORE rekord szintű, AFTER művelet szintű és AFTER rekord szintű
trigger. Az egyes trigger tipusokvégrehajtási sorrendje:
1. BEFORE művelet szintű trigger
2. ciklus az érintett rekordokra
a. BEFORE rekord szintű trigger a rekordra
b. rekord zárolása és módosítása, integritási feltételek ellenőrzése
c. AFTER rekord szintű trigger a rekordra
3. késeltett ellenőrzésű integritási feltételek ellenőrzése
4. AFTER műveleti szintű trigger
Több kiváltott tigger esetén a választevékenység minden tagjának sikeresen
végre kell hajtódnia, hogy a művelet, és minden általa kiváltott
választevékenység megőrződjön. Az integritásőrzés mechanizmusát a
későbbekben, az RDBMS-ek tárgyalásánál fogjuk majd részletezni.
A rekord szintű triggerek esetén lehetőség van arra, hogy az éppen érintett
rekord adataihoz hozzáférjünk a PL/SQL blokkon belül. A mezők értékeit két
rendszer rekordváltozón keresztül érhetjük el. A rendszer kétféle rekordváltozót
is tartalmaz, az egyik a rekord régi, módosítás előtti, míg a másik a rekord új,
módosítás utáni értékeit tartalmazza. A két rekordváltozó alapértelmezés
szerinti azonosítói:
OLD régi rekordérték
NEW új rekordérték
A PL/SQL blokkon belül e változók, mint külső, nem a PL/SQL blokkban
deklarált változók szerepelnek, ezért hivatkozáskor nevük elé egy kettőspontot
kell tenni, hasonlóan ahogy a beágyazott SQL-ben a gazdanyelvi változókat
használhatjuk. A rekordon belüli régi mezőkértékekre a
:OLD.mezőnév
míg az új értékekre a
:NEW.mezőnév
szimbólumokkal hivatkozhatunk. E rekordváltozók azonban csak a rekord
szintű triggereknél élnek, és bizonyos műveleteknél csak az egyik formátuma
él. Így az INSERT esetén nincs értelma az OLD hivatkozásnak, míg a DELETE
esetén a NEW hivatkozásnak.
Ha egy triggert több tevékenységhez kapcsoltunk, pl. beszúráshoz és
módosításhoz is, akkor a PL/SQL blokkon belül a
INSERTING beszúrás jelző
UPDATING módosítás jelző
DELETING törlés jelző
rendszer által definiált konstansok segítségével eldönthető, hogy mely művelet
volt az éppen futó választevékenység kiváltója. E változók igaz értéket vesznek
fel, ha a hozzájuk tartózó művelet volt a kiváltó tevékenység.
Az alábbi példában egy összetettebb triggert mutatunk be, amely arra szolgál,
hogy a dolgozók tábla módosítása esetén az osztályok táblát is aktualizálja. A
kapcsolat a két tábla között abban áll, hogy minden dolgozónak van egy
osztálya, ahol dolgozik, és az osztályok táblában van egy olyanmező, amely az
ott dolgozók összfizetését tartalmazza. Amikor egy dolgozó elmegy az
osztályról, vagy egy új dolgozó jön az osztályra, vagy csak a dolgozó fizetése
változik, akkor a osztályok tábla megfelelő rekordját is módosítani kell:
// trigger fejresze
// a táblába beszúrás, törlés és az oszt,fiz
// mezők módosítás figyelése
// PL/SQL blokk meghívás rekordszinten
// módosítás után
CREATE TRIGGER ossz_fiz AFTER DELETE OR
INSERT OR UPDATE OF oszt, fiz ON dolgozok
FOR EACH ROW
// PL/SQL blokk kezdete
BEGIN
// ha törlés van vagy dolgozó áthelyezés
IF DELETING OR (UPDATING AND
:OLD.oszt != :NEW.oszt) THEN
// összfizetés csökkentése a régi osztálynál
UPDATE osztalyok SET osszfiz = osszfiz - :OLD.fiz
WHERE oszt = :OLD.oszt;
END IF;
// ha új dolgozó vagy dolgozó áthelyezés
IF INSERTING OR (UPDATING AND
:OLD.oszt != :NEW.oszt) THEN
// összfizetés növelése az új osztályon
UPDATE osztalyok SET osszfiz = osszfiz + :NEW.fiz
WHERE oszt = :OLD.oszt;
END IF;
// ha fizetés módosítás
IF UPDATING AND :NEW.oszt = :OLD.oszt AND
:NEW.fiz != :OLD.fiz) THEN
// összfizetés módosítás az osztályon
UPDATE osztalyok SET osszfiz = osszfiz + :NEW.fiz
- :OLD.fiz WHERE oszt = :NEW.oszt;
END IF;
// blokk vége
END;
A triggerek alkalmazásának rugalmasságát fokozza, hogy ideiglenesen le is
lehet tiltani őket, majd egy későbbi időpontban újra lehet engedélyezni a
működését. A triggerek engedélyezése és letiltása az ALTER TRIGGER
utasítással lehetséges.
A triggerek segítségével igen hatékony integritáns ellenőrző, naplózó eszközt
kaptunk a kezünkbe, melyet célszerű alaposan elsajátitani, mivel a jöző
adatbáziskezelő rendserei egyre nagyobb mértékben fognak ezen
mchanizmusra támaszkodni.
A trigger definiálása a többi adatbázis objektum definálásához hasonlóan az
SQL nyelvenkeresztül történik. A fenti minta trigger teljes szövege egyetlen
egy SQL utasításnak fog megfelelni, amelyet pl. az SQLPlus segítségével
interaktívan is kiadhatunk.
PL/SQL blokkok definálása SQLPlus-ban
Az SQLPlus rendszerben egy utasítás végrehajtásra kerül, ha a definiáló sort
egy pontosvessző zárja le. Mivel a PL/SQL blokkban több ilyen sor is lehet és a
blokkot nem soronként, hanem együtt kell elküldeni az RDBMS-hez, ezért az
SQLPlus-ban a normál parancsbeviteli üzemmód mellett létezik egy PL/SQL
mód is, melyben több utasítást több soron keresztül is leírhatnuk, anélkül
közvetlen végrehajtásra kerülnének. Az elkészült blokkot a normál üzemmódba
visszatérve lehet majd futtatni.
A PL/SQL módra való áttérés automatikusan bekövetkezik, ha Pl/SQL blokkot
tartalmazó SQL utasításba kezdünk bele. Így például a CREATE TRIGGER
utasításban a BEGIN kulcsszó után az SQLPlus áttér PL/SQL módra. Ebben a
módban minden sor előtt egy sorszám jelenik meg. A blokk végén (vagyis a
záró END; után), egy
.
(azaz pont) utasítással térhetünk vissza a normál üzemmódba. A normál
üzemmódban a
/
utasítással hajthatjuk végre a leírt SQL utasítást.
Az SQLPlus-ban közvetlenül is írhatunk PL/SQL blokkot,melyet ütána rögtön
végre is hajthatunk. A PL/SQL blokk létrehozását a
BEGIN
utasítással kell kezdeni,mely hatására a rendszer áttér PL/SQL üzemmódba. Ott
az előzőekben leírtak alapján elkészítjük a program szövegét, majd visszatérünk
a normál üzemmódba , s ott az említett / utasításal futtathatjuk az elkészült
PL/SQL blokkot.
Tárolt eljárások
A teljesség kedvéért egy kis áttekintést adunk a tárlot eljárásokról,
függvényekről is. A tárolt eljárások egy olyan PL/SQL blokkot jelentenek,
amelyek egyrészt paraméeterzhetők, saját egyedi azonosító nevük van és az
adatbázisban lefordított formában letárolásra kerülnek. E megoldásnak az
előnye, hogy a PL/SQL blokk több helyről is elérhető, elég csak egyszer
definiálni, s gyorsabb végrehajtást tesz lehetővé, mint az egyedileg elküldött
PL/SQL blokk.
A eljárás deklarációja
CREATE PROCEDURE elárásnév (paraméterlista) AS PL/SQL_blokk;
ahol a PL/SQL_blokk az eljárás törzse, s megfelel egy szabályos PL/SQL
blokknak. A paraméterlista elemei veszővel vannak elválasztva egymástól, s
minden elem
paraméternév jelleg adattipus
hármasból áll, melyben a jelleg arra utal, hogykimenő vagy bejövő
paraméterről van-e szó. Ennek megfefelően a jelleg lehetsléges értékei:
IN bementi paraméter
OUT kimeneti paraméter
IN OUT mindkét irányba mutató adatforgalmat lebonyolító
paraméter
Az adattipus a szokásos PL/SQL adattipusok valamelyike lehet. A törzsben
szereplő PL/SQL blokkban a paraméterek ugyanúgy használhatók, mint a
normál PL/SQL változók, így nem eléjük kettőspontot sem tenni a
hivatkozáskor.
A PL/SQL blokk jellemzője, hogy nem kell benne DECLARE kulcsszót
megadni a blokk kezdetének kijelölésére.
A tárolt függvények definíciója hasonló az eljárások definíciójához, azzal a
különsbéggel, hogy itt visszatérési érték is értelmezett. A visszatéréi érték
tipusát a paraméterlistát követően, a zárójel után megadott
RETURN adattipus
taggal jelöljük. A visszatérési értéket a RETURN utasítással határozzuk meg,
mint az alábbi plda is mutatja. A példa adott tipusú autók átlagárát határozza
meg.
CREATE FUNCTION atlag (tip IN CHAR(20)) RETURN NUMBER
IS
ertek NUMBER;
BEGIN
SELECT AVG(ar) INTO ertek FROM
autok WHERE tipus LIKE tip;
RETURN (ertek);
END;
Az eljárások, függvények felhasználás az alkalmazott fejlesztő eszköztől függ,
ígyazokra majd a későbbiekben fogunk kitérni.