Delphi - TIBStoredProc komponens

Interbase 7. rész

forráskód letöltése
A sorozat most olvasható része a tárolt eljárásokról szól. Megismerjük, hogy mi is a tárolt eljárás, milyen utasítással hozhatjuk létre, hogyan használhatjuk Delphi-ből és hogy milyen előnnyel bír a használatuk. A mellékelt példa futtatása előtt az IBConsole segítségével futtassuk le a mellékelt tárolt eljárásokat tartalmazó SQL utasításokat.

A tárolt eljárások (Stored Procedures) kiterjesztett SQL-ben írt rutinok. A tárolt eljárást meg kell hívnunk, akár a kliens oldali programból is. Tulajdonképpen a Delphi egységek (unit-ok) globális függvényeihez hasonlóak.
Előnyei:
  • Utasításaink a szerveren futnak le, a klienshez csak az eredmények utaznak, ezzel csökkentve a hálózati forgalmat, de növelve az adatok biztonságát.
  • Éles rendszerekben a tárolt eljárásokat általában már az adatbázis megtervezésekor megírjuk, így később minden felhasználó képes lesz ezeket meghívni anélkül, hogy a rutinokat neki kellene megírni és a hálózaton keresztül elküldenie.
  • Végrehajtásuk gyorsabb, mint az alkotó SQL utasítások lefuttatása, mivel a tárolt eljárás ellenőrzött, optimalizált és előfordított állapotban kerül tárolásra.
  • Segítségükkel a jogosultságok is kényelmesebben és hatékonyabban szabályozhatók. Csak a tárolt eljárások végrehajtásának jogát kell szabályoznunk, nem pedig az általa használt táblák, nézetek esetleges bonyolult jogosultságrendszerét. Ez csak akkor hatásos, ha az érintett táblák közvetlen elérését megtiltjuk, és az adatokhoz csak a tárolt eljárásokon keresztül férhetünk hozzá.
Tárolt eljárásokban általában nagy adatmennyiséget feldolgozó, a jogosultságokhoz kötött tevékenységeket és a bonyolult SELECT utasításokat szoktuk megírni.

A pascal eljárásokhoz hasonlóan a tárolt eljárásoknak is lehet egy vagy több típussal megadott paramétere és visszatérési értéke. A visszatérési érték lehet egy eredmény halmaz is, ha belül egy SELECT utasítás áll.

Példaként írjunk egy tárolt eljárást, amelyik megszámolja, hogy egy meghatározott kulcsszóhoz hány darab rekord tartozik.
CREATE PROCEDURE KULCSSZO_SZAMOLAS(K_SZO CHAR(15))
RETURNS (K_SZO_SZAM DECIMAL (6,1))
AS
BEGIN
  SELECT SUM(ESM_AZONOSITO)
  FROM ESEMENYEK
  WHERE KULCSSZO = :K_SZO
  INTO :K_SZO_SZAM;
END
Figyeljük meg az INTO használatát, amely azt mondja meg a kiszolgálónak, hogy a SELECT eredményét a K_SZO_SZAM visszatérési értékbe kell tenni. Egy tárolt eljárás módosításához, illetve törléséhez az ALTER PROCEDURE és a DROP PROCEDURE utasításokat használhatjuk.

A Delphiben az eredményhalmazt visszaadó tárolt eljárásokat az IBQuery és az IBStoredProc komponensekkel is lekérdezhetjük. IBQuerynél a következő kódot használhatjuk az SQL tulajdonságában:
SELECT * FROM SZIN('PIROS')
Általában egyszerűbb az IBStoredProc komponens használata, ha az eljárásnak több vagy bonyolult paramétere van. Ezzel a komponenssel elérhetjük a kiszolgáló összes tárolt eljárását és egy egyszerű párbeszédablakkal megadhatjuk a paramétereket.

A komponens használata:
  • Dobjunk egy adatbázis, egy tárolt eljárás és egy tranzakció komponenst a Form-ra.
  • Állítsuk be az IBStoredProc komponens Database és Transaction tulajdonságait.
  • Állítsuk be a DatabaseName tulajdonságot az IBDatabase komponensnél. Erről már egy korábbi részben beszéltünk. Majd kapcsolódjunk az adatbázishoz.
  • Határozzuk meg a komponens StoredProcName tulajdonságát. A lenyíló listában megtalálhatjuk az adatbázis összes elérhető tárolt eljárását.
  • Kattintsunk kétszer a Params tulajdonságra. Ekkor egy szerkesztődoboz jelenik meg, amelynek segítségével beállíthatjuk a be- és kimenő paraméterek tulajdonságait és értékeit. Itt mindezt csak akkor végezhetjük el, ha az előző pontban leírtakat végrehajtottuk.
  • Ezt követően, amikor a programunk fut, futtathatjuk a tárolt eljárást, és kiolvashatjuk a visszaadott értékeket. A tárolt eljárás akkor is futtatható, ha a komponensre jobb egérgombbal kattintunk és a megjelenő helyi menüből kiválasztjuk az Execute pontot.
Tárolt eljárásokat futási időben is létrehozhatunk programunkban. Ehhez egy IBQuery komponens szükséges a megfelelő beállításokkal. A következőket kell tennünk:
With IBQuery1 do begin
   ParamCheck := False;
   With SQL do begin
      Clear;
      Add('CREATE PROCEDURE MAX_TUL_SZAMA');
      Add('RETURNS (MAX_TUL_SZAM DECIMAL(8,2)');
      Add('AS');
      Add('BEGIN');
      Add('  SELECT MAX(TUL_SZAMA) ');
      Add('  FROM GEPJARMUVEK');
      Add('  INTO :MAX_TUL_SZAM; ');
      Add('  SUSPEND; ');
      Add('END ');
  End;
  ExecSQL;
End;
Tárolt eljárások előkészítése

Ha tárolt eljárásokat szeretnénk használni, javasolt azokat először előkészíteni és csak azután futtatni. Az előkészítés tervezési időben automatikusan megtörténik, ha bezárjuk a paraméterek megadásánál használt szerkesztőablakot - és természetesen jól adtuk meg a paramétereket -, futási időben pedig meg kell hívnunk a Prepare metódust:
IBStoredProc1.Prepare;
Ha futási időben megváltoztattuk a tárolt eljárás paramétereinek értékét, újra meg kell hívnunk a Prepare eljárást. Ezt követően futtathatjuk a tárolt eljárásunkat. A következő kódrészlet ezt mutatja be:
IBStoredProc1.Params[0].AsString :=  Edit1.Text;
IBStoredProc1.Prepare;
IBStoredProc1.ExecProc;
Ha az előkészítést elfelejtettük volna, a tárolt eljárás futtatása előtt a rendszer automatikusan elvégzi azt, majd az előkészítés által lefoglalt erőforrásokat felszabadítja. Ha többször meg szeretnénk hívni egy tárolt eljárást, először készítsük azt elő, majd futtassuk annyiszor, ahányszor csak szeretnénk, és a végén az UnPrepare metódus segítségével szabadítsuk fel az erőforrásokat.

Ha a tárolt eljárás eredményhalmazzal tér vissza

Ha eredményhalmazzal tér vissza a tárolt eljárás, akkor az IBQuery komponenst szoktuk használni az eredmény megjelenítéséhez.
  • Tegyünk a Form-ra egy IBQuery komponenst és inicializáljuk a megfelelő tulajdonságok beállításával.
  • Az SQL tulajdonságban írjuk meg a SELECT utasítást, amely egy tárolt eljárásra hivatkozik az adatbázistábla neve helyett.
  • Ha az eljárás bemeneti paraméterekkel rendelkezik, adjuk meg azokat a Params tulajdonságnál.
  • Állítsuk az Active tulajdonságot igazra, vagy futáskor hívjuk meg az IBQuery komponens Open metódusát.
Egy adathalmazzal visszatérő tárolt eljárást a következőképpen kell megírnunk SQL utasításban:
CREATE PROCEDURE GET_GK_RENDSZAM (GYARTM CHAR(30))
RETURNS (R_SZAM CHAR(7)) AS
BEGIN
  FOR SELECT RENDSZAM  FROM GEPJARMUVEK  WHERE GYARTMANY = :GYARTM
  INTO :R_SZAM
  DO
    SUSPEND;
END;
Az IBQuery SQL tulajdonságához a következőket írhatjuk:
SELECT * FROM GET_GK_RENDSZAM('AUDI')
A fenti tárolt eljárás megadja az összes paraméterben kapott dolgozó projektjét.
Először megvizsgáljuk, hogy megfelel-e a dolgozó száma, majd végrehajtjuk a SUSPEND utasítást. Ennek hatására az éppen megfelelő project bekerül az eredményhalmazba. A FOR utasítás biztosítja, hogy a tábla minden rekordját megvizsgáljuk. Ha az ilyen típusú tárolt eljárásokat az IBStoredProc komponenssel vagy az EXECUTE PROCEDURE SQL utasítással futtatnánk, csak az első megfelelő rekord értékét kapnánk vissza.

Tárolt eljárások paraméterei

A tárolt eljárásoknál négyféle paramétert használhatunk:
  • Bemeneti paraméter, amelyet átadunk a tárolt eljárásnak feldolgozás végett.
  • Kimeneti paraméter, amelyet a tárolt eljárás ad vissza, mint eredményt.
  • Be- és kimeneti paraméterek, amelyeket átadunk a tárolt eljárásnak, hogy feldolgozza, és ugyanabba a paraméterbe kapjuk vissza az eredményt is.
  • Eredmény paraméter, amelyik egy hiba vagy státusz értékkel tájékoztatja az alkalmazásunkat a végrehajtásról. Egy tárolt eljárásnak csak egy ilyen paramétere lehet.
Bemeneti paraméterek

Az utasítás végrehajtásához szükséges adatokat juttatjuk el a tárolt eljáráshoz ezen paraméterek segítségével. Használata:
With IBStoredProc1 do begin
  ParamByName('ESM_AZONOSITO').AsInteger := 25;
  ExecProc;
End;
Kimeneti paraméterek

A végrehajtott tárolt eljárás ezen paraméterben adja vissza az eredményt. Használata:
With IBStoredProc1 do begin
  ExecProc;
  Edit1.Text := IntToStr(Params[0].AsInteger);
End;
Be-és kimeneti paraméterek

Az előbb tárgyalt két paramétertípus kombinációja. A be- és kimeneti paramétereket külön meg kell jelölni a tárolt eljárás létrehozásakor.
CREATE OR REPLACE UPDATE_THE_TABLE(IN_OUTVAR IN OUT INTEGER) AS
BEGIN
UPDATE TABLE SET NUMBERFLD = IN_OUTVAR WHERE KEYFIELD = 0;
IN_VAR := 1;
END UPDATE_THE_TABLE;
A fenti tárolt eljárás csak egy példa arra, hogy hogyan definiáljunk be-és kimeneti paramétert. Az eljárás egy tábla oszlopát frissíti, ha teljesül a feltétel, és a kimeneti változónak egy értéket ad. Használata:
With IBStoredProc1 do begin
  ParamByName('IN_OUTVAR').AsString := 13;
  ExecProc;
  IntValtozo := ParamByName('IN_OUTVAR').AsInteger
End;
Eredmény paraméterek

Néhány tárolt eljárás visszaadhat egy eredmény paramétert. Ezt gyakran arra használjuk, hogy egy hibáról vagy a feldolgozott rekordok számáról tájékoztatást kaphassunk.

Paraméterek létrehozása futási időben

A következőket kell tennünk a forráskódban:
Var
   P1,P2  :  TParam;
…
Begin
…
with StoredProc1 do begin
  SortedProcName := 'GET_GK_RENDSZAM';
  Params.Clear;
  P1 := TParam.Create(Params, ptInput); P2 := TParam.Create(Params, ptOutput);
  Try
     Params[0].Name := 'GYARTM'; Params[1].Name := 'R_SZAM';
     ParamByName('GYARTM').AsString := 'AUDI';
     ExecProc;
     Edit1.Text := ParamByName('R_SZAM').AsString;
  Finally
    P1.Free;  P2.Free;
  End;
End;
…
Futási időben beállítjuk a komponens StoredProcName tulajdonságát, létrehozzuk a paramétereket, meghatározzuk a nevüket és a bemeneti értékeket beállítjuk. Mindezek után futtathatjuk a tárolt eljárást, majd kiolvassuk a visszatérési értékeket. Fontos, hogyha valami hiba történik, a paraméterek által lefoglalt erőforrások felszabaduljanak, ezért használjuk a try … finally blokkot.

Hibakezelés tárolt eljárásokban

A tárolt eljárásokban bekövetkező hibákat többféleképpen is kezelhetjük. Erre most két megoldást ismertetünk.

Kivételek

Az adatbázisban is létrehozhatunk kivételeket, amelyek egy hiba esetén közlik a felhasználóval, hogy miért nem lehetett végrehajtani az utasításokat.
Kivételt a CREATE EXCEPTION name '<message>' utasítással hozhatunk létre. Ezt az utasítást csak az InterBase adatbázis-kezelő rendszer esetében használhatjuk, például Oracle rendszerben már nem. A name paraméter egy egyedi nevet jelent, amellyel majd meghívhatjuk a kivételkezelő eljárást. A message paraméterben a felhasználónak szánt üzenet szövege kerül. Most hozzunk létre egy kivételt:
CREATE EXCEPTION KULSOKULCSHIBA 'A dolgozohoz megadott osztaly nem szerepel a nyilvantartasban.'
A fenti utasítást kell futtatni, például egy IBQuery komponens SQL tulajdonságaként megadva. A kivétel módosításához, illetve törléséhez az ALTER EXCEPTION, illetve a DROP EXCEPTION utasításokat kell használnunk.
A hiba kiváltásához használhatunk oszlopvizsgálatot:
IF (mezonev >0) THEN EXCEPTION KULSOKULCSHIBA, 
vagy megvizsgálhatjuk az SQLCODE változó értékét:
WHEN SQLCODE -530 DO EXCEPTION KULSOKULCSHIBA.
Ha nem történik semmilyen hibát okozó művelet, akkor az SQLCODE értéke nulla.

Másik lehetséges út, ha a mi magunk létrehozta szöveget jelenítjük meg hiba esetén.
SET TERM !!;
CREATE PROCEDURE NUMBERPROC (A INTEGER, B INTEGER)
RETURNS (E CHAR(60)) AS
BEGIN
  BEGIN
  INSERT INTO TABLE1 VALUES (:A, :B);  WHEN SQLCODE -803 DO
  E = 'Hiba a beszúrási művelet közben - A értékű rekord már van';
  END;
END!!
SET TERM;  !!
A fenti eljárásban lekezeljük a (-803)-as hibát. Ez akkor következik be, amikor be szeretnénk szúrni egy már létező értéket egy olyan oszlopba, amelynek az indexelése ezt nem engedi meg. Ha a TABLE1 első oszlopában már szerepel az az érték, amit az A paraméter segítségével szeretnénk beszúrni a táblába, akkor az E kimenő paraméter a hibaüzenetet fogja tartalmazni. Ennek értékét vizsgálva eldönthetjük az alkalmazásunkban, hogy az utasítás sikeresen lefutott-e, vagy sem.

A SET TERM utasítás feladata, hogy az első meghíváskor egy új lezáró karaktert definiáljon (a példában ez !!), a második utasítás pedig visszaállítja az eredeti pontosvesszőt (;), mint lezáró karaktert.


Nézzük a komponens legfontosabb tulajdonságait:
  • ParamCount: Az aktuális lekérdezéshez tartozó paraméterek számát adja meg.
  • Params: A lekérdezéshez szükséges paramétereket adhatjuk meg.
  • Prepared: Jelzi, ha a lekérdezés már elő van készítve.
  • StoredProcedureNames: Az adatbázisban lévő tárolt eljárások listáját szolgáltatja.
  • StoredProcName: A futtatni óhajtott tárolt eljárás neve.
  • Database: Az adatbázis neve.
  • Transaction: A tranzakciós komponens kapcsolatát adja meg.
Események:
Nagyon sok, a TStoredProc komponensnél már megismert tulajdonsággal találkozhatunk. Itt csak az újabbakkal ismerkednénk meg.
  • AfterDatabaseDisconnect: Az esemény akkor aktiválódik, miután az adatbázissal megszakadt a kapcsolatunk.
  • BeforeDatabaseDisconnect: A kapcsolat megszakadása előtt váltódik ki ez az esemény.
  • DatabaseFree: Az esemény bekövetkezik, amint az adatbázis által használt memória felszabadul.
  • AfterTransactionEnd: Egy tranzakció befejezése után aktiválódó esemény.
  • BeforeTransactionEnd: A tranzakció befejezése előtt következik be ez az esemény.
  • TransactionFree: Az esemény akkor váltódik ki, miután a tranzakció által használt memória felszabadul.
Metódusok:
  • CopyParams: Az egyik tárolt eljárás paramétereit egy másik paraméterlistába másolja.
  • TIBStoredProc1.CopyParams(TIBStoredProc2.Params);
  • ExecSQL: Olyan SQL utasítások futtatására használhatjuk, amelyek nem adnak vissza eredményhalmazt.
  • ParamByName: A paraméter nevével hivatkozhatunk a paraméterre.
  • Edit1.Text := IBQuery1.ParamByName('Nev').AsString;
  • Prepare: Arra használhatjuk ezt az eljárást, hogy a lekérdezés számára erőforrást foglaljunk le, és az optimalizálása megtörténhessen. Ezáltal ha többször is meghívjuk a lekérdezést, akkor az gyorsabban fut le. A Delphi rendszer automatikusan optimalizálja a lekérdezést a futtatása előtt, ha mi ezt nem tettük meg, majd a futás után felszabadítja az erőforrásokat. Ezt annyiszor teszi meg, ahányszor az adott lekérdezés lefut.
  • Unprepare: Felszabadítja az utoljára meghívott Prepare metódus által lefoglalt erőforrásokat, mind a kliens, mind a szerver oldalán.

Interbase cikksorozat