Preuzeto sa http://www.outsourcing-it.com
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:
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‘
(izvorni kod je na dnu teksta ), 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.
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 kodu 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)?
IZVORNI KOD:DELIMITER $$
DROP PROCEDURE IF EXISTS `testDB`.`popuni_tabele` $$
CREATE PROCEDURE `testDB`.`popuni_tabele` (IN p_broj_korisnika INT, OUT p_poruka VARCHAR(255))
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'Ova procedura sluzi za popunjavanje tabela ''korisnici'' i ''transakcije'' '
BEGIN
-- deklaracija potrebnih varijabli
DECLARE v_counter INT DEFAULT 0;
DECLARE v_broj_transakcija INT DEFAULT 0;
DECLARE v_oduzmi_id INT;
DECLARE v_dodaj_id INT;
DECLARE v_iznos DOUBLE(10,2);
DECLARE nema_vise_redova BOOLEAN DEFAULT false;
DECLARE cur_korisnici CURSOR FOR
SELECT k1.id AS oduzmi, k2.id AS dodaj
FROM korisnici k1, korisnici k2
WHERE k1.id <> k2.id;
DECLARE continue HANDLER FOR SQLSTATE '02000' SET nema_vise_redova = true;
DECLARE exit HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_poruka = 'Unos prekinut zbog SQLExceptiona';
END;
-- oznacimo pocetak transakcije
START TRANSACTION;
-- petlja_za_unos_podataka
WHILE v_counter <>
DO
-- povecaj brojac za 1
SET v_counter = v_counter + 1;
INSERT INTO korisnici (id, ime, prezime, iznos, email)
VALUES ( NULL,
CONCAT('ime_', v_counter),
CONCAT('prezime_', v_counter),
RAND() * (10/RAND()) * 1000,
CONCAT('email', MOD(v_counter, 5), '@domena', MOD(v_counter, 7), '.com')
);
END WHILE;
-- otvori cursor sa korisnicima
OPEN cur_korisnici;
-- otvori petlju sa korisnicima
petlja_sa_korisnicima: LOOP
-- pokupi vrijednosti iz cursora
FETCH cur_korisnici INTO v_oduzmi_id, v_dodaj_id;
-- ako cursor ne sadrzi vise redova, zavrsi sa petljom
IF nema_vise_redova THEN
LEAVE petlja_sa_korisnicima;
END IF;
-- povecaj brojac transakcija za 1
SET v_broj_transakcija = v_broj_transakcija + 1;
SET v_iznos = RAND() * (MOD(v_broj_transakcija, 33) + 10);
-- necemo vrsiti provjeru koliko je trenutni korisnikov iznos,
-- nego cemo samo unijesti podatke radi demonstracije funkcionisanja:
UPDATE korisnici
SET iznos = iznos - v_iznos
WHERE id = v_oduzmi_id;
UPDATE korisnici
SET iznos = iznos + v_iznos
WHERE id = v_dodaj_id;
-- unesi transakciju u tabelu
INSERT INTO transakcije(id, ovome_oduzmi, ovome_dodaj, iznos, opis_transakcije)
VALUES (NULL, v_oduzmi_id, v_dodaj_id, v_iznos, CONCAT('Transakcija broj ', v_broj_transakcija));
-- zatvori petlju sa korisnicima
END LOOP petlja_sa_korisnicima;
CLOSE cur_korisnici;
-- potvrdimo unos i oznacimo kao kraj transakcije
COMMIT;
-- sastavimo OUT poruku
SET p_poruka = CONCAT('Broj unesenih korisnika: ', v_counter, '; Broj obavljenih transakcija: ', v_broj_transakcija);
-- prikazimo sadrzaj poruke:
SELECT p_poruka;
END $$
DELIMITER ;
Z.Z.Baćo Razvojni tim
