PL/SQL (folytatás) Kurzorok, függvények, eljárások
Kurzorok • Adattábla soronkénti feldolgozására szolgál • A memóriában egy munkaterületen tárolódik a kurzorhoz tartozó tábla • A kurzor az eredményhalmazban mindig egy sorra mutat • Explicit kurzor: a kurzorhoz tartozó tábla SELECT utasítással definiált • Implicit kurzor: minden INSERT, DELETE, UPDATE és explicit kurzorral nem rendelkező SELECT utasításhoz automatikusan jön létre
Kurzorfüggvények • 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%függvénynév alakúak Pl. kurzornév%ISOPEN • Implicit kurzor esetén SQL előtaggal hivatkozhatunk, Pl. SQL%FOUND.
Implicit kurzor példa DECLARE emp_row emp%ROWTYPE; BEGIN SELECT * INTO emp_row FROM emp WHERE empno = 7902; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); END;
Explicit kurzor használata • Deklaráció: CURSOR kurzornév IS lekérdezés • Megnyitás: OPEN kurzornév (megnyitáskor hajtódik végre a lekérdezés, a létrejövő eredménytábla nem frissítődik!!!) • 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 • Lezárt kurzor deklarációja továbbra is érvényben marad, később újra megnyitható
Explicit kurzor példa
DECLARE v_veznev emp.ename%TYPE; v_fiz emp.sal%TYPE; CURSOR nev_es_fiz IS SELECT ename, sal FROM emp ORDER BY ename; BEGIN OPEN nev_es_fiz; LOOP FETCH nev_es_fiz INTO v_veznev, v_fiz; EXIT WHEN nev_es_fiz%NOTFOUND; DBMS_OUTPUT.PUT_LINE(' Név: ' || v_veznev || ' Fizetése: ' || v_fiz); END LOOP; CLOSE nev_es_fiz; END;
Explicit kurzor FOR ciklussal Az alábbi FOR utasítás implicit módon hajtja végre az OPEN, FETCH, EXIT WHEN, CLOSE parancsokat: FOR rekordnév IN kurzornév LOOP utasítások END LOOP; Megjegyzés: rekordnevet nem szükséges külön deklarálni, érvényességi köre csak a FOR-ciklusra terjed ki.
Példa DECLARE CURSOR nev_es_fiz IS SELECT ename, sal FROM emp ORDER BY ename; BEGIN FOR m_rek IN nev_es_fiz LOOP DBMS_OUTPUT.PUT_LINE(' Név: ' || m_rek.ename || ' Fizetése: ' || m_rek.sal); END LOOP; END;
Paraméterezett kurzorok • A kurzor definíciója paramétereket tartalmazhat, ezek aktuális értékeit megnyitáskor adjuk át a kurzornak. • Ha többször nyitjuk meg, mindig más-más paraméterrel futtathatjuk. • CURSOR kurzornév (paraméternév adattípus, ..., paraméternév adattípus) IS alkérdés;
Tábla módosítása kurzorral • UPDATE táblanév SET oszlop = kifejezés, ..., oszlop = kifejezés [ WHERE feltétel ]; Kérdés: A kurzor által mutatott sor módosításához az UPDATE utasításban milyen WHERE feltételt kell megadni? • Lehetőségek: • ROWID használata • FOR UPDATE és CURRENT OF használata.
Tábla módosítása kurzorral, ROWID ROWID = sorazonosító: rekord fizikai címe (melyik fájl hányadik lemezblokkján hányadik rekord). ACCEPT partner_azon PROMPT 'Kérem adja meg a partner azonosítót: ' DECLARE row_id ROWID; id emp.empno%TYPE; BEGIN SELECT ROWID INTO row_id FROM emp WHERE empno = '&partner_azon'; UPDATE emp SET sal = sal + 100 WHERE ROWID = row_id; DBMS_OUTPUT.PUT_LINE(row_id); END;
FOR UPDATE és CURRENT OF használata • FOR UPDATE [OF oszlopok] [NOWAIT]: zárolás. A kurzor megnyitásakor az összes sort zárolja. A zárolás COMMIT-ig vagy ROLLBACK-ig tart. NOWAIT: ha egy másik tranzakció már zárolta a sorokat, akkor a program nem várakozik, hanem hibajelzéssel tovább fut. • NVL(x, y) függvény: NULL érték helyettesítése. • Ha x értéke NULL, akkor y-t, egyébként x értékét adja vissza.
• WHERE CURRENT OF: kurzor sorának módosítása
Tábla módosítása kurzorral, FOR UPDATE, CURRENT OF set serveroutput on DECLARE
CURSOR nev_es_fizetes IS SELECT ename, sal FROM emp ORDER BY ename FOR UPDATE OF sal; BEGIN for egydolgozo in nev_es_fizetes loop update emp set sal=sal*1.1 where current of nev_es_fizetes; DBMS_OUTPUT.PUT_LINE(egydolgozo.ename || ' ' || egydolgozo.sal); end loop; END;
Alprogramok • 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]; • Függvények: FUNCTION név [(paraméterek)] RETURN adattípus IS lokális deklarációk BEGIN utasítások END [név];
Eljárás • PROCEDURE név [(paraméterek)] IS lokális deklarációk BEGIN végrehajtható utasítások [EXCEPTION kivételkezelés] END [név]; Az eljárás RETURN utasítás(oka)t tartalmazhat, amelyek azonnali visszatérést eredményeznek.
Eljárás paraméterek szintaxisa • paraméternév [{IN | OUT | IN OUT}] adattípus • IN: bemenő paraméter, ha értéket adunk neki, fordítási hiba keletkezik. IN az alapértelmezés. • OUT: kimenő paraméter. Csak értéket kaphat, értékét felhasználni nem lehet (tehát például értékadó utasítás jobb oldalán nem szerepelhet). • IN OUT: be-kimenő paraméter, szabadon felhasználható.
• Híváskor IN esetén változó vagy konstans, OUT és INOUT esetén csak változó adható meg. • Az adattípusokra NOT NULL és egyéb megkötés nem adható meg.
Eljáráshoz példa DECLARE v_megnev emp.ename%TYPE; PROCEDURE nyomtat(szoveg IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(szoveg); END; BEGIN SELECT ename INTO v_megnev FROM emp WHERE empno = 7902; nyomtat(v_megnev); END;
Függvény • FUNCTION név [(paraméterek)] RETURN adattípus IS lokális deklarációk BEGIN végrehajtható utasítások [EXCEPTION kivételkezelés] END [név]; A függvény RETURN(kifejezés) utasítással tér vissza.
Függvény példa DECLARE v_partnerid emp.empno%TYPE; v_ber emp.sal%TYPE; FUNCTION min_ber (ber IN NUMBER) RETURN BOOLEAN IS tmp_ber emp.sal%TYPE; BEGIN SELECT MIN(ber) INTO tmp_ber FROM emp; RETURN (tmp_ber = ber); END min_ber; BEGIN SELECT MIN(empno) INTO v_partnerid FROM emp; LOOP SELECT sal INTO v_ber FROM emp WHERE empno = v_partnerid; v_partnerid := v_partnerid + 1; EXIT WHEN min_ber(v_ber); END LOOP; DBMS_OUTPUT.PUT_LINE('A minimal ber: ' || v_ber); END;
Adatbázis-objektumként tárolt alprogram • lehetőség van az adatbázisban eltárolni alprogramokat • ezek később tetszőleges, az adatbázison futtatott PL/SQL blokkból, SQL lekérdezésből, az EXEC utasítással, vagy SQL*Plus környezetben az EXECUTE paranccsal hívhatók • deklaráció elejére CREATE, IS helyett AS • CREATE PROCEDURE név (paraméterek) AS ... • CREATE FUNCTION név (paraméterek) RETURN típus AS ... • alapértelmezés IN típusú paraméterekhez: paraméternév típus DEFAULT érték, paramétert csak a lista végérõl lehet elhagyni • PROCEDURE nyomtat(szoveg IN VARCHAR2 DEFAULT 'empty') • FUNCTION min_ber (ber IN NUMBER DEFAULT 0) RETURN BOOLEAN
Feladatok 1. Írjunk egy függvényt, ami egy paraméterben átadott küszöbértéktől többet kereső alkalmazottak (emp tábla) átlagbérét adja vissza! A függvényt felhasználva írassuk is ki az eredményt, a paramétert a felhasználótól kérjük be! 2. Kurzor használatával emeljük meg a CLERK foglalkozásúak fizetését 20%-kal!
Feladatok 3. Kurzor segítségével járjuk be az EMP táblát, és ha valakinek a fizetése 2000 fölött van, dobjunk egy magasfizetes nevű saját kivételt. • Deklaráljuk a kivételt, és írjuk meg hozzá a kivételkezelőt is, ami kiírja, hogy kinek túl magas a fietése. • Az egész EMP táblát be kell járni! 4. Írjunk olyan tárolt eljárást, ami kurzor segítségével bejárja az EMP táblát, és fizetésemelést ad a dolgozóknak a következő szabály szerint: • ha valaki legalább 30 éve dolgozik a cégnél, adjunk 20% emelést, ha 20-30 évvel ezelőtt került a céghez, 10% emelést, ha pedig kevesebb, mint 20 éve, akkor 5% emelést. • Segítség:
• A to_char a 'yyyy' formátum stringgel kiveszi az éveket a dátum mezőből • A to_number pedig ezt a stingként tarolt számot szám típusúvá alakítja,
SSADM leadása • Március 17. Coospace • • • •
Becsomagolva zip-be Doksi (pdf), ábra forrásfájlok Adatbázist (táblákat) létrehozó script(ek) Alapkövetelmények (min. pontért): • • • •
Szöveges feladatleírás, követelmény katalógus Adatfolyam diagram(ok) Egyedmodell(ek) Funkció meghatározás vagy egyed-esemény mátrix vagy szerepfunkció mátrix • Relációs adatelemzés (normalizálás, adattáblák leírása)
• Következő gyakorlatra (márc. 21.) ki kell nyomtatni és beadni
ZH • Következő héten (márc. 21) ZH: • 4-5-6 gyakorlat anyagából • ami kell: • • • • • • • •
alapvető SQL parancsok (adatbázis 1.) érték beolvasása / kiíratása plsql blokkok (declare,begin,exception,end) alapvető algoritmus eszközök (IF, LOOP, WHILE, CASE, FOR) gyűjtőtáblák kivétel kezelés (rendszerbeli, saját), saját kivétel dobása alprogramok (függvény, eljárás) készítése, meghívása kurzorok
• + Kinyomtatott SSADM doksi leadása gyakorlaton! (ZH után)
1. Feladat megoldása SET SERVEROUTPUT ON; ACCEPT kuszob PROMPT 'Adj meg egy kuszoberteket:' DECLARE atl_fiz emp.sal%TYPE; FUNCTION fizetes(kuszob in NUMBER) RETURN NUMBER IS fiz emp.sal%TYPE; BEGIN SELECT AVG(sal) INTO fiz FROM emp WHERE sal>kuszob; RETURN fiz; END fizetes; BEGIN atl_fiz:=fizetes(&kuszob); DBMS_OUTPUT.PUT_LINE('Atlagfizetes a kuszobertek felett: ' || atl_fiz); END;
2. Feladat megoldása DECLARE emel emp.sal%TYPE; CURSOR fizetesemeles IS SELECT sal FROM emp WHERE ename LIKE 'CLARK' FOR UPDATE OF sal NOWAIT; BEGIN OPEN fizetesemeles; FETCH fizetesemeles INTO emel; UPDATE emp SET sal=sal*1.20 WHERE CURRENT OF fizetesemeles; CLOSE fizetesemeles; END;
3. Feladat megoldása
SET SERVEROUTPUT ON DECLARE magasfizetes EXCEPTION; CURSOR fizetes IS SELECT sal FROM emp; BEGIN FOR fizetesek IN fizetes LOOP IF fizetesek.sal>1000 THEN RAISE magasfizetes; END IF; END LOOP; EXCEPTION WHEN magasfizetes THEN dbms_output.put_line('Valakinek magas a fizetese!'); END;
4. Feladat megoldása
CREATE PROCEDURE fiz_emel AS evek number; emeles emp.sal%type := 0; cursor dolgkurzor is select hiredate, sal from emp for update of sal; begin for dolg_rek in dolgkurzor loop evek := 2012 - to_number(to_char(dolg_rek.hiredate, 'yyyy')); if (evek >= 30) then emeles := dolg_rek.sal * 0.2; elsif (evek>=20) then emeles := dolg_rek.sal * 0.1; elsif (evek>=10) then emeles := dolg_rek.sal * 0.05; end if; update emp set sal=sal+emeles where current of dolgkurzor; end loop; END fiz_emel;
