Adatbázisok szerver oldali programozása Előadás, 2006.03.10.

Ez az oldal a korábbi SCH wiki-ről lett áthozva. Az eredeti változata itt érhető el.

Ha úgy érzed, hogy bármilyen formázási vagy tartalmi probléma van vele, akkor kérlek javíts rajta egy rövid szerkesztéssel.

Ha nem tudod, hogyan indulj el, olvasd el a migrálási útmutatót

<< Előadás, 2006.03.10. >>

Kurzorok

A következő kód hibás, mert ha a =SELECT= nulla sorral tér vissza, exceptiont kapunk:

DECLARE
	myid INT:=3;
	nev VARCHAR(50);
BEGIN
	SELECT anyag_nev INTO nev FROM anyag WHERE anyag_id=myid;
	DBMS_OUTPUT.PUT_LINE(nev);
END;

Egy tábla sorain kurzor segítségével lehet végiglépkedni.

• Explicit kurzor: a programozó hozza létre
• Implicit kurzor: az Oracle hozza létre azokra a lekérdezésekre, amikre nincs explicit kurzor.

Kurzorok szintaktikája

• Kurzor deklarálása: =CURSOR kurzor_név [(param1, ...)] [RETURN visszatérési_típus] IS lekérdezés;=

A paraméterek láthatósága lokális.

• Kurzor megnyitása: =OPEN kurzor_név [(érték1, ...)];=
• Léptetés a következő sorra, és beolvasás: =FETCH kurzor_név INTO változó;=

A =FETCH= csak az =OPEN= és a =CLOSE= között használható.

• Összes sor kiolvasása egyszerre: =FETCH kurzor_név BULK COLLECT INTO tömb;=
• Kurzor bezárása: =CLOSE kurzornév;=

Attribútumok

• =kurzor_név%ISOPEN=: true, ha a kurzor meg van nyitva.
• =kurzor_név%NOTFOUND=: false, ha az utolsó =FETCH= sorral tért vissza. Első =FETCH= előtt =NULL=.
• =kurzor_név%FOUND=: true, ha az utolsó =FETCH= sorral tért vissza. Első =FETCH= előtt =NULL=.
• =kurzor_név%ROWCOUNT=: a

  FETCH

  -elt sorok száma. Első =FETCH= előtt 0.
• =kurzor_név%ROWTYPE=: a kurzorral fesorolható sorok típusa. Csak akkor kérdezhető le, ha a =RETURN= után megadtunk visszatérési típust.

Az implicit kurzorok az utolsó SQL utasításról adnak információt. Az attribútmait az =SQL%attribútum_név= kifejezéssel lehet lekérdezni.

• =SQL%ISOPEN=: mindig false.
• =SQL%NOTFOUND=: true, ha az utolsó =SELECT=, =INSERT=, =UPDATE= vagy =INSERT= 0 sort érintett.
• =SQL%FOUND=: false, ha az utolsó =SELECT=, =INSERT=, =UPDATE= vagy =INSERT= 0 sort érintett.
• =SQL%ROWCOUNT=: az utolsó SQL utasítás által érintett sorok száma.

Példák

Felsorolás LOOP ciklussal

DECLARE
	CURSOR c IS SELECT anyag_id, anyag_nev FROM anyag;
	id INT;
	nev anyag.anyag_nev%TYPE;
BEGIN
	OPEN c;
	LOOP
		FETCH c INTO id, nev;
		EXIT WHEN c%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(id || ' ' || nev);
	END LOOP;
END;

Felsorolás FOR ciklussal

A FOR automatikusan megnyitja illetve lezárja a kurzort. A ciklusváltozót nem kell deklarálni, a típusa =kurzor%ROWTYPE= lesz.

Szintaktika: =FOR rekord IN kurzor[(param1, ...)] LOOP= ... =END LOOP=;

DECLARE
	CURSOR c IS SELECT anyag_id id, anyag_nev nev FROM anyag;
BEGIN
	FOR crec IN c LOOP
		DBMS_OUTPUT.PUT_LINE(crec.id || ' ' || crec.nev);
	END LOOP;
END;

BULK COLLECT

DECLARE
	CURSOR c IS SELECT anyag_nev, anyag_id FROM anyag;
	TYPE nev_type IS TABLE OF anyag.anyag_nev%TYPE;
	TYPE id_type IS TABLE OF anyag.anyag_id%TYPE;
	nev_tomb nev_type;
	id_tomb id_type;
BEGIN
	OPEN c;
	FETCH c BULK COLLECT INTO nev_tomb, id_tomb;
	CLOSE c;
END;

Implicit kurzor FOR ciklusban

BEGIN
	FOR crec IN (SELECT anyag_id, anyag_nev FROM anyag) LOOP
		DBMS_OUTPUT.PUT_LINE(crec.anyag_id || ' ' || crec.anyag_nev);
	END LOOP;
END;

Rekord típusú kurzor

DECLARE
	TYPE anyag_rec IS RECORD (id INT, nev VARCHAR2(50));
	CURSOR c RETURN anyag_rec IS
		SELECT anyag_id, anyag_nev FROM anyag;
	rec c%ROWTYPE;
BEGIN
	OPEN c;
	LOOP
		FETCH c INTO rec;
		EXIT WHEN c1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(rec.anyag_id || ' ' || rec.anyag_nev);
	END LOOP;
END;

Kurzor változók

Az eddig használt kurzorok konstansok voltak.

• A kurzor változóhoz dinamikusan lehet hozzákötni a lekérdezést. Megnyitáskor az előző értékeket elfelejti. Az újbóli megnyitás előtt nem szükséges lezárni.
• A kurzor változót is meg kell nyitni és be kell zárni.
• A =BULK COLLECT= használható rá, de a FOR ciklus nem.
• A =%ROWTYPE= attribútum csak akkor használható, ha a változónak deklaráltuk a visszatérési típusát.

Szintaktika

• Deklaráció: =TYPE típusnév IS REF CURSOR [RETURN visszatérési_típus];=
• Megnyitás: =OPEN {kurzor_változó | :host_kurzor_változó} FOR {lekérdezés dinamikus string} [USING bind_argument1, ...];=
• Olvasás: =FETCH {kurzor_változó | :host_kurzor_változó} [BULK COLLECT] INTO változó1, ...=

Példák

Egyszerű felsorolás

DECLARE
	TYPE c_type IS REF CURSOR;
	c c_type;
	nev VARCHAR2(50);
BEGIN
	OPEN c FOR SELECT anyag_nev FROM anyag;
	LOOP
		FETCH c INTO nev;
		EXIT WHEN c%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(nev);
	END LOOP;
	OPEN c FOR SELECT stud_lname FROM student
	LOOP
		FETCH c INTO nev;
		EXIT WHEN c%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(nev);
	END LOOP;
	CLOSE c;
END;

Összetett lekérdezés

DECLARE
	CURSOR c IS
		SELECT felkesz_nev, CURSOR(
			SELECT anyag_nev, fa_mennyiseg, egyseg_nev
			FROM anyag, felkesz_anyag, egyseg
			WHERE anyag_id=fa_anyagid AND fa_felkesz_id=f.felkesz_id AND egyseg_id=anyag_egyseg)
		FROM felkesz f ORDER BY felkesz_nev;
	TYPE refcursor IS REF CURSOR;
	nev VARCHAR2(100);
	TYPE anyagdatatype IS RECORD
		(nev VARCHAR2(100), mennyiseg FLOAT, egyseg VARCHAR2(20));
	anyagadat anyagdatatype;
	mycur refcursor;
BEGIN
	OPEN c;
	LOOP
		FETCH c INTO nev, mycur;
		EXIT WHEN c%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('A ' || nev || ' összetevői:');
		LOOP
			FETCH mycur INTO anyagadat;
			EXIT WHEN mycur%NOTFOUND;
			DBMS_OUTPUT.PUT_LINE(
				'  ' || anyagadat.nev || ' ' || anyagadat.mennyiseg || ' ' || anyagadat.egyseg);
		END LOOP;
	END LOOP;
	CLOSE c;
END;

-- Peti - 2006.03.10.