Kurzorváltozók

Az explicit kurzorhoz hasonlóan a kurzorváltozó is egy aktív halmaz valamelyik sorának feldolgozására alkalmas. Amíg azonban a kurzor statikus, azaz egyetlen, a fordításkor már ismert SELECT utasításhoz kötődik, addig a kurzorváltozó dinamikus, amelyhez futási időben bármely típuskompatibilis kérdés hozzákapcsolható.

A kurzorváltozó használata az explicit kurzorok használatával megegyező módon történik (deklaráció, megnyitás, betöltés, lezárás).

A kurzorváltozó lényegében egy referencia típusú változó, amely mindig a hivatkozott sor címét tartalmazza.

A kurzorváltozó deklarálása két lépésben történik. Először létre kell hozni egy REF CURSOR saját típust, majd ezzel a típussal deklarálni egy változót. A típusdeklaráció szintaxisa:

TYPE név IS REF CURSOR [RETURN
{{ab_tábla_név | kurzor_név | kurzorváltozó_név}%ROWTYPE | rekord_név%TYPE|
rekordtípus_név |
kurzorreferenciatípus_név}];

Az ab_tábla_név olyan adatbázisbeli tábla vagy nézet neve, amelyik ismert a deklarációnál.

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 kurzorreferenciatípus_név egy már deklarált REF CURSOR típus.

Ha szerepel a RETURN utasításrész, akkor erős, egyébként gyenge kurzorreferencia típusról beszélhetünk. A kettő között a különbség abban áll, hogy az erős kurzorreferencia

típusnál a fordító tudja ellenőrizni a kapcsolt kérdés típuskompatibilitását, míg a gyengénél bármely kérdés kapcsolható.

A PL/SQL jelen verziója tartalmaz egy előre definiált gyenge kurzorreferencia típust,

ennek neve SYS_REFCURSOR. Saját gyenge kurzorreferencia típus deklarálása helyett javasoljuk ennek használatát.

Az általunk deklarált vagy előre definiált kurzorreferencia típussal aztán kurzorváltozót tudunk deklarálni.

1. példa

DECLARE
  TYPE t_egyed IS RECORD ...;

  /* gyenge típusú REF CURSOR */
  TYPE t_refcursor IS REF CURSOR;

  /* erős típusú REF CURSOR */
  TYPE t_ref_egyed IS REF CURSOR RETURN t_egyed;

  /* Kurzorváltozók */
  v_Refcursor1 t_refcursor;
  v_Refcursor2 SYS_REFCURSOR; -- ez is gyenge típusú
  v_Egyedek1 t_ref_egyed;
  v_Egyedek2 t_ref_egyed;
⋮

A kurzorváltozót is meg kell nyitni. A kurzorváltozóhoz a megnyitáskor kapcsolódik hozzá az aktív halmazt meghatározó lekérdezés. Az utasítás alakja:

OPEN kurzorváltozó_név FOR select_utasítás;

Egy adott kurzorváltozó bármennyi OPEN-FOR utasításban szerepelhet, egy újabb megnyitás előtt nem kell lezárni. Egy kurzorváltozó újramegnyitása azt jelenti, hogy egy új aktív halmaz jön létre, amelyre a kurzorváltozó hivatkozni fog és az előző törlődik.

A kurzorváltozó betöltése és lezárása a kurzoroknál tárgyalt FETCH és CLOSE utasításokkal történik.

Ha a kurzorváltozó egy alprogram formális paramétere és az alprogram csak betölti és lezárja azt, akkor a paraméterátadás IN vagy IN OUT lehet. Ha a megnyitása is az alprogramban történik, akkor a mód kötelezően IN OUT.

2. példa

DECLARE
  TYPE t_egyed IS RECORD (id NUMBER, leiras VARCHAR2(100));

  /* erős típusú REF CURSOR */
  TYPE t_ref_egyed IS REF CURSOR RETURN t_egyed;

  /* gyenge típusú REF CURSOR típust nem kell deklarálnunk,
  a SYS_REFCURSOR típust használjuk helyette */

  /* Kurzorváltozók */
  v_Refcursor SYS_REFCURSOR;
  v_Egyedek1 t_ref_egyed;
  v_Egyedek2 t_ref_egyed;
  v_Egyed t_egyed;


  /* Megnyit egy gyengén típusos kurzort. */
  PROCEDURE megnyit_konyv(p_cursor IN OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN p_cursor FOR
    SELECT id, cim FROM konyv;
  END;

  /* Megnyit egy erősen típusos kurzort. */
  FUNCTION megnyit_ugyfel RETURN t_ref_egyed IS
    rv t_ref_egyed;
  BEGIN
    OPEN rv FOR
    SELECT id, nev FROM ugyfel;
    RETURN rv;
  END;

  /* Egy sort betölt a kurzorból és visszaadja.
  A visszatérési érték másolása miatt nem célszerű használni. */
  FUNCTION betolt(p_cursor IN t_ref_egyed) RETURN t_egyed IS
    rv t_egyed;
  BEGIN
    FETCH p_cursor INTO rv;
    RETURN rv;
  END;

  /* Bezár egy tetszőleges kurzort. */
  PROCEDURE bezar(p_cursor IN SYS_REFCURSOR) IS
  BEGIN
    IF p_cursor%ISOPEN THEN
      CLOSE p_cursor;
    END IF;
  END;
BEGIN
  /* Elemezze a típuskompatibilitási problémákat! */
  megnyit_konyv(v_Egyedek1);
  v_Refcursor := megnyit_ugyfel;

  /* Innentől kezdve a v_Refcursor és a v_Egyedek2 ugyanazt
  a kurzort jelenti! */
  v_Egyedek2 := v_Refcursor;
  v_Egyed := betolt(v_Egyedek2);
  DBMS_OUTPUT.PUT_LINE(v_Egyed.id || ', ' || v_Egyed.leiras);
  v_Egyed := betolt(v_Refcursor);
  DBMS_OUTPUT.PUT_LINE(v_Egyed.id || ', ' || v_Egyed.leiras);

  /* Most pedig v_Refcursor és a v_Egyedek1 egyezik meg. */
  v_Refcursor := v_Egyedek1;
  v_Egyed := betolt(v_Egyedek1);
  DBMS_OUTPUT.PUT_LINE(v_Egyed.id || ', ' || v_Egyed.leiras);
  v_Egyed := betolt(v_Refcursor);
  DBMS_OUTPUT.PUT_LINE(v_Egyed.id || ', ' || v_Egyed.leiras);
  bezar(v_Egyedek1);
  bezar(v_Egyedek2);
  BEGIN
    /* Ezt a kurzort már bezártuk egyszer v_Egyedek1 néven! */
    v_Egyed := betolt(v_Refcursor);
    DBMS_OUTPUT.PUT_LINE(v_Egyed.id || ', ' || v_Egyed.leiras);
  EXCEPTION
    WHEN INVALID_CURSOR THEN
      DBMS_OUTPUT.PUT_LINE('Tényleg be volt zárva!');
  END;

  /* Itt nem szerepelhetne sem v_Egyedek1, sem v_Egyedek2. */
  OPEN v_Refcursor FOR
  SELECT 'alma',2,3,4 FROM DUAL;

  /* Ez az értékadás most kompatibilitási problémát eredményez!
  Az előbb nem okozott hibát, mert az ellenőrzés futási időben történik. */
  v_Egyedek2 := v_Refcursor;
EXCEPTION
  WHEN OTHERS THEN
    CLOSE v_Refcursor;
    RAISE;
END;
/

/*
Eredmény:
5, Kovács János
10, Szabó Máté István
5, A római jog története és institúciói
10, A teljesség felé
Tényleg be volt zárva!

Hiba a(z) 1. sorban:
DECLARE
*

ORA-06504: PL/SQL: Az Eredményhalmaz-változók vagy a kérdés visszaadott típusai nem illeszkednek
ORA-06512: a(z) helyen a(z) 99. sornál
*/

A PL/SQL-ben kezelhetők ún. CURSOR kifejezések is. A CURSOR kifejezés egy beágyazott kérdés sorait kezeli kurzor segítségével. Ilyenkor a kurzor által kezelt aktív halmaz sorai értékeket és kurzorokat tartalmaznak. A feldolgozás során először mindig az aktív halmaz sorai töltődnek be, majd egy beágyazott ciklus segítségével a beágyazott kurzorok alapján a beágyazott kérdések sorai.

Egy CURSOR kifejezés szerepelhet kurzordeklaráció SELECT-jében vagy kurzorváltozóhoz kapcsolt SELECT-ben. Alakja:

CURSOR(select_utasítás)

Egy beágyazott kurzor implicit módon nyílik meg, amikor a szülő kurzor valamelyik sora betöltésre kerül. A beágyazott kurzor lezáródik, ha:

3. példa (CURSOR kifejezés használatára)

DECLARE
  TYPE t_Konyv_rec IS RECORD ( id konyv.id%TYPE, cim konyv.cim%TYPE);
  TYPE t_konyvref IS REF CURSOR RETURN t_Konyv_rec;

  /* Lekérdezzük az ügyfeleket és a kölcsönzött könyveiket,
  azt az ügyfélt is, akinél nincs könyv.
  A könyveket egy CURSOR kifejezés segítségével adjuk vissza. */
  CURSOR cur_ugyfel_konyv IS
    SELECT id, nev,
      CURSOR(SELECT k.id, k.cim FROM konyv k, TABLE(konyvek) uk
             WHERE k.id = uk.konyv_id) AS konyvlista
    FROM ugyfel
    ORDER BY UPPER(nev);
  v_Uid ugyfel.id%TYPE;
  v_Unev ugyfel.nev%TYPE;
  v_Konyvek t_konyvref;
  v_Konyv t_Konyv_rec;
BEGIN
  OPEN cur_ugyfel_konyv;
  LOOP
    FETCH cur_ugyfel_konyv INTO v_Uid, v_Unev, v_Konyvek;
    EXIT WHEN cur_ugyfel_konyv%NOTFOUND;
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('Ügyfél: ' || v_Uid || ', ' || v_Unev);

    /* Most a beágyazott kurzor elemeit írjuk ki, ha nem üres.
    A beágyazott kurzort nem kell külön megnyitni és lezárni sem. */
    FETCH v_Konyvek INTO v_Konyv;
    IF v_Konyvek%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(' A kölcsönzött könyvek:');
      WHILE v_Konyvek%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(' ' || v_Konyv.id || ', ' || v_Konyv.cim);
        FETCH v_Konyvek INTO v_Konyv;
      END LOOP;
    ELSE
      DBMS_OUTPUT.PUT_LINE(' jelenleg nem kölcsönöz könyvet.');
    END IF;
  END LOOP;
  CLOSE cur_ugyfel_konyv;
END;
/

/* Eredmény:
Ügyfél: 25, Erdei Anita
A kölcsönzött könyvek:
35, A critical introduction to twentieth-century American drama - Volume 2

Ügyfél: 35, Jaripekka Hämälainen
A kölcsönzött könyvek:
35, A critical introduction to twentieth-century American drama - Volume 2
40, The Norton Anthology of American Literature - Second Edition - Volume 2

Ügyfél: 15, József István
A kölcsönzött könyvek:
15, Piszkos Fred és a többiek
20, ECOOP 2001 - Object-Oriented Programming
25, Java - start!
45, Matematikai zseblexikon
50, Matematikai Kézikönyv

Ügyfél: 30, Komor Ágnes
A kölcsönzött könyvek:
5, A római jog története és institúciói
10, A teljesség felé

Ügyfél: 5, Kovács János
jelenleg nem kölcsönöz könyvet.

Ügyfél: 10, Szabó Máté István
A kölcsönzött könyvek:
30, SQL:1999 Understanding Relational Language Components
45, Matematikai zseblexikon
50, Matematikai Kézikönyv

Ügyfél: 20, Tóth László
A kölcsönzött könyvek:
30, SQL:1999 Understanding Relational Language Components

A PL/SQL eljárás sikeresen befejeződött.
*/

Az Oracle10g a kurzorváltozók használatára az alábbi korlátozásokat alkalmazza: