Procedură stocată- Acest tip special Pachet de instrucțiuni Transact-SQL creat folosind limbajul SQL și extensiile procedurale. Principala diferență dintre un pachet și o procedură stocată este că aceasta din urmă este stocată ca obiect de bază de date. Cu alte cuvinte, procedurile stocate sunt stocate pe partea serverului pentru a îmbunătăți performanța și consistența sarcinilor repetabile.
Motorul de baze de date acceptă procedurile stocate și procedurile de sistem. Procedurile stocate sunt create în același mod ca toate celelalte obiecte de bază de date, de exemplu. folosind limbajul DDL. Proceduri de sistem sunt furnizate de Database Engine și pot fi utilizate pentru a accesa și modifica informațiile din catalogul de sistem.
Când creați o procedură stocată, puteți defini o listă opțională de parametri. În acest fel, procedura va accepta argumentele corespunzătoare de fiecare dată când este apelată. Procedurile stocate pot returna o valoare care contine definit de utilizator informații sau, în caz de eroare, un mesaj de eroare corespunzător.
Procedura stocată este precompilată înainte de a fi stocată ca obiect în baza de date. Forma precompilată a procedurii este stocată în baza de date și utilizată de fiecare dată când este apelată. Această proprietate a procedurilor stocate oferă avantajul important de a elimina (în aproape toate cazurile) compilările repetate de proceduri și de a obține îmbunătățiri corespunzătoare de performanță. Această proprietate a procedurilor stocate are, de asemenea, un efect pozitiv asupra cantității de date schimbate între sistemul de baze de date și aplicații. În special, apelarea unei proceduri stocate care are o dimensiune de câteva mii de octeți poate necesita mai puțin de 50 de octeți. Atunci când mai mulți utilizatori efectuează sarcini repetitive folosind proceduri stocate, efectul cumulativ al acestor economii poate fi destul de semnificativ.
Procedurile stocate pot fi utilizate și în următoarele scopuri:
pentru a crea un jurnal de acțiuni cu tabele de baze de date.
Utilizarea procedurilor stocate oferă un nivel de control al securității care depășește cu mult securitatea oferită prin utilizarea instrucțiunilor GRANT și REVOKE, care acordă diferite privilegii de acces utilizatorilor. Acest lucru este posibil deoarece autorizarea de a executa o procedură stocată este independentă de autorizarea de modificare a obiectelor conținute în procedura stocată, așa cum este descris în secțiunea următoare.
Procedurile stocate care creează jurnalele de operații de scriere și/sau citire a tabelelor oferă oportunitate suplimentară asigurarea securității bazei de date. Utilizând astfel de proceduri, administratorul bazei de date poate monitoriza modificările aduse bazei de date de către utilizatori sau programele de aplicație.
Crearea și executarea procedurilor stocate
Procedurile stocate sunt create folosind o instrucțiune CREAȚI PROCEDURA, care are următoarea sintaxă:
CREATE PROC proc_name [((@param1) type1 [ VARYING] [= default1] )] (, ...) AS lot | NUME EXTERN nume_metodă Convenții de sintaxă
Parametrul schema_name specifică numele schemei care este atribuit de proprietarul procedurii stocate create. Parametrul proc_name specifică numele procedurii stocate. Parametrul @param1 este un parametru de procedură (argument formal) al cărui tip de date este determinat de parametrul tip1. Parametrii procedurii sunt locali în cadrul procedurii, la fel cum variabilele locale sunt locale în cadrul pachetului. Parametrii procedurii sunt valori care sunt transmise de apelant procedurii pentru utilizare în ea. Parametrul default1 specifică valoarea implicită pentru parametrul de procedură corespunzător. (Valoarea implicită poate fi, de asemenea, NULL.)
Opțiunea IEȘIRE indică faptul că un parametru de procedură este un parametru de returnare și poate fi utilizat pentru a returna o valoare dintr-o procedură stocată către procedura sau sistemul care apelează.
După cum am menționat mai devreme, forma precompilată a unei proceduri este stocată în baza de date și utilizată de fiecare dată când este apelată. Dacă dintr-un motiv oarecare procedura stocată trebuie compilată de fiecare dată când este apelată, atunci când declarați procedura, utilizați CU opțiunea RECOMPILE. Utilizarea opțiunii WITH RECOMPILE anulează unul dintre cele mai importante beneficii ale procedurilor stocate: îmbunătățirea performanței datorită unei singure compilări. Prin urmare, opțiunea WITH RECOMPILE ar trebui utilizată numai atunci când obiectele bazei de date utilizate de procedura stocată sunt modificate frecvent.
clauza EXECUTE AS definește contextul de securitate în care procedura stocată ar trebui să se execute după ce este apelată. Prin setarea acestui context, Motorul de baze de date poate controla selecția conturilor de utilizator pentru a verifica permisiunile de acces la obiectele la care se face referire de procedura stocată.
În mod implicit, numai membrii rolului de server fix sysadmin și rolurile de bază de date fixe db_owner sau db_ddladmin pot folosi instrucțiunea CREATE PROCEDURE. Dar membrii acestor roluri pot atribui acest drept altor utilizatori folosind declarația PROCEDURA DE CREARE A GRANT.
Exemplul de mai jos arată cum să creați o procedură stocată simplă pentru a lucra cu tabelul Proiect:
UTILIZAȚI SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE Project SET Buget = Buget + Buget * @percent/100;
După cum sa spus mai devreme, pentru a separa două pachete, utilizați GO instrucțiuni. Instrucțiunea CREATE PROCEDURE nu poate fi combinată cu alte instrucțiuni Transact-SQL din același lot. Procedura stocată IncreaseBudget mărește bugetele pentru toate proiectele cu un anumit procent, determinat de parametrul @percent. Procedura definește, de asemenea, o valoare procentuală implicită (5) care este utilizată dacă acest argument nu este prezent atunci când se execută procedura.
Procedurile stocate pot accesa tabele care nu există. Această proprietate vă permite să depanați codul de procedură fără a crea mai întâi tabelele adecvate sau chiar să vă conectați la serverul de destinație.
Spre deosebire de procedurile stocate primare, care sunt întotdeauna stocate în baza de date curentă, este posibil să se creeze proceduri stocate temporare care sunt întotdeauna stocate în baza de date temporară a sistemului tempdb. Un motiv pentru a crea proceduri stocate temporare poate fi evitarea executării repetate a unui anumit grup de instrucțiuni atunci când vă conectați la o bază de date. Puteți crea proceduri temporare locale sau globale. Pentru a face acest lucru, numele procedurii locale este specificat cu un singur caracter # (#proc_name), iar numele procedurii globale este specificat cu un caracter dublu (##proc_name).
O procedură stocată temporară locală poate fi executată numai de utilizatorul care a creat-o și numai în timp ce este conectată la baza de date în care a fost creată. O procedură temporară globală poate fi executată de toți utilizatorii, dar numai până la terminarea ultimei conexiuni pe care este executată (de obicei conexiunea creatorului procedurii).
Ciclul de viață al unei proceduri stocate constă din două etape: crearea și executarea acesteia. Fiecare procedură este creată o dată și executată de mai multe ori. Procedura stocată este executată folosind EXECUTE declarații un utilizator care este proprietarul unei proceduri sau are privilegiul EXECUTE pentru a accesa procedura respectivă. Instrucțiunea EXECUTE are următoarea sintaxă:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] valoare | [@parameter1=] @variable ] | DEFAULT).. Convenții de sintaxă
Cu excepția parametrului return_status, toți parametrii instrucțiunii EXECUTE au aceeași semnificație logică ca aceiași parametri ai instrucțiunii CREATE PROCEDURE. Parametrul return_status specifică o variabilă întreagă care stochează starea de returnare a procedurii. O valoare poate fi atribuită unui parametru folosind fie o constantă (valoare), fie o variabilă locală (@variabilă). Ordinea valorilor parametrilor numiți nu este importantă, dar valorile parametrilor nenumiți trebuie furnizate în ordinea în care sunt definiți în instrucțiunea CREATE PROCEDURE.
clauza DEFAULT furnizează valoarea implicită pentru un parametru de procedură care a fost specificat în definiția procedurii. Când o procedură așteaptă o valoare pentru un parametru pentru care nu a fost definită nicio valoare implicită și parametrul lipsește sau este specificat cuvânt cheie IMPLICIT, atunci apare o eroare.
Când instrucțiunea EXECUTE este prima instrucțiune a unui lot, cuvântul cheie EXECUTE poate fi omis. Cu toate acestea, este mai sigur să includeți acest cuvânt în fiecare pachet. Utilizarea instrucțiunii EXECUTE este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; EXECUTĂ Creșterea Bugetului 10;
Instrucțiunea EXECUTE din acest exemplu execută procedura stocată IncreaseBudget, care crește bugetul tuturor proiectelor cu 10%.
Exemplul de mai jos arată cum să creați o procedură stocată pentru procesarea datelor în tabelele Employee și Works_on:
Exemplul de procedură ModifyEmpId ilustrează utilizarea procedurilor stocate ca parte a procesului de menținere a integrității referențiale (în acest caz între tabelele Employee și Works_on). O procedură stocată similară poate fi utilizată în interiorul unei definiții de declanșare, care oferă de fapt integritate referențială.
Următorul exemplu arată utilizarea unei clauze OUTPUT într-o procedură stocată:
Această procedură stocată poate fi executată folosind următoarele instrucțiuni:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @OUTPUT; PRINT N"Angajați șterși: " + convert(nvarchar(30), @quantityDeleteEmployee);
Această procedură numără numărul de proiecte la care lucrează angajatul cu număr de personal @empId și atribuie valoarea rezultată parametrului ©counter. După ce toate rândurile pentru un anumit număr de personal sunt șterse din tabelele Employee și Works_on, valoarea calculată este atribuită variabilei @quantityDeleteEmployee.
Valoarea parametrului este returnată la procedura de apelare numai dacă este specificată opțiunea OUTPUT. În exemplul de mai sus, procedura DeleteEmployee transmite parametrul @counter procedurii de apelare, prin urmare procedura stocată returnează o valoare sistemului. Prin urmare, parametrul @counter trebuie specificat atât în opțiunea OUTPUT la declararea unei proceduri, cât și în instrucțiunea EXECUTE la apelarea acesteia.
Clauza WITH RESULTS SETS a instrucțiunii EXECUTE
ÎN SQL Server 2012 pentru instrucțiunea EXECUTE este introdusă WITH RESULTS SETS clauza, prin care, atunci când sunt îndeplinite anumite condiții, puteți modifica forma setului de rezultate al unei proceduri stocate.
Următoarele două exemple vor ajuta la explicarea acestei propoziții. Primul exemplu este un exemplu introductiv care arată cum ar putea arăta rezultatul atunci când clauza WITH RESULTS SETS este omisă:
Procedura EmployeesInDept este o procedură simplă care afișează numerele de personal și numele de familie ale tuturor angajaților care lucrează într-un anumit departament. Numărul departamentului este un parametru de procedură și trebuie specificat la apelarea acestuia. Executarea acestei proceduri produce un tabel cu două coloane ale căror titluri se potrivesc cu numele coloanelor corespunzătoare din tabelul bazei de date, i.e. Id și Nume. Pentru a modifica anteturile coloanelor de rezultate (precum și tipul lor de date), SQL Server 2012 utilizează noua clauză WITH RESULTS SETS. Aplicarea acestei propoziții este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; EXEC EmployeesInDept „d1” CU SETURI DE REZULTATE (( INT NU NULL, [Nume] CHAR(20) NU NULL));
Rezultatul executării unei proceduri stocate numită în acest fel va fi următorul:
După cum puteți vedea, rularea unei proceduri stocate folosind clauza WITH RESULT SETS din instrucțiunea EXECUTE vă permite să schimbați numele și tipurile de date ale coloanelor din setul de rezultate produs de procedură. Astfel, această nouă funcționalitate oferă o mai mare flexibilitate în executarea procedurilor stocate și plasarea rezultatelor acestora într-un nou tabel.
Modificarea structurii procedurilor stocate
Motorul de baze de date acceptă, de asemenea, instrucțiunea PROCEDURA DE ALTERARE pentru a modifica structura procedurilor stocate. Instrucțiunea ALTER PROCEDURE este de obicei utilizată pentru a schimba instrucțiunile Transact-SQL în cadrul unei proceduri. Toți parametrii instrucțiunii ALTER PROCEDURE au aceeași semnificație ca aceiași parametri ai instrucțiunii CREATE PROCEDURE. Scopul principal al utilizării acestei declarații este de a evita suprascrierea drepturilor existente de procedură stocată.
Motorul de baze de date acceptă tip de date CURSOR. Acest tip de date este folosit pentru a declara cursoarele în procedurile stocate. Cursor este un construct de programare folosit pentru a stoca rezultatele unei interogări (de obicei un set de rânduri) și pentru a permite utilizatorilor să afișeze acel rezultat rând cu rând.
Pentru a șterge una sau un grup de proceduri stocate, utilizați INSTRUCȚIUNEA PROCEDURII DE DERARE. Doar proprietarul sau membrii rolurilor fixe db_owner și sysadmin pot șterge o procedură stocată.
Proceduri stocate și runtime de limbaj comun
SQL Server acceptă Common Language Runtime (CLR), care vă permite să dezvoltați diverse obiecte de bază de date (proceduri stocate, funcții definite de utilizator, declanșatoare definite de utilizator) functii statisticeși tipuri de date personalizate), folosind C# și Visual Basic. CLR vă permite, de asemenea, să executați aceste obiecte folosind sistemul de rulare comun.
Runtimeul limbajului comun este activat și dezactivat folosind opțiunea clr_enabled procedura de sistem sp_configure, care este lansat pentru execuție prin instrucțiune RECONFIGURAȚI. Următorul exemplu arată cum puteți utiliza procedura de sistem sp_configure pentru a activa CLR:
UTILIZAȚI SampleDb; EXEC sp_configure „clr_enabled”,1 RECONFIGURĂ
Pentru a crea, compila și salva o procedură folosind limbajul comun de execuție, trebuie să finalizați următoarea secvență de pași în ordinea afișată:
Creați o procedură stocată în C# sau Visual Basic și apoi compilați-o folosind compilatorul corespunzător.
Utilizarea instrucțiunilor CREAȚI ANSAMBLU, creați fișierul executabil corespunzător.
Executați procedura utilizând instrucțiunea EXECUTE.
Figura de mai jos prezintă o diagramă grafică a pașilor subliniați anterior. Mai jos este mai mult descriere detaliata acest proces.
Mai întâi creați programul necesar într-un mediu de dezvoltare, cum ar fi Studio vizual. Compila program gata făcutîn codul obiect folosind compilatorul C# sau Visual Basic. Acest cod este stocat într-un fișier de bibliotecă cu legături dinamice (.dll), care servește ca sursă pentru instrucțiunea CREATE ASSEMBLY, care creează codul executabil intermediar. Apoi, lansați o instrucțiune CREATE PROCEDURE pentru a salva codul de execuție ca obiect de bază de date. În cele din urmă, rulați procedura utilizând instrucțiunea EXECUTE familiară.
Exemplul de mai jos arată codul sursă pentru o procedură stocată în C#:
Utilizarea System.Data.SqlClient; folosind Microsoft.SqlServer.Server; clasă parțială publică StoredProcedures ( public static int CountEmployees() ( int rânduri; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "selectați count(*) ca „Număr de angajați” „ + „de la angajat”; rânduri = (int)cmd.ExecuteScalar(); connection.Close(); rânduri returnate; ) )
Această procedură implementează o interogare pentru a număra numărul de rânduri din tabelul Employee. Utilizarea directivelor la începutul unui program specificați spațiile de nume necesare pentru a executa programul. Utilizarea acestor directive vă permite să specificați cod sursa nume de clasă fără a specifica în mod explicit spațiile de nume corespunzătoare. În continuare, este definită clasa StoredProcedures, pentru care este aplicată Atributul SqlProcedure, care informează compilatorul că această clasă este o procedură stocată. Metoda CountEmployees() este definită în codul clasei. O conexiune la sistemul de baze de date este stabilită printr-o instanță a clasei SqlConnection. Pentru a deschide o conexiune, se folosește metoda Open() a acestei instanțe. A Metoda CreateCommand(). vă permite să accesați o instanță a unei clase SqlCommnd, căruia i se transmite comanda SQL necesară.
În următorul fragment de cod:
Cmd.CommandText = "selectați count(*) ca "Număr de angajați" " + "de la angajat";
folosește o instrucțiune SELECT pentru a număra numărul de rânduri din tabelul Employee și pentru a afișa rezultatul. Textul comenzii este specificat prin setarea proprietății CommandText a variabilei cmd la instanța returnată de metoda CreateCommand(). În continuare se numește Metoda ExecuteScalar(). Instanță SqlCommand. Această metodă returnează o valoare scalară în care este convertită tipul întreg int date și atribuite variabilei rows.
Acum puteți compila acest cod folosind Visual Studio. Am adăugat această clasă la un proiect numit CLRStoredProcedures, astfel încât Visual Studio va compila un ansamblu cu același nume cu o extensie *.dll. Exemplul de mai jos arată următorul pas în crearea unei proceduri stocate: crearea codului executabil. Înainte de a rula codul din acest exemplu, trebuie să știți locația fișierului dll compilat (de obicei situat în folderul Debug al proiectului).
UTILIZAȚI SampleDb; CREAȚI ANSABLUL CLRStoredProcedures DIN „D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll” CU PERMISSION_SET = SAFE
Instrucțiunea CREATE ASSEMBLY ia cod gestionat ca intrare și creează un obiect corespunzător pe care puteți crea proceduri stocate CLR, funcții definite de utilizator și declanșatoare. Această instrucțiune are următoarea sintaxă:
CREATE ASSEMBLY nume_asamblare [ AUTHORIZATION nume_proprietar ] FROM (fișier_dll) Convenții de sintaxă
Parametrul assembly_name specifică numele ansamblului. Clauza opțională AUTHORIZATION specifică numele rolului ca proprietar al acestui ansamblu. Clauza FROM specifică calea unde se află ansamblul de încărcat.
Clauza WITH PERMISSION_SET este o clauză foarte importantă a instrucțiunii CREATE ASSEMBLY și trebuie întotdeauna specificată. Acesta definește setul de permisiuni acordate codului de asamblare. Setul de permisiuni SAFE este cel mai restrictiv. Codul de asamblare care are aceste drepturi nu poate accesa resursele externe ale sistemului, cum ar fi fișierele. Setul de drepturi EXTERNAL_ACCESS permite codului de asamblare să acceseze anumite resurse externe ale sistemului, în timp ce setul de drepturi UNSAFE permite accesul nerestricționat la resurse atât în interiorul, cât și în afara sistemului bazei de date.
Pentru a salva informațiile despre codul de asamblare, utilizatorul trebuie să poată emite o instrucțiune CREATE ASSEMBLY. Proprietarul ansamblului este utilizatorul (sau rolul) care execută instrucțiunea. Puteți face un alt utilizator proprietarul ansamblului utilizând clauza AUTHORIZATION a instrucțiunii CREATE SCHEMA.
Motorul de baze de date acceptă, de asemenea, instrucțiunile ALTER ASSEMBLY și DROP ASSEMBLY. Declarație ALTER ASSEMBLY folosit pentru a actualiza ansamblul la ultima versiune. Această instrucțiune adaugă sau elimină și fișierele asociate cu ansamblul corespunzător. INSTRUCȚIUNEA DE MONTARE PICĂTURĂÎndepărtează ansamblul specificat și toate fișierele asociate acestuia din baza de date curentă.
Exemplul de mai jos arată cum să creați o procedură stocată pe baza codului gestionat pe care l-ați implementat mai devreme:
UTILIZAȚI SampleDb; CREATE PROCEDURA CountEmployees CA NUME EXTERN CLRStoredProcedures.StoredProcedures.CountEmployees
Instrucțiunea CREATE PROCEDURE din exemplu diferă de aceeași instrucțiune din exemplele anterioare prin faptul că conține Parametrul NUME EXTERN. Această opțiune specifică faptul că codul este generat de common language runtime. Numele din această propoziție este format din trei părți:
nume_asamblare.nume_clasă.nume_metodă
assembly_name - indică numele ansamblului;
class_name - indică numele clasei generale;
method_name - parte opțională, specifică numele metodei care este definită în interiorul clasei.
Execuția procedurii CountEmployees este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Returnează 7
Instrucțiunea PRINT returnează numărul curent de rânduri din tabelul Employee.
Proceduri stocate
Subiectul acestui capitol este unul dintre cele mai puternice instrumente oferite dezvoltatorilor de aplicații de baze de date InterBase pentru implementarea logicii de afaceri. performanța întregii aplicații, centralizați procesarea datelor și reduceți cantitatea de cod necesară pentru a finaliza sarcinile atribuite. Aproape orice aplicație de bază de date destul de complexă nu poate face fără utilizarea procedurilor stocate.
Pe lângă aceste avantaje binecunoscute ale utilizării procedurilor stocate, comune majorității SGBD-urilor relaționale, procedurile stocate InterBase pot acționa ca seturi de date aproape complete, permițând ca rezultatele pe care le returnează să fie utilizate în interogări SQL obișnuite.
Adesea, dezvoltatorii începători își imaginează procedurile stocate pur și simplu ca un set de interogări SQL specifice care fac ceva în interiorul bazei de date și există opinia că lucrul cu proceduri stocate este mult mai dificil decât implementarea aceleiași funcționalități într-o aplicație client în limbaj. nivel inalt
Deci, ce sunt procedurile stocate în InterBase?
O procedură stocată (SP) este o parte a metadatelor bazei de date, care este o subrutină compilată în reprezentarea internă a InterBase, scrisă într-un limbaj special, al cărui compilator este încorporat în nucleul serverului InteiBase.
O procedură stocată poate fi apelată din aplicații client, de la declanșatoare și din alte proceduri stocate. Procedura stocată rulează în interiorul procesului serverului și poate manipula datele din baza de date, precum și poate returna rezultatele execuției sale către clientul care a apelat-o (adică declanșator, HP, aplicație)
Baza capabilităților puternice inerente HP este un limbaj de programare procedural, care include atât instrucțiuni modificate ale SQL obișnuit, cum ar fi INSERT, UPDATE și SELECT, cât și instrumente pentru organizarea ramurilor și buclelor (IF, WHILE), precum și instrumente de tratare a erorilor și situații excepționale Limbajul procedurilor stocate vă permite să implementați algoritmi complecși pentru lucrul cu date și, datorită concentrării pe lucrul cu date relaționale, HP este mult mai compact decât procedurile similare din limbile tradiționale.
Trebuie remarcat faptul că același limbaj de programare este utilizat pentru declanșatoare, cu excepția unui număr de caracteristici și limitări. Diferențele dintre subsetul de limbaj utilizat în declanșatoare și limbajul HP sunt discutate în detaliu în capitolul „Declanșatoare” (partea 1).
Exemplu de procedură stocată simplă
Este timpul să creați prima procedură stocată și să o utilizați ca exemplu pentru a învăța procesul de creare a procedurilor stocate. Dar mai întâi, ar trebui să spunem câteva cuvinte despre cum să lucrați cu procedurile stocate Faptul este că HP își datorează reputația de instrument obscur și incomod instrumentelor standard extrem de slabe pentru dezvoltarea și depanarea procedurilor stocate. Documentația InterBase recomandă crearea de proceduri folosind fișiere de script SQL care conțin text HP, care sunt furnizate ca intrare pentru interpretorul isql, și astfel crearea și modificarea HP If în acest script SQL, în etapa de compilare a textului procedurii în BLR (aproximativ BLR, vezi Capitolul „Structura bazei de date InterBase” (Partea 4)) dacă apare o eroare, isql va afișa un mesaj despre linia pe care a apărut această eroare a fișierului script SQL. Corectați greșeala și repetați totul din nou. Despre depanare înțelegere modernă Acest cuvânt, adică urmărirea execuției, cu capacitatea de a analiza valorile intermediare ale variabilelor, nu este deloc discutat. Evident, această abordare nu contribuie la creșterea atractivității procedurilor stocate în ochii dezvoltatorului
Cu toate acestea, pe lângă abordarea minimalistă standard a dezvoltării HP<_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Sintaxa procedurilor stocate este descrisă după cum urmează:
CREATE PROCEDURE nume
[ (tip de date param [, tip de date param ...]) ]
)]
LA FEL DE
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARE VARIABLE var tip de date;
ÎNCEPE
< compound_statement>
[< compound_statement> ...]
Sfârşit
< compound_statement> =
(
Pare destul de voluminos și poate fi chiar greoi, dar de fapt totul este foarte simplu Pentru a stăpâni treptat sintaxa, să ne uităm treptat la exemple mai complexe.
Deci, iată un exemplu de procedură stocată foarte simplă care ia două numere ca intrare, le adaugă și returnează rezultatul:
CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DUBLĂ PRECIZIE)
RETURNARE (Rezultat DUBĂ PRECIZIE)
LA FEL DE
ÎNCEPE
Rezultat=first_arg+second_arg;
SUSPENDA;
Sfârşit
După cum puteți vedea, totul este simplu: după comanda CREATE PROCEDURE este indicat numele procedurii nou create (care trebuie să fie unică în baza de date) - în acest caz SP_Add, apoi parametrii de intrare HP - first_arg și second_arg - sunt enumerate în paranteze, separate prin virgule, indicând tipurile acestora.
Lista parametrilor de intrare este o parte opțională a instrucțiunii CREATE PROCEDURE - există cazuri când o procedură primește toate datele pentru funcționarea sa prin interogări către tabele din interiorul corpului procedurii.
Procedurile stocate folosesc orice tip de date scalare InteiBase Nu permite utilizarea de matrice și tipuri definite de utilizator - domenii
Urmează cuvântul cheie RETURNS, după care parametrii returnați sunt enumerați în paranteze, indicând tipurile lor - în acest caz, doar unul - Rezultat.
Dacă procedura nu trebuie să returneze parametrii, atunci cuvântul RETURNS și lista parametrilor returnați lipsesc.
După RETURNSQ este specificat cuvântul cheie AS. Înainte ca cuvântul cheie AS să dispară titlu, si dupa aceea - techo proceduri.
Corpul unei proceduri stocate este o listă de descrieri ale variabilelor sale interne (locale) (dacă există, le vom analiza mai detaliat mai jos), separate prin punct și virgulă (;) și un bloc de instrucțiuni cuprinse între paranteze operator. ÎNCEPUT Sfârșit. În acest caz, corpul HP este foarte simplu - cerem să adăugăm două argumente de intrare și să atribuim rezultatul acestora celui de ieșire, apoi apelăm comanda SUSPEND. Puțin mai târziu vom explica esența acțiunii acestei comenzi, dar deocamdată vom reține doar că este necesar să transferăm parametrii returnați de unde a fost apelată procedura stocată.
Delimitatori în procedurile stocate
Rețineți că o instrucțiune dintr-o procedură se termină cu punct și virgulă (;). După cum știți, punctul și virgulă este un separator standard de comandă în SQL - este un semnal pentru interpretul SQL că textul comenzii a fost introdus în întregime și ar trebui să înceapă procesarea acestuia. Nu s-ar dovedi că dacă interpretul SQL găsește un punct și virgulă în mijlocul HP, va presupune că comanda a fost introdusă în întregime și va încerca să execute o parte din procedura stocată? Această presupunere nu este lipsită de merit. Într-adevăr, dacă creați un fișier în care să scrieți exemplul de mai sus, adăugați o comandă de conexiune din baza de date și încercați să executați acest script SQL folosind interpretul isql, va fi returnată o eroare din cauza neașteptății, în opinia interpretului, care se încheie a comenzii de creare a procedurii stocate. Dacă creați proceduri stocate folosind fișiere de script SQL, fără a utiliza instrumente specializate pentru dezvoltatori InterBase, atunci înainte de fiecare comandă de creare HP (același lucru se aplică declanșatorilor) trebuie să schimbați separatorul de comenzi de script cu un alt caracter, altul decât punct și virgulă, iar după textul HP pentru a o restabili. Comanda isql care modifică separatorul de clauze SQL arată astfel:
SETĂ TERM
Pentru un caz tipic de creare a unei proceduri stocate, arată astfel:
SET TERM^;
CREATE PROCEDURE some_procedure
... . .
Sfârşit
^
SETĂ TERM ;^
Apelarea unei proceduri stocate
Dar să revenim la procedura noastră stocată. Acum că a fost creat, trebuie să îl apelați cumva, să îi transmiteți parametri și să primiți rezultatele returnate. Acest lucru este foarte ușor de făcut - trebuie doar să scrieți o interogare SQL ca aceasta:
SELECTAȚI *
FROM Sp_add(181.35, 23.09)
Această interogare ne va returna o linie care conține doar un câmp de rezultat, care va conține suma numerelor 181.35 și 23.09, adică 204.44.
Astfel, procedura noastră poate fi folosită în interogări SQL obișnuite executate atât în programele client, cât și în alte HP sau declanșatoare. Această utilizare a procedurii noastre este posibilă prin utilizarea comenzii SUSPEND la sfârșitul procedurii stocate.
Cert este că în InterBase (și în toate clonele sale) există două tipuri de proceduri stocate: proceduri selectabile și proceduri executabile. Diferența în funcționarea acestor două tipuri de HP este că procedurile de eșantionare returnează de obicei multe seturi de parametri de ieșire, grupați linie cu linie, care arată ca un set de date, în timp ce procedurile executabile ar putea fie să nu returneze deloc parametri, fie să returneze doar un set de parametri de ieșire, listați în Returnări, unde o linie de parametri. Procedurile Select sunt apelate în interogările SELECT, iar procedurile executabile sunt apelate folosind comanda EXECUTE PROCEDURE.
Ambele tipuri de proceduri stocate au aceeași sintaxă de creare și nu sunt în mod formal diferite, astfel încât orice procedură executabilă poate fi apelată într-o interogare SELECT și orice procedură de selecție poate fi apelată folosind EXECUTE PROCEDURE. Întrebarea este cum se va comporta HP când tipuri diferite apel. Cu alte cuvinte, diferența constă în proiectarea procedurii pentru un anumit tip de apel. Adică, procedura de selectare este creată special pentru a fi apelată dintr-o interogare SELECT, iar procedura executabilă este creată special pentru a fi apelată utilizând PROCEDURA EXECUTE. Să ne uităm la care sunt diferențele în designul acestor două tipuri de HP.
Pentru a înțelege cum funcționează procedura de eșantionare, va trebui să aprofundați puțin în teorie. Să ne imaginăm o interogare SQL obișnuită, cum ar fi SELECT ID, NAME FROM Table_example. În urma execuției sale, obținem un tabel format din două coloane (ID și NUME) și un anumit număr de rânduri (egal cu numărul de rânduri din tabelul Table_example). Tabelul returnat din această interogare se mai numește și set date SQL Să ne gândim la modul în care se formează setul de date în timpul execuției acestei interogări. Serverul, după ce a primit interogarea, determină la ce tabele se referă, apoi află ce subset de înregistrări din aceste tabele trebuie inclus în rezultatul interogării. Apoi, serverul citește fiecare înregistrare care satisface rezultatele interogării, selectează câmpurile necesare din ea (în cazul nostru, ID și NUME) și le trimite clientului. Apoi procesul se repetă din nou - și așa mai departe pentru fiecare înregistrare selectată.
Toată această digresiune este necesară pentru ca dragul cititor să înțeleagă că toate seturile de date SQL sunt generate rând cu rând, inclusiv în procedurile stocate! Și principala diferență dintre procedurile de preluare și procedurile executabile este că primele sunt concepute pentru a returna multe rânduri, în timp ce ultimele sunt concepute pentru a returna doar unul. De aceea sunt folosite diferit: procedura de selectare este apelată cu ajutorul comenzii SELECT, care „cere” ca procedura să renunțe la toate înregistrările pe care le poate returna. Procedura executabilă este numită folosind EXECUTE PROCEDURE, care „scoate” doar o linie din HP și ignoră restul (chiar dacă există!).
Să ne uităm la un exemplu de procedură de eșantionare pentru a fi mai clar. Pentru > iertare, să creăm o procedură stocată care funcționează exact ca o interogare SELECT ID, NAME FROM Table_Example, adică selectează pur și simplu câmpurile ID și NAME din întregul tabel. Iată acest exemplu:
CREATE PROCEDURE Simple_Select_SP
SE INTOARCE (
PROCID INTEGER,
procNAME VARCHAR(80))
LA FEL DE
ÎNCEPE
PENTRU
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
ÎNCEPE
SUSPENDA;
Sfârşit
Sfârşit
Să ne uităm la pașii acestei proceduri, numite Simple_Select_SP. După cum puteți vedea, nu are parametri de intrare și are doi parametri de ieșire - ID și NUME. Cel mai interesant lucru, desigur, constă în corpul procedurii. Construcția FOR SELECT este folosită aici:
PENTRU
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
ÎNCEPE
/*faceți ceva cu variabilele procID și procName*/
Sfârşit
Această bucată de cod înseamnă următoarele: pentru fiecare rând selectat din tabelul Table_example, puneți valorile selectate în variabilele procID și procName, apoi faceți ceva cu aceste variabile.
S-ar putea să faci o față surprinsă și să întrebi: „Variabile? Ce alte variabile? 9” Este un fel de surpriză a acestui capitol că putem folosi variabile în procedurile stocate. În limbajul HP, puteți declara atât propriile variabile locale în cadrul unei proceduri și puteți utiliza parametrii de intrare și de ieșire ca variabile.
Pentru a declara o variabilă locală într-o procedură stocată, trebuie să plasați descrierea acesteia după cuvântul cheie AS și înainte de primul cuvânt BEGIN. Descrierea unei variabile locale arată astfel:
DECLARE VARIABIL
De exemplu, pentru a declara o variabilă locală întreagă Mylnt, ar trebui să inserați următoarea declarație între AS și BEGIN
DECLARE VARIABILĂ Mylnt INTEGER;
Variabilele din exemplul nostru încep cu două puncte. Acest lucru se face deoarece acestea sunt accesate în cadrul comenzii SQL FOR SELECT, deci pentru a face distincția între câmpurile din tabelele care sunt utilizate în SELECT și variabile, acestea din urmă trebuie să fie precedate de două puncte. La urma urmei, variabilele pot avea exact același nume ca și câmpurile din tabele!
Dar două puncte dinaintea unui nume de variabilă ar trebui folosite numai în interogările SQL. În afara textelor, se face referire la o variabilă fără două puncte, de exemplu:
procName="Un nume";
Dar să revenim la corpul procedurii noastre. clauza FOR SELECT returnează date nu sub forma unui tabel - un set de date, ci un rând la un moment dat. Fiecare câmp returnat trebuie plasat în propria sa variabilă: ID => procID, NAME => procName. În partea DO, aceste variabile sunt trimise clientului care a apelat procedura folosind comanda SUSPEND
Astfel, comanda FOR SELECT...DO trece în buclă prin înregistrările selectate în partea SELECT a comenzii. În corpul buclei formate din partea DO, următoarea înregistrare generată este transferată către client folosind comanda SUSPEND.
Deci, procedura de selecție este concepută pentru a returna unul sau mai multe rânduri, pentru care este organizată o buclă în interiorul corpului HP care umple parametrii variabili rezultați. Și la sfârșitul corpului acestei bucle există întotdeauna o comandă SUSPEND, care va returna următorul rând de date către client.
Bucle și instrucțiuni de ramuri
Pe lângă comanda FOR SELECT...DO, care organizează o buclă prin înregistrările unei selecții, există un alt tip de buclă - WHILE...DO, care vă permite să organizați o buclă pe baza verificării oricăror condiții. Iată un exemplu de HP care utilizează bucla WHILE..DO. Această procedură returnează pătratele numerelor întregi de la 0 la 99:
CREATE PROCEDJRE QUAD
RETURNĂRI (INTEGR CADRAT)
LA FEL DE
DECLARE VARIABILĂ I ÎNTREG;
ÎNCEPE
I = 1;
In timp ce eu<100) DO
ÎNCEPE
QUADRAT= I*I;
I=I+1;
SUSPENDA;
Sfârşit
Sfârşit
Ca urmare a executării interogării SELECT FROM QUAD, vom primi un tabel care conține o coloană QUADRAT, care va conține pătratele numerelor întregi de la 1 la 99.
Pe lângă iterarea rezultatelor unui eșantion SQL și a unei bucle clasice, limbajul de procedură stocată folosește operatorul IF...THEN..ELSE, care vă permite să organizați ramificarea în funcție de execuția oricăror condiții. Sintaxa sa este similară la majoritatea operatorilor de ramificare în limbaje de programare de nivel înalt, cum ar fi Pascal și C.
Să ne uităm la un exemplu mai complex de procedură stocată care face următoarele.
- Calculează prețul mediu în tabelul Table_example (vezi capitolul „Tabele Chei primare și generatoare”)
- Apoi, pentru fiecare intrare din tabel, efectuează următoarea verificare: dacă prețul existent (PREZUL) este mai mare decât prețul mediu, atunci stabilește un preț egal cu prețul mediu, plus un procent fix specificat
- Dacă prețul existent este mai mic sau egal cu prețul mediu, atunci stabilește un preț egal cu prețul anterior, plus jumătate din diferența dintre prețul anterior și prețul mediu.
- Returnează toate rândurile modificate din tabel.
Mai întâi, să definim numele HP, precum și parametrii de intrare și de ieșire Toate acestea sunt scrise în antetul procedurii stocate.
CREAȚI PROCEDURA Creșteți prețurile (
Creștere procentuală 2ln PRECIZIE DUBLĂ)
RETURNĂRI (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRECIZIE AS
Procedura se va numi IncreasePrices, are un parametru de intrare Peiceni21nciease de tip DOUBLE PRECISION, si 3 parametri de iesire - ID, NAME si new_pnce. Rețineți că primii doi parametri de ieșire au aceleași nume ca și câmpurile din tabelul Table_example cu care vom lucra. Acest lucru este permis de regulile limbajului procedurii stocate.
Acum trebuie să declarăm o variabilă locală care va fi folosită pentru a stoca valoarea medie Declarația va arăta astfel:
DECLARE VARIABILE avg_price DUBLĂ PRECIZIE;
Acum să trecem la corpul procedurii stocate. Deschideți corpul HP
cu cuvântul cheie BEGIN.
Mai întâi trebuie să efectuăm primul pas al algoritmului nostru - să calculăm prețul mediu. Pentru a face acest lucru, vom folosi următorul tip de interogare:
SELECTAȚI AVG(Preț_l)
DIN Tabel_Exemplu
INTO:avg_price,-
Această interogare folosește funcția de agregare AVG, care returnează media câmpului PRICE_1 dintre rândurile de interogare selectate — în cazul nostru, media PRICE_1 în întregul tabel Table_example. Valoarea returnată de cerere este plasată în variabila avg_price. Rețineți că variabila avg_pnce este precedată de două puncte pentru a o deosebi de câmpurile utilizate în cerere.
Caracteristică a acestei cereri este că returnează întotdeauna exact o singură înregistrare. Astfel de interogări sunt numite interogări singleton și numai astfel de selecții pot fi utilizate în procedurile stocate. Dacă o interogare returnează mai mult de un rând, atunci trebuie formatată ca construct FOR SELECT...DO, care organizează o buclă pentru a procesa fiecare rând returnat.
Deci, avem prețul mediu. Acum trebuie să parcurgeți întregul tabel, să comparați valoarea prețului din fiecare intrare cu prețul mediu și să luați măsurile adecvate
De la început, organizăm căutarea pentru fiecare înregistrare din tabelul Table_example
PENTRU
SELECT ID, NUME, PRICE_1
DIN Tabel_Exemplu
INTO:ID, :NAME, :new_price
DO
ÎNCEPE
/*_aici descriem fiecare intrare*/
Sfârşit
Când se execută această construcție, datele vor fi extrase din tabelul Table_example rând cu rând și valorile câmpului din fiecare rând vor fi atribuite variabilelor ID, NAME și new_pnce. Vă amintiți, desigur, că aceste variabile sunt declarate ca parametri de ieșire, dar nu trebuie să vă faceți griji că datele selectate vor fi returnate ca rezultate: faptul că parametrilor de ieșire li se atribuie ceva nu înseamnă că clientul care apelează HP va primi imediat aceste valori! Parametrii sunt transmisi numai atunci când comanda SUSPEND este executată și înainte de aceasta putem folosi parametrii de ieșire ca variabile obișnuite - în exemplul nostru facem exact asta cu parametrul new_price.
Deci, în interiorul corpului buclei BEGIN... END putem procesa valorile fiecărui rând. După cum vă amintiți, trebuie să ne dăm seama cum se compară prețul existent cu media și să luăm măsurile adecvate. Implementăm această procedură de comparare folosind instrucțiunea IF:
IF (new_price > avg_price) THEN /*dacă prețul existent este mai mare decât prețul mediu*/
ÎNCEPE
/*apoi vom stabili un nou preț egal cu prețul mediu, plus un procent fix */
preț_nou = (preț_mediu + preț_mediu*(Procent2Creștere/100));
UPDATE Tabel_exemplu
SETARE PREȚ_1 = :preț_nou
WHERE ID = :ID;
Sfârşit
ALTE
ÎNCEPE
/* Dacă prețul existent este mai mic sau egal cu prețul mediu, atunci setați un preț egal cu prețul anterior, plus jumătate din diferența dintre prețul anterior și prețul mediu */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Tabel_exemplu
SETARE PREȚ_1 = :preț_nou
WHERE ID = .ID;
Sfârşit
După cum puteți vedea, rezultatul este un construct IF destul de mare, care ar fi greu de înțeles dacă nu ar fi comentariile incluse în simbolurile /**/.
Pentru a modifica prețul în funcție de diferența calculată, vom folosi declarația UPDATE, care ne permite să modificăm înregistrările existente- una sau mai multe. Pentru a indica fără ambiguitate în ce înregistrare trebuie schimbat prețul, folosim câmpul cheie primară în condiția WHERE, comparându-l cu valoarea variabilei care stochează valoarea ID pentru înregistrarea curentă: ID=:ID. Rețineți că variabila ID este precedată de două puncte.
După executarea constructului IF...THEN...ELSE, variabilele ID, NAME și new_price conțin date pe care trebuie să le returnăm clientului care a apelat procedura. Pentru a face acest lucru, după IF, trebuie să introduceți comanda SUSPEND, care va trimite datele de unde a fost apelat HP În timpul transferului, procedura va fi suspendată, iar atunci când este necesară o nouă înregistrare de la HP va fi continuat din nou - și acest lucru va continua până când FOR SELECT...DO nu va itera prin toate înregistrările din interogarea sa.
Trebuie remarcat faptul că pe lângă comanda SUSPEND, care suspendă doar procedura stocată, există o comandă EXIT care încheie procedura stocată după trecerea șirului. Cu toate acestea, comanda EXIT este folosită destul de rar, deoarece este necesară în principal pentru a întrerupe bucla atunci când este atinsă o condiție
Totuși, în cazul în care procedura a fost apelată cu o instrucțiune SELECT și completată cu EXIT, ultimul rând preluat nu va fi returnat. Adică, dacă trebuie să întrerupeți procedura și tot > obțineți acest șir, trebuie să utilizați secvența
SUSPENDA;
IEȘIRE;
Scopul principal al EXIT este de a primi seturi de date singleton, parametri returnați prin apelarea EXECUTE PROCEDURE. În acest caz, valorile parametrilor de ieșire sunt setate, dar setul de date SQL nu este generat din aceștia, iar execuția procedurii se încheie.
Să notăm întregul text al procedurii noastre stocate, astfel încât să putem surprinde logica acesteia dintr-o privire:
CREAȚI PROCEDURA Creșteți prețurile (
Procent2Crește PRECIZIUNEA DUBLĂ)
RETURNĂ (ID INTEGER, NAME VARCHAR(80),
nou_pret PRECIZIUNE DUBLA) AS
DECLARE VARIABILE avg_price DUBLĂ PRECIZIE;
ÎNCEPE
SELECTAȚI AVG(Preț_l)
DIN Tabel_Exemplu
INTO:preț_mediu;
PENTRU
SELECT ID, NUME, PRICE_1
DIN Tabel_Exemplu
INTO:ID, :NAME, :new_price
DO
ÎNCEPE
/*procesează fiecare înregistrare aici*/
IF (new_pnce > avg_price) THEN /*dacă prețul existent este mai mare decât prețul mediu*/
ÎNCEPE
/*setează un preț nou egal cu prețul mediu plus un procent fix */
preț_nou = (preț_mediu + preț_mediu*(Procent2lncrease/100));
UPDATE Tabel_exemplu
SETARE PREȚ_1 = :preț_nou
WHERE ID = :ID;
Sfârşit
ALTE
ÎNCEPE
/* Dacă prețul existent este mai mic sau egal cu prețul mediu, atunci stabilește un preț egal cu prețul anterior plus jumătate din diferența dintre prețul anterior și prețul mediu */
nou_pret = (nou_pret + ((avg_price - new_price)/2));
UPDATE Tabel_exemplu
SETARE PREȚ_1 = :preț_nou
WHERE ID = :ID;
Sfârşit
SUSPENDA;
Sfârşit
Sfârşit
Acest exemplu de procedură stocată ilustrează utilizarea constructelor și declanșatorilor de limbaj de procedură stocată de bază. În continuare, vom analiza modalități de utilizare a procedurilor stocate pentru a rezolva unele probleme comune.
Proceduri stocate recursive
Procedurile stocate InterBase pot fi recursive. Aceasta înseamnă că o procedură stocată se poate autoinvoca. Sunt permise până la 1000 de niveluri de imbricare a procedurilor stocate, dar trebuie să ne amintim că resursele gratuite de pe server se pot epuiza înainte de a se atinge imbricarea maximă a HP.
O utilizare comună a procedurilor stocate este procesarea structurilor arborescente stocate într-o bază de date. Copacii sunt adesea folosiți în compoziția produsului, depozit, personal și alte aplicații comune.
Să ne uităm la un exemplu de procedură stocată care selectează toate produsele de un anumit tip, pornind de la un anumit nivel de imbricare.
Să avem următoarea formulare a problemei: avem un director de mărfuri cu o structură ierarhică de acest tip:
Bunuri
- Aparate
- Frigidere
- Cu trei camere
- Cameră dublă
- O singură cameră
-
Mașini de spălat
- Verticală
- Față
- Clasic
- Îngust
- Tehnologia calculatoarelor
....
Această structură a directorului de categorii de produse poate avea ramuri de diferite adâncimi. și, de asemenea, crește în timp. Sarcina noastră este să asigurăm selecția tuturor elementelor finite din director cu „extinderea numelui complet”, începând de la orice nod. De exemplu, dacă selectăm nodul „Mașini de spălat”, atunci trebuie să obținem următoarele categorii:
Mașini de spălat - Verticale
Mașini de spălat - Față Classic
Mașini de spălat - Față îngustă
Să definim structura tabelului pentru stocarea informațiilor din directorul de produse. Folosim o schemă simplificată pentru a organiza arborele într-un singur tabel:
CREATE TABLE GoodsTree
(ID_GOOD INTEGER NU NUL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
constrângere pkGooci cheie primară (ID_GOOD));
Creăm un tabel GoodsTree, în care există doar 3 câmpuri: ID_GOOD - identificatorul inteligent al categoriei, ID_PARENT_GOOD - identificatorul companiei-mamă pentru această categorie și GOOD_NAME - numele categoriei. Pentru a asigura integritatea datelor din acest tabel, vom impune o constrângere de cheie externă acestui tabel:
ALTER TABLE GoodsTree
ADĂUGAȚI CONSTRAINTĂ FK_goodstree
CHEIE STRĂINĂ (ID_PARENT_GOOD)
REFERINȚE GOODSTPEE (ID__GOOD)
Tabelul se referă la el însuși și această cheie externă ține evidența acesteia. astfel încât tabelul să nu conțină referințe la părinți inexistenți și, de asemenea, să prevină încercările de ștergere a categoriilor de produse care au copii.
Să introducem următoarele date în tabelul nostru:
| ID_BUN 1
|
ID_PARENT_BUN 0
|
BUN NUME BUNURI |
Acum că avem un loc pentru a stoca datele, putem începe să creăm o procedură stocată care va scoate toate categoriile de produse „finale” într-o formă „extinsă” - de exemplu, pentru categoria „Trei camere”, categoria completă. denumirea ar fi „Electrocasnice Frigidere” Cu trei camere”.
Procedurile stocate care procesează structurile arborescente au propria terminologie. Fiecare element al arborelui este numit nod; iar relația dintre nodurile care se referă unul la altul se numește relație părinte-copil. Nodurile care se află chiar la capătul copacului și nu au copii sunt numite „frunze”.
Pentru această procedură stocată, parametrul de intrare va fi identificatorul categoriei, de la care va trebui să începem drilldown-ul. Procedura stocată va arăta astfel:
CREATE PROCEDURA GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNĂRI (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
LA FEL DE
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
ÎNCEPE
/*0organize external bucla FOR SELECTAȚI pe descendenții imediati ai produsului cu ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good, gtl.good_name
DE LA GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
ÎNCEPE
/"Verificați folosind funcția EXISTS, care returnează TRUE dacă interogarea dintre paranteze returnează cel puțin un rând. Dacă nodul găsit cu ID_PARENT_GOOD = ID_CHILD_GOOD nu are copii, atunci este o „frunză” a arborelui și este inclus în rezultate */
DACĂ (NU EXISTĂ(
SELECTAȚI * DIN GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
APOI
ÎNCEPE
/* Treceți „frunza” copacului la rezultate */
SUSPENDA;
Sfârşit
ALTE
/* Pentru nodurile care au copii*/
ÎNCEPE
/*salvează numele nodului părinte într-o variabilă temporară */
CURR_CHILD_NAME=nume_complet_produs;
/* rulează această procedură recursiv */
PENTRU
SELECT ID_CHILD_GOOD, nume_complet_produs
DE LA GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
ÎNCEPEȚI
/*adăugați numele nodului părinte la numele copilului găsit folosind operația de concatenare a șirurilor || */
full_goods_name=CURR_CHILD_NAME| " " | Numele_complet al_marfurilor,-
SUSPENDA; /* returnează numele complet al produsului*/
Sfârşit
Sfârşit
Sfârşit
Sfârşit
Dacă executăm această procedură cu parametrul de intrare ID_GOOD2SHOW= 1, vom obține următoarele:
După cum puteți vedea, folosind o procedură stocată recursivă, am parcurs întregul arbore de categorii și am afișat numele complet al categoriilor „frunze” care se află chiar la vârfurile ramurilor.
Concluzie
Aceasta încheie analiza noastră asupra principalelor caracteristici ale limbajului procedurii stocate. Evident, este imposibil să stăpânești pe deplin dezvoltarea procedurilor stocate într-un singur capitol, dar aici am încercat să introducem și să explicăm conceptele de bază asociate procedurilor stocate. Design-urile și tehnicile descrise pentru proiectarea HP pot fi aplicate în majoritatea aplicațiilor de baze de date
Unele dintre problemele importante legate de dezvoltarea procedurilor stocate vor fi acoperite în capitolul următor - „Capacitățile avansate ale limbajului de proceduri stocate InterBase”, care este dedicat gestionării excepțiilor, rezolvării situațiilor de eroare în procedurile stocate și lucrului cu matrice.
În Microsoft SQL Server pentru a implementa și automatiza propriile algoritmi ( calculele) puteți folosi proceduri stocate, așa că astăzi vom vorbi despre cum sunt create, modificate și șterse.
Dar mai întâi, puțină teorie pentru a înțelege ce sunt procedurile stocate și de ce sunt necesare în T-SQL.
Notă! Pentru programatorii începători, recomand următoarele materiale utile pe T-SQL:
- Pentru un studiu mai detaliat Limbajul T-SQL De asemenea, recomand să citești cartea - The T-SQL Programmer's Way. Tutorial despre limbajul Transact-SQL;
- Cursuri online profesionale despre T-SQL
Ce sunt procedurile stocate în T-SQL?
Proceduri stocate– acestea sunt obiecte de bază de date care conțin un algoritm sub forma unui set de instrucțiuni SQL. Cu alte cuvinte, putem spune că procedurile stocate sunt programe din interiorul unei baze de date. Procedurile stocate sunt folosite pentru a stoca cod reutilizabil pe server, de exemplu, ați scris ceva algoritm, calcul secvențial sau instrucțiune SQL în mai mulți pași și pentru a nu executa de fiecare dată toate instrucțiunile incluse în acesta. acest algoritmÎl puteți formata ca procedură stocată. În același timp, atunci când creați o procedură SQL, serverul compilează codul și apoi, de fiecare dată când rulați această procedură SQL, serverul nu o va recompila.
Pentru a rula o procedură stocată în SQL Server, trebuie să scrieți comanda EXECUTE înaintea numelui acesteia, este de asemenea posibil să prescurtați această comandă ca EXEC. Apelarea unei proceduri stocate într-o instrucțiune SELECT, de exemplu, ca o funcție nu va mai funcționa, de exemplu. procedurile sunt lansate separat.
În procedurile stocate, spre deosebire de funcții, este deja posibilă efectuarea unor operațiuni de modificare a datelor precum: UNSERT, UPDATE, DELETE. De asemenea, puteți utiliza instrucțiuni SQL de aproape orice tip în proceduri, de exemplu, CREATE TABLE pentru a crea tabele sau EXECUTE, de exemplu. apelând la alte proceduri. Excepție fac mai multe tipuri de instrucțiuni, cum ar fi: crearea sau modificarea funcțiilor, vizualizărilor, declanșatorilor, crearea schemelor și alte câteva instrucțiuni similare, de exemplu, de asemenea, nu puteți comuta contextul conexiunii la baza de date (USE) într-o procedură stocată.
O procedură stocată poate avea parametri de intrare și parametri de ieșire, poate returna date tabulare sau nu poate returna nimic, executa doar instrucțiunile conținute în ea.
Procedurile stocate sunt foarte utile, ne ajută să automatizăm sau să simplificăm multe operațiuni, de exemplu, trebuie să generați în mod constant diverse rapoarte analitice complexe folosind tabele pivot, adică operator PIVOT. Pentru a facilita formularea interogărilor cu acest operator ( după cum știți, sintaxa lui PIVOT este destul de complexă), Puteți scrie o procedură care va genera în mod dinamic rapoarte rezumative pentru dvs., de exemplu, materialul „Dynamic PIVOT în T-SQL” oferă un exemplu de implementare a acestei caracteristici sub forma unei proceduri stocate.
Exemple de lucru cu proceduri stocate în Microsoft SQL Server
Date sursă pentru exemple
Toate exemplele de mai jos vor fi executate în Microsoft SQL Server 2016 Express. Pentru a demonstra cum funcționează procedurile stocate cu date reale, avem nevoie de aceste date, să le creăm. De exemplu, să creăm un tabel de testare și să adăugăm câteva înregistrări la el, să spunem că va fi un tabel care va conține o listă de produse cu prețurile lor.
Instrucțiune pentru crearea unui tabel CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Instrucțiune pentru adăugarea datelor INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , „Mouse”, 100), (1, „Keyboard”, 200), (2, „Phone”, 400) GO --Selectați interogarea SELECT * FROM TestTable
Avem datele, acum să trecem la crearea procedurilor stocate.
Crearea unei proceduri stocate în T-SQL - instrucțiunea CREATE PROCEDURE
Procedurile stocate sunt create folosind o instrucțiune CREAȚI PROCEDURA, după această instrucțiune trebuie să scrieți numele procedurii dvs., apoi, dacă este necesar, să definiți parametrii de intrare și de ieșire în paranteze. După aceasta, scrieți cuvântul cheie AS și deschideți blocul de instrucțiuni cu cuvântul cheie BEGIN, închideți acest bloc cu cuvântul END. În interiorul acestui bloc, scrii toate instrucțiunile care implementează algoritmul tău sau un fel de calcul secvenţial, cu alte cuvinte, programezi în T-SQL.
De exemplu, să scriem o procedură stocată care va adăuga intrare nouă, adică produs nou la masa noastră de testare. Pentru a face acest lucru, vom defini trei parametri de intrare: @CategoryId – identificatorul categoriei de produse, @ProductName – numele produsului și @Price – prețul produsului, acest parametru Vom avea unul optional, i.e. nu va fi necesar să-l treci la procedură ( de exemplu, nu știm încă prețul), în acest scop vom seta o valoare implicită în definiția acesteia. Acești parametri se află în corpul procedurii, de exemplu. în blocul BEGIN...END pot fi folosite, la fel ca variabilele obișnuite ( După cum știți, variabilele sunt notate cu semnul @). Dacă trebuie să specificați parametrii de ieșire, atunci după numele parametrului indicați cuvântul cheie OUTPUT ( sau OUT pe scurt).
În blocul BEGIN...END vom scrie o instrucțiune de adăugare a datelor, precum și o instrucțiune SELECT la finalul procedurii, astfel încât procedura stocată să ne returneze date tabelare despre produsele din categoria specificată, luând în considerare contează produsul nou, tocmai adăugat. Tot în această procedură stocată am adăugat procesarea parametrului de intrare și anume ștergerea spatii suplimentare la inceput si la sfarsit șir de text pentru a exclude situațiile în care au fost introduse accidental mai multe spații.
Iată codul pentru această procedură ( Am comentat si eu).
Creați o procedură CREATE PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Procesarea parametrilor de intrare --Eliminarea spațiilor suplimentare la început și la sfârșitul liniei de text SET @ProductName = LTRIM(RTRIM(@ProductName)); --Adăugați o înregistrare nouă INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Return data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Rularea unei proceduri stocate în comanda T-SQL - EXECUTE
Puteți rula o procedură stocată, așa cum am menționat deja, folosind comanda EXECUTE sau EXEC. Parametrii primiți sunt trecuți procedurilor prin simpla listă a acestora și specificând valorile corespunzătoare după numele procedurii ( pentru parametrii de ieșire trebuie să specificați și comanda OUTPUT). Cu toate acestea, este posibil ca numele parametrilor să nu fie specificate, dar în acest caz este necesar să se urmeze succesiunea de specificare a valorilor, adică. specificați valorile în ordinea în care sunt definiți parametrii de intrare ( acest lucru se aplică și parametrilor de ieșire).
Parametrii care au valori implicite nu trebuie să fie specificați, aceștia sunt așa-numiții parametri opționali.
Iată câteva moduri diferite, dar echivalente, de a rula procedurile stocate, în special procedura noastră de testare.
1. Apelați procedura fără a specifica prețul EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Apelați procedura indicând prețul EXEC TestProcedure @CategoryId = 1, @ProductName = „Testați produsul 2”, @Price = 300 --3. Apelați procedura fără a specifica numele parametrilor EXEC TestProcedure 1, "Test product 3", 400
Schimbarea unei proceduri stocate în instrucțiunea T-SQL - ALTER PROCEDURE
Puteți face modificări algoritmului procedurii folosind instrucțiunile PROCEDURA DE ALTERARE. Cu alte cuvinte, pentru a schimba o procedură deja existentă, trebuie doar să scrieți ALTER PROCEDURE în loc de CREATE PROCEDURE și să modificați orice altceva după cum este necesar.
Să presupunem că trebuie să facem modificări procedurii noastre de testare, să spunem parametrul @Price, de exemplu. preț, îl vom face obligatoriu, pentru aceasta vom elimina valoarea implicită și, de asemenea, ne imaginăm că nu mai trebuie să obținem setul de date rezultat, pentru aceasta vom elimina pur și simplu instrucțiunea SELECT din procedura stocată.
Schimbăm procedura ALTER PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Procesarea parametrilor de intrare --Eliminarea spațiilor suplimentare la început și sfârșitul liniilor de text SET @ProductName = LTRIM(RTRIM(@ProductName)); --Adăugați o înregistrare nouă INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO
Ștergerea unei proceduri stocate în instrucțiunea T-SQL - DROP PROCEDURE
Dacă este necesar, puteți șterge procedura stocată, aceasta se face folosind instrucțiunile PROCEDURA DE RĂDARE.
De exemplu, să ștergem procedura de testare creată de noi.
DROP PROCEDURE TestProcedure
La ștergerea procedurilor stocate, merită să ne amintim că, dacă procedura este referită prin alte proceduri sau instrucțiuni SQL, după ce va fi ștearsă, acestea vor eșua cu o eroare, deoarece procedura la care se referă nu mai există.
Atât am, sper că materialul ți-a fost interesant și util, pa!
In aceea manual vei invata cum creați și ștergeți proceduri în SQL Server(Transact-SQL) cu sintaxă și exemple.
Descriere
În SQL Server, o procedură este un program stocat căruia îi puteți transmite parametri. Nu returnează o valoare ca o funcție. Cu toate acestea, poate returna o stare de succes/eșec la procedura care a numit-o.
Creați o procedură
Puteți crea propriile proceduri stocate în SQL Server (Transact-SQL). Să aruncăm o privire mai atentă.
Sintaxă
Sintaxa procedurilor în SQL Server (Transact-SQL):
CREATE ( PROCEDURE | PROC ) procedure_name
[ @tip de date parametru
[ VARIABIL ] [ = implicit ] [ OUT | IEȘIRE | NUMAI CITIRE]
, @parametru tip de date
[ VARIABIL ] [ = implicit ] [ OUT | IEȘIRE | NUMAI CITIT ] ]
[ CU ( CRIPTARE | RECOMPILE | EXECUTE AS Clauza ) ]
[PENTRU REPLICAȚIE]
LA FEL DE
ÎNCEPE
executabil_section
SFÂRŞIT;
Parametri sau Argumente
schema_name este numele schemei căreia îi aparține procedura stocată.
procedure_name este numele căruia i se atribuie această procedură în SQL Server.
@parameter - unul sau mai mulți parametri sunt trecuți procedurii.
type_schema_name este schema care deține tipul de date, dacă este cazul.
Datatype — tipul de date pentru @parameter .
VARYING - Setați pentru parametrii cursorului când setul de rezultate este un parametru de ieșire.
default este valoarea implicită de atribuit parametrului @parameter.
OUT - Aceasta înseamnă că @parameter este parametrul de ieșire.
OUTPUT - Aceasta înseamnă că @parameter este parametrul de ieșire.
READONLY - Aceasta înseamnă că @parameter nu poate fi suprascris de procedura stocată.
CRIPTARE - Aceasta înseamnă că sursa procedurii stocate nu va fi stocată ca text simplu în vizualizările de sistem SQL Server.
RECOMPILE - Aceasta înseamnă că planul de interogare nu va fi stocat în cache pentru această procedură stocată.
EXECUTE AS - Setează contextul de securitate pentru executarea procedurii stocate.
PENTRU REPLICAȚIE - Aceasta înseamnă că procedura stocată este executată numai în timpul replicării.
Exemplu
Să ne uităm la un exemplu de creare a unei proceduri stocate în SQL Server (Transact-SQL).
Mai jos este un exemplu simplu de procedură:
Transact-SQL
CREATE PROCEDURE FindSite @site_name VARCHAR(50) OUT AS BEGIN DECLARE @site_id INT; SET @site_id = 8; DACA @site_id< 10 SET @site_name = "yandex.com"; ELSE SET @site_name = "google.com"; END;
CREATE PROCEDURA FindSite @site_name VARCHAR(50) OUT ÎNCEPE DECLARE @site_id INT ; SET @site_id = 8 ; IF@site_id< 10 SET @ site_name = "yandex.com" ; ALTE SET @site_name = "google.com" ; SFÂRŞIT ; |
Această procedură se numește FindSite. Are un parametru numit @site_name care este un parametru de ieșire care este actualizat pe baza variabilei @site_id.
Apoi, puteți face referire la o nouă procedură stocată numită FindSite, astfel.
MySQL 5 are multe caracteristici noi, dintre care una dintre cele mai semnificative este crearea de proceduri stocate. În acest tutorial, voi vorbi despre ce sunt acestea și despre cum vă pot face viața mai ușoară.
Introducere
O procedură stocată este o modalitate de a încapsula acțiuni repetitive. Procedurile stocate pot declara variabile, pot manipula fluxul de date și pot folosi alte tehnici de programare.
Motivul creării lor este clar și este confirmat de utilizarea frecventă. Pe de altă parte, dacă vorbești cu cei care lucrează cu ei neregulat, părerile se vor împărți în două flancuri complet opuse. Nu uita asta.
In spate
- Partajarea logicii cu alte aplicații. Procedurile stocate încapsulează funcționalitatea; aceasta oferă conectivitate pentru accesul și gestionarea datelor în diferite aplicații.
- Izolarea utilizatorilor de tabelele bazei de date. Acest lucru vă permite să acordați acces la procedurile stocate, dar nu și la datele tabelului în sine.
- Oferă un mecanism de protecție. Conform punctului anterior, dacă puteți accesa date numai prin proceduri stocate, nimeni altcineva nu vă poate șterge datele prin comanda SQL DELETE.
- Performanță îmbunătățită ca urmare a reducerii trafic de rețea. Folosind proceduri stocate, mai multe interogări pot fi combinate.
Împotriva
- Încărcare crescută pe serverul bazei de date datorită faptului că cea mai mare parte a lucrării este efectuată pe partea de server și mai puțin pe partea clientului.
- Va trebui să înveți multe. Va trebui să învățați sintaxa expresiei MySQL pentru a vă scrie procedurile stocate.
- Dublați logica aplicației în două locuri: codul serverului și codul pentru procedurile stocate, complicând astfel procesul de manipulare a datelor.
- Migrarea de la un SGBD la altul (DB2, SQL Server etc.) poate duce la probleme.
Instrumentul cu care lucrez se numește MySQL Query Browser, care este destul de standard pentru interacțiunea cu bazele de date. Instrument Linie de comanda MySQL este o altă alegere excelentă. Motivul pentru care vă spun acest lucru este că phpMyAdmin favorit al tuturor nu acceptă rularea procedurilor stocate.
Apropo, folosesc o structură de bază de tabel pentru a vă facilita înțelegerea acestui subiect. Vorbesc despre procedurile stocate și sunt suficient de complexe pentru a necesita aprofundarea în structura greoaie a tabelului.
Pasul 1: Plasați un limitator
Un delimitator este un caracter sau șir de caractere care este folosit pentru a indica clientului MySQL că ați terminat de scris expresia SQL. De secole, punctul și virgulă a fost delimitătorul. Cu toate acestea, pot apărea probleme deoarece pot exista mai multe expresii într-o procedură stocată, fiecare dintre acestea trebuie să se termine cu punct și virgulă. În acest tutorial folosesc șirul „//” ca delimitator.
Pasul 2: Cum să lucrați cu procedurile stocate
Crearea unei proceduri stocate
DELIMITER // CREATE PROCEDURE `p2` () LIMBA SQL DETERMINISTIC SQL DEFINITOR DE SECURITATE COMMENT "O procedură" BEGIN SELECT "Hello World!"; SFÂRŞIT//
Prima parte a codului creează o procedură stocată. Următorul conține parametri opționali. Apoi urmează numele și, în sfârșit, corpul procedurii în sine.
Numele procedurilor stocate țin cont de majuscule și minuscule. De asemenea, nu puteți crea mai multe proceduri cu același nume. Nu pot exista expresii în interiorul unei proceduri stocate care modifică baza de date în sine.
4 caracteristici ale unei proceduri stocate:
- Limbă: în scopuri de portabilitate, implicit este SQL.
- Determinist: dacă procedura returnează întotdeauna același rezultat și ia aceiași parametri de intrare. Aceasta este pentru procesul de replicare și înregistrare. Valoarea implicită NU este DETERMINISTICĂ.
- Securitate SQL: drepturile utilizatorului sunt verificate în timpul apelului. INVOKER este utilizatorul care apelează procedura stocată. DEFINER este „creatorul” procedurii. Valoarea implicită este DEFINER.
- Comentariu: în scopuri de documentare, valoarea implicită este „”
Apelarea unei proceduri stocate
Pentru a apela o procedură stocată, trebuie să tastați cuvântul cheie CALL, urmat de numele procedurii, urmat de parametrii (variabile sau valori) din paranteze. Sunt necesare paranteze.
CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);
Modificarea unei proceduri stocate
MySQL are o instrucțiune ALTER PROCEDURE pentru modificarea procedurilor, dar este potrivită doar pentru modificarea anumitor caracteristici. Dacă trebuie să modificați parametrii sau corpul unei proceduri, ar trebui să o ștergeți și să o recreați.
Eliminarea unei proceduri stocate
PROCEDURA DE REDARE DACĂ EXISTĂ p2;
Acest comandă simplă. Instrucțiunea IF EXISTS prinde o eroare dacă o astfel de procedură nu există.
Pasul 3: Opțiuni
Să vedem cum putem trece parametrii unei proceduri stocate.
- CREATE PROCEDURE proc1(): lista goala parametrii
- CREATE PROCEDURE proc1 (IN varname DATA-TYPE): un parametru de intrare. Cuvântul IN este opțional deoarece parametrii impliciti sunt IN (in).
- CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): a fost returnat un parametru.
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): un parametru, atât de intrare, cât și de returnat.
Desigur, puteți specifica mai mulți parametri de diferite tipuri.
Exemplu de parametru IN
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; SFÂRŞIT//
Exemplu de parametru OUT
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Acesta este un test"; SFÂRŞIT //
Exemplu de parametru INOUT
DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; SFÂRŞIT //
Pasul 4: Variabile
Acum vă voi învăța cum să creați variabile și să le stocați în cadrul procedurilor. Trebuie să le declarați în mod explicit la începutul blocului BEGIN/END, împreună cu tipurile lor de date. Odată ce ați declarat o variabilă, o puteți utiliza în același mod ca variabilele de sesiune, literalele sau numele de coloane.
Sintaxa declarației variabilelor arată astfel:
DECLARE varname DATA-TYPE DEFAULT valoare implicită;
Să declarăm câteva variabile:
DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE astăzi TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Lucrul cu variabile
Odată ce ați declarat o variabilă, puteți seta valoarea acesteia folosind comenzile SET sau SELECT:
DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE astăzi TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = „Sunt un șir”; SELECTARE CONCAT(str,paramstr), azi DIN tabelul2 WHERE b >=5; SFÂRŞIT //
Pasul 5: Structuri de control al firelor
MySQL acceptă constructele IF, CASE, ITERATE, LEAVE LOOP, WHILE și REPEAT pentru a controla firele într-o procedură stocată. Vom analiza cum să folosiți IF, CASE și WHILE, deoarece sunt cele mai frecvent utilizate.
IF design
Folosind constructul IF, putem efectua sarcini care conțin condiții:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabila1 = param1 + 1; DACA variabila1 = 0 THEN SELECT variabila1; ENDIF; IF param1 = 0 THEN SELECT "Valoarea parametrului = 0"; ELSE SELECT "Valoarea parametrului<>0"; END IF; END //
Design CASE
CASE este o altă metodă de testare a condițiilor și de selectare a unei soluții adecvate. Aceasta este o modalitate excelentă de a înlocui multe construcții IF. Construcția poate fi descrisă în două moduri, oferind flexibilitate în gestionarea mai multor expresii condiționate.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabila1 = param1 + 1; CAZ variabila1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variabila1); ELSE INSERT INTO table1 VALUES (99); CAPITARE CAZ; SFÂRŞIT //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabila1 = param1 + 1; CAZUL CÂND variabila1 = 0 ATUNCI INSERT INTO table1 VALORI (param1); WHEN variabila1 = 1 ATUNCI INSERT INTO table1 VALUES (variabila1); ELSE INSERT INTO table1 VALUES (99); TERMINAL CAZ; SFÂRŞIT //
În timp ce design
Din punct de vedere tehnic, există trei tipuri de bucle: bucla WHILE, bucla LOOP și bucla REPEAT. Puteți, de asemenea, să bucleți folosind tehnica de programare Darth Vader: instrucțiuni GOTO. Iată un exemplu de buclă:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variabila1, variabila2 INT; SET variabila1 = 0; WHILE variabila1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Pasul 6: Cursore
Cursoarele sunt folosite pentru a parcurge setul de rânduri returnate de o interogare și pentru a procesa fiecare rând.
MySQL acceptă cursoare în procedurile stocate. Iată o scurtă sintaxă pentru crearea și utilizarea unui cursor.
DECLARE nume-cursor CURSOR FOR SELECT ...; /*Declararea unui cursor și completarea acestuia */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Ce să faci când nu mai există înregistrări*/ OPEN cursor-name; /*Deschide cursorul*/ FETCH cursor-name INTO variabila [, variabila]; /*Atribuiți o valoare unei variabile egală cu valoarea curentă a coloanei*/ CLOSE cursor-name; /*Închide cursorul*/
În acest exemplu vom efectua câteva operații simple folosind un cursor:
DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUARE HANDLER PENTRU SET NU GĂSIT b = 1; DESCHIS cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; DACA b = 0 ATUNCI SETATI c = c + a; ENDIF; SFÂRȘIT CÂND; ÎNCHIS cur1; SET param1 = c; SFÂRŞIT //
Cursoarele au trei proprietăți pe care trebuie să le înțelegeți pentru a evita obținerea de rezultate neașteptate:
- Nesensibil: un cursor care se deschide o dată nu va reflecta modificările din tabel care apar ulterior. În realitate, MySQL nu garantează că cursorul va fi actualizat, așa că nu vă bazați pe el.
- Numai citire: cursoarele nu pot fi modificate.
- Fără derulare înapoi: cursorul se poate mișca într-o singură direcție - înainte, nu veți putea sări peste linii fără a le selecta.
Concluzie
În acest tutorial, v-am prezentat elementele de bază ale lucrului cu procedurile stocate și unele dintre proprietățile specifice asociate acestora. Desigur, va trebui să vă aprofundați cunoștințele în domenii precum securitatea, expresiile SQL și optimizarea înainte de a deveni un adevărat guru al procedurilor MySQL.
Ar trebui să calculați beneficiile utilizării procedurilor stocate în aplicația dvs. specifică și apoi să creați numai procedurile necesare. În general, folosesc proceduri; În opinia mea, merită implementate în proiecte datorită securității, întreținerii codului și designului general. De asemenea, rețineți că procedurile MySQL sunt încă în lucru. Așteptați-vă îmbunătățiri în ceea ce privește funcționalitatea și îmbunătățirile. Vă rugăm să nu ezitați să vă împărtășiți opiniile.
Se încarcă...