Uskladištene procedure su blokovi SQL koda, koji su u stanju da izvrše neku aktivnost na serveru. Ovi blokovi koda su najčešće apstraktni i prihvataju parametre, tako da su upotrebljivi više puta i u različitim situacijama.

Svrha uskladištenih procedura je automatizacija koda i brzina rukovanja podacima. Što se tiče prvog, verovatno je prilično jasno, jer su uskladištene procedure, veoma slične funkcijama iz bilo kog modernog programskog jezika.

Brzina rukovanja podacima možda je malo manje jasna. Poenta je zapravo u brzini rukovanja podacima od strane aplikacije koja rukuje bazom. Umesto da aplikacija, koja rukuje sa bazom, šalje na server i izvršava sekvencijalno SQL naredbe, ona može samo proslediti parametar nekoj uskladištenoj proceduri na serveru, koja posle može sama uraditi kompletan proces, bez daljeg kontakta sa aplikacijom. Dakle, aplikacija je, umesto x broja SQL naredbi, prosledila serveru samo jedan parametar.

MySQL, za razliku od drugih proizvođača baza podataka, uskladištene procedure stavlja u kategoriju uskladištenih rutina, gde se nalaze i korisnički definisane funkcije, pa se funkcije i procedure smatraju veoma sličnim u semantičkom smislu, iako postoje neke različitosti u njihovoj funkcionalnosti i izvođenju.

Uskladištena (Stored) procedura je objekat na serveru, kao i svaki drugi. Stoga, da bismo je kreirali, koristimo DDL naredbu CREATE:

create procedure mp () select * from orders;


Iako jeste validno kreirana (i funkcioniše), ovo zapravo i nije simbol prave uskladišetne procedure, jer izvršava samo jednu naredbu (SELECT). Zato će sintaksa za kreiranje procedure, češće izgledato ovako:

delimiter //
create procedure mojaprocedura ()
begin
select 'pozdrav';
end//
delimiter ;


Ipak, kao što se vidi iz primera, kreacija uskladištene proocedure, sadrži još neke osobenosti (naredbu DELIMITER). Ova naredba u stvari i ne spada u definicioni deo procedure, već se koristi radi zaobilaženja jednog problema.

Osnovni delimiter u MySQL-u je oznaka ; . To znači da se svaka linija koda, ukoliko želimo da ih bude više, mora završiti tom oznakom. S obzirom na to da se uskladištene rutine pamte u bazi u vidu teksta i poseduju obično više od jedne linije koda, potrebno je nekako smestiti sve te linije koda zajedno sa delimiterima (;).

Problem nastaje kada MySQL prilikom kreacije uskladištene procedure naiđe na ovu oznaku, jer, umesto da je tretira kao tekst rutine, on ga, jednostavno, tretira kao SQL simbol.

Da bismo to izbegli, koristimo SQL naredbu DELIMITER, koja omogućava definisanje podrazumevanog delimitera baze. U primeru, umesto oznake ; stavili smo oznaku //, što znači da će svaka linija, koja na kraju sadrži ovu oznaku (tačnije dve oznake), biti tretirana kao SQL naredba.

Na taj način, možemo bezbedno unositi oznaku ; .

Na kraju, završavamo definiciju procedure oznakom end// (// menja ;) i konačno, vraćamo delimiter na staru vrednost naredbom DELIMITER ; .

Iz ovoga se može zaključiti da je sintaksa za definiciju stored procedure sledeća:

create procedure mojaprocedura()
begin
telo procedure
end;


Napominjemo da je definicija procedure višelinijska skripta, pa ne može biti startovana iz MySQL Query Browser resultset sekcije, već iz nekog alata koji omogućava izvršavanje ovakvih skripti (skript sekcija istog programa, MySQL monitor...)

Aktivacija procedure vrši se naredbom call:

call mojaprocedura;

ili

call mojaprocedura ();

 

Ako izvršimo jednu od ove dve naredbe, dobićemo kao rezultat izvršenje bloka u proceduri mojaprocedura.

Iz primera kreacije vidimo da telo procedure počinje ključnom rečju begin, a završava se ključnom rečju end sa delimiterom. Ovo pravilo važi samo prilikom kreiranja procedure, ali ne i njene izmene (alter). Ukoliko hoćemo da izmenimo telo procedure, naredba ALTER nije upotrebljiva, već je jedini način brisanje procedure i ponovna izgradnja.

Što se tiče naredbe ALTER, njome je moguće izmeniti jedino strukturu procedure, ali ne i njeno telo.

Kada jednom kreiramo STORED proceduru, ona funkcioniše samo na nivou baze u kojoj je kreirana, i to samo za korisnike koji imaju pravo na njeno korišćenje (grant execute on procedure mp to petar). Za ova prava važe ista pravila kao i za prava u pogledima. Korisnik ne mora imati prava nad podacima, da bi imao prava nad stored procedurom i kroz nju mogao videti podatke. Treba biti pažljiv u slučajevima izmene tela procedure, kada koristimo naredbu DROP i CREATE. Naredbom DROP gube se sva korisnička prava nad procedurom, čak i ako nova procedura ima isto ime kao i stara.

Podrazumevano, proceduru može menjati ili brisati jedino korisnik koji je i kreirao. Zato možemo, prilikom same kreacije, eksplicitno naglasiti kreatora procedure, ključnom rečju definer.

delimiter //
create definer = petar procedure mojaprocedura ()
begin
select * from orders;
end//
delimiter ;

 

Procedura može biti izgrađena sa nekolicinom strukturnih parametara (CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA) koje opisuju prirodu procedure, ali nemaju sintaksnog uticaja na naše rukovanje, već pomažu samom MySQL-a prilikom kreiranja statistika i optimizacije:

create procedure mojaprocedura()
MODIFIES SQL DATA
begin
telo procedure
end;

 

Sličnu svrhu ima i parametar DETERMINISTIC (i NON DETERMINISTIC). Ovaj parametar, takođe, veoma utiče na rad optimizacionog Engine-a, ali nema preteran uticaj na naše sintaksne obaveze prilikom pisanja procedure. Ako je procedura označena kao deterministik, znači da se njen izlaz nikada ne menja, kao u prvoj proceduri koju smo napisali, a koja emituje poruku „pozdrav“. Ako procedura nije deterministik (što je podrazumevani parametar), MySQL ne očekuje da rezultat procedure bude uvek isti (na primer, ako procedura sadrži funkciju now(), koja prikazuje tačno vreme i naravno, nikada ne daje identičan rezultat).

  

Parametrizacija

Uskladištena procedura ima mogućnost prihvatanja i prosleđivanja parametara, prilikom čega je moguće koristiti tri vrste parametara: IN, OUT i INOUT. Iako su sami nazivi prilično slikoviti, ukratko ćemo ih objasniti: IN, podrazumeva ulazni parametar, koji je nakon prihvatanja vidljiv samo u telu procedure. OUT je kreiran unutar procedure, ali može biti vidljiv izvan nje. INOUT se kreira izvan procedure i vidljiv je sve vreme, i unutar procedure i izvan nje.

IN je podrazumevani pravac kretanja parametra. To znači da ne morate eksplicitno naglasiti to njegovo svojstvo. Namerno kažemo pravac kretanja, a ne tip, jer parametri takođe poseduju i tipove (koji nisu isto što i pravac kretanja). Štaviše, nemoguće je proslediti parametar proceduri, a da unutar njene definicije ne postoji definisan i njegov očekivani tip.

Evo jednog primera parametrizovane procedure:

create procedure mojaprocedura (param1 int, param2 int)
select param1+param2;

 

Procedura sabira dva broja (poziv je call mojaprocedura(1,2)). Ove brojeve procedura prihvata kroz parametre param1 i param2, a zatim ih tretira kroz kod. Primećujemo da parametri nemaju standardnu notaciju (sa oznakom @), već se prosleđuju samo po nazivu. Ipak, kombinacije notacije parametara unutar same procedure su moguće. Kada bismo blok procedure izmenili, tako da izgleda ovako:

set @x=5;
select param1+param2+@x;

 

Procedura bi prikazala kao rezultat broj 8 jer ulazni parametri i parametri unutar procedure identično tretirani.

Promenljive koje se nalaze u proceduri, nisu vidljive van nje. Dakle, promenljiva @x biće vidljiva samo unutar begin i end bloka.

Ukoliko hoćemo da parametre unutar procedure koristimo i van nje, potrebni su nam izlazni (OUT) ili izlazno ulazni (INOUT) parametri.

Izlazni parametri zahtevaju da parametar bude definisan pre poziva procedure:

create procedure mojaprocedura (out param varchar(20)) set param='pozdrav!';

 

Sada smo parametar prosledili uz ključnu reč out (što znači da je parametar izlazni). Zatim, u kodu standardno tretiramo taj parametar.

Prilikom pozivanja procedure, sada ne moramo da prosleđujemo parametar koji ima stvarnu vrednost, već bilo koji parametar (pa čak u Null), koji čak ne mora biti ni prethodno definisan:

call mojaprocedura(@p);
select @p;

 

Nakon izvršene procedure, vrednost promenljive p biće izmenjena u skladu sa samom procedurom.

Obratite pažnju na to da je u pitanju višelinijski upit, tako da ne može biti izvršen u MySQL Query Browser resultset tab-u.

Kada bismo kreirali proceduru mojaprocedura na sledeći način:

create procedure mojaprocedura (out param int) set param=param+1;


i pozvali je na sledeći način:

set @a=1;
call mp(@a);
select @a;

 

Rezultat upita bio bi Null jer out parametar nije tretiran kao pokazivač, već jednostavno samo kao parametar koji će biti vraćen sistemu. Da bi parametar bio tretiran kao pokazivač, trebalo bi da upotrebimo inout pravac:

create procedure mojaprocedura (inout param int) set param=param+1;

 

Uz ovu izmenu, parametar će biti tretiran kao pokazivač i izmene na parametru (promenljivoj) u toku procedure, biće aktuelne za ceo kod u kome se ta procedura poziva.

Sve strukturalne podatke o proceduri možete dobiti naredbom:

show create procedure mojaprocedura

 

a o svim procedurama:

show procedure status

 

Uskladištena procedura briše se ključnom rečju drop (drop procedure mojaprocedura).

 

Najvažnije iz lekcije:

  1. Uskladištene procedure omogućavaju portabilnost poslovne logike aplikacije i smanjenje protoka podataka (odnosno, veću brzinu) između aplikacije i baze podataka.
  2. Sintaksa za kreiranje uskladištene procedure na MySQL serveru je create procedure mojaProcedura().
  3. Poziv procedure na MySQL serveru vrši se naredbom CALL (call mojaprocedura()).
  4. Moguće je proslediti parametre u uskladištenu proceduru.
  5. Moguće je preuzeti parametre iz uskladištene procedure.
  6. Postoje tri pravca kretanja parametara u uskladištenim procedurama in, out i inout.
  7. Procedura se kreira naredbom CREATE i briše naredbom DROP, ali se naredbom ALTER mogu samo izmeniti strukturalni parametri procedure, a ne i sam blok.

Napomena

Ukoliko procedure radite u phpmyadmin alatu, potrebno je izmeniti
opciju delimiter u /

U suprotnom, potrebno je na početku aplikacije postaviti delimiter opcijom:

delimiter //


a na kraju ga vratiti opcijom:

delimiter ;

 

Vežba 1

Problem:

Kreirati proceduru za unos korisnika, sa nazivom usp_insertuser koja kao parametar prihvata ime korisnika. Korisnik će biti unet sa statusom user.

 

Rešenje:

create PROCEDURE insertuser(newname varchar(50))
BEGIN
   insert into users (name,status) values (newname,1);
END;

 

Vežba 2

Problem:

Potrebno je napraviti uskladištenu proceduru koja kreira korisnika prema parametrizovanom imenu. Ukoliko korisnik sa tim imenom već postoji u tabeli, neće biti kreiran. Korisnik se unosi sa statusom user.

 

Rešenje:

create PROCEDURE insertuser(newname varchar(50))
BEGIN
   declare usersCount int;
   select count(id) from users where name=newname into usersCount;
   if usersCount<1 then
   insert into users (name,status) values (newname,1);
   end if;
END;

 

Vežba 3

Problem:

Potrebno je napraviti proceduru koja će unositi korisnika u bazu. Procedura prihvata kao parametre ime i šifru korisnika. Ukoliko korisnik sa tim imenom ne postoji, uneće novog. Ukoliko korisnik postoji, biće mu zamenjena šifra novom. Korisnik se unosi sa statusom user.

 

Rešenje:

create PROCEDURE insertuser(newname varchar(50),newpass varchar(50))
BEGIN
   declare usersCount int;
   select count(id) from users where name=newname into usersCount;
   if usersCount<1 then
   insert into users (name,password,status) values (newname,password,1);
   else
   update users set password=newpass where name = newname;
   end if;
END;

Dodaj komentar Sviđa mi se - (3) Ne sviđa mi se - (1)    

  • Uskladištene procedure 1
  • Uskladištene procedure 2
  • Uskladištene procedure 3