Kreiranje uskladistene procedure u MySQL-u
(How to create MySQL stored procedure)
Novije 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:
mysql> CREATE TABLE `testDB`.`korisnici` (
-> `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `ime` VARCHAR(45) NOT NULL DEFAULT '',
-> `prezime` VARCHAR(45) NOT NULL DEFAULT '',
-> `iznos` DOUBLE(10,2),
-> `email` VARCHAR(100),
-> PRIMARY KEY(`id`),
-> UNIQUE INDEX (`email`)
-> )
-> ENGINE = InnoDB
-> CHARACTER SET utf8
-> COLLATE utf8_general_ci
-> COMMENT = 'Test tabela za uskladistenu proceduru';
Query OK, 0 rows affected (0.06 sec)
mysql> 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';
Query OK, 0 rows affected (0.09 sec)
Nakon sto smo kreirali te dvije tabele, popunicemo ih nekim testnim podacima, a za to cemo upotrijebiti 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).
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ôdu 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 da zamislim 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... ;)
Nazad na MySQL stranicu sa tutorijalima | Nazad na MySQL stranicu | Nazad na glavnu stranicu
