Kreiranje uskladistene procedure (How to create MySQL stored procedure)
Tuesday, 10.10.2006 – DejanNovije verzije MySQL-a podrzavaju izmedju ostalog i uskladistene procedure (stored procedures), pa bih zelio prikazati na jednom primjeru, kako se one kreiraju i koriste.
Za primjer cemo uzeti jednu novcanu transakciju, odnosno prebacivanje novca sa racuna jednog korisnika na racun drugog korisnika. Kreirajmo dvije jednostavne tabele ‘korisnici‘ i ‘transakcije‘, u kojima cemo drzati podatke o korisnicima i obavljenim transakcijama:
CREATE TABLE `testDB`.`korisnici` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`ime` VARCHAR(45) NOT NULL DEFAULT '',
`iznos` DOUBLE(10,2),
`prezime` VARCHAR(45) NOT NULL DEFAULT '',
`email` VARCHAR(100),
PRIMARY KEY(`id`),
UNIQUE INDEX (`email`)
)
ENGINE = InnoDB
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Test tabela za uskladistenu proceduru';
CREATE TABLE `testDB`.`transakcije` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`ovome_oduzmi` INTEGER UNSIGNED,
`ovome_dodaj` INTEGER UNSIGNED,
`iznos` DOUBLE(10,2),
`opis_transakcije` VARCHAR(255),
PRIMARY KEY(`id`),
CONSTRAINT `FK_transakcije_oduzmi`
FOREIGN KEY `FK_transakcije_oduzmi` (`ovome_oduzmi`)
REFERENCES `korisnici` (`id`)
ON DELETE SET NULL,
CONSTRAINT `FK_transakcije_dodaj`
FOREIGN KEY `FK_transakcije_dodaj` (`ovome_dodaj`)
REFERENCES `korisnici` (`id`)
ON DELETE SET NULL
)
ENGINE = InnoDB
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Lookup tabela sa transakcijama';
Napomena: Da biste koristili ACID transakcije u MySQL-u, morate imati aktiviran InnoDB engine.
Nakon sto smo kreirali te dvije tabele, popunicemo ih nekim testnim podacima, a za to cemo upotrebiti jednu uskladistenu proceduru ‘popuni_tabele‘ (Download: izvorni k么d ; Download: screenshot). Kreirajte tu proceduru koristeci prilozeni izvorni k么d i pozovite ju sa odgovarajucim parametrima – prvi parametar oznacava broj korisnika, koje zelite kreirati, a u drugom parametru ce se nalaziti izlazna poruka procedure:
mysql> call popuni_tabele(30, @poruka); +----------------------------------------------------------------------------+ | p_poruka | +----------------------------------------------------------------------------+ | Broj unesenih korisnika: 30; Broj obavljenih transakcija: 870 | +----------------------------------------------------------------------------+ Query OK, 0 rows affected (0.29 sec) mysql>SELECT @poruka; +----------------------------------------------------------------------------+ | @poruka | +----------------------------------------------------------------------------+ | Broj unesenih korisnika: 30; Broj obavljenih transakcija: 870 | +----------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Sada bi se u tabelama trebalo nalaziti dovoljno podataka za testiranje.
Za obavljanje jedne novcane transakcije, kreiracemo proceduru ‘obavi_transakciju‘ (Download: izvorni k么d; Download: screenshot), koja prihvata slijedece parametre:
– p_oduzmi_id (broj korisnika, kojemu se skida odredjeni iznos sa racuna),
– p_dodaj_id (broj korisnika, kojemu se dodaje odredjeni iznos na racun),
– p_iznos (iznos, koji ce se jednom korisniku skinuti sa racuna, a drugom dodati),
– p_opis (opis transakcije) i
– p_poruka (izlazna poruka procedure).
DELIMITER $$
DROP PROCEDURE IF EXISTS `testDB`.`obavi_transakciju` $$
CREATE PROCEDURE `testDB`.`obavi_transakciju`(IN p_oduzmi_id INT,
IN p_dodaj_id INT,
IN p_iznos DOUBLE,
IN p_opis VARCHAR(255),
OUT p_poruka VARCHAR(255))
BEGIN
DECLARE v_trenutno_stanje DOUBLE(10, 2) DEFAULT 0;
BEGIN
DECLARE exit HANDLER FOR NOT FOUND
SET p_poruka = 'Korisnik nije pronadjen pod zadanim brojem!';
DECLARE exit HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_poruka = 'Greska';
END;
-- oznaci pocetak transakcije
START TRANSACTION;
-- provjeri da li korisnik, kojemu se skida sa racuna, postoji i da li
-- ima dovoljno love da mu se zadani iznos moze skinuti:
SELECT iznos
INTO v_trenutno_stanje
FROM korisnici
WHERE id = p_oduzmi_id;
-- ako ima, onda obavi transakciju:
IF v_trenutno_stanje > p_iznos THEN
UPDATE korisnici
SET iznos = iznos - p_iznos
WHERE id = p_oduzmi_id;
UPDATE korisnici
SET iznos = iznos + p_iznos
WHERE id = p_dodaj_id;
-- sacuvaj informacije o obavljenoj transakciji:
INSERT INTO transakcije (id, ovome_oduzmi, ovome_dodaj, iznos, opis_transakcije)
VALUES (NULL, p_oduzmi_id, p_dodaj_id, p_iznos, p_opis);
ELSE
SET p_poruka = 'Korisnik nema dovoljno love';
END IF;
END;
-- potvrdi unos i oznaci kraj transakcije
-- ili ponisti unos
IF p_poruka != '' THEN
ROLLBACK;
ELSE
COMMIT;
SET p_poruka = 'SUCCESS! Transakcija je uspjesno obavljena';
END IF;
SELECT p_poruka;
END $$
DELIMITER ;
Najprije cemo prikazati rezultat nekoliko testova, a onda slijedi objasnjenje kompletne procedure.
Test 1.
Sa racuna korisnika 1 potrebno je prebaciti 500 EUR na racun korisnika 2.
Pogledajmo prvo trenutno stanje racuna oba korisnika:
mysql> SELECT * FROM korisnici WHERE id IN(1, 2); +----+--------+----------+------------+--------------------+ | id | ime | iznos | prezime | email | +----+--------+----------+------------+--------------------+ | 1 | ime_1 | 8813.18 | prezime_1 | email1@domena1.com | | 2 | ime_2 | 10384.60 | prezime_2 | email2@domena2.com | +----+--------+----------+------------+--------------------+
Pozovimo proceduru ‘obavi_transakciju‘:
mysql> CALL obavi_transakciju(1, 2, 500.00, 'Prebaci 500 EUR
-> sa racuna korisnika 1 na racun korisnika 2', @poruka);
+--------------------------------------------+
| p_poruka |
+--------------------------------------------+
| SUCCESS! Transakcija je uspjesno obavljena |
+--------------------------------------------+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.06 sec)
Provjerimo opet trenutno stanje oba korisnika nakon obavljene transakcije:
mysql> SELECT * FROM korisnici WHERE id IN (1, 2); +----+-------+----------+-----------+--------------------+ | id | ime | iznos | prezime | email | +----+-------+----------+-----------+--------------------+ | 1 | ime_1 | 8313.18 | prezime_1 | email1@domena1.com | | 2 | ime_2 | 10884.60 | prezime_2 | email2@domena2.com | +----+-------+----------+-----------+--------------------+ 2 rows in set (0.00 sec)
Iznos kod prvog korisnika se smanjio za 500, a kod drugog se povecao za 500. Provjerimo i u tabeli ‘transakcije’, da li je transakcija uspjesno obavljena:
mysql> SELECT * FROM transakcije ORDER BY id DESC LIMIT 1\G
*************************** 1. row ***************************
id: 875
ovome_oduzmi: 1
ovome_dodaj: 2
iznos: 500.00
opis_transakcije: Prebaci 500 EUR sa racuna korisnika 1 na racun korisnika 2
1 row in set (0.00 sec)
Dakle, test je uspjesno obavljen.
Test 2.
Sa racuna korisnika 1 prebacimo na racun korisnika 2 veci iznos, nego sto korisnik 1 posjeduje. Transakcija ne smije biti obavljena.
Trenutno stanje je isto kao nakon obavljene transakcije u prvom testu. Pozovimo proceduru ‘obavi_transakciju‘:
mysql> CALL obavi_transakciju(1, 2, 9999.99, 'Sa racuna korisnika 1 prebaci -> na racun korisnika 2 veci iznos nego sto korisnik 1 posjeduje', @poruka); +-----------------------------+ | p_poruka | +-----------------------------+ | Korisnik nema dovoljno love | +-----------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
Provjerimo trenutno stanje:
mysql> SELECT * FROM korisnici WHERE id IN (1, 2); +----+-------+----------+-----------+--------------------+ | id | ime | iznos | prezime | email | +----+-------+----------+-----------+--------------------+ | 1 | ime_1 | 8313.18 | prezime_1 | email1@domena1.com | | 2 | ime_2 | 10884.60 | prezime_2 | email2@domena2.com | +----+-------+----------+-----------+--------------------+ 2 rows in set (0.00 sec)
Kao sto vidite, stanje je nepromijenjeno. Provjeravanjem tabele ‘transakcije‘, uvjericemo se da transakcija nije obavljena.
Test 3.
Pokusajmo prebaciti 500 EUR sa racuna nepostojeceg korisnika na racun korisnika 2. Transakcija nece biti obavljena i pojavice se greska o nepostojecem korisniku.
Pozovimo proceduru ‘obavi_transakciju‘:
mysql> CALL obavi_transakciju(31, 2, 500.00, 'Prebaci 500 EUR sa
-> racuna korisnika 31 na racun korisnika 2', @poruka);
+---------------------------------------------+
| p_poruka |
+---------------------------------------------+
| Korisnik nije pronadjen pod zadanim brojem! |
+---------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Provjeravanjem trenutnog stanja i liste obavljenih transakcija, uvjericemo se da transakcija nije obavljena, jer zadani korisnik ne postoji.
Objasnjenje procedure ‘obavi_transakciju’
Prvo pogledajte prilozeni screenshot, na kojem se nalazi izvorni k么d procedure uz brojevima oznacene linije.
Linija 1: Posto se u MySQL-u znak ‘;‘ koristi za terminaciju nekog izraza, odnosno kao ‘end-of-query‘ terminator, u uskladistenim procedurama i funkcijama moramo koristiti neki drugi terminator (delimiter). Drugi delimiter postavljamo pomocu naredbe DELIMITER, a u ovoj proceduri smo kao delimiter uzeli $$.
Linija 3: MySQL trenutno ne podrzava izraz ‘CREATE OR REPLACE …” pomocu kojeg bismo jednostavno mogli kreirati novu proceduru ili zamijeniti vec postojecu, pa zato moramo najprije ukloniti postojecu proceduru.
Linije 5-9: Naredbom ‘CREATE PROCEDURE‘ kreiramo zeljenu proceduru sa odgovarajucim parametrima. Parametri mogu biti IN (ulazni), OUT (izlazni) i INOUT (ulazno-izlazni). U ovom slucaju imamo 4 ulazna i 1 izlazni parametar.
Linija 10: Naredbom BEGIN zapocinjemo izvrsni dio procedure (routine body), odnosno blok (compund statement) u kojem se izvrsavaju odredjeni izrazi.
Linija 11: Vrsimo deklaraciju varijable v_trenutno_stanje, u koju cemo spremati trenutni iznos racuna od zadanog korisnika.
Linija 13: Naredbom BEGIN zapocinjemo unutrasnji (ugnijezdjeni, nested) blok izraza.
Linija 14: Vrsimo deklaraciju handler-a (btw. moze li mi neko smisleno prevesti ovu rijec handler?) za odredjeni uslov/uzrok. Kada dodje do zadanog uzroka, handler izvrsava zadane naredbe. Trenutno postoje 2 vrste handler-a: CONTINUE i EXIT, a osim njih, tu je jos i handler UNDO, koji ce biti podrzan u nekoj od iducih verzija MySQL-a.
Ako za odredjeni uslov definisemo CONTINUE handler, onda ce izvrsavanje bloka, u kojem se taj handler nalazi, biti nastavljeno bez prekida.
Ako za odredjeni uslov definisemo EXIT handler, onda ce izvrsavanje bloka, u kojem se taj handler nalazi, biti prekinuto.
Ako za odredjeni uslov definisemo UNDO handler, onda ce izvrsavanje bloka, u kojem se taj handler nalazi, biti prekinuto i sve izmjene uzrokovane tom procedurom ce biti ponistene (ROLLBACK).
Uslov (condition) moze biti naveden kao oznaka neke MySQL greske i to u obliku kao:
– SQLSTATE vrijednost
– predefinisana oznaka za neke od generalnih uslova: NOT FOUND, SQLWARNING ili SQLEXCEPTION
– rucno (proizvoljno) deklarisan uslov, koji je vezan za odredjeno stanje (SQLSTATE) ili neku MySQL gresku
SQLWARNING obuhvata sve SQLSTATE k么dove, koji zapocinju sa 01.
NOT FOUND obuhvata sve SQLSTATE k么dove, koji zapocinju sa 02.
SQLEXCEPTION obuhvata sve SQLSTATE k么dove, koji nisu obuhvaceni sa SQLWARNING ili NOT FOUND.
U nasem slucaju smo za uslov NOT FOUND definisali handler EXIT. To znaci, da ce uslov NOT FOUND biti ispunjen, ukoliko SELECT naredba u proceduri (linije 28-31) ne pronadje zadanog korisnika. Posto taj uslov bude ispunjen, handler EXIT ce biti automatski pozvan, pri cemu ce u izlaznu varijablu p_poruka spremiti odgovarajuci tekst i nakon toga prekinuti daljnje izvrsavanje tog bloka.
Linije 17-21: Vrsimo deklaraciju handler-a za sve uslove, koji nisu prethodno obuhvaceni. Ukoliko dodje do neke greske, ponistavaju se sve izmjene nad podacima (ROLLBACK) i smjestamo odgovarajuci tekst u izlaznu varijablu p_poruka.
Linija 23: U izvornom k锟絛u mozemo ubacivati proizvoljne komentare. Komentar zapocinje sa duplom crticom.
Linija 24: Naredbom ‘START TRANSACTION‘ oznacavamo pocetak transakcije.
Linije 28-31: Ovim SELECT upitom provjeravamo da li zadani korisnik postoji (p_oduzmi_id) i koliko love ima trenutno na racunu. Ukoliko zadani korisnik nije pronadjen, odnosno ukoliko ne postoji, bice uzrokovan uslov NOT FOUND i handler EXIT ce prekinuti daljnje izvrsavanje ovog bloka.
Linije 34-48: Ukoliko zadani korisnik postoji i ukoliko na racunu trenutno ima veci iznos od iznosa, koji mu se mora skinuti, onda mu se zadani iznos (p_iznos) skida sa racuna i prebacuje na racun drugog zadanog korisnika (p_dodaj_id). Nakon sto to prebacivanje novca izvrsi, sacuvacemo informacije o toj obavljenoj transakciji.
Ukoliko zadani korisnik postoji, ali nema dovoljno love na racunu, prebacivanje novca nece biti obavljeno i u izlaznu poruku ce biti ubacen tekst “Korisnik nema dovoljno love“.
Linija 49: Naredbom END oznacavamo kraj unutrasnjeg (nested) bloka.
Linije 53-58: Provjeravamo da li je izlazna varijabla prazna ili nije.
Ukoliko jeste, onda je sve u redu i mozemo da potvrdimo uspjesno obavljenu transakciju sa naredbom COMMIT, pri cemu u izlaznu varijablu ubacujemo tekst o uspjesno obavljenoj transakciji.
Ukoliko nije, ponistavaju se sve izmjene sa naredbom ROLLBACK.
Linija 60: Opcionalno mozete izlistati sadrzaj varijable p_poruka.
Linija 61: Naredbom END oznacavamo kraj glavnog bloka. Delimiterom $$ izvrsavamo prethodno napisanu proceduru.
Linija 63: Vracamo delimiter na originalnu prvobitnu vrijednost ‘;‘.
Procedura je spremna za koristenje. 馃檪
Zasto koristiti uskladistene procedure?
Mnogo zadataka se moze obaviti i izvan nekog RDBMS-a, bez obzira koji programski jezik ili platformu koristili. Medjutim, neke zadatke je daleko lakse i brze izvesti direktno u RDBMS-u, cime ce aplikacija biti rasterecena i efikasnija u obavljanju preostalih zadataka.
Pretpostavimo da morate ovaj zadatak (novcana transakcija) obaviti pomocu PHP-a i MySQL-a. Ne smijem ni zamisliti koliko izvornog k么da biste morali napisati u PHP-u i koliko zahtjeva biste morali proslijediti MySQL-u da bi obavio sve naredbe potrebne za uspjesno obavljanje ove transakcije.
Uz pomoc ove uskladistene procedure, iz PHP-a je potrebno samo pozvati ovu proceduru i proslijediti odgovarajuce parametre.
Osim toga, zamislite da morate obaviti mnogo operacija nad vise tabela (sumiranje, grupisanje, sortiranje i sl.) – zar se ne namece samo po sebi rjesenje sa uskladistenim rutinama (procedurama i funkcijama)?
Za kraj bih mogao samo reci da je MySQL u verziji 5.* dosta napredovao, tako da ce svi ovi novi dodaci ubrzo biti masovno koristeni.
Samo jos da hosting firme redovno upgrade-uju MySQL… 馃槈
2 Responses to “Kreiranje uskladistene procedure (How to create MySQL stored procedure)”
Ne mogu da verujem da postoje ovakve pomoci i naravno ljudi koji su zeljni da pomognu. Zaista sam zahvalan jer mi pomazete da resim neke nedoumice. Dugo sam izbegavao mysql misleci da nema proceduralne mogucnosti, pa bih iskoristo da pitam kakva su sve mogucnosti mySQL u tom smislu (proceduralno programiranje) i kakva je tu buducnost. Ja sam se dosta ozbiljno bavio ORACLE-om (developer i PL-sql programiranjem), ali sam na moju nesrecu pre vise od 5 godina prestao jer sam radio relativno dobro placen posau na vfp. Hteo bih sada da se vratim ORACLE-u zbog odrzavanja kondicije, a od Vas bih molio analizu sta se sve moze, as sta ne ocekivati od MYSQL u sporedbi sa ORACLE-om
By Desimir on Feb 28, 2007
Sto se tice proceduralnog programiranja u MySQL-u u odnosu na Oracle, onda te moram razocarati … MySQL je jos u pelenama u odnosu na Oracle, ali vremenom se dodaju mnoge novotarije – izmedju ostalog i uskladistene procedure.
Sve je stvar budzeta – ako imas love, uzmi Oracle. U suprotnom MySQL. 馃檪
Mada, mozda ti moze i MySQL biti dovoljan u mnogim slucajevima…
By Dejan on Oct 2, 2008