Crearea procedurilor stocate
Procedurile stocate reprezinta secvente de cod SQL care pot fi rulate pe server pentru a indeplini anumite sarcini. Procedurile stocate sunt salvate in baza de date si pot fi apelate la un moment dat de un program, declansator (trigger) sau chiar de o alta procedura stocata.
Principalele avantaje ale utilizarii procedurilor stocate pot fi sintetizate astfel:
• procedurile stocate cresc performantele aplicatiilor; dupa creare, procedurile stocate sunt compilate si salvate in baza de date; in felul acesta, ele vor rula mai rapid decat comenzile SQL care sunt trimise din aplicatii;
• procedurile stocate reduc traficul intre aplicatii si serverul de baze de date, deoarece aplicatiile nu mai trimit secvente de cod SQL necompilate, ci doar numele unor proceduri stocate pe server;
• procedurile stocate permit reutilizarea secventelor de cod SQL; prin intermediul lor sunt oferite anumite functionalitati, care nu trebuie implementate pentru fiecare aplicatie in parte;
• procedurile stocate sunt sigure; administratorii bazelor de date stabilesc aplicatiile care pot accesa anumite proceduri stocate, fara a acorda privilegii pe tabelele bazei de date.
Totusi, procedurile stocate prezinta si anumite dezavantaje, dintre care pot fi amintite urmatoarele:
• procedurile stocate contin, in general, instructiuni SQL, motiv pentru care nu este posibila implementarea unor facilitati complexe, oferite de limbajele de programare;
• procedurile stocate necesita aptitudini specializate in randul dezvoltatorilor;
• procedurile stocate solicita memoria si puterea de procesare a serverului de baze de date, prin rularea unor operatii complexe.
Sistemul MySQL ofera suport pentru proceduri stocate incepand cu versiunea 5.0. Crearea unei proceduri stocate se realizeaza cu ajutorul instructiunii CREATE PROCEDURE, care prezinta urmatoarea sintaxa in MySQL:
CREATE [DEFINER = {utilizator | CURRENT_USER}] PROCEDURE nume_procedura_stocata ([IN | OUT | INOUT nume_parametru tip_parametru [,…]]) SQL SECURITY {DEFINER | INVOKER} corp_procedura
Clauzele DEFINER si SQL SECURITY specifica contul MySQL care urmeaza a fi utilizat pentru a verifica privilegiile, la rularea procedurii stocate.
Daca pentru clauza DEFINER este specificata o valoare, aceasta trebuie sa corespunda unui utilizator de pe serverul MySQL (user_name@host_name). Valoare implicita pentru clauza DEFINER este aceeasi cu numele utilizatorului care executa instructiunea CREATE PROCEDURE.
Valorile permise pentru clauza SQL SECURITY sunt DEFINER si INVOKER. Acestea indica faptul ca procedura stocata va fi executata cu privilegiile utilizatorului care a creat procedura stocata sau cu cele ale utilizatorului care o invoca. Utilizatorul care creeaza sau invoca o procedura stocata trebuie sa detina permisiunea de a accesa baza de date cu care procedura stocata este asociata.
Valoarea implicita pentru clauza SQL SECURITY este DEFINER. Daca valoarea corespunzatoare clauzei SQL SECURITY este DEFINER si contul indicat in clauza DEFINER nu exista cand procedura stocata este executata, atunci este generata o eroare.
Pentru a utiliza instructiunea CREATE PROCEDURE este necesar privilegiul CREATE ROUTINE. De exemplu, in cazul sistemelor de tip MySQL sunt automat alocate privilegiile ALTER ROUTINE si EXECUTE pentru utilizatorul care creeaza o procedura stocata.
Pentru a marca finalul unei instructiuni CREATE PROCEDURE este utilizat un delimitator. Acesta poate fi cel implicit, simbolul (;), sau unul stabilit prin intermediul instructiunii DELIMITER.
O astfel de abordare este utila, daca avem in vedere faptul ca o procedura stocata poate include mai multe instructiuni SQL delimitate prin simbolul (;). In acest caz, trebuie utilizata instructiunea DELIMITER, inaintea crearii unei proceduri stocate, pentru a stabili simbolul care va marca finalul procedurii stocate.
Corpul unei proceduri stocate este delimitat de cuvintele cheie BEGIN, respectiv END, si cuprinde instructiuni SQL.
Urmatorul exemplu introduce o procedura stocata, numita getActors(), care permite colectarea tuturor inregistrarilor din tabelul actors al bazei de date sakila.
USE sakila; | |
DROP PROCEDURE IF EXISTS get_actors; | |
DELIMITER | | |
CREATE PROCEDURE get_actors() | |
BEGIN | |
SELECT actor_id, first_name, last_name | |
FROM actor | |
ORDER BY actor_id ASC | |
LIMIT 5; | |
END | | |
DELIMITER ; |
O procedura stocata pe server poate fi rulata prin intermediul instructiunii SQL CALL.
mysql> CALL sakila.get_actors(); +----------+------------+--------------+ | actor_id | first_name | last_name | +----------+------------+--------------+ | 1 | PENELOPE | GUINESS | | 2 | NICK | WAHLBERG | | 3 | ED | CHASE | | 4 | JENNIFER | DAVIS | | 5 | JOHNNY | LOLLOBRIGIDA | +----------+------------+--------------+ 5 rows in set Query OK, 0 rows affected
Utilizarea parametrilor
Lista de parametri a unei proceduri stocate poate fi vida sau poate cuprinde unul sau mai multi parametri. Denumirile parametrilor nu sunt de tip case-sensitive.
Parametrii pot fi impartiti in trei categorii, dupa cum urmeaza:
• IN: daca nu este precizata categoria pentru un parametru, implicit aceasta este IN; un parametru IN transmite o valoare in procedura stocata; procedura poate modifica aceasta valoare, dar modificarea nu este vizibila la apelare;
• OUT: un parametru OUT transmite o valoare la apelant; valoare sa initiala este NULL;
• INOUT: un parametru INOUT este initializat de apelant, poate fi modificat in procedura stocata si orice modificare facuta in procedura este vizibila la apelant.
Pentru fiecare parametru OUT sau INOUT este necesara precizarea unei variabile de utilizator in instructiunea care ruleaza procedura stocata, pentru a putea retine valoarea returnata.
USE sakila; | |
DROP PROCEDURE IF EXISTS film_actors; | |
DELIMITER | | |
CREATE PROCEDURE film_actors(IN film_title VARCHAR(45), OUT no_actors INT) | |
BEGIN | |
SELECT COUNT(a.actor_id) INTO no_actors | |
FROM actor AS a | |
INNER JOIN film_actor USING(actor_id) | |
INNER JOIN film AS f USING(film_id) | |
WHERE f.title = film_title; | |
END | | |
DELIMITER ; |
mysql> CALL sakila.film_actors("ACE GOLDFINGER", @no_actors); Query OK, 1 row affected mysql> SELECT @no_actors; +------------+ | @no_actors | +------------+ | 4 | +------------+ 1 row in set
In interiorul procedurilor stocate pot fi utilizate variabile pentru a salva anumite rezultate. Declararea unei variabile se face folosind instructiunea DECLARE, cu urmatoarea sintaxa generala:
DECLARE nume_variabila tip_date(dimensiune) DEFAULT valoare_implicita
Pentru declararea unei variabile pot fi utilizate tipuri de date disponibile la nivelul sistemului, precum: INT, VARCHAR, DATETIME.
Dupa declarare, unei variabile ii poate fi atribuita o valoare cu ajutorul instructiunii SET. In acest caz, pot fi utilizate doua simboluri pentru implementarea operatiei de atribuire: (=) sau (:=).
SET nume_variabila := valoare
Structuri de control disponibile
Intr-o procedura stocata mai pot fi utilizate urmatoarele structuri de control:
• IF, CASE (instructiuni conditionale);
IF expresie THEN instructiuni [ELSEIF expresie THEN instructiuni] … [ELSE instructiuni] END IF
CASE WHEN expresie THEN instructiuni [WHEN expresie THEN instructiuni] … [ELSE instructiuni] END CASE
• WHILE, REPEAT, LOOP (instructiuni repetitive);
WHILE expresie DO instructiuni END WHILE
REPEAT instructiuni UNTIL expresie END REPEAT
eticheta_loop: LOOP instructiuni END LOOP
• LEAVE, ITERATE (instructiuni de salt);
Instructiunea LEAVE permite parasirea unei iteratii; este asemanatoare instructiunii BREAK din limbajele de programare. Instructiunea ITERATE permite trecerea la urmatoarea iteratie; este asemanatoare instructiunii CONTINUE din limbajele de programare.
DELIMITER | | |
DROP PROCEDURE IF EXISTS lefti.numerePare | | |
CREATE PROCEDURE lefti.numerePare(IN maxim INT) | |
BEGIN | |
DECLARE x INT; | |
DECLARE str VARCHAR(255); | |
SET x = 1; | |
SET str = ''; | |
loop1: LOOP | |
IF x > maxim THEN LEAVE loop1; | |
END IF; | |
SET x = x + 1; | |
IF x mod 2 != 0 THEN ITERATE loop1; | |
ELSE SET str = CONCAT(str, x, ', '); | |
END IF; | |
END LOOP; | |
SELECT str; | |
END | | |
DELIMITER ; |
mysql> CALL lefti.numerePare(20); +--------------------------------------+ | str | +--------------------------------------+ | 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, | +--------------------------------------+ 1 row in set Query OK, 0 rows affected
Stergerea procedurilor stocate
Sintaxa utilizata pentru stergerea unei proceduri stocate este urmatoarea:
DROP PROCEDURE [IF EXISTS] nume_procedura
Pentru a utiliza aceasta instructiune, utilizatorul trebuie sa detina privilegiul ALTER ROUTINE pentru procedura care urmeaza a fi stearsa.
Clauza IF EXISTS inhiba afisarea mesajului de eroare care apare la incercarea de stergere a unei proceduri care nu exista. Daca aceasta clauza este prezenta, pentru orice procedura stocata care nu exista este generata o averizare (WARNING).
mysql> DROP PROCEDURE IF EXISTS sakila.get_stars; Query OK, 0 rows affected, 1 warning
mysql> SHOW WARNINGS; +-------+------+--------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------+ | Note | 1305 | PROCEDURE sakila.get_starts does not exist | +-------+------+--------------------------------------------+ 1 row in set