O procedură stocată este structura programului cea mai des utilizată în baze de date, care este un script special proiectat (sau mai degrabă un pachet) care este stocat într-o bază de date, mai degrabă decât într-un fișier separat. Procedurile stocate diferă de scripturi prin faptul că permit utilizarea parametrilor de intrare și ieșire, precum și valori returnate care nu pot fi efectiv utilizate într-un scenariu normal.
O procedură stocată este pur și simplu un nume asociat cu codul de program T-SQL care este stocat și executat pe server. Poate conține aproape orice structură sau comandă, execuția cărora este acceptată în SQL Server. Procedurile pot fi folosite pentru a schimba datele, pentru a returna valorile scalare sau pentru seturile de rezultate întregi. Procedurile stocate sunt interfața principală pe care aplicațiile ar trebui să o utilizeze pentru a accesa orice date din baze de date. Procedurile stocate permit nu numai controlul accesului la baza de date, ci și izolarea codului bazei de date pentru a simplifica întreținerea.
Ca programe server-side, procedurile stocate au mai multe avantaje.
• Procedurile stocate sunt stocate într-o formă compilată, astfel încât acestea să ruleze mai repede decât pachetele sau interogările.
• Efectuarea de procesare a datelor pe server, mai degrabă decât pe stația de lucru, reduce semnificativ încărcarea rețelei locale.
• Procedurile stocate sunt modulare, astfel încât acestea sunt ușor de implementat și modificate. Dacă aplicația client solicită procedura stocată pentru a efectua o anumită operație, modificarea procedurii într-un singur loc afectează execuția acesteia pentru toți utilizatorii.
• Procedurile stocate pot fi considerate o componentă importantă a sistemului de securitate al bazei de date. Dacă toți clienții accesează date utilizând proceduri stocate, accesul direct la tabele poate fi negat și toate acțiunile utilizatorilor vor fi controlate. Mai important, procedurile stocate ascund structura bazei de date de la utilizator și îi permit să efectueze numai operațiile care sunt programate în procedura stocată.
Gestionarea procedurilor stocate
Procedurile stocate sunt controlate de instrucțiunile din limbajul de definire a datelor CREATE, ALTER și DROP (DDL).
Sintaxa generală a codului T-SQL pentru crearea unei proceduri stocate este:
CREATE PROC | PROCEDURĂ
[ <@parameter>
[BEGIN]
Structura acestei instrucțiuni corespunde sintaxei de bază CREATE
Fiecare procedură trebuie să aibă un nume unic în cadrul bazei de date (procedure_name) care să respecte regulile pentru identificatorii de obiect.
Procedurile pot avea orice număr de parametri de intrare (@parametr) ai tipului de date dat (tip de date), care sunt folosiți în cadrul procedurii ca variabile locale. La executarea procedurii, trebuie transmise valori reale pentru fiecare dintre parametrii formali declarați. Sau, pentru parametrul de intrare, poate fi definită o valoare implicită, care trebuie să fie constantă sau egală cu NULL. În acest caz, procedura poate fi efectuată fără a se specifica valoarea argumentului corespunzător. Utilizarea parametrilor de intrare este opțională.
De asemenea, puteți specifica parametrii de ieșire (etichetați OUTPUT) care permit procedurii memorate să returneze una sau mai multe valori scalare în subrutina din care a fost apelată. Când creați proceduri, puteți seta trei parametri. Când creați o procedură cu parametrul ENCRYPTION, SQL Server criptează definiția procedurii. Când specificați parametrul RECOMPILE, SQL Server recompine procedeul stocat de fiecare dată când este rulat. Parametrul EXECUTE AS specifică contextul de securitate al procedurii.
La sfârșitul definiției procedurii stocate, corpul procedurii (sql_statements) ca un cod dintr-una sau mai multe instrucțiuni T-SQL ar trebui imediat urmat de cuvântul cheie AS.
Instrucțiunea DROP șterge procedura stocată din baza de date. Instrucțiunea ALTER modifică conținutul întregii proceduri stocate. Pentru a face modificări, este preferabil să utilizați instrucțiunea ALTER, mai degrabă decât o combinație de ștergere și de a crea instrucțiuni, deoarece ultima metodă elimină toate permisiunile.
Exemplu de procedură memorată fără parametri
CREATE PROCEDURE spr_getOrders
SELECT IdOrd, IdCust, OrdDate
Pentru a testa noua procedură, deschideți o nouă interogare SQL Server și executați următorul cod.
Comanda EXECUTE sau abreviată EXEC execută procedura stocată specificată.
Aplicarea parametrilor de intrare
Procedura memorată furnizează anumite opțiuni procedurale (și, în cazul în care se aplică în infrastructura .NET, astfel de oportunități sunt foarte semnificative), precum și furnizarea de performanțe îmbunătățite, dar în cele mai multe cazuri, procedura stocată nu permite mult, în cazul în care nu a oferit o oportunitate de a da unele date care indică cu privire la acțiunile care ar trebui efectuate cu ajutorul acestuia. În special, principala problemă cu procedura precedentă stocată (spr_getOrders) este comportamentul său static. Dacă utilizatorii au nevoie de informații despre comenzi pentru o altă perioadă de timp, atunci această procedură nu le va ajuta. Prin urmare, este necesar să se prevadă posibilitatea transferării către acesta a parametrilor de intrare adecvați care să permită modificarea dinamică a perioadei de eșantionare.
numele parametrului [AS] datatype [= default | NULL]
În următorul exemplu, procedura stocată spr_getOrders este completată cu doi parametri de intrare care permit să se specifice în mod explicit perioada de eșantionare.
PROCEDURA ALTER [dbo]. [Spr_getOrders]
@Description = Produs N'New ',
@IdProd = @IdProd OUTPUT
SELECT @ IdProd ca N '@ IdProd'
Rețineți că atunci când se solicită procedura, valorile nu sunt transferate pentru toți parametrii. Opțiunea InStock este opțională, deoarece are o valoare implicită de zero, care va fi utilizată dacă nu este furnizată în mod explicit nicio altă valoare. În acest caz, dacă apelul la procedura stocată și transferul valorilor au avut loc folosind parametrii poziționali, ar trebui să umplem fiecare poziție din lista de parametri, cel puțin înainte ca ultimul parametru pentru care ar trebui furnizată valoarea să fi fost întâlnit.
Confirmarea unei închideri reușite sau nereușite utilizând valori returnate. Utilizați comanda RETURN.
Orice procedură stocată care este chemată să execute returnează o valoare, indiferent dacă o valoare de retur este furnizată în ea sau nu. În mod implicit, după o procedură SQL Server de succes, SQL Server returnează automat o valoare de zero.
Pentru a transfera o valoare de retur din procedura stocată înapoi la codul de apel, este suficient să aplicați operatorul RETURN:
RETURN [<Целое число>]
Rețineți că valoarea returnată trebuie să fie un număr întreg.
Valorile returnate sunt destinate exclusiv pentru a indica finalizarea sau nereusita a unei proceduri stocate și chiar să ne permită să indicăm mărimea sau natura succesului sau a eșecului. Utilizarea valorii returnate pentru a returna date reale, cum ar fi valoarea de identificare sau datele referitoare la numărul de rânduri afectate de procedura stocată, este considerată o practică de programare inacceptabilă. Valoarea returnată de 0 indică executarea cu succes a procedurii și este setată implicit. Microsoft a rezervat valori de la -99 la -1 pentru uz intern. Pentru ca dezvoltatorii să returneze o stare de eroare, este recomandat ca utilizatorul să utilizeze -100 sau mai puțin.
Una dintre cele mai importante caracteristici ale instrucțiunii RETURN este că executarea acesteia conduce la terminarea necondiționată a operației și la ieșirea din procedura stocată. Aceasta înseamnă că, indiferent de locația instrucțiunii RETURN din codul procedurii stocate, după executare nu va fi executată nicio linie de cod. Închiderea necondiționată înseamnă că acțiunea furnizată de operatorul RETURN se efectuează indiferent de locul în care se găsește în cod. Pe de altă parte, este posibil să existe mai multe instrucțiuni RETURN în codul procedurii stocate, iar execuția acestor operatori apare numai dacă structura obișnuită de control al execuției codului duce la aceasta.
În exemplul anterior, când ați încercat să adăugați informații despre un produs nou cu un nume duplicat în tabelul Produs, ar putea apărea o eroare, deoarece constrângerea de unicitate este organizată de numele produsului. Extinem procedura stocată spr_addProduct, oferind o verificare preliminară pentru prezența produsului specificat în baza de date și o indicație a succesului operației prin parametrul de ieșire.
PROCEDURA ALTER [dbo]. [Spr_addProduct]
Sarcina pentru munca independentă: creați proceduri stocate care pun în aplicare următoarele acțiuni:
• Returnați o listă a tuturor comenzilor care conțin produsul specificat (de IdProd).
• Determinați numărul de clienți care nu au o singură comandă. Rezultatul trebuie returnat prin parametrul de ieșire.
• Ștergeți informații despre un anumit client din baza de date (de IdCust). Dacă există înregistrări asociate (comenzi) cu acest client, ștergerea trebuie anulată. Valoarea returnată trebuie să determine succesul operației.
Setați ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
PROCEDURA ALTER [dbo]. [Spr_getOrder]
SELECTați coduri, coduri, categorii, marca, preț
Articole similare
-
Cum se returnează o valoare dintr-o procedură stocată ms sql
-
Cât timp pot stoca tortul - baza de date a termenilor de depozitare
Trimiteți-le prietenilor: