Proceduri stocate SQL: Crearea și utilizarea

Procedurile stocate SQL sunt un modul de program executabil care poate fi stocat într-un bază de date în formă de diverse obiecte. Cu alte cuvinte, este un obiect care conține instrucțiuni SQL. Aceste proceduri stocate pot fi executate în clientul aplicației pentru a obține performanțe bune. În plus, astfel de obiecte sunt adesea numite din alte scenarii sau chiar din alte secțiuni.

introducere

Mulți cred că sunt asemănători cu procedurile diferitelor limbi de programare la nivel înalt (respectiv, cu excepția MS SQL). Poate că așa este. Aceștia au parametri asemănători, pot produce valori similare. În plus, în unele cazuri sunt în contact. De exemplu, acestea sunt combinate cu bazele de date DDL și DML, precum și cu funcțiile utilizatorilor (cod nume - UDF).

De fapt, procedurile stocate SQL au o gamă largă de avantaje care le diferențiază de procesele similare. Securitatea, variabilitatea programării, productivitatea - toate acestea atrage utilizatorii care lucrează cu bazele de date, din ce în ce mai mult. Vârful popularității procedurilor a avut loc în perioada 2005-2010, când a fost lansat programul de la Microsoft, numit SQL Server Management Studio. Cu ajutorul său, lucrul cu bazele de date a devenit mult mai ușor, mai practic și mai convenabil. Din an în an, cum ar fi metoda de transfer de informații câștiga popularitate în rândul programatorilor. azi MS SQL Server este un program absolut familiar, care pentru utilizatorii care "comunică" cu bazele de date, a fost la egalitate cu "Excel".

Atunci când se solicită o procedură, aceasta este procesată instant de către server în sine, fără procese inutile și intervenția utilizatorului. După aceasta, puteți efectua orice acțiune cu informații: ștergere, executare, modificare. Pentru toate acestea este responsabilitatea operatorului DDL, care singur efectuează cea mai complexă procesare a obiectelor. Și toate acestea se întâmplă foarte repede, iar serverul nu este de fapt încărcat. Această viteză și performanță vă permit să transferați foarte rapid cantități mari de informații de la utilizator la server și invers.

Pentru a pune în aplicare această tehnologie de lucru cu informații, există mai multe limbi de programare. Acestea includ, de exemplu, PL / SQL din baze de date Oracle, PSQL în sistemele InterBase și Firebird, precum și clasicul "Microsoft" Transact-SQL. Toate acestea sunt concepute pentru a crea și executa proceduri stocate, ceea ce ne permite să folosim propriii algoritmi în manipulatorii de baze de date mari. Este necesar și să se asigure că cei care efectuează gestionarea acestor informații, poate proteja toate obiectele de la terțe părți neautorizate și, prin urmare, crearea, modificarea sau ștergerea anumitor date.

productivitate

Aceste obiecte baze de date pot fi programate în diverse moduri. Acest lucru permite utilizatorilor să aleagă tipul de metodă folosită, care va fi cea mai potrivită, ceea ce economisește timp și efort. În plus, procedeul în sine este procesat, ceea ce evită timpul mare petrecut pentru schimbul între server și utilizator. De asemenea, modulul poate fi reprogramat și schimbat în direcția dorită în orice moment. Mai ales demn de remarcat viteza cu care are loc lansarea procedurii memorate SQL: procesul este mai rapid alt similar cu acesta, făcându-l un convenabil și versatil.

siguranță

Acest tip de prelucrare a informațiilor diferă de procesele similare prin faptul că garantează o securitate sporită. Acest lucru este dat de faptul că accesul altor utilizatori la proceduri poate fi exclus în întregime și complet. Acest lucru va permite administratorului să desfășoare operațiuni cu aceștia în mod independent, fără teama de a intercepta informații sau de a accesa neautorizat baza de date.

Transfer de date

Relația dintre procedeul stocat SQL și aplicația client este utilizarea parametrilor și a valorilor returnate. Acesta din urmă nu are nevoie să transfere date în procedura stocată, cu toate acestea această informație (în principal la cererea utilizatorului) și procesată pentru SQL. Procedura Odată stocată a terminat treaba, acesta trimite pachetele de date înapoi (dar, din nou, dacă se dorește), la cererea de asteptare folosind o varietate de metode prin care pot fi puse în aplicare ca un apel la o procedură SQL stocată și retur, de exemplu:

- transfer de date utilizând parametrul Output;

- transfer de date utilizând o declarație de returnare;

- transfer de date utilizând operatorul de selecție.

Acum, să vedem cum arată acest proces din interior.

1. Creați o procedură stocată EXEC în SQL

Puteți crea o procedură în MS SQL (Managment Studio). După crearea procedurii, aceasta va fi transferată la un nod de bază de date programabil, în care procedura de creare este efectuată de operator. Pentru a executa, procedurile stocate SQL utilizează un proces EXEC care conține numele obiectului în sine.

Când se creează o procedură, apare mai întâi numele, după care se produc unul sau mai mulți parametri alocați. Parametrii pot fi opționali. După ce parametrul (parametrii), adică corpul procedurii, sunt scrise, trebuie să efectuați unele operații necesare.

Problema este că organismul poate avea variabile locale situate în el, iar aceste variabile sunt locale și în raport cu procedurile. Cu alte cuvinte, acestea pot fi văzute doar în interiorul corpului procedurii Microsoft SQL Server. Procedurile stocate sunt apoi considerate locale.

Deci, pentru a crea o procedură, avem nevoie de numele procedurii și de cel puțin un parametru ca și corpul procedurii. Rețineți că o opțiune excelentă în acest caz este crearea și executarea unei proceduri cu numele schemei din clasificator.

Organismul de procedură poate avea orice fel Instrucțiuni SQL, cum ar fi crearea unui tabel, introducerea unuia sau mai multor rânduri de tabele, setarea tipului și naturii bazei de date și așa mai departe. Cu toate acestea, corpul procedurii restricționează executarea anumitor operațiuni în cadrul acesteia. Unele dintre limitările importante sunt enumerate mai jos:

- organismul nu trebuie să creeze altă procedură stocată;

- Organismul nu trebuie să creeze o imagine falsă a obiectului;

- Organismul nu ar trebui să creeze niciun declanșator.

2. Setarea unei variabile în corpul procedurii

Puteți face variabilele locale la procedura organismului și apoi acestea vor fi localizate exclusiv în interiorul corpului procedurii. O bună practică este de a crea variabile la începutul corpului procedurii stocate. Dar, de asemenea, puteți seta variabile oriunde în corpul acestui obiect.

Uneori veți observa că mai multe variabile sunt setate într-o singură linie, iar fiecare parametru variabil este separat de virgulă. De asemenea, rețineți că variabila are prefixul @. În corpul procedurii, puteți seta o variabilă unde doriți. De exemplu, variabila @ NAME1 poate fi declarată mai aproape de sfârșitul corpului procedurii. Pentru a atribui valoarea variabilei declarate, se utilizează un set de date cu caracter personal. Spre deosebire de situația în care mai multe variabile sunt declarate într-o singură linie, în această situație se utilizează doar un set de date cu caracter personal.

Adesea, utilizatorii pun întrebarea: "Cum să atribuiți mai multe valori într-un singur operator în corpul procedurii?" Ei bine. Întrebarea este interesantă, dar este mult mai ușor de făcut decât credeți. Răspuns: utilizați perechi, cum ar fi "Selectați Var = valoare". Puteți utiliza aceste perechi, separându-le cu o virgulă.

3. Crearea unei proceduri stocate SQL

Într-o varietate de exemple, oamenii arată cum se creează o procedură memorată și se execută. Cu toate acestea, procedura poate lua astfel de parametri ca procesul de apelare va avea valori apropiate de el (dar nu întotdeauna). Dacă coincid, atunci procesele corespunzătoare încep în interiorul corpului. De exemplu, dacă creați o procedură care va lua orașul și regiunea din apelantului și a reveni datele despre cât de mult autorii se referă la orașul și regiunea corespunzătoare. Procedura va interoga tabelele autorilor bazei de date, de exemplu Pubs, pentru a efectua acest număr de autor. Pentru a obține aceste baze de date, de exemplu, Google încarcă scriptul SQL din pagina SQL2005.

În exemplul anterior, procedura are doi parametri, care în limba engleză vor fi convențional numiți @State și @City. Tipul de date corespunde tipului definit în aplicație. Organismul procedurii are variabile interne @TotalAuthors (toți autori), iar această variabilă este folosită pentru afișarea numărului lor. Apoi, apare o secțiune pentru selectarea interogării care contează. În cele din urmă, valoarea calculată este afișată în fereastra de ieșire utilizând operatorul de imprimare.

Cum se execută o procedură stocată în SQL

Există două modalități de a efectua procedura. Prima cale arată când trece parametrii, deoarece după numele procedurii este executată o listă separată prin virgulă. Să presupunem că avem două valori (ca în exemplul anterior). Aceste valori sunt colectate utilizând variabilele parametrilor de procedură @State și @City. În acest mod de transfer al parametrilor, ordinea este importantă. Această metodă se numește transferul ordinar al argumentelor. În cea de-a doua metodă, parametrii sunt deja atribuiți direct, iar în acest caz ordinea nu este importantă. Această a doua metodă este cunoscută sub numele de transferul argumentelor numite.

Procedura se poate abate într-o oarecare măsură de cea tipică. Totul este același ca în exemplul precedent, dar numai aici parametrii sunt mutați. Asta este, parametrul @City este memorat mai întâi și @State este stocat lângă valoarea implicită. Setarea implicită este de obicei separată. Procedurile stocate SQL parcurg ca parametri simpli. În acest caz, cu condiția ca parametrul "UT" să înlocuiască valoarea implicită "CA". În cea de-a doua execuție, pentru parametrul @City este trecută doar o singură valoare de argument, iar parametrul @State ia valoarea implicită "CA". Programatorii experimentați recomandă ca toate variabilele implicite să fie situate mai aproape de sfârșitul listei de parametri. În caz contrar, execuția nu este posibilă, iar apoi trebuie să lucrați cu transferul argumentelor numite, care este mai lung și mai dificil.

4. Proceduri stocate SQL Server: metode de returnare

Există trei modalități importante de trimitere a datelor către o procedură stocată numită. Acestea sunt enumerate mai jos:

- returnați valoarea procedurii stocate;

- ieșirea parametrului procedurii stocate;

- selectați una dintre procedurile memorate.

4.1 Revenirea valorilor procedurilor memorate SQL

În această tehnică, procedura atribuie o valoare unei variabile locale și o returnează. Procedura poate, de asemenea, returna direct o valoare constanta. În următorul exemplu, am creat o procedură care returnează numărul total de autori. Dacă comparați această procedură cu cea precedentă, puteți vedea că valoarea pentru tipărire este înlocuită cu cea opusă.

Acum, să vedem cum să executăm procedura și să scoatem valoarea returnată acesteia. Executarea procedurii necesită setarea variabilei și tipăririi, care se realizează după tot acest proces. Rețineți că în loc de instrucțiunea de imprimare, puteți utiliza operatorul Select, de exemplu, Selectați @RetValue și, de asemenea, OutputValue.

4.2 Ieșiți din parametrul procedurii memorate SQL

Valoarea răspunsului poate fi utilizată pentru a returna o variabilă, pe care am văzut-o în exemplul anterior. Utilizarea parametrului Output permite procedurii să trimită una sau mai multe valori variabile părții care apelează. Parametrul de ieșire este desemnat de același cuvânt cheie "Ieșire" la crearea procedurii. Dacă parametrul este specificat ca parametru de ieșire, atunci obiectul procedurii trebuie să îi atribuie o valoare. Procedurile SQL stocate, exemple de care pot fi văzute mai jos, sunt apoi returnate cu informații sumare.

În exemplul nostru, există două nume de ieșire: @TotalAuthors și @TotalNoContract. Acestea sunt specificate în lista parametrilor. Aceste variabile atribuie valori în cadrul corpului procedurii. Atunci când folosim parametrii de ieșire, apelantul poate vedea valoarea setată în interiorul corpului procedurii.

În plus, în scenariul precedent, două variabile sunt declarate pentru a vedea valorile care stabilesc procedurile memorate MS SQL Server în parametrul de ieșire. Apoi se procedează prin furnizarea valorii normale a parametrului "CA". Următorii parametri sunt afișați și, prin urmare, variabilele declarate sunt transmise în ordinea stabilită. Rețineți că atunci când treceți variabilele, cuvântul cheie de ieșire este de asemenea specificat aici. După ce procedura este reușită, valorile returnate de parametrii de ieșire sunt afișate în fereastra de mesaje.

4.3 Selectați una dintre procedurile memorate SQL

Această tehnică este utilizată pentru a returna un set de valori ca tabel de date (RecordSet) la procedura stocată în așteptare. În acest exemplu, o procedură stocată SQL cu parametrii @AuthID interoghează tabelul Autori prin filtrarea înregistrărilor returnate utilizând acest parametru @AuthId. Operatorul Select decide ce trebuie returnat procedurii stocate. Când se execută o procedură stocată, AuthId este trecut înapoi. O astfel de procedură întoarce întotdeauna numai o singură înregistrare sau deloc. Dar procedura stocată nu are restricții privind returnarea mai multor înregistrări. Este adesea posibil să se găsească exemple în care returnarea datelor utilizând parametrii selectați cu participarea variabilelor calculate are loc prin furnizarea mai multor valori sumare.

În concluzie

O procedură stocată este un modul software destul de serios care se întoarce sau transmite, și stabilește, de asemenea, variabilele necesare prin intermediul aplicației client. Deoarece procedura stocată este executată pe server în sine, schimbul de date în volume mari între server și aplicația client (pentru unele calcule) pot fi evitate. Acest lucru vă permite să reduceți încărcarea pe serverul SQL, care, desigur, intră în mâinile deținătorilor lor. Unul dintre subspecii este procedurile memorate T SQL, cu toate acestea, ele trebuie să fie studiate de cei care creează baze de date impresionante. Există, de asemenea, o cantitate mare chiar, mare de nuanțe care pot fi utile în studiul procedurilor stocate, cu toate acestea, această necesitate pentru cei care sunt de planificare pentru a face programare strânsă, inclusiv profesional.