Statikus kurzorok
Egy kurzor segítségével végig tudunk haladni egy select eredményhalmazán, és jelen esetben kiíratni azt a képernyőre.
set serveroutput on;
--Server kimenet bekapcsolása
create table test_for_cursor (
a varchar2(10),
b number
);
--elkészítjük a táblát
insert into test_for_cursor values ('one', 1);
insert into test_for_cursor values ('two', 2);
insert into test_for_cursor values ('three', 3);
insert into test_for_cursor values ('four', 4);
insert into test_for_cursor values ('five', 5);
commit;
--feltöltöm alapadatokkal
declare
cursor cur_t is select * from test_for_cursor;
--létrehozom a kursort és egyenlővé teszem a select eredményhalmazával
rec_t test_for_cursor%rowtype;
--felveszek egy változót amelynek a segítségével végig tudok haladni a
--cursor minden egyes során.
begin
open cur_t;
--megnyitom a cursort
Loop
--elindítok egy ciklust
--sorra kiveszem a változóba az eredmény halmaz sorait
fetch cur_t into rec_t;
--Amikor véget ér az eredmény halmaz akkor kiléptetem
exit when cur_t%notfound;
--egyébként pedig kiíratom az output képernyőre az eredményt
dbms_output.put_line('a: ' || rec_t.a || ', b: ' || rec_t.b);
end loop;
end;
/
Dinamikus kurzorok
A dinamikus kurzor abban tér el a statikustól, hogy nem hoz létre átmeneti táblákat. A kurzorral sétáló alkalmazás észreveszi, ha új sort szúrnak be a táblába, mert az új sorok megjelennek a kurzor eredményhalmazában.
A package-ekről részletes leírás található.
--Létrehozok egy package-et ami tartalmazza a dinamikus kurzor deklaráló --és kiírató részét
create or replace package dynamic_cursor is
type t_crs is ref cursor;
procedure dyn_sel (
tab_name in varchar2,
field_name in varchar2,
val in varchar2,
crs in out t_crs);
procedure openCursor;
end dynamic_cursor;
/
--elkészítjük a kifejtő részt
create or replace package body dynamic_cursor as
procedure dyn_sel (
tab_name in varchar2,
field_name in varchar2,
val in varchar2,
crs in out t_crs)
--kimeneti változója a crs változó ami a selectet fogja hordozni
is
stmt varchar2(100);
begin
--felveszek egy változót ami a select-tel lesz egyenlő amit az eljárás
--átad a dinamikus cursor eljárásnak ami ezekután lekezeli
stmt := 'select * from ' || tab_name || ' where ' || field_name || ' = :1 ';
open crs for stmt using val;
end dyn_sel;
procedure openCursor is
tc t_crs;
f1 varchar2(50);
f2 varchar2(50);
begin
--meghívjuk a dinamikus curzort paraméterekkel
dyn_sel('test_for_cursor','a','two',tc);
loop
fetch tc into f1,f2;
exit when tc%notfound;
dbms_output.put_line(f2);
end loop;
end openCursor;
end dynamic_cursor;
/
begin
--Meghívjuk a package opencursor eljárását
dynamic_cursor.openCursor;
end;
/
Kivételkezelés
Az alábbi példában nem használtunk kurzort, azaz csak egysoros eredményeket tudunk kezelni. Az ettől eltérő eredmények kezelését kivételkezeléssel oldottuk meg.
Bővebben a kivételkezelés fejezetben.
set feedback off
set serveroutput on
create table foo (
a varchar2(10),
b varchar2(10),
i number
);
insert into foo values ('xxx','yyy',1);
insert into foo values ('zzz','aaa',1);
insert into foo values ('qqq','mmm',3);
commit;
declare
l_a foo.a%type;
l_b foo.b%type;
begin
select a,b into l_a, l_b from foo where i=1;
dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
--kimenetnél mivel nem hoztunk létre curzort ezért csak egy eredményt
--írhat vissza. Kivételre fut ha több sor van vagy ha egyáltalán nincs sor.
exception
when too_many_rows then
dbms_output.put_line('*** Exc: too many rows');
when no_data_found then
dbms_output.put_line('*** Exc: no data');
end;
/
declare
l_a foo.a%type;
l_b foo.b%type;
begin
select a,b into l_a, l_b from foo where i=2;
dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
exception
when too_many_rows then
dbms_output.put_line('*** Exc: too many rows');
when no_data_found then
dbms_output.put_line('*** Exc: no data');
end;
/
declare
l_a foo.a%type;
l_b foo.b%type;
begin
select a,b into l_a, l_b from foo where i=3;
dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
exception
when too_many_rows then
dbms_output.put_line('*** Exc: too many rows');
when no_data_found then
dbms_output.put_line('*** Exc: no data');
end;
/
Trigger
A triggerek olyan speciális procedúrák egy adatbankban, amelyet az INSERT, UPDATE, DELETE parancsok végrehajtások előtt vagy után hív meg a rendszer. Ezzel lehetőség nyílik olyan logikai hibák megakadályozására, amelyeket egyszerü CHECK paranccsal nem lehetne megakadályozni. Röviden: a trigger vagy engedélyezi vagy elveti az adott táblázaton történt módosításokat.
A trigger csak Smith nevű vásárlókat enged beszúrni a táblába.
create table Customer (
Name varchar(20) not null
);
--Trigger létrehozás
CREATE OR REPLACE TRIGGER NoSmiths
--mielőtt a beszúrnánk vagy update-elnék a táblába azelőtt fut le
BEFORE INSERT OR UPDATE OF Name
ON Customer
FOR EACH ROW
BEGIN
--ha az új elem name mezője Smith-től különbőzik akkor adjon hibát (kivétel)
--és nem teszi be a táblába az elemet
IF :new.name not like '%SMITH%' THEN
RAISE_APPLICATION_ERROR(-20000, 'No People Named Smith Allowed!');
END IF;
END NoSmiths;
insert into Customer values ('Terry SMITH');
insert into Customer values ('papa');
insert into Customer values ('JOHN SMITH');
commit;
Select * from Customer;
A példákat az Oracle Enterprise Manager és az SQL*Plus Worksheet segítségével teszteltem.
Alapok, eljárások
Képzeljünk el egy könyvkiadót, ami szerzőket alkalmaz! Adatokat tárolnak az alkalmazott szerzőkről, az általuk írt könyvekről.
--Tabla letrehozasa a szerzok tarolasara.
create table szerzo(
id number not null, --azonosito
vezeteknev varchar2(50) not null, --vezeteknev
keresztnev varchar2(50) not null, --keresztnev
alnev varchar(50), --iroi alnev
cim varchar(80) not null, --a szerzo lakhelye
belepes date not null, --a szerzo csatlakozasa a kiadohoz
fizetes number not null, --a szerzo fizetese
konyvek_szama number default 0 --a szerzo konyveinek szama
);
--Vegyuk eszre a fenti utasitasban a modositokat!
-- not null: a mezoben nem szerepelhet null ertek
-- default value: alapertelmezett ertek, ha nem adunk meg erteket beszurasnal,
-- akkor ez lesz beszurva
--Elsodleges kulcs hozzaadasa utolag a szerzo tablahoz, az id mezo lesz a kulcs.
alter table szerzo add primary key (id);
--Tabla letrehozasa a szerzok konyveinek tarolasara.
create table konyv(
kid number not null primary key, --konyv azonositoja
kcim varchar2(50), --konyc cime
szerzo number --konyv szerzojenek azonositoja
);
--A fenti utasitasban lathato, hogy az elsodleges kulcsot itt is meg lehet adni,
--amennyiben az csak egy attributombol all.
--Idegen kulcs hozzaadasa a konyv tablahoz.
--Ezzel osszekotjuk a konyv tabla szerzo mezojet a szerzo tabla id mezojevel.
alter table konyv add foreign key (szerzo) references szerzo(id);
-- Szerzo tabla feltoltese pelda adatokkal.
insert into szerzo values (1,'Szabó','József',null,'Budapest',to_date('2010.01.01','yyyy.mm.dd'),100000,0);
insert into szerzo(id,vezeteknev,keresztnev,cim,belepes,fizetes,alnev) values
(2,'Nagy','Béla','Szeged',to_date('2010.02.10','yyyy.mm.dd'),50000,'nabo');
-- A fenti ket utasitas az insert szintaxisara mutat peldat. Az elso sorban pozicio
-- alapjan, a masodik sorban nev szerint szurja be a tablaba az adatokat.
-- Szerzok lekerdezese.
select * from szerzo;
-- Hozzunk letre egy szekvenciat a konyv tabla id mezojehez, hogy ne manualisan
-- kelljen az id-t kitolteni!
CREATE SEQUENCE KONYV_ID_SEQ minvalue 0 INCREMENT BY 1 START WITH 0 ORDER;
-- A szekvencia 1-tol indul es egyesevel no.
-- Hozzunk letre egy eljarast, mellyel megadhatunk egy szerzohoz egy konyvet.
create or replace procedure uj_konyv(szerzo_azonosito number, konyv_cim varchar2) is
begin
-- A konyvet beszurjuk a konyv tablaba.
-- Lathatjuk a szekvencia hasznalatat, az id feltoltesekor lekerjuk a kovetkezo szabad id-t.
insert into konyv(kid, kcim, szerzo) values (konyv_id_seq.nextval, konyv_cim, szerzo_azonosito);
-- Frissitjuk a konyvek szamat a szerzok tablaban.
update szerzo set konyvek_szama=konyvek_szama+1 where id=szerzo_azonosito;
end;
/
-- Az alabbiakban az elobbi eljaras 3 fele meghivasa latszik.
-- A call parancsot rutinok meghivasara hasznaljak.
-- Az exec[ute] parancsot pedig egy PL/SQL parancs lefuttatasara.
call uj_konyv(1,'Zsiráfok Afrikában');
exec uj_konyv(1,'Zsiráfok és zebrák');
execute uj_konyv(2,'Zöld és szürke');
/
-- Konyv tabla lekerdezese.
select * from konyv;
--A tranzakciot veglegesitjuk.
commit;
-- Listazzuk ki egy azonositohoz tartozo szerzo osszes konyvet!
declare
NINCS_SZERZO exception; --Nincs az adott azonositohoz tartozo szerzo exception.
--beagyazott eljaras
procedure szerzo_konyvei(szerzo_azonosito in number) is
nev varchar2(100);
alnev varchar2(50);
darab number; -- az azonositohoz tartozo szerzok szama
begin
-- Ellenorizzuk, hogy tartozik-e az azonositohoz szerzo.
select count(*) into darab from szerzo where id = szerzo_azonosito;
if darab = 0 then
raise NINCS_SZERZO; -- Ha nem tartozik, akkor kivetelt dobunk;
end if;
-- select into utasitassal lekerdezzuk az adott szerzo nevet es alnevet.
-- Lathatjuk, hogy sql muveletek is hasznalhatoak, itt peldaul a concat fuggveny,
-- amely osszefuz ket szoveget.
select concat(concat(vezeteknev, ' '), keresztnev) nev, alnev into nev, alnev from szerzo
where id = szerzo_azonosito;
-- itt egy elore definialt csomagnak (dbms_output) egy eljarasat (put_line) hivjuk meg
dbms_output.put_line('Szerzo azonositoja: ' || szerzo_azonosito);
dbms_output.put_line('Szerzo neve: ' || nev);
-- elagazas
-- Vegyuk eszre a is [not] null operator hasznalatat!
-- Nem pedig alnev != null.
if alnev is not null then
dbms_output.put_line('Szerzo alneve: ' || alnev);
end if;
dbms_output.put_line('');
dbms_output.put_line(rpad('id',17) || 'Konyv cime');
dbms_output.put_line(lpad(' ',40,'-'));
-- cursor valtozo hasznalata
-- A cur valtozo sorban felveszi a lekerdezes altal visszaadott sorok ertekeit.
for cur in (select kid,kcim from konyv where (szerzo_azonosito = konyv.szerzo)) loop
-- Lathatjuk, hogy a sorok egyes mezoit a . operatorral erjuk el.
dbms_output.put_line(rpad(cur.kid,17) || cur.kcim);
end loop;
end;
begin
-- A beagyazott eljaras meghivasa a 1-es azonositoval.
szerzo_konyvei(1);
-- hibakezeles
exception
when NINCS_SZERZO then
dbms_output.put_line('Nincsen ilyen szerzo.');
end;
/
-- Fontos, hogy a blokkok veget lezarjuk egy / jellel, igy a kornyezet egyetlen utasitaskent kezeli azt.
-- Szamoljuk ki egy fuggvennyel, hogy a kiadonak mennyi az ossz kiadasa!
create or replace function ossz_kiadas return number is
osszeg number;
begin
-- fizetesek osszegenek lekerdezese
select sum(fizetes) into osszeg from szerzo;
-- Noveljuk meg az osszeget a 20%-os jarulekokkal!
osszeg := osszeg*1.2;
-- Visszaadjuk a jarulekokkal novelt osszeget.
return osszeg;
end;
/
-- TAKARITAS
drop table konyv;
drop table szerzo;
drop sequence konyv_id_seq;
drop procedure uj_konyv;
drop function ossz_kiadas;
A példa Oracle SQLDeveloperrel készült. (Rácz Gábor, 2011 2. félév)
Dinamikus SQL
DBMS_SQL csomag használata. A program paraméterként kap két táblanevet, illetve két oszlop sorszámot, amire elvégez néhány számítást, úgy mint: átlag, szumma, szórás.
(Pl.: két cég dolgozóinak a fizetésének összehasonlítása)
create or replace
PROCEDURE compare_stat(
tabla1 IN VARCHAR2 ,
col1 IN NUMBER ,
tabla2 IN VARCHAR2 ,
col2 IN NUMBER
)
AS
type results
IS
TABLE OF NUMBER;
res results := results(1);
first_col NUMBER;
summa NUMBER;
avarage NUMBER;
elteres_negyzet NUMBER;
elteres_sum NUMBER;
elteres_atlag NUMBER;
mycursor INTEGER;
result_rows INTEGER;
counter NUMBER;
trigger_str VARCHAR2(1000);
type array_t IS varray(2) OF VARCHAR2(20);
name_array array_t := array_t(tabla1, tabla2);
type array_t2 IS varray(2) OF NUMBER;
col_array array_t2 := array_t2(col1, col2);
BEGIN
dbms_output.put_line('szumma atlag szoras');
FOR i IN 1..2
LOOP
counter := 1;
summa := 0;
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor, 'SELECT * FROM ' || name_array(i), dbms_sql.native);
dbms_sql.define_column(mycursor, col_array(i), first_col);
result_rows := dbms_sql.execute(mycursor);
LOOP
EXIT
WHEN dbms_sql.fetch_rows (mycursor) = 0;
dbms_sql.column_value(mycursor, col_array(i), first_col);
res.extend(counter);
res(counter) := first_col;
summa := summa + first_col;
counter := counter +1;
END LOOP;
dbms_sql.close_cursor(mycursor);
avarage := summa/(counter-1);
elteres_sum := 0;
FOR i IN 1..counter-1
LOOP
elteres_negyzet := (avarage - res(i))**2;
elteres_sum := elteres_sum + elteres_negyzet;
IF i = counter -1 THEN
elteres_atlag := elteres_sum /i;
END IF;
END LOOP;
dbms_output.put(summa);
dbms_output.put(TO_CHAR(avarage, '999999999.99'));
dbms_output.put_line(TO_CHAR(sqrt(elteres_atlag), '999999999.99'));
end loop;
END compare_stat;
A példa Oracle SQLDeveloperrel készült. (Frank Krisztina, 2013 2. félév)
Hierarchikus lekérdezés
Hierarchikus lekérdezés bemutatása. A tábla sorai családfát reprezentálnak, ezt be lehet járni, és egy adott gyökérelemtől lévő távolságot egy Oracle pszeudo-oszlopban lekérdezni.
-- Tábla előkészítése
CREATE TABLE bibliai_nevek
(
id number not null , -- rekord azonosító sorszám
nev varchar2(20) , -- név
nem varchar(1) , -- neme, lehet "F" vagy "N", aszerint hogy férfi vagy nő
apja_id number not null , -- apja id-je
anyja_id number not null -- anyja id-je
);
alter table bibliai_nevek add primary key (id) ;
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 1 , 'Sára' , 'N' , 0 , 0 ) ;
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 2 , 'Hágár' , 'N' , 0 , 0 ) ;
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 3 , 'Rebeka' , 'N' , 0 , 0 ) ;
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 4 , 'Ábrahám' , 'F' , 0 , 0 ) ;
-- Ábrahám leszármazottai Izsák, Ismáel és Ismeretlen
-- Izsák anyja Sára
-- Ismáel anyja Hágár
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 5 , 'Izsák' , 'F' , 4 , 1 ) ;
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 6 , 'Ismáel' , 'F' , 4 , 2 ) ;
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 7 , 'Ismeretlen' , 'N' , 4 , 0 ) ;
-- Izsák leszármazottai (fiai) Jákob és Ézsau, anyjuk Rebeka
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 8 , 'Jákob' , 'F' , 5 , 3 ) ;
insert into bibliai_nevek ( id , nev , nem , apja_id , anyja_id ) values ( 9 , 'Ézsau' , 'F' , 5 , 3 ) ;
commit ;
-- CONNECT BY PRIOR: a szülő sor megfelelő oszlopát helyettesíti be
-- LEVEL pszeudo-oszlop: a gráfban a gyökérelemtől való távolság, >=1
-- SYS_CONNECT_BY_PATH: elérési út stringként a gyökérelemtől az adott elemig
-- leszármazottaktól az apák felé
select id, nem, nev, level as "Generáció" , apja_id , substr(sys_connect_by_path(nev , '/'),1,20) as Rokonsag
from bibliai_nevek start with nev='Ábrahám'
connect by prior id = apja_id order by id
;
-- Output:
/*
ID N NEV Generáció APJA_ID ROKONSAG
---------- - -------------------- ---------- ---------- ------------------------------
4 F Abrahám 1 0 /Abrahám
5 F Izsák 2 4 /Abrahám/Izsák
6 F Ismáel 2 4 /Abrahám/Ismáel
7 N Ismeretlen 2 4 /Abrahám/Ismeretlen
8 F Jákob 3 5 /Abrahám/Izsák/Jákob
9 F Ézsau 3 5 /Abrahám/Izsák/Ézsau
6 rows selected.
*/
-- }}}2
-- leszármazottaktól az anyák felé
select id, nem, nev, level as "Generáció" , anyja_id , substr(sys_connect_by_path(nev , '/'),1,20) as Rokonsag
from bibliai_nevek start with nev in ('Sára', 'Hágár', 'Rebeka')
connect by prior id = anyja_id order by id
;
-- Output:
/*
ID N NEV Generáció ANYJA_ID ROKONSAG
---------- - -------------------- ---------- ---------- ------------------------------
1 N Sára 1 0 /Sára
2 N Hágár 1 0 /Hágár
3 N Rebeka 1 0 /Rebeka
5 F Izsák 2 1 /Sára/Izsák
6 F Ismáel 2 2 /Hágár/Ismáel
8 F Jákob 2 3 /Rebeka/Jákob
9 F Ézsau 2 3 /Rebeka/Ézsau
7 rows selected.
*/
A példaprogram és outputja gvim text editor és SQL*Plus command line utility segítségével készült. (Princz Péter, 2013 2. félév)
Táblából CREATE TABLE utasítás generálása
Egy táblanévből és a tábla tulajdonosának a nevéből kiírja a táblához tartozó CREATE TABLE utasítást. (Természetesen csak az oszlop definíciókat írja ki)
-- Eljaras ami kiirja a egy tabla create table utasitasat
-- owner: tabla tulajdonosa
-- tbl: tabla neve
-- eljaras meghivasa: CALL print_create_table('galovics', 'emp')
CREATE OR REPLACE PROCEDURE print_create_table(owner VARCHAR2, tbl VARCHAR2)
IS
-- Letrehozunk egy kurzort, ami a kovetkezo lekerdezes eredmenyhalmazat hasznalja,
-- amint lathato az owner es tbl parameterek nem erzekenyek kis es nagy beture
CURSOR tbl_data IS SELECT * FROM all_tab_columns atc WHERE upper(atc.table_name) like upper(tbl) AND upper(atc.owner) = upper(owner);
-- Ebben a valtozoban taroljuk a CREATE TABLE utasitast
create_table_code VARCHAR2(2048);
-- A lekerdezes altal visszaadott eredmenyhalmaz tipusa, jelen esetben minden oszlopot tartalmazni fog a rekord
tbl_row tbl_data%rowtype;
BEGIN
-- CREATE TABLE utasitas kezdete
create_table_code := 'CREATE TABLE ' || tbl || '(';
-- kurzor kinyitasa
OPEN tbl_data;
-- Ciklussal vegigmegyunk az eredmenyhalmazon
LOOP
-- Egy sort betoltunk a tbl_row valtozoba
FETCH tbl_data INTO tbl_row;
-- Kilepunk ha mar nincs tobb sor
EXIT WHEN tbl_data%NOTFOUND;
-- Beszurunk egy uj sort (CHR(10) = Line feed = ujsor) majd hozzafuzzuk az oszlop nevet es az oszlop tipusat
create_table_code := create_table_code || CHR(10) || ' ' || tbl_row.COLUMN_NAME || ' ' || tbl_row.DATA_TYPE ;
-- Ha van karakter hosszusag tulajdonsag, akkor azt hozzairjuk
IF tbl_row.CHAR_COL_DECL_LENGTH IS NOT NULL AND tbl_row.DATA_TYPE LIKE '%CHAR%' THEN
create_table_code := create_table_code || '(' || tbl_row.CHAR_COL_DECL_LENGTH || ')';
-- Ha van DATA_SCALE es DATA_PRECISION tulajdonsag akkor azt is hozzairjuk
ELSIF tbl_row.DATA_SCALE IS NOT NULL THEN
create_table_code := create_table_code || '(' || tbl_row.DATA_PRECISION;
IF tbl_row.DATA_SCALE > 0 THEN
create_table_code := create_table_code || ',' || tbl_row.DATA_SCALE;
END IF;
create_table_code := create_table_code || ')';
END IF;
create_table_code := create_table_code || ',';
END LOOP;
CLOSE tbl_data;
-- Berakunk egy ujsort a konnyebb olvashatosag miatt, viszont elotte levagjuk a legutolso ,-t
create_table_code := RTRIM(create_table_code,',') || CHR(10) || ');';
-- Kiirjuk a CREATE TABLE utasitast
dbms_output.put_line(create_table_code);
END;
Tesztelés
CALL print_create_table('usernev', 'emp');
A példa Oracle SQLDeveloperrel készült. (Gálovics Arnold, 2014 1. félév)
Paraméterként kapott karaktersorozattal kezdődő táblák kiírása
Az eljárás kiírja az összes olyan tábla nevét, amely táblák a paraméterben megkapott karaktersorozattal kezdődnek.
-- Kiirja azokat a tablakat, amik a parameterben adott szoval kezdodik
-- p_kar: a karaktersorozat amivel a tablanak kezdodnie kell
-- meghivas: CALL print_table('a');
CREATE OR REPLACE PROCEDURE print_table(p_kar VARCHAR2) IS
-- Cursor a megadott lekerdezeshez
CURSOR curs IS SELECT table_name, owner FROM ALL_TABLES WHERE upper(table_name) LIKE upper(p_kar)||'%';
-- A lekerdezes tipusanak megfelelo rekord.
rec curs%ROWTYPE;
BEGIN
-- Kinyitjuk a kurzort
OPEN curs;
-- Ciklusban kiirjuk az osszes tabla nevet es tulajdonosat amit a lekerdezes visszaadott
LOOP
-- Betoltunk egy sort a rekordba
FETCH curs INTO rec;
-- Ha nincs mar tobb sor a kurzorban
EXIT WHEN curs%NOTFOUND;
-- Kirrjuk a tabla nevet es a tabla tulajdonosat
dbms_output.put_line(rec.table_name||' - '||rec.owner);
END LOOP;
CLOSE curs;
END;
Tesztelés
CALL print_table('a');
A példa Oracle SQLDeveloperrel készült. (Gálovics Arnold, 2014 1. félév)
Faktoriális függvény
Rekurzív módon implementált faktoriális függvény
-- Rekurziv faktorialis szamolo fuggveny
-- BEGIN
-- dbms_output.put_line(factorial(5));
-- END;
CREATE OR REPLACE FUNCTION factorial(n NUMBER) RETURN NUMBER IS
BEGIN
-- Ha n == 1 akkor a visszateresi ertek 1
IF n = 1 THEN
return 1;
END IF;
-- Egyebkent ha n <> 1 akkor pedig visszaterunk n*factorial(n-1)-el.
return n*factorial(n-1);
END;
Tesztelés
BEGIN
dbms_output.put_line(factorial(5));
END;
A példa Oracle SQLDeveloperrel készült. (Gálovics Arnold, 2014 1. félév)
BEFORE trigger
BEFORE trigger az EMP táblára vonatkozóan.
-- Egy salary update trigger. Amint akarmelyik sorban megvaltozik a salary oszlop erteke, a trigger torzse lefut.
-- teszt: update emp set sal=100 where ename='SMITH';
CREATE OR REPLACE TRIGGER salary_updated_trigger
-- Ezzel a kulcsszoval donthetjuk el, hogy a trigger mikor fusson le. (BEFORE/AFTER)
BEFORE
-- Itt definialhatjuk, hogy milyen esemeny hatasara fusson le. (INSERT, UPDATE, ...)
UPDATE OF sal
-- Tabla neve amire a triggert rakjuk.
ON emp
-- Minden egyes sorra ervenyes legyen.
FOR EACH ROW
-- Deklaracios resz
DECLARE
salary NUMBER(10);
BEGIN
-- Annak eldontesere, hogy milyen esemeny tortent egy switch vezerlesi szerkezetet hasznalunk
CASE
-- Ha a 'sal' oszlop updatelve lett valamelyik sorban, akkor ez az ag fut le.
WHEN UPDATING('sal') THEN
dbms_output.PUT_LINE('Updating salary');
-- Betoltjuk az uj 'salary' erteket a salary valtozoba
SELECT :new.sal INTO salary FROM dual;
dbms_output.PUT_LINE('Updated salary is: ' || salary);
END CASE;
END;
Tesztelés
UPDATE emp SET sal=100 WHERE ename='SMITH';
A példa Oracle SQLDeveloperrel készült. (Gálovics Arnold, 2014 1. félév)
AFTER trigger
AFTER trigger az EMP táblára vonatkozóan
-- Egy deptno update trigger. Amint akarmelyik sorban megvaltozik a salary oszlop erteke, a trigger torzse lefut.
-- teszt: update emp set deptno=10 where ename='SMITH';
CREATE OR REPLACE TRIGGER deptno_updated_trigger
-- Ezzel a kulcsszoval donthetjuk el, hogy a trigger mikor fusson le. (BEFORE/AFTER)
AFTER
-- Itt definialhatjuk, hogy milyen esemeny hatasara fusson le. (INSERT, UPDATE, ...)
UPDATE OF deptno
-- Tabla neve amire a triggert rakjuk.
ON emp
-- Minden egyes sorra ervenyes legyen.
FOR EACH ROW
-- Deklaracios resz
DECLARE
deptnumber NUMBER(3);
BEGIN
-- Annak eldontesere, hogy milyen esemeny tortent egy switch vezerlesi szerkezetet hasznalunk
CASE
-- Ha a 'deptno' oszlop updatelve lett valamelyik sorban, akkor ez az ag fut le.
WHEN UPDATING('deptno') THEN
-- Betoltjuk a regi 'deptno' erteket a deptnumber valtozoba
SELECT :old.deptno INTO deptnumber FROM dual;
dbms_output.PUT_LINE('Old deptno: ' || deptnumber);
-- Betoltjuk az uj 'deptno' erteket a deptnumber valtozoba
SELECT :new.deptno INTO deptnumber FROM dual;
dbms_output.PUT_LINE('New deptno: ' || deptnumber);
END CASE;
END;
Tesztelés
UPDATE emp SET deptno=10 WHERE ename='SMITH';
A példa Oracle SQLDeveloperrel készült. (Gálovics Arnold, 2014 1. félév)
Dinamikus SQL
PL/SQL kódba beágyazhatunk SQL utasításokat. Ezeket az utasításokat
a PL/SQL lefordítja, és minden futás során ugyanazokat az eredményeket
fogják produkálni (az adatbázis tartalmától függően). Ezeknek az
utasításoknak az alakja fordítási időben már ismert, ezért az
ilyen kódrészleteket statikus SQL utasításoknak nevezzük.
Azonban PL/SQL-ben lehetőségünk van olyan SQL utasításokat megfogalmazni,
melyek nem feltétlenül ismertek teljes egészükben fordítási időben,
egy részük csak futási időben dől el, hogy micsoda. Ezeket az
utasítsokat dinamikus SQL utasításoknak nevezzük, mert lehetőségünk
van futási időben - dinamikusan - változtatni azt. A dinamikus
SQL utasítások formailag karakterláncok egy változóban tárolva,
melyet kedvünkre módosíthatunk céljainktól függően.
Több esetben is szükségünk lehet arra munkánk közben, hogy
olyan módosítást eszközöljünk az adatbázison, aminek a
mibenléte csak futás közben derül ki: új objektumot (tárolt eljárást,
adatbázis táblát) szeretnénk létrehozni, csak egy utasítás
WHERE feltételén szeretnénk változtatni futás közben. Ilyen esetekben
dinamikus SQL-t kell használnunk.
Execute Immediate
Ilyen futási időben indított SQL utasításokat az EXECUTE IMMEDIATE
parancs segítségével tudunk futtatni, melynek szintaxisa a következő:
EXECUTE IMMEDIATE dinamikus_sztring
[INTO {változó [,változó]…|rekord}]
[USING [IN|OUT|IN OUT] kapcsoló_argumentum
[,[IN|OUT|IN OUT] kapcsoló_argumentum]…]
[{RETURNING|RETURN} INTO kapcsoló_argumentum
[,kapcsoló_argumentum]…];
A "dinamikus sztring" rendszerint egy varchar2 változóban tárolt
karakterlánc, maga az utasítás szövege. Ebbe az utasításba nem
szabad pontosvesszőt írnunk, mert akkor a PL/SQL invalid character
hibát fog dobni, és nem fog futni a kódunk.
A "változó" egy egyetlen adatot tároló adattag, a "rekord" pedig
egy teljes rekordot tároló adattag.
Az "INTO" kulcsszó akkor használatos, ha egy sort ad vissza a
select-ünk.
A USING kulcsszó után kötött változókat sorolhatunk fel, melyek
a dinamikus SQL utasításunkban (kettős ponttal) megjelölt helyekre
fognak behelyettesítődni. Érdemes így oldani meg a parancs felépítését,
mert ezen a módon megvédjük a programunkat az "SQL-injection" nevű
támadási formától.
A RETURNING INTO utasításrész olyan dinamikus DML utasítások esetén
használható, amelyek RETURNING utasításrészt tartalmaznak.
A kulcsszó után felsorolt változókba fog elmentődni a lekérdezés
által kinyert adat.
Minden EXECUTE IMMEDIATE végrehajtásakor a végrehajtandó parancs
elemzésre kerül, és érvénytelen utasítás esetén ugyanolyan hibát
kapunk, mint statikus SQL esetében.
create or replace package EMP_EI_TEST is
-- megkezdjuk a package header definialasat
-- a lenti 4 sor komment a PL/SQL Developer (amugy egeszen jo) fejlesztoi
-- kornyezete altal lett generalva
-- Author : Adam Ujvari
-- Created : 2014.05.08. 12:22:46
-- Purpose : Demonstrate the power of Execute Immediate
-- Tested on : 10g
-- deklaraljuk a publikus lathatosagu eljarasokat es fuggvenyeket
procedure INSERT_WITH_USING
(
pDeptNo in number,
pDname in varchar2,
pLoc in varchar2
);
function SELECT_INTO return number;
procedure MULTI_ROW_QUERY(pSal in number);
procedure CREATE_TABLES;
procedure DROP_TABLES;
procedure INJECT_SQL_UPD_SAL(userName in varchar2);
procedure INJECT_SQL_UPD_SAL_SAFE(userName in varchar2);
end EMP_EI_TEST; -- package header vege
/
create or replace package body EMP_EI_TEST is
-- package body, ide kerulnek a public lathatosagu fuggvenyek torzsei valamint a private lathatosagu eljarasok es fuggvenyek.
-- fuggveny definicio
procedure INJECT_SQL_UPD_SAL(userName in varchar2) is
-- 2000 hosszu varchar2 (azaz string) valtozo deklaralasa
command varchar2(2000);
begin
-- konkatenacioval szeretnenk osszeepiteni egy stringet amit majd kesobb futtatunk.
command := 'update emp set sal = 10000 where ename = ''' || userName || '''';
-- execute immediate futtatja futasi idoben a parancsot amit osszeepitettunk az elobb
execute immediate command;
end INJECT_SQL_UPD_SAL;
procedure INJECT_SQL_UPD_SAL_SAFE(userName in varchar2) is
begin
-- ha nem konkatenacioval epitunk parancsoakt, hanem hasznaljuk az execute imediate
-- using kulcsszavat, akkor megvedjuk a kodunkat (es a cegunket) a gonosz hackerek
-- tamadasaitol.
execute immediate 'update emp set sal = 10000 where ename = :param' using userName;
end INJECT_SQL_UPD_SAL_SAFE;
procedure INSERT_WITH_USING
(
-- parameterek megadasanal nem kell megadnunk, hogy mekkora lesz
-- a parameter valtozo (tehat nem kell szamot irnunk a number
-- es a varchar2 utan.
pDeptNo in number,
pDname in varchar2,
pLoc in varchar2
) is
-- konstans definicio: igy nem kell szenvednunk az
-- esetleges aposztrofokkal.
insert_script constant varchar2(2000) := Q'(
insert into dept values (:1, :2, :3)
)';
begin
-- hasznaljuk a using kulcsszot kotott parameterekhez
execute immediate insert_script
using pDeptNo, pDname, pLoc;
commit;
end;
function SELECT_INTO return number is
l_cnt varchar2(20);
-- konstans definicio: igy nem kell szenvednunk az
-- esetleges aposztrofokkal.
select_script constant varchar2(2000) := Q'(
select count(1) from emp
)';
begin
-- ha select parancsot futtatunk execute immediate-ben, akkor
-- használjuk az into kulcsszot, hogy egy valtozoba olvassuk
-- a lekerdezes eredmenyet. no_data_found exception persze
-- ilyenkor is kivaltodhat.
execute immediate select_script
into l_cnt;
return l_cnt;
end;
procedure MULTI_ROW_QUERY(pSal in number) is
-- szurjunk be tobb sort a temp tablaba
ins_multirow_script constant varchar2(2000) := Q'(
insert into temp(empno, ename)
select empno, ename
from emp where sal > :1
)';
begin
-- ismet kotott valtozot hasznalunk
execute immediate ins_multirow_script
using pSal;
end;
-- ezzel az eljarassal hozzuk letre a tablakat amiken dolgozni
-- fogunk majd a package tobbi fuggvenyevel
procedure CREATE_TABLES is
-- a fent bemutatott konstans definicios modszert
-- alkalmazzuk, mert igy konyebben atlathato
-- a kod amit majd futtatni fogunk
tbl_gen_script1 constant varchar2(30000) := Q'(
CREATE TABLE DEPT(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)))';
tbl_gen_script2 constant varchar2(30000) := Q'( CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)))';
tbl_gen_script3 constant varchar2(30000) := Q'( CREATE TABLE TEMP(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)))';
insert_script1 constant varchar2(30000) := Q'( INSERT INTO EMP
VALUES
(7369,
'SMITH',
'CLERK',
7902,
TO_DATE('1960.01.01', 'yyyy.mm.dd'),
800,
NULL,
20)
)';
insert_script2 constant varchar2(30000) := Q'( INSERT INTO EMP
VALUES
(7499,
'ALLEN',
'SALESMAN',
7698,
TO_DATE('1960.01.01', 'yyyy.mm.dd'),
1600,
300,
30)
)';
insert_script3 constant varchar2(30000) := Q'(
INSERT INTO EMP
VALUES
(7521,
'WARD',
'SALESMAN',
7698,
TO_DATE('1960.01.01', 'yyyy.mm.dd'),
1250,
500,
30)
)';
insert_script4 constant varchar2(30000) := Q'(
INSERT INTO EMP
VALUES
(7566,
'JONES',
'MANAGER',
7839,
TO_DATE('1960.01.01', 'yyyy.mm.dd'),
2975,
NULL,
20)
)';
insert_script5 constant varchar2(30000) := Q'(INSERT INTO EMP
VALUES
(7654,
'MARTIN',
'SALESMAN',
7698,
TO_DATE('1960.01.01', 'yyyy.mm.dd'),
1250,
1400,
30)
)';
insert_script6 constant varchar2(30000) := Q'(
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
)';
insert_script7 constant varchar2(30000) := Q'(
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS')
)';
begin
-- itt futtatjuk le a fent definialt programkodokat
execute immediate (tbl_gen_script1);
execute immediate (tbl_gen_script2);
execute immediate (tbl_gen_script3);
execute immediate (insert_script1);
execute immediate (insert_script2);
execute immediate (insert_script3);
execute immediate (insert_script4);
execute immediate (insert_script5);
execute immediate (insert_script6);
execute immediate (insert_script7);
end;
procedure DROP_TABLES is
-- a fent bemutatott konstans definicios modszert
-- alkalmazzuk, mert igy konyebben atlathato
-- a kod amit majd futtatni fogunk
drop_tbl_script1 constant varchar2(30000) := Q'( DROP TABLE EMP)';
drop_tbl_script2 constant varchar2(30000) := Q'( DROP TABLE DEPT)';
drop_tbl_script3 constant varchar2(30000) := Q'( DROP TABLE TEMP)';
begin
-- itt futtatjuk le a fent definialt programkodokat
execute immediate (drop_tbl_script1);
execute immediate (drop_tbl_script2);
execute immediate (drop_tbl_script3);
exception
when others then
-- hiba lehet, ha a tablak mar leteznek, vagy
-- valami miatt nincs jogunk torolni oket, ekkor
-- nem fog elszallni a programunk, mert kezeljuk
-- a kivetelt.
dbms_output.put_line('Cant drop tables.');
end;
end EMP_EI_TEST; -- ez a package definicio vege.
/
A package tárolt eljárásban először a package header-t definiáljuk.
Itt adjuk meg azoknak a függvényeknek a szingatúráját, amelyeket
publikus láthatósággal szeretnénk ellátni. Azok a függvények,
amelyek törzse implementálva lett a package törzsében, de
a szingnatúrájuk nincsen feltüntetve a headerben, azokat
private láthatósággal látja el az Oracle.
A package body-ban kell definiálnunk a függvényeket, esetleg
az objektumokat amelyeket létrehozunk (a példakódban nics ilyen),
valamint itt hozhatunk létre package szinten globális változókat is.
A csomagot a lenti névtelen blokkban található parancsok segítségével tudjuk
tesztelni olyan módon, hogy egy adott sorban eltávolítjuk a kommentet, és
futtatjuk a teljes névtelen blokkot.
declare
temp number(3);
begin
--EMP_EI_TEST.DROP_TABLES;
--EMP_EI_TEST.CREATE_TABLES;
--EMP_EI_TEST.INSERT_WITH_USING(70, 'AIRFORCE', 'SZEGED');
--temp := EMP_EI_TEST.SELECT_INTO;
--dbms_output.put_line(temp);
--EMP_EI_TEST.MULTI_ROW_QUERY(20);
--EMP_EI_TEST.INJECT_SQL_UPD_SAL('JONES'); -- works
--EMP_EI_TEST.INJECT_SQL_UPD_SAL(''' or ''1''=''1'); -- I'm bad
--EMP_EI_TEST.INJECT_SQL_UPD_SAL_SAFE('JONES'); -- works
--EMP_EI_TEST.INJECT_SQL_UPD_SAL_SAFE(''' or ''1''=''1');
end;
A példa package PL/SQL Developer IDE-ben készült
Ujvári Ádám 2013-2014 / 2
Függvény, eljárás, csomag
Adatbázis táblái: MUNKA, mely tartalmazza a beosztást, a hozzá tartozó azonosítót, fizetést, és százalékmegjelölést, amely megadja, hogy mennyi lehet az adott beosztással járó minimális fizetés. A második tábla a BEOSZTAS, mely a MUNKA táblával megegyezően épül fel. A harmadik tábla a TELEPHELY, amely tartalmazza a telephely azonosítóját, nevét, helyét. A negyedik tábla a DOLGOZO, mely tartalmazza a dolgozók azonosítóját, nevét, beosztásuk azonosítóját, főnökük azonosítóját, belépésük dátumát, fizetésük és jutalékuk összegét, telephelyük azonosítóját.
A táblák létrehozása és feltöltése:
create table munka
(
m_azon number(2) not null,
m_fogl varchar2(20) not null,
m_fiz number(10) not null,
m_szaz number(3) default 80
);
create table telephely
(
telephely_azon number(2) not null,
nev varchar2(15) not null,
hely varchar2(30) not null
);
create table beosztas
(
fogl_azon number(2) not null,
beosztas varchar2(20) not null,
fizetes number(10) not null,
szazalek number(3) default 80
);
create table dolgozo(
dolgozo_azon number(4) not null,
nev varchar2(20) not null,
fogl_azon number(2) not null,
fonok number(4),
belepes_dat date,
fizetes number(10),
jutalek number(10),
telephely_azon number(2)
);
insert into telephely (telephely_azon, nev, hely) values (11,'Központ','Budapest');
insert into telephely (telephely_azon, nev, hely) values (12,'Kutatás','Debrecen');
commit;
insert into beosztas (fogl_azon, beosztas, fizetes) values (10,'Elnök',500000);
insert into beosztas (fogl_azon, beosztas, fizetes, szazalek) values (20,'Igazgató',280000,90);
insert into beosztas (fogl_azon, beosztas, fizetes, szazalek) values (30,'Elemzõ',100000,100);
commit;
insert into dolgozo (dolgozo_azon, nev, fogl_azon, fonok, belepes_dat, fizetes, jutalek, telephely_azon) values
(1111,'Albert',30,1234,to_date('1981.02.20','YYYY.MM.DD'),160000,30000,12);
insert into dolgozo (dolgozo_azon, nev, fogl_azon, fonok, belepes_dat, fizetes, jutalek, telephely_azon) values
(1234,'Smidt',20,1222,to_date('1980.12.17','YYYY.MM.DD'),80000,null,11);
insert into dolgozo (dolgozo_azon, nev, fogl_azon, fonok, belepes_dat, fizetes, jutalek, telephely_azon) values
(1222,'Kiss',10,null,to_date('1981.04.02','YYYY.MM.DD'),125000,50000,11);
commit;
insert into munka (m_azon, m_fogl, m_fiz) values (15,'Alelnök',450000);
insert into munka (m_azon, m_fogl, m_fiz,m_szaz) values (20, 'Igazgató',400000,90);
insert into munka (m_azon,m_fogl,m_fiz,m_szaz) values (25,'Fõelemzõ',330000,100);
commit;
Az alábbiakban létrehozunk egy csomagot, amely egy eljárásból és két függvényből épül fel.
--Először létre kell hozni a csomag specifikációs részét.
create or replace package irodai_csomag as
function min_fiz(p_dolg in dolgozo.dolgozo_azon%type) return number;
function telephely_nev(p_telephely in telephely.telephely_azon%type) return varchar2;
procedure beosztas_update;
end irodai_csomag;
--Majd létrehozzuk a csomag törzsét.
Create or replace package body irodai_csomag as
function min_fiz(p_dolg in dolgozo.dolgozo_azon%type) return number as
/* Megadja, hogy az adott dolgozónak mennyi a foglalkozásának megfelelo minimum */
/* fizetés. (a foglalkozas tablaban megadott százalék szerinti fizetés) */
ret_val number;
v_beoszt number;
begin
select fogl_azon
into v_beoszt
from dolgozo
where dolgozo_azon = p_dolg;
select round(fizetes*szazalek/100,-2)
into ret_val
from beosztas
where fogl_azon=v_beoszt;
return ret_val;
exception when no_data_found then return null;
end min_fiz;
function telephely_nev(p_telephely in telephely.telephely_azon%type) return varchar2 as
/* Az iroda_nev függvény visszaadja az iroda azonosítója alapján a nevét. Ha nem megfelelo */
/* azonosítót kap, akkor a neve helyett 'Nem létezo iroda' szöveget adja vissza. */
ret_val telephely.nev%type;
begin
if p_telephely is null
then return null;
end if;
select nev into ret_val
from telephely where telephely_azon=p_telephely;
return ret_val;
exception when no_data_found then
return 'Nem létezo telephely';
end telephely_nev;
procedure beosztas_update as
/*A munka tábla adatait összefésüli a foglalkozas tábla adataiva: a létezoket karbantartja, */
/*a nem létezoket beszúrja a táblába. */
db number;
begin
for r in (select * from munka)
loop
begin
select count(*) into db
from beosztas
where fogl_azon=r.m_azon;
if db=0 then
insert into beosztas (fogl_azon, beosztas ,fizetes,szazalek) values (r.m_azon,r.m_fogl,r.m_fiz,r.m_szaz);
else
update beosztas
set beosztas = r.m_fogl,
fizetes=r.m_fiz,
szazalek=r.m_szaz
where fogl_azon=r.m_azon;
end if;
commit;
end;
end loop;
end beosztas_update;
end irodai_csomag;
A példacsomag PL/SQL Developer-ben készült
Kezdeti ügyfél és tranzakciókezelés egy megvalósítása PL/SQL-ben
NOTE: a következő példa nincs teljesen kész, jelenlegi állapotában ellenőrzi és végrehajtja a bejövő ügyfélregisztrációkat, valamint fel van készítve, hogy a további - az ügyfelekhez tartozó - tényleges vásárlási tranzakciókat feldolgozza, regisztrálja, az utána járó kedvezményeket kikalkulálja. Közepes mennyiségű munkával elég nagy méretű és komplexitású programmá is fejleszthető, illetve jó gyakorlási lehetőség, tekintettel arra, hogy általában nehéz találni normális PL/SQL kódtömeget. Bármilyen kérdés esetén küldj e-mailt a drdarkman@vipmail.hu címre!
------------------------------
--** DDL scripts
------------------------------
CREATE SEQUENCE GLOBAL_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 999999 MINVALUE 1;
CREATE TABLE input_table_reg
(
input_id NUMBER,
name VARCHAR2(300),
status VARCHAR2(50),
creation_date DATE
);
CREATE TABLE input_table_data
(
id NUMBER,
input_id NUMBER,
line_num NUMBER,
data VARCHAR2(1000)
);
CREATE TABLE customer
(
cust_id NUMBER,
name VARCHAR2(300),
email VARCHAR2(300),
city VARCHAR2(300),
is_vip NUMBER,
crd DATE,
lmd DATE,
app_date DATE
);
CREATE TABLE cust_x_cards
(
cust_id NUMBER,
card_id NUMBER
);
CREATE TABLE cust_balance
(
cust_id NUMBER,
balance NUMBER,
lmd DATE
);
CREATE TABLE shops
(
shop_id NUMBER,
name VARCHAR2(300),
city VARCHAR2(300),
crd DATE,
lmd DATE
);
CREATE TABLE products
(
prod_id NUMBER,
description VARCHAR2(300),
default_value NUMBER,
crd DATE,
lmd DATE
);
CREATE TABLE error_log
(
error_id NUMBER,
program_unit VARCHAR2(200),
message VARCHAR2(1000),
crd DATE
);
------------------------------
--** Program Units
------------------------------
--------------------------------------------------------
-- Procedure PROC_ERROR_LOG
--------------------------------------------------------
CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_ERROR_LOG"
(
p_prog_unit VARCHAR2 DEFAULT NULL,
p_msg VARCHAR2 DEFAULT NULL
) AS
/*Create error log with a few params, easily callable in every program unit and
get the stack's content with it*/
v_msg VARCHAR2(1000) := p_msg || ' ' || SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), 1, (1000 - length(p_msg)));
BEGIN
INSERT INTO error_log(error_id,program_unit,message,crd)
VALUES(global_seq.nextval , p_prog_unit, v_msg, SYSDATE);
COMMIT;
END proc_error_log;
/
--------------------------------------------------------
-- Procedure PROCESS_INPUT
--------------------------------------------------------
CREATE OR REPLACE PROCEDURE "SYSTEM"."PROCESS_INPUT"
AS
--Purpose: Process all proper input, depends on its statuses
-- from the input_table
--2014.05.08.; DARKMAN;
--Constants
c_proc_name CONSTANT VARCHAR2(18) := 'PROCESS_INPUT';
c_file_name CONSTANT VARCHAR2(4) := 'FILE';
c_call_name CONSTANT VARCHAR2(4) := 'CALL';
--Variables
v_name input_table_reg.name%TYPE;
v_error_msg error_log.message%TYPE;
error_excp EXCEPTION;
CURSOR cr_input(p_status VARCHAR2) IS
SELECT *
FROM input_table_reg
WHERE status = p_status
ORDER BY creation_date;
BEGIN
--open cursor and process the incoming data per input_type
FOR cr_i IN cr_input(pk_const.g_start_status) LOOP
SELECT name
INTO v_name
FROM input_table_reg
WHERE input_id = cr_i.input_id;
IF upper(substr(v_name, 1, 4)) = c_file_name THEN
process_input_file(cr_i.input_id);
ELSIF upper(substr(v_name, 1, 4)) = c_call_name THEN
process_input_call(cr_i.input_id);
END IF;
END LOOP;
EXCEPTION
WHEN error_excp THEN
proc_error_log(c_proc_name, v_error_msg);
WHEN OTHERS THEN
proc_error_log(c_proc_name, v_error_msg);
END process_input;
/
--------------------------------------------------------
-- Procedure PROCESS_INPUT_CALL
--------------------------------------------------------
CREATE OR REPLACE PROCEDURE "SYSTEM"."PROCESS_INPUT_CALL" (p_id NUMBER)
AS
--Purpose: Process a specified input calls per ID,
--execute the customer registration, without input-checks(!)
--Processable incoming pattern:
--APP_DATE(YYYYMMDDHH24MISS);NAME;EMAIL;CITY;CUST_ID
--2014.05.08.; DARKMAN;
c_proc_name CONSTANT VARCHAR2(18) := 'PROCESS_INPUT_CALL';
v_app_date customer.app_date%TYPE;
v_name customer.name%TYPE;
v_email customer.email%TYPE;
v_city customer.city%TYPE;
v_cust_id customer.cust_id%TYPE;
v_error_msg error_log.message%TYPE;
v_count NUMBER := 0;
error_excp EXCEPTION;
CURSOR cr_data(p_id NUMBER) IS
SELECT *
FROM input_table_data
WHERE input_id = p_id;
BEGIN
FOR cr_d IN cr_data(p_id) LOOP
v_app_date := to_date(substr(cr_d.data,0,12), 'YYYYMMDDHH24MI');
v_name := rtrim(substr(cr_d.data,14,100));
v_email := rtrim(substr(cr_d.data,115,50));
v_city := rtrim(substr(cr_d.data,166,50));
v_cust_id := to_number(substr(cr_d.data,217,7));
INSERT INTO customer(cust_id , name , email , city , is_vip, crd , lmd , app_date)
VALUES(v_cust_id, v_name, v_email, v_city, 0 , SYSDATE, SYSDATE, v_app_date );
END LOOP;
UPDATE input_table_reg
SET status = pk_const.g_proc_status
WHERE input_id = p_id;
COMMIT;
EXCEPTION
WHEN error_excp THEN
proc_error_log(c_proc_name, v_error_msg);
ROLLBACK;
WHEN OTHERS THEN
proc_error_log(c_proc_name, v_error_msg);
ROLLBACK;
END;
/
--------------------------------------------------------
-- Procedure PROCESS_INPUT_FILE
--------------------------------------------------------
CREATE OR REPLACE PROCEDURE "SYSTEM"."PROCESS_INPUT_FILE" (p_id NUMBER) AS
--Purpose: Process a specified input files per ID
--NOTE: the "game" begins here, checks are created,
-- but the further logic is all on you :)
--2014.05.08.; DARKMAN; Created.
c_proc_name CONSTANT VARCHAR2(18) := 'PROCESS_INPUT_FILE';
v_card_id cust_x_cards.card_id%TYPE;
v_shop_id shops.shop_id%TYPE;
v_prod_id products.prod_id%TYPE;
v_points NUMBER;
v_quantity NUMBER;
v_date DATE;
v_error_msg error_log.message%TYPE;
v_count NUMBER := 0;
error_excp EXCEPTION;
CURSOR cr_data(p_id NUMBER) IS
SELECT *
FROM input_table_data
WHERE input_id = p_id;
BEGIN
FOR cr_d IN cr_data(p_id) LOOP
v_card_id := to_number(substr(cr_d.data,0,7));
v_shop_id := to_number(substr(cr_d.data,9,3));
v_prod_id := to_number(substr(cr_d.data,13,5));
v_points := to_number(substr(cr_d.data,19,7));
v_quantity := to_number(substr(cr_d.data,27,6));
v_date := to_date(substr(cr_d.data,34,12), 'YYYYMMDDHH24MI');
--CHECK card_id
BEGIN
SELECT COUNT(1)
INTO v_count
FROM cust_x_cards
WHERE card_id = v_card_id;
EXCEPTION
WHEN INVALID_NUMBER THEN
v_error_msg := 'Hiba a kártyaszám kinyerésekor: Hibás formátum! ';
RAISE error_excp;
WHEN NO_DATA_FOUND THEN
v_error_msg := 'Hiba a kártyaszám kinyerésekor: Nincs ilyen kártyaszám! ';
RAISE error_excp;
END;
--CHECK shop_id
BEGIN
SELECT COUNT(1)
INTO v_count
FROM shops
WHERE shop_id = v_shop_id;
EXCEPTION
WHEN INVALID_NUMBER THEN
v_error_msg := 'Hiba az üzlet kinyerésekor: Hibás formátum! ';
RAISE error_excp;
WHEN NO_DATA_FOUND THEN
v_error_msg := 'Hiba az üzlet kinyerésekor: Nincs ilyen üzlet! ';
RAISE error_excp;
END;
--CHECK prod_id
BEGIN
SELECT COUNT(1)
INTO v_count
FROM products
WHERE prod_id = v_prod_id;
EXCEPTION
WHEN INVALID_NUMBER THEN
v_error_msg := 'Hiba a termék kinyerésekor: Hibás formátum! ';
RAISE error_excp;
WHEN NO_DATA_FOUND THEN
v_error_msg := 'Hiba a termék kinyerésekor: Nincs ilyen termék! ';
RAISE error_excp;
END;
END LOOP;
EXCEPTION
WHEN error_excp THEN
proc_error_log(c_proc_name, v_error_msg);
WHEN OTHERS THEN
proc_error_log(c_proc_name, v_error_msg);
END;
/
--------------------------------------------------------
-- Package PK_CONST
--------------------------------------------------------
CREATE OR REPLACE PACKAGE pk_const AS
/*
Contains all database-global constant variables
*/
g_start_status CONSTANT VARCHAR2(11) := 'INPUT_START';
g_check_status CONSTANT VARCHAR2(13) := 'INPUT_CHECKED';
g_proc_status CONSTANT VARCHAR2(15) := 'INPUT_PROCESSED';
g_finish_status CONSTANT VARCHAR2(13) := 'INPUT_FINISHED';
END pk_const;
/
A példa Oracle SQLDeveloperrel készült, Oracle 12c adatbázison. (Tóth Gábor, 2014, II. félév)