SELECT INTO
8. fejezet - Kurzorok és kurzorváltozók
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.
1. Kurzorok
1.1. Kurzor deklarálá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.
Kurzorok és kurzorváltozók
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
Kurzorok és kurzorváltozók
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;
1.2. Kurzor megnyitása
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.
Kurzorok és kurzorváltozók
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;
1.3. Sorok betöltése
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
⋮
Kurzorok és kurzorváltozók
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;
⋮
1.4. Kurzor lezárása
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)