Adatbázis Rendszerek II. 4. PLSQL Kurzorok, hibakezelés
B IT M A N
53/1
v: 2017.03.02 B IT MAN
A
53/2
PL/SQL alapjai
Adatok kezelése
Kurzorok
Hibakezelés
B IT M A N
Adatok kezelése PL/SQL-ben Műveletek:
– – – –
Írás (INSERT) Módosítás (UPDATE) Törlés (DELETE) Olvasás (SELECT … INTO)
INSERT UPDATE DELETE
SELECT
Információforrás:
– Adatbázis adatai – PL/SQL-blokk változók
53/3
B IT M A N
Adatok kezelése - Írás INSERT utasítással
– Formátuma megegyezik az SQL szabványban megismerttel, de bővíthető a returning kifejezéssel! – Mezőértékek megadásánál szerepelhetnek PL/SQL változók Példa: x := 3; y := 'alma'; INSERT INTO gyumi VALUES (x, y, 4);
53/4
B IT M A N
Adatok kezelése - Törlés DELETE utasítással
– Formátuma megegyezik az SQL szabványban megismerttel, de bővíthető a returning kifejezéssel! – PL/SQL változók helye: • Értékkifejezésben • Feltételi részben Példa: x := 3; DELETE FROM gyumi WHERE id = x;
53/5
B IT M A N
Adatok kezelése - Módosítás UPDATE utasítással
– Formátuma megegyezik az SQL szabványban megismerttel, de bővíthető a returning kifejezéssel! – PL/SQL változók helye: • Értékkifejezésben • Feltételi részben Példa:
x := 3; y := 43; UPDATE gyumi SET kor = y WHERE id = x; 53/6
B IT M A N
Returning utasításrész Egysoros, egyszerűsített SELECT .. INTO kifejezés A select parancs nincs kiírva Az adatok mindig az érintett táblából származnak
Az into előtti mezőlistának meg kell egyeznie az into
utáni változó listával Termek
declare Kategoria Tkod Nev Ar Leiras v_nev varchar2(20); begin delete from termek where tkod='t07' returning nev into v_nev; dbms_output.put_line('A törölt termék neve: '||v_nev); end;
53/7
B IT M A N
Adatok kezelése – returning kifejezés create sequence seq1; create table T4 (kod int, adat varchar2(40)); create trigger t4t1 before insert on t4 for each row begin :new.kod := seq1.nextval; end;
T4 kod
adat
declare a int; begin insert into t4 (adat) values ('Tulipán') returning kod into a; dbms_output.put_line('A rekord kódja: '||a); end;
53/8
B IT M A N
Adatok kezelése – returning kifejezés create or replace procedure updterm (maxar in number, szaz in number, db out number) is begin update termek set ar=ar*(1+szaz/100) where ar < maxar returning count(*) into db; end; declare x int; begin updterm (200, 7, x); dbms_output.put_line('Módosított termékek száma: '||x); end;
53/9
B IT M A N
Adatok kezelése – Lekérdezés SELECT … INTO utasítással INTO kulcsszó után változólista:
– PL/SQL változók – A lekérdezés eredménye kerül bele – A listában több változót is fel lehet sorolni (projekciós részben szereplő mezők számával megegyezően) – Egyelemű (rekordtípusú - %ROWTYPE) Akkor alkalmazható, ha az eredmény pontosan egy rekordból áll. (Kell, hogy értéket kapjon, és nem kaphat több értéket!)
53/10
B IT M A N
Adatok kezelése – Lekérdezés (Példa) declare egyed ember%rowtype; nev char(20); begin select * into egyed from ember where id = 2345; nev := egyed.nev; dbms_output.put_line(nev); end;
53/11
B IT M A N
Adatok kezelése – Lekérdezés (Példa) declare enev char(20); ekor int; begin select nev, kor into enev, ekor from ember where id = 2345; dbms_output.put_line(enev, ekor); end;
53/12
B IT M A N
A
53/13
PL/SQL alapjai
Adatok kezelése
Kurzorok
Hibakezelés
B IT M A N
Kurzor Az adatok kezelésére használt memóriarészt környezeti területnek
nevezi az Oracle. A kurzor olyan eszköz, amivel megnevezhetjük a környezeti területet, hozzáférhetünk az ott lévő adatokhoz. Az Oracle kétféle kurzort használ: Implicit kurzor: az Oracle automatikusan hozza létre, és kezeli minden DML művelet esetén, akkor is, ha csak egy sort érint a művelet. Ez a típus rejtett, nem láthatók a benne lévő adatok. Explicit kurzor: a felhasználó a programjában deklarálja, és kezeli. A kurzor megnyitható, a benne lévő adatok elérhetők, kiolvashatók belőle. A kurzor mindig egy select parancs eredményeként jön létre.
53/14
B IT M A N
Implicit kurzor
Az implicit kurzor neve mindig sql! Használható attribútumok: - %found – Van benne adat? - %notfound – Nincs benne adat? - %isopen – Nyitva van? (False) - %rowcount – Feldolgozott sorok száma
declare sordb number(5); begin update dolgozok set fizetes = fizetes + 5000; if sql%notfound then dbms_output.put_line('A dolgozók tábla üres'); elsif sql%found then sordb := sql%rowcount; dbms_output.put_line(sordb||' dolgozó fizetése frissítve'); end if; end;
53/15
B IT M A N
Explicit kurzor
Az explicit kurzornak mi adunk nevet! Használható attribútumok: - %found – Van benne adat? - %notfound – Nincs benne adat? - %isopen – Nyitva van? - %rowcount – Feldolgozott sorok száma
declare c_id dolgozok.id%type; c_nev dolgozok.nev%type; c_cim dolgozok.cim%type; cursor c_dolgozok is select id, nev, cim from dolgozok; begin open c_dolgozok; loop fetch c_dolgozok into c_id, c_nev, c_cim; exit when c_dolgozok%notfound; dbms_output.put_line(c_id||'-'||c_nev||'-'||c_cim); end loop; close c_dolgozok; end;
53/16
B IT M A N
Adatok kezelése – Kurzor Több rekordot visszaadó lekérdezés esetén használandó az explicit kurzor. Életciklusa: 1. Kurzor deklaráció (DECLARE részben) 2. Kurzor megnyitás 3. Rekord kiolvasások ciklusa 4. Kurzor lezárás
Nem
DECLARE
53/17
OPEN
FETCH
Üres?
Igen CLOSE
B IT M A N
Kurzor attribútumok %ROWCOUNT – Értéke az adott pillanatig beolvasott
rekordok száma. %FOUND – Értéke TRUE, ha az adott FETCH utasítás még talál benne rekordot. %NOTFOUND – Értéke TRUE, ha az adott FETCH utasítás már nem talál benne rekordot. %ISOPEN – Értéke TRUE, ha a kurzor nyitott.
53/18
B IT M A N
Adatok kezelése – Kurzor (elvi példa) declare cursor cursornév(paraméterek) is select … ; vlista cursornév%rowtype; begin open cursornév(akt_param); loop Kurzor deklarálása fetch cursornév into vlista; Megnyitás exit when cursornév%notfound; Kiolvasás adatok kiírása, feldolgozása Lezárás end loop; close cursornév; end;
53/19
B IT M A N
Adatok kezelése – Kurzor példa A kurzor működése create or replace procedure tlp as cursor cur is select * from kategoria; cv cur%rowtype; begin open cur; loop fetch cur into cv; exit when cur%notfound; dbms_output.put_line('Név: '||cv.nev); dbms_output.put_line('Feldolgozva: '||cur%rowcount); end loop; close cur; end;
53/20
B IT M A N
Adatok kezelése – Kurzor példa Adott nap előtt születettek beszúrása egy másik táblába: declare cursor lista (datum date) is select nev, lakcim from szemelyek where szuldat < datum; c1 lista%rowtype; begin open lista('2000-01-01'); loop fetch lista into c1 exit when lista%notfound; insert into szemelyek2 values(c1.nev, c1.lakcim); end loop; close lista; end;
53/21
B IT M A N
Adatok kezelése – Kurzor példa FOR ciklussal A kurzor működése create or replace procedure tlp as cursor cur is select * from kategoria; begin for cv in cur loop dbms_output.put_line('Név: '||cv.nev); dbms_output.put_line('Feldolgozva: '||cur%rowcount); end loop; end;
53/22
Csak a kurzort kell deklarálni, nem kell változó a kiolvasáshoz Nem kell megnyitni a kurzort A fetch automatikusan zajlik Nem kell lezárni a kurzort
B IT M A N
Autós példa 19.
auto rsz
tipus
szin
kor
ar
PL/SQL blokk írása a piros autók lekérdezésére és külön táblában eltárolására. declare x auto%rowtype; cursor piros is select * from auto where szin like 'piros%'; begin open piros; loop fetch piros into x; exit when piros%notfound; insert into piros_auto values (x.rsz, x.tipus, x.szin, x.kor, x.ar); end loop; close piros; end;
53/23
B IT M A N
Autós példa 20.
auto rsz
tipus
szin
kor
ar
PL/SQL blokk írása a piros autók lekérdezésére és külön táblában eltárolására, de: FOR ciklussal! declare cursor piros is select * from auto where szin like 'piros%'; begin for ps in piros loop insert into piros_auto values (ps.rsz, ps.tipus, ps.szin, ps.kor, ps.ar); end loop; end;
53/24
B IT M A N
Adatok kezelése – Módosítható KURZOR SELECT utasítás végére opcionális tag:
– FOR UPDATE OF mezolista Módosításkor az UPDATE utasítás feltételi részében: – CURRENT OF kurzornév – A kurzor munkarekordjának adott mezőértékei az eredeti táblában is módosulni fognak.
53/25
B IT M A N
Módosítható KURZOR
auto rsz
tipus
szin
kor
ar
Adott színű autók árának növelése adott százalékkal. create procedure aut_arnov(szinbe in char, ert in int) is cursor cur_a is select * from auto where szin=szinbe for update of ar; a cur_a%rowtype; begin open cur_a; loop fetch cur_a into a; exit when cur_a%notfound; update auto set ar=a.ar*(1+ert/100) where current of cur_a; end loop; close cur_a; end; begin aut_arnov('piros', 10); end; 53/26
B IT M A N
Módosítható KURZOR
auto rsz
tipus
szin
kor
ar
Adott színű autók árának növelése adott százalékkal, függvénnyel, for ciklussal. create or replace function aut_arnov2 (szinbe in char, ert in int) return number as cursor cur_a is select * from auto where szin=szinbe for update of ar; db number := 0; begin for cv in cur_a loop update auto set ar=cv.ar*(1+ert/100) where current of cur_a; db:=db+1; end loop; return db; declare end; x number(6) := 0; begin select aut_arnov2('piros', 10) from dual; x:=aut_arnov2('piros', 10); dbms_output.put_line(x); end; 53/27 IT A N
B
M
Módosítható KURZOR
auto rsz
tipus
szin
kor
ar
Adott színű autók árának növelése adott százalékkal. create or replace procedure aut_arnov3 (szinbe in char, ert in int) is cursor cur_a is select * from auto where szin=szinbe for update of ar; db number:=0; begin for cv in cur_a loop update auto set ar=cv.ar*(1+ert/100) where current of cur_a; db:=cur_a%rowcount; end loop; dbms_output.put_line('Árváltozás: '||db); end;
53/28
begin aut_arnov3('piros', 10); end;
B IT M A N
A
53/29
PL/SQL alapjai
Adatok kezelése
Kurzorok
Hibakezelés
B IT M A N
PL/SQL blokkok hibakezelése DECLARE Deklarációs rész BEGIN Végrehajtási rész (kód rész)
EXCEPTION Hibakezelő rész END;
53/30
B IT M A N
PL/SQL blokkok hibakezelése A futási időben bekövetkező hibák hibaüzeneteket
váltanak ki A hibák lekezelésére vannak beépített (előre definiált) hiba típusok, de írhatunk saját (felhasználó által definiált) típusokat is. Minden hibának van kódja és szövege, pl: ORA-01403 - NO_DATA_FOUND ORA-01722 – INVALID_NUMBER
A lekezelt hibákat kivételeknek hívjuk, emiatt nevezik a
hibakezelést kivételkezelésnek is.
53/31
B IT M A N
Kivételek típusai Előre definiált beépített kivételek
– Az általában előforduló hibákra vannak kivételek Definiálható beépített kivételek – Ugyanúgy működnek, mint az előre definiált hibák, de a felhasználó alakítja ki őket – Hibakódjuk -20000 és -20999 között lehet, szövegük megadható – Meghívásuk: raise_application_error eljárással – Speciális esetekhez, program magyarításhoz Felhasználói kivételek – Nem hibának tűnnek, hanem felhasználói üzenetnek 53/32
B IT M A N
Autós példa 14.
auto rsz
tipus
szin
kor
ar
Próbáljuk meg lekérdezni az ABC-500 rendszámú autó típusát. Nézzük mi történik, ha nincs ilyen rendszám! declare x auto.tipus%type; begin select tipus into x from auto where rsz='ABC-500'; dbms_output.put_line('Az autó típusa: '||x); end; www.bitman.hu
53/33
B IT M A N
Autós példa 14.
auto rsz
tipus
szin
kor
ar
Próbáljuk meg lekérdezni az ABC-500 rendszámú autó típusát. Ha nincs ilyen rekord a táblában, figyelmeztessük a felhasználót. declare x auto.tipus%type; begin select tipus into x from auto where rsz='ABC-500'; dbms_output.put_line('Az autó típusa: '||x); exception when no_data_found then dbms_output.put_line('Nincs ilyen rendszám!'); end; www.bitman.hu
53/34
B IT M A N
Autós példa 16.
auto rsz
tipus
szin
kor
ar
Próbáljuk meg lekérdezni, és egy táblába beszúrni a piros autó adatait. Nézzük mi történik, ha több ilyen rekord is van a táblában. declare x auto%rowtype; begin select * into x from auto where szin like 'piros%'; insert into piros_auto values(x.rsz, x.tipus, x.szin, x.kor, x.ar);
end; www.bitman.hu
53/35
B IT M A N
Autós példa 16.
auto rsz
tipus
szin
kor
ar
Próbáljuk meg lekérdezni, és egy táblába beszúrni a piros autó adatait. Ha több ilyen rekord is van a táblában, figyelmeztessük a felhasználót. declare x auto%rowtype; begin select * into x from auto where szin like 'piros%'; insert into piros_auto values(x.rsz, x.tipus, x.szin, x.kor, x.ar); exception when too_many_rows then dbms_output.put_line('Több piros autó is van!'); end; www.bitman.hu
53/36
B IT M A N
Előre definiált hibák kezelése declare v_ber melos.ber%type; v_veznev melos.vezeteknev%type; v_kernev melos.keresztnev%type; begin v_ber := 200000; select vezeteknev, keresztnev into v_veznev, v_kernev from melos where ber = v_ber; dbms_output.put_line(v_veznev || ' ' || v_kernev); exception when no_data_found then dbms_output.put_line('Nincs ilyen fizetés'); when too_many_rows then dbms_output.put_line('Több embernek is ez a fizetése'); when others then dbms_output.put_line('Egyéb hiba'); end; 53/37
B IT M A N
Autós példa 17. Írjunk olyan pl/sql blokkot, amely a felhasználó által definiált beépített hiba segítségével nem engedi felvinni a 20 évnél idősebb autókat. declare rsz varchar2(30) := 'SQW-123'; tipus varchar2(30) := 'skoda'; szin varchar2(30) := 'fehér'; kor number(2) := 22; ar number(8) := 400000; begin if kor not between 1 and 20 then raise_application_error (-20502, 'Hibás az autó kora!'); else insert into auto values(rsz, tipus, szin, kor, ar); end if; end; www.bitman.hu
53/38
B IT M A N
Autós példa 18. Írjunk olyan pl/sql blokkot, amely saját magunk által definiált hiba segítségével nem engedi felvinni a 20 évnél idősebb autókat. declare rsz varchar2(30) := 'SQW-123'; tipus varchar2(30) := 'skoda'; szin varchar2(30) := 'fehér'; kor number(2) := 22; ar number(8) := 400000; rossz_kor exception; begin if kor not between 1 and 20 then raise rossz_kor; else insert into auto values(rsz,tipus,szin,kor,ar); end if; exception when rossz_kor then dbms_output.put_line('Hibás az autó kora'); 53/39 end;
www.bitman.hu
B IT M A N
A hibakezelés logikája 1
1 2 3
53/40
DECLARE saját_hiba EXCEPTION; BEGIN … EXCEPTION WHEN saját_hiba THEN … WHEN beépített_hiba THEN … WHEN OTHERS THEN … END;
B IT M A N
Előre definiált (beépített) kivételek
53/41
Hiba neve
Hiba kódja
access_into_null case_not_found collection_is_null cursor_already_open dup_val_on_index invalid_cursor invalid_number login_denied no_data_found not_logged_on program_error rowtype_mismatch self_is_null storage_error subscript_beyond_count subscript_outside_limit sys_invalid_rowid timeout_on_resource too_many_rows value_error zero_divide
ORA-06530 ORA-06592 ORA-06531 ORA-06511 ORA-00001 ORA-01001 ORA-01722 ORA-01017 ORA-01403 ORA-01012 ORA-06501 ORA-06504 ORA-30625 ORA-06500 ORA-06533 ORA-06532 ORA-01410 ORA-00051 ORA-01422 ORA-06502 ORA-01476
B IT M A N
Autós példa 19. Tárolt alprogram, mely töröl egy paraméterként megadott rendszámú autót, ha nincs olyan rendszám, akkor egy üzenetet ír a képernyőre! create or replace procedure del_rsz (rszb in char) as db number; h_rsz exception; begin select count(*) into db from auto where rsz=rszb; if db=0 then raise h_rsz; else delete from auto where rsz=rszb; end if; exception when h_rsz then dbms_output.put_line('Hibás rendszám'); end; begin del_rsz(′bbrbr′); end 53/42
B IT M A N
Autós példa 19. Tárolt alprogram, mely töröl egy paraméterként megadott rendszámú autót, ha nincs olyan rendszám, akkor egy üzenetet ír a képernyőre! create or replace procedure del_rsz (rszb in char) as begin delete from auto where rsz=rszb; if sql%rowcount = 0 then dbms_output.put_line('Hibás rendszám'); end if; end;
begin del_rsz(′bbrbr′); end
53/43
B IT M A N
Órai feladatok 1.
Kategoria
Termek
Kkod Nev
Kategoria Tkod Nev Ar
Leiras
Készítsen egy TermekKategoria nevű eljárást, mely kiírja a képernyőre a termékek nevét és kategóriájuk nevét. Használjon kurzort és for ciklust.
HELP:
create or replace procedure tlp as cursor cur is select * from kategoria; begin for cv in cur loop dbms_output.put_line('Név: '||cv.nev); end loop; 53/44 end;
B IT M A N
Órai feladatok 2.
53/45
Készítsen egy TermekAr nevű tárolt eljárást, mely egy paraméterként megadott terméknév esetén kiírja a termék árát. Használjon gyári hibakezelést. Ha nincs ilyen termék, íja ki a képernyőre (Nem létező termék: terméknév). Ha több ilyen termék is van (pl. csipsz), íja ki a képernyőre (Több ilyen nevű termék létezik: terméknév).
B IT M A N
Órai feladatok 2.
Készítsen egy TermekAr nevű tárolt eljárást, mely egy paraméterként megadott terméknév esetén kiírja a termék árát. Használjon gyári hibakezelést. Ha nincs ilyen termék, íja ki a képernyőre (Nem létező termék: terméknév). Ha több ilyen termék is van (pl. csipsz), íja ki a képernyőre (Több ilyen nevű termék létezik: terméknév).
HELP: begin v_ber := 200000; select vezeteknev, keresztnev into v_veznev, v_kernev from melos where ber = v_ber; dbms_output.put_line(v_veznev || ' ' || v_kernev); exception when no_data_found then dbms_output.put_line('Nincs ilyen fizetés'); when too_many_rows then dbms_output.put_line('Több embernek is ez a fizetése'); 53/46 IT end;
B
MAN
Órai feladatok 3.
Készítsen egy TermekLista nevű eljárást, mely egy paraméterként megadott kategória kód esetén kiírja a képernyőre a kategóriához tartozó termékek nevét.
Az eljárás bemenő paramétere legyen a kategória neve (nem a kódja!) Használjon kurzort. Készítsen kétféle változatot, az egyikben loop ciklust, a másikban for ciklust használjon.
Módosítás: A for ciklusos változatot egészítse ki hibakezeléssel: ha nem létezik a megadott kategória, írja ki ennek tényét az eljárás a képernyőre.
Módosítás: Hozzon létre egy hiba_naplo [szöveg, dátum, szöveg] táblát, és ne a képernyőre, hanem ebbe írja a hibát, a dátumot, és a usernevet. (Hibás kategória: kategória neve, dátum, user)
53/47
B IT M A N
Órai feladatok 4.
Készítsen egy VasarloLista nevű eljárást, mely egy paraméterként megadott terméknév esetén kiírja a képernyőre azon vásárlók nevét, akik már vásároltak a termékből.
Használjon kurzort.
Módosítás: Egészítse ki az eljárást hibakezeléssel: ha nem létezik a megadott termék, írja ki ennek tényét az eljárás a képernyőre.
Módosítás: Naplózza a hibát a hiba_naplo [szöveg, dátum, szöveg] táblába. (Hibás terméknév: terméknév, dátum, user)
53/48
B IT M A N
Órai feladatok 5.
53/49
Készítsen egy Bevetel nevű függvényt, amely kiszámítja, és visszatérő értékként visszaadja a bevételt. (termékár * darab) A függvény paramétere legyen a vásárlás táblában lévő dátum és sorszám. A függvény az adott napi, adott sorszámtól kisebb sorszámú tételekből számítsa a bevételt. Használjon kurzort, melybe az adott sorszámnak eleget tevő rekordokból az ár és a darabszám kerüljön. Ciklussal összesítse a bevételt a kurzort felhasználva. Hívja meg az elkészült függvényt a select paranccsal. Módosítás: egészítse ki hibakezeléssel a függvényt. Ha nem létező dátumot adunk meg, írja ki a függvény: (Nem létező dátum: dátum!)
B IT M A N
Órai feladatok 6.
Készítsen egy Arnovelo nevű tárolt alprogramot, mely egy paraméterként megadott kategória esetén megemeli az adott kategóriájú termékek árát egy szintén paraméterként megadott százalékkal.
Az eljárás bemenő paramétere legyen a kategória neve (nem a kódja!), és a százalék értéke (pl. 10%). Használjon módosítható kurzort.
53/50
B IT M A N
Órai feladatok 7.
53/51
Készítsen egy UjTermek nevű tárolt eljárást, mely: - az egyes mezők értékét paraméterként kapja meg, - saját hibát dobva figyelmeztet (képernyőüzenettel), ha nem létező kategóriájú terméket akarnánk felvinni, - saját hibát dobva figyelmeztet (képernyőüzenettel), ha már létező azonosítójú terméket akarnánk felvinni.
B IT M A N
Felhasznált irodalom Kovács László: PL/SQL, elektronikus jegyzet Barabás Péter: Adatbázis rendszerek 2.,
elektronikus jegyzet Jeffery D. Ullman, Jennifer Widom: Adatbázisrendszerek Kende Mária, Nagy István: ORACLE példatár Don Burleson: Oracle Tips (www.dba-oracle.com)
53/52
B IT M A N
VÉGE 53/53
B IT M A N
