Egy SQL utasítás feldolgozásához az Oracle a memóriában egy speciális területet használ, melyet környezeti területnek hívunk. A környezeti terület információkat tartalmaz az utasítás által feldolgozott sorokról, lekérdezés esetén tartalmazza a visszaadott sorokat (amit aktív halmaznak nevezünk) és tartalmaz egy mutatót az utasítás belső reprezentációjára.
A kurzor olyan eszköz, amellyel megnevezhetjük a környezeti területet, segítségével hozzáférhetünk az ott elhelyezett információkhoz és amennyiben az aktív halmaz több sort tartalmaz, azokat egyenként elérhetjük, feldolgozhatjuk.
A PL/SQL kétfajta kurzort kezel, az explicit és az implicit kurzort. A PL/SQL automatikusan felépít egy implicit kurzort minden DML utasításhoz, beleértve az olyan lekérdezéseket is, amelyek pontosan egy sort adnak vissza. A több sort visszaadó (pontosabban az akárhány sort visszaadó) lekérdezések eredményének kezeléséhez viszont explicit kurzort célszerű használnunk.
Egy explicit kurzor kezelésének négy lépése van, ezek az alábbiak:
kurzor deklarálása;
kurzor megnyitása;
sorok betöltése PL/SQL változókba;
kurzor lezárása.
Egy kurzordeklaráció elhelyezhető blokk, alprogram vagy csomag deklarációs részében. A kurzor deklarációjának formája a következő:
CURSOR név [(paraméter[,paraméter]…)]
[RETURN sortípus] IS select_utasítás;
A név megnevezi a kurzort. A paraméter a kurzor formális paramétere. Alakja:
paraméter_név [IN] típus [{:=|DEFAULT} kifejezés]
Az alprogramok formális paramétereinél elmondottak itt is érvényesek. A paraméterek lokálisak a kurzorra nézve és szerepelhetnek az IS után megadott SELECT utasításban minden olyan helyen, ahol konstans szerepelhet.
A sortípus a kurzor által szolgáltatott érték típusa, amely rekord vagy adatbázistábla sorának
típusa lehet. Alakja:
{{ab_tábla_név| kurzor_név| kurzorváltozó_név}%ROWTYPE| rekord_név%TYPE| rekordtípus_név}
Az ab_tábla_név egy olyan adatbázisbeli tábla vagy nézet neve, amelyik a deklarációnál ismert.
A kurzor_név egy korábban deklarált explicit kurzor, a kurzorváltozó_név egy kurzorváltozó neve.
A rekord_név egy korábban deklarált rekord neve.
A rekordtípus_név egy korábban deklarált RECORD típus neve.
A select_utasítás egy INTO utasításrészt nem tartalmazó SELECT utasítás, amely a kurzor által feldolgozható sorokat állítja elő. A kurzor paraméterei csak itt használhatók fel.
Példák
/* Megadja az ügyfeleket ábécé sorrendben.
Van RETURN utasításrész. */
CURSOR cur_ugyfelek RETURN ugyfel%ROWTYPE IS
SELECT * FROM ugyfel
ORDER BY UPPER(nev);
v_Uid ugyfel.id%TYPE;
/* Megadja annak az ügyfélnek a nevét és telefonszámát, melynek
azonosítóját egy blokkbeli változó tartalmazza.
Nincs RETURN utasításrész, hisz a kérdésből ez úgyis kiderül. */
CURSOR cur_ugyfel1 IS
SELECT nev, tel_szam FROM ugyfel
WHERE id = v_Uid;
/* Megadja a paraméterként átadott azonosítóval rendelkező ügyfelet.
Ha nincs paraméter, akkor a megadott kezdőérték érvényes. */
CURSOR cur_ugyfel2(p_Uid ugyfel.id%TYPE DEFAULT v_Uid) IS
SELECT * FROM ugyfel
WHERE id = p_Uid;
/* Megadja az adott dátum szerint lejárt kölcsönzésekhez az ügyfél nevét,
a könyv címét, valamint a lejárat óta eltelt napok számának egész részét.
Ha nem adunk meg dátumot, akkor az aktuális dátum lesz a kezdeti érték. */
CURSOR cur_lejart_kolcsonzesek(p_Datum DATE DEFAULT SYSDATE) IS
SELECT napok, u.nev, k.cim
FROM ugyfel u, konyv k,
(SELECT TRUNC(p_Datum, 'DD') - TRUNC(datum)
- 30*(hosszabbitva+1) AS napok, kolcsonzo, konyv
FROM kolcsonzes) uk
WHERE uk.kolcsonzo = u.id
AND uk.konyv = k.id
AND napok > 0
ORDER BY UPPER(u.nev), UPPER(k.cim);
/* Lekérdezi és zárolja az adott azonosítójú könyv sorát.
Nem az egész táblát zárolja, csak az aktív halmaz elemeit!
Erre akkor lehet például szükség, ha egy könyv kölcsönzésénél
ellenőrizzük, hogy van-e még példány.
Így biztosan nem lesz gond, ha két kölcsönzés egyszerre történik
ugyanarra a könyvre. Az egyik biztosan bevárja a másikat. */
CURSOR cur_konyvzarolo(p_Kid konyv.id%TYPE) IS
SELECT * FROM konyv
WHERE id = p_Kid
FOR UPDATE OF cim;
/* Kisérletet tesz az adott könyv zárolására.
Ha az erőforrást foglaltsága miatt nem
lehet megnyitni, ORA-00054 kivétel váltódik ki. */
CURSOR cur_konyvzarolo2(p_Kid konyv.id%TYPE) IS
SELECT * FROM konyv
WHERE id = p_Kid
FOR UPDATE NOWAIT;
/* Ezek a változók kompatibilisek az előző kurzorokkal.
Döntse el, melyik kurzor melyik változóval kompatibilis! */
v_Ugyfel ugyfel%ROWTYPE;
v_Konyv konyv%ROWTYPE;
v_Unev ugyfel.nev%TYPE;
v_Utel_szam ugyfel.tel_szam%TYPE;
A kurzor megnyitásánál lefut a kurzorhoz rendelt lekérdezés, meghatározódik az aktív halmaz és az aktív halmazhoz rendelt kurzormutató az első sorra áll rá. Ha a SELECT utasításban van FOR UPDATE utasításrész, akkor az aktív halmaz sorai zárolódnak. A megnyitást a következő utasítással végezhetjük:
OPEN kurzor_név [(aktuális_paraméter_lista)];
Az aktuális és formális paraméterek viszonyára és a paraméterátadásra vonatkozó információkat a 6.2. alfejezet tartalmazza.
Megnyitott kurzort újra megnyitni nem lehet. Megnyitott kurzorra kiadott OPEN utasítás a CURSOR_ALREADY_OPEN kivételt váltja ki. A megnyitott kurzor neve nem szerepeltethető kurzor FOR ciklusban.
Példák
BEGIN
v_Uid := 15; -- József István ügyfél azonosítója
/* Mely sorok lesznek az aktív halmaz elemei ? */
OPEN cur_ugyfel1;
OPEN cur_ugyfel2(15);
/* Mivel a paraméter mindig IN típusú,
kifejezés is lehet aktuális paraméter. */
OPEN cur_lejart_kolcsonzesek(TO_DATE('02-MÁJ. -09'));
BEGIN
OPEN cur_lejart_kolcsonzesek; -- CURSOR_ALREADY_OPEN kivételt vált ki!
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('Hiba: ' || SQLERRM);
END;
⋮
END;
Az aktív halmaz sorainak feldolgozását a FETCH utasítás teszi lehetővé, melynek alakja:
FETCH {kurzor_név|kurzorváltozó_név}
{INTO{rekord_név|változó_név[,változó_név]…}|
BULK COLLECT INTO kollekciónév[,kollekciónév]…
LIMIT sorok};
A FETCH utasítás az adott kurzorhoz vagy kurzorváltozóhoz (lásd 8.2. alfejezet) tartozó kurzormutató által címzett sort betölti a rekordba vagy a megadott skalárváltozókba, és a kurzormutatót a következő sorra állítja. A skalárváltozókba a sor oszlopainak értéke kerül, a változók és oszlopok típusának kompatibilisnek kell lenniük. Rekord megadása esetén az oszlopok és mezők típusa kell kompatibilis legyen. A skalárváltozók száma, illetve a rekord mezőinek száma meg kell egyezzen az oszlopok számával.
A BULK COLLECT utasításrészt a 12. fejezetben tárgyaljuk. Nem megnyitott kurzor vagy kurzorváltozó esetén a FETCH utasítás az INVALID_CURSOR kivételt váltja ki.
Ha a FETCH utasítást az utolsó sor feldolgozása után adjuk ki, akkor a változó vagy a rekord előző értéke megmarad. Nem létező sor betöltése nem vált ki kivételt. Ezen szituáció ellenőrzésére használjuk a %FOUND, %NOTFOUND attribútumokat (lásd 8.3. alfejezet).
Példa
⋮
LOOP
FETCH cur_ugyfel1 INTO v_Unev, v_Utel_szam;
EXIT WHEN cur_ugyfel1%NOTFOUND;
/* Itt jön a feldolgozás, kiíratjuk a neveket. */
DBMS_OUTPUT.PUT_LINE(v_Unev || ', ' || v_Utel_szam);
END LOOP;
⋮
A kurzor lezárása érvényteleníti a kurzor vagy kurzorváltozó és az aktív halmaz közötti kapcsolatot és megszünteti a kurzormutatót. A kurzor lezárása a CLOSE utasítással történik, melynek alakja:
CLOSE {kurzornév|kurzorváltozó_név};
Lezárni csak megnyitott kurzort vagy kurzorváltozót lehet, különben az INVALID_CURSOR kivétel váltódik ki.
1. példa
⋮
CLOSE cur_ugyfel1;
CLOSE cur_ugyfel2;
⋮
2. példa (Az előző kurzorpéldák egy blokkban és további kurzorpéldák)
DECLARE
/* Megadja az ügyfeleket ábécé sorrendben. Van RETURN utasításrész. */
CURSOR cur_ugyfelek RETURN ugyfel%ROWTYPE IS
SELECT * FROM ugyfel
ORDER BY UPPER(nev);
v_Uid ugyfel.id%TYPE;
/* Megadja annak az ügyfélnek nevét és telefonszámát, melynek
azonosítóját egy blokkbeli változó tartalmazza.
Nincs RETURN utasításrész, hisz a kérdésből ez úgyis kiderül. */
CURSOR cur_ugyfel1 IS
SELECT nev, tel_szam FROM ugyfel
WHERE id = v_Uid;
/* Megadja a paraméterként átadott azonosítóval rendelkező ügyfelet.
Ha nincs paraméter, akkor a megadott kezdőérték érvényes. */
CURSOR cur_ugyfel2(p_Uid ugyfel.id%TYPE DEFAULT v_Uid) IS
SELECT * FROM ugyfel
WHERE id = p_Uid;
/* Megadja az adott dátum szerint lejárt
kölcsönzésekhez az ügyfél nevét, a könyv címét,
valamint a lejárat óta eltelt napok számának egész részét.
Ha nem adunk meg dátumot, akkor az aktuális
dátum lesz a kezdeti érték. */
CURSOR cur_lejart_kolcsonzesek(p_Datum DATE DEFAULT SYSDATE) IS
SELECT napok, u.nev, k.cim
FROM ugyfel u, konyv k,
(SELECT TRUNC(p_Datum, 'DD') - TRUNC(datum)
- 30*(hosszabbitva+1) AS napok, kolcsonzo, konyv
FROM kolcsonzes) uk
WHERE uk.kolcsonzo = u.id
AND uk.konyv = k.id
AND napok > 0
ORDER BY UPPER(u.nev), UPPER(k.cim);
/* Egy megfelelő típusú változóba lehet majd a kurzor sorait betölteni */
v_Lejart cur_lejart_kolcsonzesek%ROWTYPE;
v_Nev v_Lejart.nev%TYPE;
/* Lekérdezi és zárolja az adott azonosítójú könyv sorát.
Nem az egész táblát zárolja, csak az aktív halmaz elemeit!
Erre akkor lehet például szükség, ha egy könyv kölcsönzésénél
ellenőrizzük, hogy van-e még példány.
Így biztosan nem lesz gond, ha két kölcsönzés egyszerre történik
ugyanarra a könyvre. Az egyik biztosan bevárja a másikat. */
CURSOR cur_konyvzarolo(p_Kid konyv.id%TYPE) IS
SELECT * FROM konyv
WHERE id = p_Kid
FOR UPDATE OF cim;
/* Kísérletet tesz az adott könyv zárolására.
Ha az erőforrást foglaltsága miatt nem
lehet megnyitni, ORA-00054 kivétel váltódik ki. */
CURSOR cur_konyvzarolo2(p_Kid konyv.id%TYPE) IS
SELECT * FROM konyv
WHERE id = p_Kid
FOR UPDATE NOWAIT;
/* Ezek a változók kompatibilisek az előző kurzorokkal.
Döntse el, melyik kurzor melyik változóval kompatibilis! */
v_Ugyfel ugyfel%ROWTYPE;
v_Konyv konyv%ROWTYPE;
v_Unev ugyfel.nev%TYPE;
v_Utel_szam ugyfel.tel_szam%TYPE;
BEGIN
v_Uid := 15; -- József István ügyfél azonosítója
/* Mely sorok lesznek az aktív halmaz elemei? */
OPEN cur_ugyfel1;
OPEN cur_ugyfel2(15);
LOOP
FETCH cur_ugyfel1 INTO v_Unev, v_Utel_szam;
EXIT WHEN cur_ugyfel1%NOTFOUND;
/* Itt jön a feldolgozás, kiíratjuk a neveket. */
DBMS_OUTPUT.PUT_LINE(v_Unev || ', ' || v_Utel_szam);
END LOOP;
CLOSE cur_ugyfel1;
CLOSE cur_ugyfel2;
DBMS_OUTPUT.NEW_LINE;
/* Mivel a paraméter mindig IN típusú,
kifejezés is lehet aktuális paraméter. */
OPEN cur_lejart_kolcsonzesek(TO_DATE('02-MÁJ. -09'));
BEGIN
OPEN cur_lejart_kolcsonzesek; -- CURSOR_ALREADY_OPEN kivételt vált ki!
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('Hiba: ' || SQLERRM);
END;
v_Nev := NULL;
LOOP
FETCH cur_lejart_kolcsonzesek INTO v_Lejart;
EXIT WHEN cur_lejart_kolcsonzesek%NOTFOUND;
/* Jöhet a feldolgozás, mondjuk figyelmeztető e-mail küldése.
Most csak kiírjuk az egyes nevekhez a lejárt könyveket. */
IF v_Nev IS NULL OR v_Nev <> v_Lejart.nev THEN
v_Nev := v_Lejart.nev;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('Ügyfél: ' || v_Nev);
END IF;
DBMS_OUTPUT.PUT_LINE(' ' || v_Lejart.napok || ' nap, ' || v_Lejart.cim);
END LOOP;
CLOSE cur_lejart_kolcsonzesek;
END;
/
/*
Eredmény:
József István, 06-52-456654
Hiba: ORA-06511: PL/SQL: a kurzor már meg van nyitva
Ügyfél: Jaripekka Hämälainen
22 nap, A critical introduction to twentieth-century American drama - Volume 2
22 nap, The Norton Anthology of American Literature - Second Edition - Volume 2
Ügyfél: József István
17 nap, ECOOP 2001 - Object-Oriented Programming
17 nap, Piszkos Fred és a többiek
A PL/SQL eljárás sikeresen befejeződött.
*/
3. példa (FOR UPDATE használatára)
DECLARE
/* Kísérletet tesz az adott könyv zárolására.
Ha az erőforrást foglaltsága miatt nem lehet megnyitni,
ORA-00054 kivétel váltódik ki. */
CURSOR cur_konyvzarolo2(p_Kid konyv.id%TYPE) IS
SELECT * FROM konyv
WHERE id = p_Kid
FOR UPDATE NOWAIT;
probak NUMBER;
t NUMBER;
foglalt EXCEPTION;
PRAGMA EXCEPTION_INIT(foglalt, -54);
BEGIN
probak := 0;
/* Legfeljebb 10-szer próbáljuk megnyitni. */
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
WHILE probak < 10 LOOP
probak := probak + 1;
BEGIN
OPEN cur_konyvzarolo2(15);
-- Sikerült! Kilépünk a ciklusból
EXIT;
EXCEPTION
WHEN foglalt THEN
NULL;
END;
/* Várunk kb. 10 ms-t. */
t := TO_CHAR(SYSTIMESTAMP, 'SSSSSFF');
WHILE t + 10**8 > TO_CHAR(SYSTIMESTAMP, 'SSSSSFF') LOOP
NULL;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
DBMS_OUTPUT.PUT_LINE('A kurzort '
|| CASE cur_konyvzarolo2%ISOPEN WHEN TRUE THEN '' ELSE 'nem ' END
|| 'sikerült megnyitni.');
IF cur_konyvzarolo2%ISOPEN THEN
CLOSE cur_konyvzarolo2;
END IF;
END;
/
Próbálja ki az utolsó példát a következő két szituációban:
Egyetlen munkamenettel kapcsolódjon az adatbázishoz. Futtassa le a blokkot. Mit tapasztal?
Két munkamenettel kapcsolódjon az adatbázishoz, például úgy, hogy két SQL*Plusszal kapcsolódik. Az egyikben módosítsa a konyv tábla sorát:
UPDATE konyv SET cim = 'Próba' WHERE id = 15;
A másik munkamenetben futtassa le a blokkot! Mit tapasztal? Ezután az első munkamenetben adjon ki egy ROLLBACK parancsot, a második munkamenetben pedig futtassa le ismét a blokkot! Mit tapasztal?
A PL/SQL minden DML utasításhoz (azon lekérdezésekhez is, amelyeket nem explicit kurzorral kezelünk) felépít egy implicit kurzort. Ennek neve: SQL, ezért az implicit kurzort gyakran SQL kurzornak is hívják. Az implicit kurzorra nem alkalmazhatók az OPEN, FETCH, CLOSE utasítások. A megnyitást, betöltést, lezárást automatikusan a PL/SQL motor végzi.