Kalmár György Adatbázis alapú rendszerek
Oracle-ben az SQL utasítások feldolgozásához szükség van
egy ún. kontextus memóriára, amely az összes lényeges információt tárolja egy utasítás végrehajtásához. Pl: feldolgozandó sorok száma.
A kurzor ezen területre mutató pointer, amelyen keresztül
tehát információt szerezhetünk az SQL utasításokról és azok eredményeiről.
Két féle kurzor létezik: Explicit Implicit
Automatikusan generálódik minden utasításhoz. Nem tudjuk módosítani. INSERT esetén: tárolja az adatot, ami beszúrásra kerül DELETE, UPDATE esetén: meghatározza a sorokat, amelyek érintettek lesznek
Attribútumai: SQL%FOUND: a legutóbbi SQL utasítás legalább egy sort feldolgozott SQL%NOTFOUND: a legutóbbi SQL utasítás nem dolgozott fel sort SQL%ROWCOUNT: a kurzorral összesen feldolgozott sorok száma
SQL%ISOPEN: igaz, ha a kurzor meg van nyitva Explicit kurzor esetén kurzornev%... alakúak
--Folyómenti városok száma. set serveroutput on; DECLARE TYPE folyomenti_tabla IS TABLE OF VAROSOK%ROWTYPE INDEX BY PLS_INTEGER; folyomenti_varosok folyomenti_tabla; BEGIN SELECT * BULK COLLECT INTO folyomenti_varosok FROM VAROSOK WHERE FOLYOMENTIE=1; DBMS_OUTPUT.PUT_LINE('Folyomenti varosok szama: '||SQL%ROWCOUNT); END; /
BULK COLLECT Az előző példában láthatjuk, hogyan használható a BULK
COLLECT arra, hogy egyszerre több sort mentsünk el egy asszociatív tömb (gyűjtőtábla) típusú változóba.
Mi definiáljuk és nagyobb ráhatásunk van a mutatott területre. A DECLARE-blokkban kell definiálni. Deklaráció: CURSOR kurzornév IS lekérdezés Megnyitás: OPEN kurzornév (megnyitáskor hajtódik végre a lekérdezés) Léptetés: FETCH kurzornév INTO változók (az aktuális sor adatai a változókba
kerülnek és a kurzor eggyel előre lép, ellenőrizni kell, hogy a kurzorhoz tartozó eredménytáblának van-e sora)
Lezárás: CLOSE kurzornév
--Folyómenti városok végigjárása explicit kurzorral.
set serveroutput on; DECLARE CURSOR folyomentik IS SELECT * FROM VAROSOK WHERE FOLYOMENTIE=1; aktualis_varos VAROSOK%ROWTYPE;
BEGIN OPEN folyomentik; LOOP FETCH folyomentik INTO aktualis_varos;
EXIT WHEN folyomentik%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Folyomenti: '||aktualis_varos.NEV); END LOOP; CLOSE folyomentik; END; /
--Folyómenti városok végigjárása explicit kurzorral. set serveroutput on; DECLARE CURSOR folyomentik IS SELECT * FROM VAROSOK WHERE FOLYOMENTIE=1; BEGIN FOR aktualis_varos IN folyomentik LOOP DBMS_OUTPUT.PUT_LINE('Folyomenti: '||aktualis_varos.NEV); END LOOP;
END; /
--Folyómenti városok végigjárása explicit paraméteres kurzorral. set serveroutput on;
DECLARE CURSOR varoslista(folyomenti_e VAROSOK.FOLYOMENTIE%TYPE) IS SELECT NEV FROM VAROSOK WHERE FOLYOMENTIE=folyomenti_e; folyomenti_e_var NUMBER(1) := 0;
BEGIN FOR aktualis_varos IN varoslista(folyomenti_e_var) LOOP DBMS_OUTPUT.PUT_LINE('Folyomenti_e: '||folyomenti_e_var|| ' ,neve:'||aktualis_varos.NEV); END LOOP; END; /
CURRENT OF KURZORNÉV - utoljára FETCH-elt sor módosítását vagy törlését
engedi meg.
FOR UPDATE OF ADATTAG - megmondjuk, hogy mely adattagot szeretnénk a
kurzoron keresztül frissiteni
NOWAIT - Ha egy másik tranzakció zárolta a kérédéses sort, akkor hibajelentéssel
tovább fut
--A megadott nevü város irányítószámának növelése eggyel. set serveroutput on; DECLARE CURSOR novelendo(varosnev VAROSOK.NEV%TYPE) IS
SELECT * FROM VAROSOK WHERE NEV=varosnev FOR UPDATE OF IRSZ NOWAIT; varos_record VAROSOK%ROWTYPE;
irsz_valt VAROSOK.IRSZ%TYPE; BEGIN OPEN novelendo('&bekert'); FETCH novelendo INTO varos_record; DBMS_OUTPUT.PUT_LINE('Elotte: '||varos_record.irsz); UPDATE VAROSOK SET IRSZ = varos_record.irsz+1 WHERE CURRENT OF novelendo; SELECT IRSZ INTO irsz_valt FROM VAROSOK WHERE NEV=varos_record.NEV; DBMS_OUTPUT.PUT_LINE('Utana: '||irsz_valt); END;
/
Pseudooszlop – nem általunk tárolt adat, de minden rekordhoz tárolt Egy rekord fizikai címe
--A megadott nevü város országának kiirása ROWID-t használva. set serveroutput on;
DECLARE row_id ROWID; orszag_valt VAROSOK.ORSZAG%TYPE;
BEGIN SELECT ROWID INTO row_id FROM VAROSOK WHERE NEV LIKE '&beker'; SELECT ORSZAG INTO orszag_valt FROM VAROSOK WHERE ROWID=row_id;
DBMS_OUTPUT.PUT_LINE('A megadott varos '||orszag_valt||' orszagban talalhato.'); END; /
Névvel ellátott és paraméterezhető blokk Deklarációjuk a főprogram DECLARE szegmens végén Eljárások:
PROCEDURE név [(paraméterek)] IS lokális deklarációk BEGIN utasítások
END [név]; IN, OUT, IN OUT paraméterek
--Személy kiirása eljárás segítségével set serveroutput on; DECLARE szemely_valt SZEMELY%ROWTYPE; PROCEDURE szemely_kiir( szemely_p IN SZEMELY%ROWTYPE ) IS BEGIN DBMS_OUTPUT.PUT_LINE('A '||szemely_p.SZIGSZ||' szigsz-u szemely, kinek neve '|| szemely_p.NEV||' , a(z) '||szemely_p.ORSZAG||' orszag '||szemely_p.IRSZ|| ' iranyitoszamu varosaban lakik. Szuletett: '||TO_CHAR(szemely_p.SZULETESIDATUM,'YYYY.MM.DD')); END; BEGIN SELECT * INTO szemely_valt FROM SZEMELY WHERE ROWNUM<2; szemely_kiir(szemely_valt); END; /
Visszatérési értékkel is rendelkeznek.
FUNCTION név [(paraméterek)] RETURN adattípus IS lokális deklarációk
BEGIN utasítások END [név];
--Megadott város lakosainak számának visszaadása függvénnyel.
set serveroutput on; DECLARE lakosszam NUMBER; FUNCTION lakosok_szama(varosnev_p IN VAROSOK.NEV%TYPE) RETURN NUMBER IS cnt NUMBER; BEGIN
SELECT COUNT(SZEMELY.NEV) INTO cnt FROM VAROSOK,SZEMELY WHERE VAROSOK.NEV LIKE varosnev_p AND SZEMELY.IRSZ=VAROSOK.IRSZ AND SZEMELY.ORSZAG=VAROSOK.ORSZAG; RETURN cnt; END; BEGIN lakosszam := lakosok_szama('&varosnev'); DBMS_OUTPUT.PUT_LINE('Lakosok szama: '||lakosszam);
END;
Az elözöekhez hasonlóan kell öket definiálni, de nem csak
az adott programban érhetök el, hanem az adatbázisban definiálódnak.
deklaráció elejére CREATE feltüntetése alapértelmezés IN típusu paraméterekhez: paraméternév
típus DEFAULT érték, paramétert csak a lista végéről lehet elhagyni
tárolt eljárás PL/SQL blokkból futtatható, vagy SQL*Plus
környezetben az EXECUTE paranccsal
CREATE PROCEDURE szemely_kiir( szemely_p IN SZEMELY%ROWTYPE ) IS BEGIN DBMS_OUTPUT.PUT_LINE('A '||szemely_p.SZIGSZ||' szigsz-u szemely, kinek neve '|| szemely_p.NEV||' , a(z) '||szemely_p.ORSZAG||' orszag '||szemely_p.IRSZ|| ' iranyitoszamu varosaban lakik. Szuletett: '||TO_CHAR(szemely_p.SZULETESIDATUM,'YYYY.MM.DD')); END;
