Delphi - Kurzorok deklarálása PL/SQL-ben

Oracle adatbázis-kezelés 7. rész

forráskód letöltése
Az Oracle rendszerekben is van lehetőség az SQL utasítások végrehajtásának adminisztrálására speciális memóriaterületekkel, úgynevezett kurzorokkal. Cikkünkben bemutatjuk, milyen típusú kurzorok segítik a fejlesztő munkáját a PL/SQL-ben, ezeket hogyan lehet deklarálni, használatukra milyen beépített műveletek állnak rendelkezésre, és hogy milyen módon lehet a kurzorokat felhasználni egy adott feladat elvégzéséhez.
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 cikksorozat harmadik részében a sémát két adattáblával egészítettük ki, melyek a cikksorozat további részeinek tábláit képezik, így az Oracle07.sql script lefuttatása csak az említett táblák hiánya esetén szükséges. Ezek meglétéről meg kell győződni, ugyanis ezek hiányában a létrehozandó további objektumok létrejönnek ugyan, de fordítási hibával. A sémába bejelentkezve le kell viszont futtatni a mellékelt Oracle07ph.sql és Oracle07pb.sql parancsállományokat a hivatkozott további objektumok létrehozásához.
Áttekintés
Ahogy említettük a bevezetőben, az Oracle az SQL utasítások végrehajtásának adminisztrálására speciális memóriaterületeket, úgynevezett kurzorokat használ. Egy adott SELECT utasításhoz tartozó kurzort megnevezhetjük, segítségével a lekérdezés végrehajtását kézben tarthatjuk, és állapotáról információkat szerezhetünk.
A PL/SQL kétféle kurzort ismer:
  • Implicit kurzor: az Orace automatikusan hozza létre, és kezeli.
  • Explicit kurzor: a felhasználó a programjában explicit módon deklarálja, és kezeli.
Implicit kurzor keletkezik a PL/SQL-ben olyankor, ha egy PL/SQL blokk törzsében SELECT utasítás szerepel. Az ilyen SELECT utasításnak azonban pontosan egy rekordot kell visszaadnia. Ha egynél több rekordot akarunk feldolgozni, explicit kurzort kell használnunk.
Az explicit kurzor kezelésére használható műveletek:
  • CURSOR: definiálja a kurzor nevét, és azt a SELECT utasítást, melyre a kurzor épül. Hatására a SELECET utasítás szintaktikus elemzése is megtörténik, de az utasítás még nem hajtódik végre.
  • OPEN: végrehajtja a SELECT utasítást. A SELECT utasítás eredményét aktív halmaznak nevezzük.
  • FETCH: Az aktuális sorból változókba tölti az adatokat. A kurzor tartalmaz egy mutatót, mely az aktuális sor számát tárolja. A FETCH utasítás ezt a mutatót mindig megnöveli eggyel, így az előző FETCH utasításhoz képest már más adatokat talál.
  • CLOSE: Lezárja a kurzort, és felszabadítja a lefoglalt erőforrásokat.
CURSOR utasítás
Explicit kurzorokat a CURSOR utasítással deklarálhatunk. A CURSOR utasítás paramétereket is tartalmazhat, melyek az OPEN utasításban kapnak aktuális értéket. A kurzor SELECT utasításában változókra is hivatkozhatunk, ezeket azonban előzőleg deklarálnunk kell. Szintaxisa a következő:
CURSOR <kurzor neve> [(<paraméter>{,<paraméter>})] IS
  <SELECT utasítás>
<paraméter> ::= <név> [IN] <típus> [:= <kifejezés>]
Lássunk erre egy példát, melyben létrehozunk egy c_trips nevű kurzort a SELECT utasítás feldolgozására:
DECLARE
  CURSOR c_trips IS
    SELECT ID, PRICE
    FROM TRIPS
    WHERE ID = 1001;
  v_id TRIPS.ID%TYPE; 
BEGIN
  ...
END;
OPEN utasítás
Végrehajtja a kurzor SELECT utasítását. Szintaxisa:
OPEN <kurzor neve> [(<aktuális paraméterek>)];
Használata:
OPEN c_trips;
FETCH utasítás
A FETCH utasítás segítségével az aktuális rekordban szereplő adatokat változókba tölthetjük, melyek lehetnek PL/SQL vagy külső változók. A felhasznált változókat a FETCH utasítás használata előtt deklarálni kell. A beolvasott rekord minden oszlopához külön változót kell deklarálni, vagy egy rekord típusú változót kell a FETCH utasításban megadni. A FETCH után a változók értékei további utasításokban használhatók fel. A FETCH utasítást és az adatokat feldolgozó utasításokat általában egy ciklus törzsén belül hajtjuk végre.
Szintaxisa:
FETCH <kurzor neve> INTO [<változó>{, <változó>}];
Használata:
FETCH c_trips INTO v_price, v_date;
CLOSE utasítás
Lezárja a kurzort, és felszabadítja erőforrásait. Szintaxisa:
CLOSE <kurzor neve>;
Használata:
CLOSE c_trips;
Az explicit kurzor attribútumai
Az implicit kurzorokhoz hasonlóan az explicit kurzor jellemzői is hozzáférhetőek. Ehhez a kurzorok következő attribútumai használhatók fel:
Attribútum Leírás
%ROWCOUNT Értéke az adott pillanatig beolvasott rekordok száma.
%FOUND Értéke TRUE, ha az adott FETCH utasítás még talál rekordot.
%NOTFOUND Értéke TRUE, ha az adott FETCH utasítás már ne talál rekordot.
%ISOPEN Értéke TRUE, ha a kurzor nyitott.
Használatuk:
IF NOT c_trips%ISOPEN THEN
  OPEN c_trips;
END IF;
FETCH c_trips
  INTO v_price, v_date;
...
A FETCH utasítás többszöri végrehajtása
A FETCH és azt követő utasítások ismételt végrehajtásánál ügyelnünk kel arra, hogy az iterációt a megfelelő pillanatban abba kell hagyni. Ha a FETCH utasítás már nem talál rekordot, akkor a %NOTFOUND attribútum értéke TRUE lesz. Ha ezután ismételt beolvasást kísérlünk meg, akkor hibajelzést kapunk.
A megelőzésre egy példa:
OPEN c_trips
LOOP
  FETCH c_trips INTO v_price, v_date;
  EXIT WHEN c_trips%NOTFOUND;
  ...
END LOOP;
CLOSE c_trips;
Hivatkozás aktuális rekordra DML utasításokban
Az explicit kurzorokban a DML utasítások WHERE szegmensében hivatkozhatunk az aktuális rekordra.
Ennek egyik módja, hogy a kurzor SELECT utasításában a hivatkozott táblák ROWID pszeudo oszlopait is kiválasztjuk, melyekre aztán a DML utasítások feltétel részében hivatkozhatunk. Használatára a mellékelt alkalmazásban láthatunk példát.
A módszer egyszerű, azonban bizonyos esetekben nem elég hatékony, mivel nem foglalja le előre a módosítandó rekordokat. A WHERE CURRENT OF utasításrész alkalmazása esetén a ROWID használata nélkül is végrehajthatunk változtatásokat az aktuális rekordon. A WHERE CURRENT OF használatához a kurzor deklarációjában levő SELECT utasításban kötelező a FOR UPDATE OF utasításrész. Nézzünk egy példát a TRIPS tábla PRICE oszlopának módosítására létrehozott kurzor használatára:
DECLARE CURSOR c_trips IS
  SELECT PRICE, DATE
  FROM TRIPS
  WHERE ID = 1001
  FOR UPDATE OF PRICE;
trips_rec c_trips%ROWTYPE;
BEGIN
  OPEN c_trips
  LOOP
    FETCH c_trips INTO trips_rec;
    EXIT WHEN c_trips%NOTFOUND;
    IF trips_rec.PRICE < 15000 THEN
      UPDATE TRIPS
      SET PRICE = 150000
      WHERE CURRENT OF c_trips;
  END LOOP;
  CLOSE c_trips;
  COMMIT;
END;
Paraméterezett kurzorok
Segítségével a SELECT utasításban olyan változókra is hivatkozhatunk, melyek a kurzor megnyitásakor kapnak konkrét értéket, így ugyanazt a kurzort többször is megnyithatjuk különböző paraméter értékekkel, és ez eltérő eredményekhez vezet. Szintaxisa:
CURSOR <kurzor neve> [(<paraméter>{,<paraméter>})] IS
  <SELECT utasítás>
<paraméter> ::= <név> [IN] <típus> [:= <kifejezés>]
A paraméterek olyan típusúak lehetnek, mint a PL/SQL skalár változói, de a paramétereknek nem adhatunk méretet és pontosságot. A paraméterekre a kurzor SELECT utasításában nevükkel hivatkozhatunk, akár a közönséges változókra. Példát a mellékelt alkalmazásban láthatunk, a CursorWithParams tárolt eljárásban implementáltunk egy ilyen típusú kurzort.
Kurzor ciklus
A PL/SQL-ben létezik a speciális kurzor FOR ciklus, mely egy kurzor által szolgáltatott rekordhalmaz elemeit veszi sorra. A kurzor FOR ciklusban a kezdő és végértékek helyén egy kurzorra hivatkozhatunk, mely a ciklus kezdetén automatikusan megnyílik, minden egyes ciklus felhoz egy következő rekordot a kurzorból, majd az utolsó rekord felhozatala után automatikusan lezáródik, és a ciklus véget ér. A kurzor FOR ciklusban a ciklusváltozó szerepét egy implicit módon, a ciklussal együtt deklarált rekordváltozó tölti be. Ebbe töltődnek a ciklusban a kurzorból felhozott rekordok. A rekord mezőire hivatkozhatunk.
Szintaxisa:
FOR <rekordnév> IN <kurzor név> [({<paraméter>})] LOOP
  <törzs>
END LOOP;
Használatára szintén a mellékelt alkalmazásban találhatunk példát, a CursorWithoutParams tárolt eljárásban implementáltunk egy ilyen típusú kurzort.
SELECT utasítás a kurzor ciklusban
A kurzor FOR ciklusok olyan lekérdezések feldolgozására is használhatók, melyet a FOR ciklus fejében adunk meg. Mivel ebben az esetben a kurzort nem explicit módon deklaráljuk, nincs neve, ezért a kurzor attribútumai nem kérdezhetők le. Szintaxisa:
FOR <rekordnév> IN <SELECT utasítás> LOOP
  <törzs>
END LOOP;
Használatát a mellékelt alkalmazás SelectInCursor tárolt eljárásának lefuttatásával próbálhatjuk ki.
A mellékelt alkalmazás
A mellékelt Oracle07ph.sql és Oracle07pb.sql scriptek futtatásával három tárolt eljárás kerül be az adatbázisba egy CursorPack nevű csomagban. Ezek rendben CursorWithParams, CursorWithoutParams és SelectInCursor nevűek.
Mindegyik eljárás egy-egy kurzor implementációjára mutat példát. A példa programjának Form-jára helyezett TTabControl vezérlő három fülének egyikére kattintva adhatók meg a szükséges paraméterek a tárolt eljárások futtatásához. Mindegyik eljárás ugyanazt a műveletet végzi el, csak más kurzor felhasználásával.
A tárolt eljárásoknak meg kell adni a TRIPS táblában található utazások eladóját azonosító ID oszlop értékét, valamint egy ár limitet, mely a tárolt eljárásban értelmeződik, és ennek megfelelően nő valamennyivel a megadott ID-vel rendelkező rekordok PRICE oszlopértéke.
A műveletek az egyes fülek alatt elhelyezett ’Végrehajtás’ feliratú gomb megnyomására indíthatók.
Amennyiben a megadott ár limitnél az adott rekord PRICE értéke kisebb, a PRICE oszlop értéke 15000-el növekszik, ellenkező esetben a kétszeresére nő.
A program kódjában minden műveletvégző gombra klikkelve ugyanaz az eseménykezelő függvény fut le, csak eltérő paraméterekkel. Ennek neve DoProcedure. Implementációjában az első paraméterben adjuk meg a tárolt eljárásban szükséges ID értéket, a második paraméterben az ár limit értéket, és a harmadik paraméterben a végrehajtó objektumot, mely TADOCommand típusú:
function TForm1.DoProcedure(id:string;price:TCaption;c:TADOCommand):integer;
var res : integer;
begin
  res := 0;
  c.Parameters[0].Value := id;
  c.Parameters[1].Value := price;
  c.Execute;
  res := c.Parameters[2].Value;
  DoProcedure := res;
end;
Az egyes gombok kezelőjében a fenti függvény visszaadott értékétől függően adunk pozitív vagy negatív üzenetet egy üzenetablakban.

Oracle adatbázis-kezelés cikksorozat