Proceduri memorate mysql, mysql

O procedură memorată MySQL este o subrutină stocată într-o bază de date. Acesta conține un nume, o listă de parametri și instrucțiuni SQL. Toate sistemele populare de gestionare a bazelor de date suportă procedurile stocate. Acestea au fost introduse în MySQL 5.

Există două tipuri de subrutine: proceduri stocate și funcții care returnează valori care sunt utilizate în alte instrucțiuni SQL (de exemplu, pi ()).

Principala diferență este că funcțiile pot fi folosite ca orice altă expresie din instrucțiunile SQL. și procedurile stocate trebuie să fie apelate folosind instrucțiunea CALL.

Care este avantajul procedurilor stocate?

Procedurile stocate funcționează rapid. Avantajul serverului MySQL este că utilizează cache-ul, precum și operatorii predefiniți. Creșterea principală a vitezei este reducerea traficului în rețea. Dacă există sarcini repetitive care necesită testare, procesare buclă, declarații multiple și nu necesită interacțiune cu utilizatorul, aceasta poate fi implementată utilizând un apel de procedură unic stocat pe server;
Procedurile stocate în MySQL sunt universale. Când scrieți o procedură stocată pe SQL, aceasta va funcționa pe orice platformă care utilizează MySQL. Acesta este avantajul SQL față de alte limbi, cum ar fi Java. C sau PHP;
Codul sursă pentru procedurile stocate este întotdeauna disponibil în baza de date. Este o practică eficientă de a lega datele de procesele care le procesează.

Crearea unei proceduri

În mod implicit, procedura este asociată cu baza de date utilizată în prezent. Pentru a asocia o procedură cu o bază de date specifică, specificați-o pentru a crea o procedură memorată: name_name_name.parsame_protection_name. Sintaxa completă:

Înainte să implementați un apel MySQL la o procedură stocată. este necesar să se obțină anumite informații.

Verificarea versiunii MySQL

Următoarea comandă afișează versiunea MySQL:

Verificați privilegiile utilizatorului curent

Comenzile CREATE PROCEDURE și CREATE FUNCTION necesită privilegiul utilizatorului CREATE ROUTINE. Este posibil să aveți nevoie și de privilegiul SUPER. acest lucru depinde de valoarea DEFINER. care va fi descris mai târziu. Dacă este activat jurnalul binar pentru CREATE FUNCTION. atunci poate fi necesar privilegiul SUPER. În mod implicit, MySQL oferă în mod automat privilegii ALTER ROUTINE și EXECUTE creatorului subrutinei. Acest comportament poate fi modificat prin dezactivarea variabilei de sistem automatic_sp_privileges:

Selectarea unei baze de date

Acum, selectați baza de date "hr" și scoateți lista de tabele:

Selectați un separator

Un delimiter este un caracter sau un șir de caractere care se utilizează pentru a închide instrucțiunea SQL. Implicit, delimitatorul este un punct și virgulă (;). Dar acest lucru cauzează probleme în procedurile și declanșatoarele stocate de MySQL. deoarece poate avea numeroși operatori și fiecare trebuie să se încheie cu un punct și virgulă. Prin urmare, ca separator, vom folosi semnul dublu de dolari - $$. Pentru a utiliza ulterior delimitatorul ";" din nou, executați comanda "DELIMITER; $$". Mai jos este codul pentru schimbarea separatorului:

Acum DELIMITER este implicit la "$$". Să executăm o comandă SQL simplă:

Acum rulați următoarea comandă pentru a seta din nou ";" ca separator:

Exemplu de procedură MySQL

Vom crea o procedură MySQL simplă numită job_data. atunci când se execută, va scoate toate datele din tabelul "joburi":

explicaţie:

Comanda CREATE PROCEDURE creează o procedură stocată;
Următoarea parte este numele procedurii "job_data";
Numele procedurilor nu sunt sensibile la minuscule, deci job_data este echivalent cu JOB_DATA;
Nu puteți utiliza două proceduri cu același nume în aceeași bază de date;
Puteți utiliza numele în formatul "name-procedure.name-database-database", de exemplu, "hr.job_data";
Numele procedurilor pot fi împărțite. Dacă numele este împărțit, acesta poate conține spații;
Lungimea maximă a numelui procedurii este de 64 de caractere;
Evitați utilizarea numelor funcțiilor MySQL încorporate;
Ultima parte a "CREATE PROCEDURE" este o pereche de paranteze conținând o listă de parametri. Deoarece această procedură nu are parametri, lista este goală;
Următoarea parte este SELECT * FROM JOBS; $$ este ultima instrucțiune din sintaxa procedurilor memorate MySQL. Semicolonul (;) nu este obligatoriu aici, deoarece sfârșitul real al instrucțiunii este $$.

Instrumente pentru crearea procedurilor MySQL

Puteți scrie o procedură utilizând instrumentul pentru linia de comandă MySQL sau utilizând MySQL Workbench.

Instrumentul pentru linia de comandă MySQL:

Selectați "clientul de linie de comandă MySQL" din meniul Start:

Veți vedea următoarea fereastră:

Acum, puteți scrie și rula propriile proceduri, vedeți exemplul următor:

MySQL Workbench (5.3 CE):

Selectați "Start" "MySQL Workbench":

După aceasta, veți vedea următoarea fereastră:

Introduceți acreditările:

După aceea, faceți clic dreapta pe elementul "Rutine" și o nouă fereastră pop-up va apărea pe ecran:

După aceea, pe ecran va apărea fereastra care vă permite să creați propria procedură MySQL:

După ce scrieți procedura, faceți clic pe butonul "Aplicați" și pe ecran va apărea următoarea fereastră:

Faceți clic pe butonul "Finalizare" și porniți procedura:

Apel de procedură

Instrucțiunea CALL este utilizată pentru a apela o procedură care este stocată în baza de date. Sintaxa este:

Procedurile memorate MySQL care nu acceptă argumente pot fi numite fără paranteze. Prin urmare, CALL job_data () este echivalent cu CALL job_data.

Să executăm procedura:

ARĂTAȚI CREȘTEREA PROCEDURII

Acest operator este o extensie a MySQL. Acesta returnează un șir exact care poate fi utilizat pentru a recrea procedura specificată stocată. Sintaxa este:

Să implementăm o procedură stocată în MySQL:

MySQL: blocuri de funcții

Sintaxa instrucțiunii CREATE PROCEDURE permite utilizarea blocurilor care descriu caracteristicile procedurii. Blocurile sunt specificate după brațe, dar înaintea corpului procedurii. Aceste blocuri sunt opționale.

COMMENT este o extensie MySQL. Se utilizează pentru a descrie subrutina stocată și această informație este afișată utilizând instrucțiunea SHOW CREATE PROCEDURE.

Caracteristica LANGUAGE indică faptul că corpul procedurii este scris în SQL.

NU DETERMINISTIC

Aceasta este o caracteristică informațională. Procedura este considerată "deterministă" dacă dă întotdeauna același rezultat pentru aceiași parametri de intrare, altfel nu este "deterministă".

CONȚINE SQL | NU SQL | CITEȘTE DATE SQL MODIFICĂ DATELE SQL

CONȚINE SQL înseamnă că nu există nicio declarație în procedura memorată MySQL care citește sau scrie date. De exemplu, declarațiile SET @x = 1 sau DO RELEASE_LOCK ('abc'). acestea sunt executate, dar nu citesc sau scriu date. Aceasta este valoarea implicită dacă nu este specificată o altă valoare caracteristică.

NO SQL înseamnă că procedura nu conține instrucțiuni SQL.

READS SQL DATA - Procedura conține instrucțiuni care citesc date (de exemplu, SELECT), dar nu conține instrucțiuni care scriu date.

Modifică metodele SQL DATA. că subrutina conține operatori care pot scrie date (de exemplu, INSERT sau DELETE).

Toate blocurile de caracteristici enumerate au valori implicite. Următorii doi operatori dau același rezultat:

la fel ca:

Înainte de a merge la parametrii MySQL. ia în considerare mai multe declarații compuse MySQL.

MySQL: declarații compuse

În această secțiune, vom examina primele patru operatori asociați parametrilor instrucțiunii CREATE PROCEDURE.

Sintaxa operatorului compus BEGIN. END

Se folosește atunci când trebuie plasate mai multe instrucțiuni într-o subrutină (de exemplu, o procedură memorată MySQL, un declanșator sau un eveniment). Sintaxa este:

operator_list: una sau mai multe instrucțiuni care se termină cu punct și virgulă (;). În sine, lista operatorilor este opțională, deci instrucțiunea BEGIN END goală este validă.

Etichete operator

Etichetele sunt permisiunile de execuție pentru blocurile BEGIN. END și operatorii ciclurilor REPEAT și WHILE. Sintaxa este:

Atunci când se aplică etichete, se aplică următoarele reguli:

Start_mark trebuie să fie închis cu un colon;
Start_mark poate fi folosit fără end_mark. Dacă semnul end_mark este prezent, trebuie să aparțină aceluiași bloc ca și start_mark;
Marcajul end_mark nu poate fi utilizat fără un start_mark;
Etichetele care aparțin aceluiași nivel imbricat trebuie separate;
Etichetele pot avea lungimi de până la 16 caractere.

DECLARE declarație

Folosit pentru a defini diferite elemente locale când MySQL creează o procedură stocată. De exemplu, variabilele locale, condițiile, manipulatorii, cursorii. Declarația DECLARE este utilizată numai în cadrul instrucțiunii compuse BEGIN. END și trebuie să fie la început înaintea tuturor celorlalți operatori.

Variabilele din programele stocate

Pentru a furniza o valoare pentru variabila implicită, utilizați blocul DEFAULT. Valoarea poate fi specificată ca expresie; nu trebuie să fie o constantă. Dacă nu există bloc DEFAULT, valoarea inițială este NULL.

Exemplu: variabile locale

Variabilele locale sunt declarate în procedurile memorate MySQL. Ele sunt valabile numai în blocul END. BEGIN. în care sunt anunțate. Variabilele locale pot conține orice tip de date SQL. Următorul exemplu arată folosirea variabilelor locale într-o procedură stocată:

Acum efectuați procedura:

Exemplu: Variabile personalizate

În procedurile stocate în MySQL, variabilele utilizatorului sunt accesate prin caracterul ampersand (@) înainte de numele variabilei utilizator (de exemplu, @ x și @y). Următorul exemplu arată utilizarea variabilelor personalizate în cadrul unei proceduri stocate:

MySQL: parametrii de procedură

Sintaxa pentru CREATE PROCEDURE este după cum urmează:

Opțiunile sintaxei:

CREATE PROCEDURE proc_name ().
CREATE PROCEDURE proc_name ([IN] tipul parametrului_name).
CREATE PROCEDURE proc_name ([OUT] tip parametru_name).
CREATE PROCEDURE proc_name ([INOUT] tipul parametrului_name).

În primul exemplu, lista de parametri este goală.
În al doilea exemplu, parametrul IN transmite valoarea procedurii. Această procedură poate schimba valoarea. Dar, atunci când procedura returnează o valoare, aceasta nu va fi vizibilă agentului apelant.
În cel de-al treilea exemplu, parametrul OUT transmite valoarea din procedura înapoi la agentul apelant. Valoarea sa inițială este în procedura NULL. și când procedura returnează o valoare, este vizibilă agentului apelant.
În al patrulea exemplu, parametrul INOUT este inițializat de agentul apelant, poate fi modificat printr-o procedură și atunci când procedura returnează o valoare, orice modificare făcută de procedura stocată MySQL va fi vizibilă agentului apelant.

În cadrul procedurii, fiecare parametru este parametrul IN implicit. Pentru a schimba acest lucru, folosiți cuvântul cheie OUT sau INOUT înainte de numele parametrului.

Procedura MySQL: Exemplu parametru IN

Următoarea procedură utilizează parametrul IN "var1" (tipul întreg), care ia un număr de la utilizator. În corpul procedurii există o instrucțiune SELECT. care selectează rânduri din tabelul "joburi". Numărul de linii este specificat de utilizator. Procedura este după cum urmează:

Pentru a selecta primele două linii din tabelul "joburi", executați următoarea comandă:

Acum, selectați primele cinci rânduri din tabelul "joburi":

Procedura MySQL: Exemplu parametru OUT

Apoi, procedura stocată în MySQL este un exemplu în care este utilizat parametrul OUT. Ca parte a procedurii MySQL, funcția MAX () extrage salariul maxim din coloana MAX_SALARY a tabelului "jobs":

În corpul procedurii, parametrul primește cel mai mare salariu din coloana MAX_SALARY. După chemarea procedurii, cuvântul OUT este raportat de DBMS. că valoarea provine din procedură. Valoarea highest_salary este numele parametrului de ieșire și în instruciunea CALL am trecut valoarea sa la o variabilă de sesiune numită @M:

Procedura MySQL: Exemplu de parametru INOUT

Următorul exemplu arată o procedură memorată MySQL simplă. care utilizează parametrul INOUT și parametrul IN. Utilizatorul furnizează "M" sau "F" prin parametrul IN (emp_gender) pentru a număra numărul de angajați masculi sau feminini din tabela user_details. Parametrul INOUT (mfgender) returnează rezultatul utilizatorului. Iată codul și rezultatul procedurii:

Acum verificăm numărul de angajați masculi și femei în tabelul specificat:

MySQL: operatori de control al fluxului

MySQL suportă structuri pentru gestionarea fluxului de date în programele IF stocate. CASE. ITERATE. CONCEDIUL. LOOP. WHILE și REPEAT. De asemenea, RETURN este suportat în cadrul procedurilor stocate MySQL.

Instrucțiunea MySQL: IF

Instrucțiunea IF implementează constructul condiției de bază, trebuie să se încheie cu un punct și virgulă. Există, de asemenea, o funcție IF (), care este diferită de operatorul IF. Iată sintaxa instrucțiunii IF:

Dacă condiția este îndeplinită, operatorii blocurilor corespunzătoare THEN sau ELSE IF sunt executați.

Dacă condiția nu este îndeplinită, se execută instrucțiunile din declarația ELSE. Fiecare instrucțiune este compusă din una sau mai multe instrucțiuni SQL; operatorii goi nu sunt permise.

În exemplul următor, trecem prin parametrul IN user_id. pentru a obține numele de utilizator. Ca parte a procedurii, am folosit instrucțiunile IF ELSE IF și ELSE. pentru a obține numele de utilizator din setul de ID-uri de utilizator. Numele de utilizator va fi stocat în parametrul user_name INOUT:

Implementați apelul procedurii memorate MySQL:

MySQL: declarație CASE

Instrucțiunea CASE este folosită pentru a crea o construcție complexă condiționată în cadrul procedurii stocate MySQL. Instrucțiunea CASE nu poate conține blocul ELSE NULL și trebuie închis de END CASE. nu END. sintaxa:

Explicație: Prima sintaxă

O valoare este o expresie care este comparată cu valoarea din fiecare bloc WHEN. până când acestea sunt egale. Dacă se găsește potrivirea valorii, lista operator_operator a blocului THEN corespunzător este executată.

Dacă valorile nu sunt egale, atunci ELSE bloc_list este executat. (dacă există una).

Explicație: A doua sintaxă

Fiecare expresie din blocul search_value este evaluată. până când una dintre ele este adevărată. În acest moment, se execută lista operatorilor blocului THEN corespunzător.

Dacă niciuna dintre expresiile condiției de căutare nu este adevărată, atunci se execută lista ELSE a operatorilor. dacă există. Fiecare operator_list constă dintr-una sau mai multe instrucțiuni SQL; O listă goală de operatori nu este permisă.

Avem un tabel numit "joburi" cu următoarele intrări:

Vom calcula numărul de angajați care îndeplinesc următoarele condiții:

MIN_SALARY> 10000
MIN_SALARY

Articole similare

Pagina anterioară

Pagina următoare