Programozás | SQL » A PL/SQL nyelv felépítése

PL/SQL Gyakorlat 2. A PL/SQL NYELV FELÉPÍTÉSE Tárolt eljárás, függvény: − neve van, lehet rá hivatkozni, más blokkokból meg lehet hívni − két eljárás illetve függvény azonos névvel nem létezhet a rendszerben − lehet törölni: DROP PROCEDURE / FUNCTION nev; − a kódot az adatbázis tárolja: többször végrehajtható Létezı eljárások lekérdezése: SELECT object name FROM ‘PROCEDURE%’; Létezı függvények lekérdezése: SELECT object name FROM ‘FUNCTION%’; user objects WHERE object type LIKE user objects WHERE object type LIKE Eljárás létrehozása: CREATE PROCEDURE nev (parameternev IN / OUT / IN OUT tipus, ) IS PL/SQL blokk; Függvény létrehozása: CREATE FUNCTION nev (parameternev IN / OUT / IN OUT tipus, ) RETURN tipus IS BEGIN feldolgozás RETURN kifejezes; END; Példa: CREATE FUNCTION osszead (s1 IN NUMBER, s2 IN NUMBER) RETURN NUMBER IS BEGIN RETURN s1+s2; END; Meghívása más PL/SQL blokkból: x := osszead(3,5); Néhány

példa parancssorban történı megadásra: Feladat: Eljárás készítése a 10 évnél idısebb autók törlésére. SQL> create procedure del is 2 begin 3 delete from auto where kor>10; 4 end; 5 . SQL> / //eljárás létrehozása SQL> execute del //eljárás végrehajtása SQL> drop procedure del; //tárolt eljárás törlése Feladat: Függvény készítése, mellyel egy numerikus érték növelhetı 5-el, majd plsql blokk írása a függvény meghívására és a megnövelt érték napló táblában történı eltárolására. SQL> create function novel5 (x in number) return number is 2 begin 3 return x+5; 4 end; 5 . SQL> / //függvény létrehozása SQL> declare 2 x number(6) := 12; 3 ujx number(6); 4 begin 5 ujx := novel5(x); //függvény hívása 6 insert into naplo values(ujx, sysdate, user); 7 end; 8 . SQL> / //blokk végrehajtása SQL> drop function novel5; Feladat: Eljárás készítése a legmagasabb ár lekérdezésére az autó

táblából és ennek eltárolására a napló táblában. SQL> create procedure sel is 2 x number(10); 3 begin 4 select max(ar) into x from auto; 5 insert into naplo values(x, sysdate, user); 6 end; 7 . SQL> / Tárolt eljárásnál és függvénynél nem kell megadni a DECLARE kulcsszót! Példák fájlban történı megadásra: Feladat: Tárolt eljárás készítése, mely két számot vár bemenı paraméterként és kivonja a nagyobból a kisebbet, majd az eredményt eltárolja a napló táblában. SQL> !pico eljaras.sql create or replace procedure elj(x in number, y in number) is begin if x > y then insert into naplo values(x-y, sysdate, user); else insert into naplo values(y-x, sysdate, user); end if; end; . / SQL> @eljaras.sql SQL> exec elj(15,3) SQL> drop procedure elj; Feladat: Függvény készítése, mely összead két megadott számot. Majd írjunk pl/sql blokkot, amely meghívja ezt a függvényt és a mővelet eredményét eltárolja a napló

táblában. SQL> host pico szum.sql create or replace function oad (x in number, y in number) return number is begin return x+y; end; . / SQL> @szum //függvény létrehozása SQL> declare 2 szumma number(6); 3 begin 4 szumma := oad(34,689); 5 insert into naplo values(szumma, sysdate, user); 6 end; 7 . SQL> / SQL> drop function oad; //függvény hívása Hibakezelı rész: A hibakezelı részben saját hibakezelı rutinokat lehet megadni: EXCEPTION WHEN hibakod THEN valasztevekenyseg; Hibakód lehet: NO DATA FOUND, VALUE ERROR, ZERO DIVIDE, TOO MANY ROWS stb. A gyári hibák mellett lehet saját hibát is definiálni és arra hibakezelı rutint írni. DECLARE sajathiba EXCEPTION; BEGIN feldolgozás RAISE sajathiba; EXCEPTION WHEN sajathiba THEN valasztevékenység; END; Feladat: Próbáljuk meg lekérdezni az 500 eFt-nál olcsóbb autók típusát. A pl/sql blokk kezelje le a hibát (naplózza), ha nincs a feltételnek megfelelı rekord a táblában. SQL> host

pico hiba1.sql declare x auto.tipus%type; begin select tipus into x from auto where ar<500000; insert into naplo values(x, sysdate, user); exception when no data found then insert into naplo values(no data found hiba, sysdate, user); end; . / SQL> @hiba1 Feladat: Próbáljuk meg SELECT . INTO utasítással lekérdezni a piros autók adatait A PL/SQL blokk kezelje le a hibát (naplózza) ha a lekérdezés eredménye egy rekordnál több, ezért nem tudja fogadni az adatokat. SQL> host pico hiba2.sql declare x auto%rowtype; begin select * into x from auto where szin like piros%; insert into piros auto values(x.rsz, xtipus, xszin, xkor, xar); exception when too many rows then insert into naplo values(too many rows hiba, sysdate, user); end; . / SQL> @hiba2 Sajáthiba kezelésére példák Feladat: Írjunk olyan pl/sql blokkot, amely saját magunk által definiált hiba segítségével azt figyeli, hogy ha 20 évnél idısebb autót akarunk felvinni, ezt ne engedje (naplózza

a hibát), egyébként engedélyezze a rekordfelvitelt. SQL> host pico hiba3.sql declare rsz auto.rsz%type := xyz123; tipus auto.tipus%type := skoda; szin auto.szin%type := feher; kor number(2) := 22; ar number(8) := 400000; invalid number exception; begin if kor not between 1 and 20 then raise invalid number; else insert into auto values(rsz, tipus, szin, kor, ar); end if; exception when invalid number then insert into naplo values(invalid number hiba, sysdate, user); end; . / SQL> @hiba3 Feladat: Tárolt eljárás készítése, melynek meg kell adni az autó korát. Ha ez 20-nál nagyobb, akkor naplózza a hibát (nem engedi felvinni a rekordot), egyébként felviszi a megadott autó rekordot az adott kor értékkel. SQL> host pico hiba4.sql create or replace procedure hibas(kor in number) is invalid number exception; begin if kor not between 1 and 20 then raise invalid number; else insert into auto values(svt234, fiat, kek, kor, 1000000); end if; exception when invalid

number then insert into naplo values(invalid number hiba, sysdate, user); end; . / SQL> @hiba4 SQL> exec hibas(22)