O introducere în procedurile stocate în MySQL 5

MySQL 5 a introdus o multitudine de caracteristici noi - procedurile stocate fiind una dintre cele mai semnificative. În acest tutorial, ne vom concentra pe ceea ce sunt și cum vă pot face viața mai ușoară.

Dacă lucrați foarte mult cu MySQL, poate doriți să verificați gama de scripturi și plugin-uri de cod MySQL de pe Envato Market.

Introducere

? O rutină stocată este un set de instrucțiuni SQL care pot fi stocate pe server.?

Procedura stocată este o metodă de încapsulare a sarcinilor repetate. Ele permit declarații variabile, controlul fluxului și alte tehnici de programare utile.

Academicul? poziția în acest sens este destul de clară și susține folosirea extinsă a procedurilor stocate. Pe de altă parte, când țineți cont de opiniile celor care lucrează cu ei în fiecare zi, veți observa că reacțiile variază de la un sprijin complet și neclintit la ură. Țineți cont de acestea.

Pro-uri

Distribuiți logica cu alte aplicații. Procedurile stocate încorporează funcționalitatea; acest lucru asigură că accesul la date și manipularea sunt coerente între diferite aplicații.
Izola utilizatorilor din tabelele de date. Aceasta vă oferă posibilitatea de a acorda acces la procedurile stocate care manipulează datele, dar nu direct la tabele.
Furnizeaza un Securitate mecanism. Având în vedere elementul prealabil, dacă puteți accesa numai datele utilizând procedurile memorate definite, nimeni altcineva nu poate executa a ȘTERGE Instrucțiunea SQL și ștergeți datele.
La îmbunătăți performanța deoarece reduce traficul în rețea. Cu o procedură stocată, mai multe apeluri pot fi combinate într-una.

Contra

Cresterea sarcinii pe serverul bazei de date - cea mai mare parte a muncii se face pe partea de server, și mai puțin pe partea clientului.
E un lucru decent curbă de învățare. Veți avea nevoie să aflați sintaxa instrucțiunilor MySQL pentru a scrie proceduri stocate.
Tu esti repetând logica a aplicației dvs. în două locuri diferite: codul serverului și codul procedurilor stocate, ceea ce face ca lucrurile să fie mai greu de întreținut.
Migrare la un sistem diferit de gestionare a bazelor de date (DB2, SQL Server, etc) ar putea fi mai dificil.

Instrumentul cu care lucrez în acest tutorial, MySQL Query Browser, este destul de standard pentru interacțiunile bazei de date. Instrumentul din linia de comandă MySQL este o altă alegere excelentă. Notez acest lucru deoarece popularul phpMyAdmin nu suportă executarea procedurilor stocate.

În plus, voi folosi structuri de masă foarte rudimentare, strict pentru a ușura explicația. Demonesc proceduri stocate și sunt destul de complexe fără a vă îngrijora mesele mari.

Pasul 1 - Alegerea unui separator

Delimiterul este caracterul sau șirul de caractere pe care le veți folosi pentru a le spune clientului mySQL că ați terminat de scris într-o instrucțiune SQL. De-a lungul veacurilor, delimitatorul a fost întotdeauna o punct și virgulă. Aceasta, totuși, provoacă probleme, deoarece, într-o procedură stocată, se pot face multe declarații și fiecare trebuie să se încheie cu un punct și virgulă. În acest tutorial voi folosi??

Pasul 2 - Cum să lucrați cu o procedură stocată

Crearea unei proceduri stocate

DELIMITER // CREATE PROCEDURE 'p2' () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'O procedură' BEGIN SELECT 'Hello World!'; SFÂRȘIT//

Prima parte a instrucțiunii creează procedura. Următoarele clauze definesc caracteristicile opționale ale procedurii. Apoi, aveți numele și, în sfârșit, codul corporal sau de rutină.

Numele de proceduri stocate sunt insuficiente pentru litere mici și nu puteți crea proceduri cu același nume. În interiorul unui corp de procedură, nu puteți pune declarații de manipulare a bazei de date.

Cele patru caracteristici ale unei proceduri sunt:

Limba : Pentru scopuri de portabilitate; valoarea implicită este SQL.
Determinat : Dacă procedura întoarce întotdeauna aceleași rezultate, având aceeași intrare. Aceasta este pentru scopuri de replicare și de înregistrare. Valoarea implicită este NU DETERMINISTIC.
SQL Security : La ora apelului, verificați privilegiile utilizatorului. invocatorul este utilizatorul care solicită procedura. DEFINER este creatorul procedurii. Valoarea implicită este DEFINER.
cometariu : În scopuri de documentare; valoarea implicită este „“

Apelarea unei proceduri stocate

Pentru a apela o procedură, trebuie doar să introduceți cuvântul APEL, urmată de numele procedurii, apoi de paranteze, inclusiv toți parametrii dintre ei (variabile sau valori). Parentheses sunt obligatorii.

CALL stored_procedure_name (param1, param2 ,?) Procedura CALL1 (10, 'parametru string', @parameter_var);

Modificați o procedură stocată

MySQL oferă o PROCEDURA ALTERĂ declarație pentru a modifica o rutină, dar permite doar abilitatea de a schimba anumite caracteristici. Dacă trebuie să modificați corpul sau parametrii, trebuie să renunțați și să recreați procedura.

Ștergeți o procedură stocată

PROCEDURA DROP DACĂ EXISTĂ p2;

Aceasta este o comandă simplă. DACĂ ESTE EXISTĂ clauza previne o eroare în cazul în care procedura nu există.

Pasul 3 - Parametrii

Să examinăm cum puteți defini parametrii în cadrul unei proceduri stocate.

CREATE PROCEDURE proc1 () : Lista parametrilor este goală
CREATE PROCEDURE proc1 (IN VARAM DATA-TYPE) : Un parametru de intrare. Cuvantul ÎN este opțională deoarece sunt parametrii ÎN (intrare) implicit.
CREATE PROCEDURA proc1 (OUT varname DATA-TYPE) : Un parametru de ieșire.
CREATE PROCEDURA proc1 (INOUT varname DATA-TYPE) : Un parametru care este atât intrare cât și ieșire.

Desigur, puteți defini mai mulți parametri definiți cu diferite tipuri.

În exemplu

 DELIMITER // CREATE PROCEDURA 'proc_IN' (IN var1 INT) BEGIN SELECT var1 + 2 rezultat AS; SFÂRȘIT//

OUT exemplu

DELIMITER // CREATE PROCEDURA 'proc_OUT' (OUT var1 VARCHAR (100)) BEGIN SET var1 = 'Acesta este un test'; SFÂRȘIT //

Exemplul INOUT

DELIMITER // CREATE PROCEDURA 'proc_INOUT' (OUT var1 INT) BEGIN SET var1 = var1 * 2; SFÂRȘIT //

Pasul 4 - Variabile

Următorul pas vă va învăța cum să definiți variabilele și să stocați valori într-o procedură. Trebuie să le declarați în mod explicit la începutul BEGIN / END bloc, împreună cu tipurile de date ale acestora. Odată ce ați declarat o variabilă, o puteți folosi oriunde ați putea utiliza o variabilă de sesiune sau un nume literal sau de coloană.

Declarați o variabilă utilizând următoarea sintaxă:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Să spunem câteva variabile:

DECLARAȚI a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DECLARE astăzi TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Lucrul cu variabilele

După ce au fost declarate variabilele, le puteți atribui valorile utilizând A STABILIT sau SELECTAȚI comanda:

DELIMITER // CREATE PROCEDURA 'var_proc' (IN paramstr VARCHAR (20)) ÎNCEPE DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DECLARE astăzi TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSCRIȚI ÎN tabelul 1 VALORI (a); SET str = 'Sunt un șir'; SELECT CONCAT (str, paramstr), astăzi FROM table2 WHERE b> = 5; SFÂRȘIT //

Pasul 5 - Structuri de control al fluxului

MySQL acceptă DACĂ, CASE, ITERATE, LEAVE LOOP, WHILE și REPETA construiește pentru controlul fluxului în cadrul programelor stocate. Vom revizualiza modul de utilizare DACĂ, CAZ și IN TIMP CE în special, deoarece acestea se întâmplă să fie cele mai frecvent utilizate declarații în rutine.

`DACĂ` afirmație

Cu DACĂ , putem face față sarcinilor care implică condiții:

DELIMITER // CREATE PROCEDURA 'proc_IF' (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabilă1 = param1 + 1; IF variabilă1 = 0 THEN SELECT variabilă1; END IF; Dacă param1 = 0 THEN SELECT 'Valoarea parametrului = 0'; ELSE SELECT 'Valoarea parametrilor <> 0'; END IF; SFÂRȘIT //

`CAZ` afirmație

CAZ declarația este un alt mod de a verifica condițiile și de a lua calea potrivită. Este o modalitate excelentă de a înlocui mai multe DACĂ declarații. Declarația poate fi scrisă în două moduri diferite, oferind o mare flexibilitate pentru a face față mai multor condiții.

DELIMITER // CREATE PROCEDURA 'proc_CASE' (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabilă1 = param1 + 1; VARA CASE1 CÂND 0 INSCRIȚI ÎN TABEL1 VALORI (param1); CÂND 1 INSCRIEȚI ÎN TABEL1 VALORILE (variabila1); ELSE INSERT INTO table1 VALORI (99); CAUTĂ FINALĂ; SFÂRȘIT //

DELIMITER // CREATE PROCEDURA 'proc_CASE' (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabilă1 = param1 + 1; CAZUL CÂND variabila1 = 0 ÎNTÂLNIREA ÎN tabelul1 VALORI (param1); CÂND variabila1 = 1 ÎNTÂLNARE ÎN TABEL1 VALORI (variabilă1); ELSE INSERT INTO table1 VALORI (99); CAUTĂ FINALĂ; SFÂRȘIT //

`IN TIMP CE` afirmație

Există tehnic trei bucle standard: IN TIMP CE bucle, BUCLĂ buclele și REPETA bucle. Aveți, de asemenea, opțiunea de a crea o buclă folosind "Darth Vader"? tehnicilor de programare: MERGI LA afirmație. Consultați acest exemplu de buclă în acțiune:

DELIMITER // CREATE PROCEDURA 'proc_WHILE' (IN param1 INT) BEGIN DECLARE variabila1, variabila2 INT; SET variabilă1 = 0; WHILE variabil1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Pasul 6 - Cursoare

Cursor este folosit pentru a itera printr-un set de rânduri returnate de o interogare și procesează fiecare rând.

MySQL sprijină cursor în procedurile stocate. Iată un rezumat al sintaxei esențiale pentru a crea și utiliza un cursor.

DECLARE cursorul-nume CURSOR PENTRU SELECT? ; / * Declare și să populeze cursorul cu o instrucțiune SELECT * / DECLARE CONTINUE HANDLER PENTRU NU SUNT FOUND / * Specificați ce să faceți atunci când nu mai găsiți alte înregistrări * / OPEN cursor-name; / * Deschide cursorul pentru utilizare * / FETCH cursor-name INTO variabila [, variabila]; / * Atribuirea variabilelor cu valorile coloanelor curente * / CLOSE cursor-name; / * Închideți cursorul după utilizare * /

În acest exemplu, vom efectua câteva operații simple folosind un cursor:

DELIMITER // CREATE PROCEDURA 'proc_CURSOR' (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 pentru SELECT col1 FROM table1; DECLARAȚI CONTINUAȚI MANIPULATORUL PENTRU CARE NU S-A FOST SETAT b = 1; OPEN cur1; SET b = 0; SET c = 0; CÂND b = 0 DO FETCH cur1 INTO a; Dacă b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; SFÂRȘIT //

Cursorul are trei caracteristici importante pe care trebuie să le cunoașteți pentru a evita rezultatele neașteptate:

ASENSITIVE : Odată deschis, cursorul nu va reflecta modificările din tabelele sale sursă. De fapt, MySQL nu garantează faptul că cursorul va fi actualizat, deci nu vă puteți baza pe el.
Numai citire Cursoarele nu sunt actualizabile.
Nu este derulantă : Cursoarele pot fi traversate numai într-o singură direcție, în față și nu puteți sări peste înregistrări de la preluare.

Concluzie

În această lecție am acoperit fundamentele procedurilor stocate și unele proprietăți specifice care le aparțin. Desigur, ar trebui să vă continuați studiile în domenii precum securitatea, instrucțiunile SQL și performanța înainte de a vă putea controla rutinele MySQL.

Trebuie să evaluați avantajele pe care le pot aduce procedurile stocate aplicațiilor dvs. și apoi să faceți o implementare rezonabilă care să corespundă cerințelor dvs. În general, folosesc proceduri; beneficiile acestora în ceea ce privește securitatea, întreținerea codurilor și proiectarea de software le face demne de folos, în opinia mea. În plus, rețineți că procedurile din MySQL sunt încă o lucrare în desfășurare. Ar trebui să așteptați pe deplin îmbunătățiri în ceea ce privește funcționalitatea și performanța în viitor

Nu ezitați să comentați și să împărtășiți ideile și opiniile dvs. Și să aruncați o privire la scripturile de cod MySQL și plug-in-ul Envato Market pentru a vedea dacă găsiți ceva care să vă ajute acolo.

Cod