In PHP we Trust

Subrutinele sunt mici bucăţi de cod executabil care sunt stocate într-o bază de date relaţională, definite de către utilizator ce are posibilitatea de a le chema atunci când situaţia o cere. La fel ca o funcţie definită de utilizator în cadrul unui limbaj de programare, o subrutină extinde funcţionalitatea nativă oferită de SGBD-ul în care se scrie această subrutină însă personalizată pe baza de date în care este scrisă şi structura acesteia.

Utilizatorii MySQL s-au putut bucura de aceste facilităţi începând cu versiunea 5 a bazei de date, moment în care acestea s-au introdus alături de view-uri sau trigger-i. Până în acest moment (2005), dezvoltatorii MySQL erau nevoiţi să stocheze aceste coduri în aplicaţie sau în diverse fişiere pe server. De-a lungul timpului s-au detaşat 2 şcoli de gândire dintre care unii susţin că funcţionalitatea legată de baza de date ar trebui sa stea în aplicaţie şi alţii care susţin că această funcţionalitate ar trebui sa stea în interiorul bazei de date. Evident, adevărul este undeva la mijloc. Majoritatea programatorilor nu se bazează exclusiv pe una din cele două modalităţi de gândire, de multe ori folosirea unei modalităţi este preferată pentru că aceasta face sens în diverse situaţii. Este interesant totuşi de observat că mulţi gânditori ai şcolii numite în aplicaţie au luat această poziţie pentru că nu exista altă varianta, neexistând proceduri stocate in trecut, şi că aceştia sunt din ce în ce mai puţini. Şi totuşi, de ce ar stoca cineva subrutinele într-o bază de date şi nu în aplicţie?

Avantajele principale ale subrutinelor stocate sunt acelea referitoare la portabilitate şi îmbunătăţire a funcţionalităţii. O subrutină definită în baza de date, va rula în orice condiţii şi pe orice platformă suportată de catre baza de date gazdă. Mai mult, fiind inglobate în baza de date nu are importanţă ce aplicaţie(limbaj de programare) se foloseşte din exteriorul acesteia pentru a le utiliza şi pentru a returna date. Acestea au şi mai multă logică când se folosesc mai multe tehnologii pentru a crea aplicaţii complexe şi interconectabile cu o bază de date centrală. Deasemeni acestea pot reduce considerabil traficul dintre aplicaţie şi baza de date, deci într-un final rezultând un timp de execuţie mai bun al acţiunilor dorite. Imaginaţi-vă o situaţie în care pentru a obtine o raportare este nevoie de interogarea bazei de date, de obţinere a unui set mare de date, prelucrarea acestora în cadrul aplicaţiei, iar apoi pentru fiecare element din setul de date se impune interogarea bazei de date pentru a obţine alt set de date. Este uşor de observat ca transmiterea datelor între baza de date şi aplicaţie este mult mai uşoară si mai puţin costisitoare daca s-ar face când toate datele ar fi pregătite: datele iniţiale ar rămâne în interiorul bazei de date şi nu s-ar trimite la aplicaţie, s-ar prelucra în baza de date, s-ar interoga din nou baza de date direct din interiorul subrutinei, iar la final după ce s-ar fi obţinut şi al doilea set de date, acestea (împreună cu primul) s-ar returna aplicaţiei.

Subrutinele sunt de două feluri: proceduri stocate şi funcţii definite de utilizator. Diferenţa dintre cele 2 este că cea din urmă poate returna o valoare scalară în timp ce procedura stocată poate “returna” şi unul sau mai multe seturi de date. Un exemplu simplu de procedură stocată este următorul:


CREATE PROCEDURE `inphpwetrust`.`spTest`()
BEGIN
SELECT * FROM tbl;
END;
/*
am definit o procedura stocata pe baza inphpwetrust numita spTest
care va returna datele din tabela numita tbl
*/
CREATE PROCEDURE `inphpwetrust`.`spTest`(in varID int)
BEGIN
SELECT * FROM tbl where tbl_id = varID;
END$$
/*
aceasta a doua procedura are rolul de a filtra datele din tabelul tbl dupa un parametru
*/


În definirea unei proceduri stocate se pot folosi mai mulţi parametri de intrare, care la rândul lor pot fi de mai multe feluri dintre care cel implicit este primul:

• IN va transmite procedurii stocate un parametru care va putea fi folosit, modificat, însă modificările asupra lui nu vor avea efect în exteriorul procedurii
• OUT nu va transmite procedurii stocate nici o valoare, sau o va considera pe cea trimisă ca fiind NULL; aceasta va fi modificată în interiorul procedurii iar modificările vor avea efect în exteriorul funcţiei
• INOUT înglobează caracteristicile precedentelor; astfel se va putea trimite un parametru la procedura stocată care va putea fi folosit, modificat şi moridificările vor fi vizibile în exteriorul procedurii


/*
exemplu de procedura stocata ce se foloseste de parametri IN
acestia pot fi specificati sau nu
*/
CREATE PROCEDURE `spParamIN`(IN param1 varchar(10), param2 varchar(10))
BEGIN
select concat(param1, param2) as `concat`;
END;
/*
exemplu de procedura stocata ce se foloseste de parametri OUT
*/
CREATE PROCEDURE `spParamOUT`(IN param1 varchar(10), OUT param2 int)
BEGIN
set param2 = length(param1);
END;
/*
exemplu de procedura stocata ce se foloseste de parametri INOUT
*/
CREATE PROCEDURE `spParamINOUT`(INOUT param1 varchar(10), IN param2 int)
BEGIN
set param1 = param1 + param2;
END;


Folosire

În crerea unei proceduri stocate vă puteţi folosi de orice instrucţiune SQL existentă sau combinaţii ale acestor instrucţiuni. Se pot selecta data din bază, se pot adauga, edita sau şterge înregistrări ale diverselor tabele. Pe lângă aceste operaţii se pot folosi şi varibilele definite de utilizator. Acestea se declară local, folosind sintaxa:


DECLARE var1[,var2, ... , varn] TYPE [DEFAULT value]


Aceste variabile pot fi folosite fie pentru “bucătăria internă” a subrutinei, fie pentru a transmite anumite valori returnate din cadrul unor interogări SQL. Astfel, în exemplul urmator, prima instrucţiune SELECT va genera returnarea unui set de date, pe când cel de-al doilea va atribui valoarea din campul găsit către variabila indicată folosind operatorul INTO


CREATE PROCEDURE `spInto`(IN valID INT)
BEGIN
DECLARE tmpName VARCHAR(100);
SELECT * FROM customers;
SELECT name INTO tmpName FROM customers WHERE id = valID;
END;


Orice modificări aduse conţinutului varibilelor se poate face cu ajutorul operatorului SET, pe una sau mai multe variabile simultan. Se pot utiliza oricare din structurile de control existente in MySQL precum şi cursori sau tranzacţii.

Alte opţiuni

În definirea unei subrutine pot interveni şi alţi parametri ce pot influenţa modul de funcţionare a acestia. Modul general de definire a acestor subrutine este urmatorul:


CREATE
[DEFINER = { utilizator | CURRENT_USER }]
PROCEDURE nume_procedura ([parametrii[,...]])
[caracteristici ...] corp_rutina

LANGUAGE SQL este o opţiune care indica limbajul folosit pentru a crea subrutina. Acesta este limbajul implicit, deci poate fi ignorat. O procedură este considerata determinista (specificată prin opţiunea DETERMINISTIC) dacă aceasta va întoarce acelaşi set de date pentru aceeaşi parametri de intrare sau nedeterministă în caz contrar(implicit). Următoarele variabile au in MySQL doar rol informativ şi se referă la natura datelor folosite in cadrul rutinei, MySQL nerestricţionând intrucţiunile folosite în interiorul rutinei bazându-se pe aceste informaţii:

• CONTAINS SQL specifică faptul că subrutina conţine cod SQL
• NO SQL indică faptul că subrutina nu conţine cod SQL
• READS SQL DATA indică faptul că subrutina citeşte date din baza de date (folosind instrucţiunea SELECT)
• MODIFIES SQL DATA indică faptul ca subrutina va scrie(modifica) date din baza de date (folosind INSERT, UPDATE sau DELETE)

SQL SECURITY se referă la faptul că o subrutină va putea fi executată folosind drepturile utilizatorului care a creat (DEFINER) sau care invocă (INVOKER) acea subrutină. COMMENT permite adăugarea de informaţii suplimentare asupra acelei subrutine

Restricţii

Există câteva restricţii care MySQL le aplică în momentul creării de funcţii sau proceduri stocate şi de care este bine să se ţină cont.

• instrucţiunile CHECK TABLE, OPTIMIZE TABLE, LOCK TABLES, UNLOCK TABLES, ALTER VIEW, LOAD DATA, LOAD TABLE nu pot fi folosite
• folosirea de expresii predefinite cu ajutorul PREPARE, EXECUTE, DEALLOCATE PREPARE este interzisă; începând cu versiunea 5.0.13 această restricţie a fost ridicată în cazul procedurilor stocate, însă se menţine în cazul funcţiilor
• folosirea instrucţiunii INSERT DELAY este identică cu INSERT în interiorul subrutinelor

Următoarele restricţii sunt aplicate doar funcţiilor definite de utilizator:

• operaţiile care execută COMMIT sau ROLLBACK
• operaţiile care returnează seturi de date
• operaţia FLUSH
• folosirea lor recursiv
• modificări asupra tabelei are se operează citiri sau scrieri de către instrucţiunea care a chemat funcţia respectivă

Securitate

Pentru a avea posibilitatea de a crea proceduri stocate resepctiv funcţii, utilizatorul de MySQL trebuie să deţină drepturi de CREATE ROUTINE. MySQL va atribui automat drepturi pentru operaţiile ALTER ROUTINE şi EXECUTE pentru utilizatorul care a creat subrutina respectivă.