Tárolt eljárások és függvények fejlesztése

A példaprogram használata előtt létre kell hozni egy SOUSER sémát az Oracle adatbázis-rendszerünkben, és fel kell ruházni DBA jogosultsággal. A sémába bejelentkezve le kell futtatni a mellékelt Oracle03.sql, Oracle03p.sql és Oracle03f.sql script-eket a hivatkozott objektumok létrehozásához.

PL/SQL vezérlő szerkezetek

A programnyelvek megismerésekor nagy hangsúlyt fektetünk az adott nyelv szintaktikai előírásainak betartására, hiszen ezen alapok birtokában vagyunk csak képesek hibátlan kódsorokat megírni. Mikor ugyanis kódblokkokat hozunk létre a PL/SQL szintaktikának megfelelően, akkor természetesen programot írunk. Az alprogramok kezelése viszont egyedi az Oracle rendszerben.

A tárolt alprogram létrehozása után az adatbázisban fizikailag nem keletkezik új objektum (tehát az alprogramoknak nincs külön szegmensük), hanem megfelelő adatszótár nézetekben tárolódik az alprogramok neve, forrásszövege és egyéb velük kapcsolatos fontos információk, melyek megfelelő jogosultságok birtokában az adatszótárból ki is olvashatók. Az alprogramok futás előtt fordulnak le tárgykóddá, melyet a PL/SQL gép végrehajt. Ezek a kódok a P-kódok. A P-kód az adatszótár nézeteken keresztül nem hozzáférhető, csak közvetett módon az adott alprogram végrehajtásával.

A PL/SQL által támogatott vezérlő szerkezetek segítségével építhetünk logikát kódsorainkba. Ezek a következők:

Jelentéstartalmát tekintve azonos más procedurális programnyelv hasonló utasításával, és lehetővé teszi, hogy a végrehajtás egy feltételes utasítás kiértékelésének eredményétől függően elágazzon. Szintaxisa a következő:

IF <feltétel> THEN
  {<utasítás>;}
[{ELSIF <feltétel> THEN {<utasítás>;}}]
[{ELSE <utasítás>;}]
END IF;

Ha a feltétel értéke FALSE vagy NULL, akkor az ELSE ág hajtódik végre, egyébként a THEN ág. A vezérlés az END IF után folytatódik. Tetszőleges mélységben egymásba ágyazható, minden IF ágat egy END IF zár le.

A PL/SQL nem tartalmaz többszörös elágazást (CASE struktúrát). Ez kiváltható egymásba skatulyázott ELSIF utasításokkal. Ilyenkor csak az első IF-et kell lezárni END IF-el. Például

IF a = 1 THEN <utasítás1>;
ELSIF a = 2 THEN <utasítás2>;
...
END IF;

A legegyszerűbb ciklus a LOOP és END LOOP közé zárt utasítás sorozat. Amennyiben nincs a ciklus törzsében valamilyen feltételes utasítás, mely feltételes módon kilép, a ciklus végtelen. Ilyen utasítás az EXIT, mely az END LOOP-ra adja a vezérlést. Például

LOOP
  counter := counter + 1;
  ...
  IF counter = 10 THEN
    EXIT;
  END IF;
END LOOP;

A FOR utasításban a LOOP utasítás előtt egy fejrész található, mely meghatározza, hogy a ciklus hányszor fusson le. Szintaxisa:

FOR <ciklusváltozó> IN [REVERSE] <kezdőérték>..<végérték> LOOP
  {<utasítás>;}
END LOOP;

Alapértelmezésben a ciklusváltozó a kezdőértéktől a végértékig nő. A REVERSE használatakor csökken.

A ciklusváltozó a FOR ciklus implicit lokális változója, melyet nem kell külön deklarálni, nem is lehet. Ugyanis a blokkban explicit módon, azonos néven deklarált változót a ciklusváltozó elfedi. Ebből eredően a ciklus után is használni kívánt ciklusváltozót egy a blokk előtt deklarált változóba el kell menteni. EXIT utasítás itt is szerepelhet. Például

DECLARE c NUMBER := 0;
...
FOR i IN 0..10 LOOP
  c := c + 1;
END LOOP;

A WHILE ciklus szintaxisa a következő:

WHILE <feltétel> LOOP
  {<utasítás>;}
END LOOP;

A feltétel a ciklusba való belépéskor és minden ismétlődéskor kiértékelődik, és ha értéke FALSE, a ciklus véget ér. Például

DECLARE c NUMBER := 10;
...
WHILE c > 0 LOOP
  ...
  c := c – 1;
END LOOP;

Tárolt eljárások és függvények fejlesztése

Az alprogram, a PL/SQL utasításabsztrakciós eszközeként lehetővé teszi, hogy egy kódblokkot névvel és formális paraméterlistával lássunk el. A blokk törzsét a későbbiekben a név és az aktuális paraméterek megadásával egyetlen utasításként hajthatjuk végre. Az alprogramoknak két fajtája van: az eljárás és a függvény. Mindkettő rendelkezik paraméterlistával, de csak a függvény ad vissza értéket kötelezően.

Közös jellemzőjük, hogy az adatbázisban tárolódnak, bármely adatbázisszintű eszközből, vagy alkalmazásból hívhatók. Az alkalmazások számára az elérhetőséget a hozzáférési jogosultságok szabályozzák. A tárolt alprogramok az SQL Plus környezetből is elérhetőek az EXECUTE parancs kiadásával, megadva az eljárás/függvény nevét, paramétereit.

Az eljárások készítése egy szövegszerkesztővel lehetséges, a kész forrást egy parancsállományba mentjük. A forrásállományban a létrehozó utasítást le kell zárni egy „/” (per) jellel.

Új eljárást a CREATE PROCEDURE utasítással hozhatunk létre. Deklarálhatjuk a formális paraméterlistát, valamint a törzsben végrehajtódó utasításokat. Szintaxisa:

CREATE [OR REPLACE]
PROCEDURE <eljárásnév> [(<paraméter>{,<paraméter>})] IS
  {<lokális változó deklaráció>;}
BEGIN
  {<eljárásnév>;}
END [<eljárásnév>];
...
<paraméter> ::= <név> [IN|OUT|IN OUT] <típus> [:=<default érték>]

Ha az eljárás már létezik, elkerülendő a DROP PROCEDURE utasítást, használhatjuk az OR REPLACE opciót. A PL/SQL blokkot minden esetben a BEGIN kulcsszóval, vagy egy lokális változó deklarációjával kezdjük. A blokkot lezárhatja akár END, akár END <eljárásnév> is. Az értékek átvételét vagy visszaadását a formális paraméterlista teszi lehetővé. A paraméterek IN, OUT és IN OUT módúak lehetnek.

Változóink típusának megadásához használhatjuk a %TYPE és %ROWTYPE attribútumokat.

Új függvényt a CREATE FUNCTION utasítással hozhatunk létre. A RETURN részben kell megadnunk a függvény által visszaadott érték típusát. Szintaxisa:

CREATE [OR REPLACE]
FUNCTION <függvénynév> [(<paraméter>{,<paraméter>})]
RETURN <visszaadott érték típusa> IS
  {<lokális változó deklaráció>;}
BEGIN
  {<eljárásnév>;}
END [<függvénynév>];
...
<paraméter> ::= <név> [IN|OUT|IN OUT] <típus> [:=<default érték>]

Ha az eljárás már létezik, elkerülendő a DROP FUNCTION utasítást, használhatjuk az OR REPLACE opciót.

Természetesen használat előtt tesztelhetjük eljárásunkat, illetve függvényünket az SQL Plus környezet segítségével. Például az SQL Plus környezetbe a következő sorokat begépelve meghívhatunk egy „f” nevű függvényt param2 paraméterrel, amely visszaad egy értéket a param1 paraméternek értéket adva. Hozzunk létre egy változót a VARIABLE utasítással, mely tárolja a függvény visszatérési értékét:

VARIABLE param1 NUMBER;

Adjuk meg a bemenő paramétert, mely most egy NUMBER érték (blokkbeli felhasználása most mellékes):

ACCEPT param2 PROMPT 100;

Futtassuk a függvényt az EXECUTE paranccsal:

EXECUTE :param1 := f(¶m2);

Írassuk ki a param1 változó értékét:

PRINT param1;

Példa alkalmazás

A mellékelt parancsállományokat lefuttatva két adattábla kerül az SOUSER sémába, TRIPS és DEALERS. A DEALERS tábla tartalmazza egy képzeletbeli utazási iroda dolgozóinak adatait, a TRIPS tábla pedig az értékesített utak adatait. A TRIPS tábla ID nevű oszlopa elsődleges kulcs a DEALERS táblában. Sorozatunk további részeiben ezeket a táblákat használjuk.

Szintén az adatbázis része lesz egy GetSumPrice nevű tárolt eljárás, valamint egy GetSumPriceFunction nevű függvény, melyek ugyanazt a feladatot végzik el, csak eltérő módon szolgáltatják eredményeiket. Ezen alprogramok bemenő paraméterként egy nevet várnak, majd ennek segítségével a két táblából meghatározzák az adott dolgozó által értékesített utak értékének összesített értékét. A tárolt eljárásunk egy kimenő paraméterben adja vissza az eredményt, míg a függvény egy visszatérési értékben.

A mellékelt programban a fő Form alsó táblázatában tekinthető meg a TRIPS tábla adathalmaza, míg a DEALERS tábla értékeit a „DEALERS tábla” feliratú gombra kattintva megjelenő Form-on nézhetjük meg. Ezekből követhetjük az eredmény helyességét.

A Form-on elhelyezett TabControl kontrol megfelelő fülét választva, majd a dolgozó nevét megadva a ComboBox kontrolban kapható meg az eredmény az „Összesítés” gomb megnyomásával.

A script-ek állományaiban (Oracle03p.sql és Oracle03f.sql) megtekinthető az eljárás és függvény kódja, ezt külön nem részletezzük. Az elvégzendő feladat egyszerű, a példa inkább a szintaktikai szabályok bemutatására összpontosít.