Microsoft SQL Server 2018
Procedura stocată - acesta este cel mai des utilizat în bazele de date de structura programului, care este proiectat într-un mod special script-ul (sau, mai degrabă, pachetul), care este stocat în baza de date, mai degrabă decât într-un fișier separat. Procedurile stocate diferă de scenariile care sunt autorizate să folosească parametrii de intrare și de ieșire și pentru a reveni valori, care nu pot fi efectiv utilizate în scenariul obișnuit.
O procedură stocată este pur și simplu un nume asociat cu software-ul cod T-SQL, care este stocat și executat pe server. Acesta poate conține aproape orice desen sau instrucțiuni a cărui execuție este susținută în SQL Server. Procedurile pot fi folosite pentru a modifica datele, revenind valori scalare sau întregul set de rezultate. Procedurile stocate sunt interfața principală care ar trebui să fie utilizat de aplicații pentru a avea acces la date în bazele de date. Procedurile memorate nu poate controla numai accesul la baza de date, dar, de asemenea, pentru a izola codul bazei de date pentru a simplifica întreținerea.
Ca server de proceduri stocate de software au o serie de avantaje.
- Procedurile stocate sunt stocate într-o formă compilat, astfel încât alerga mai repede decât pachete sau cereri.
- Efectuarea de prelucrare a datelor de pe server în loc de stația de lucru, reduce semnificativ sarcina de rețea.
- Procedurile stocate au un aspect modular, astfel încât acestea sunt ușor de implementat și modifica. În cazul în care aplicația client solicită o procedură stocată pentru a efectua o operație, procedurile de modificare într-un singur loc afectează performanțele sale pentru toți utilizatorii.
- Procedurile memorate pot fi văzute ca o componentă importantă de securitate a bazei de date. În cazul în care toți clienții care accesează datele prin proceduri stocate, accesul direct la tabele pot fi dezactivate, iar acțiunile tuturor utilizatorilor vor fi sub control. Mai important, procedurile stocate pentru a ascunde de la structura bazei de date de utilizator și îi permite să realizeze numai acele operațiuni care sunt programate într-o procedură stocată.
Gestionați proceduri stocate
Procedurile stocate sunt controlate de către instrucțiunile de limbaj de definire a datelor (DDL) CREATE, ALTER și DROP.
Cod comună T-SQL sintaxa pentru a crea o procedură stocată este după cum urmează:
CREATE PROC | PROCEDURĂ
[ <@parameter>
[BEGIN]
Structura acestui operator este în conformitate cu sintaxa de bază a CREATE
Fiecare procedură trebuie să fie unic în numele bazei de date (procedure_name), care corespunde normelor de identificatori de obiect.
Procedurile pot avea orice număr de parametri de intrare (@parametr) un anumit tip de date (data_type), care sunt utilizate în procedura ca variabile locale. Atunci când se efectuează procedura pentru fiecare dintre parametrii formali declarate valorile reale care urmează să fie transmise. In mod alternativ, o valoare prestabilită (implicit) poate fi definită pentru parametrul de intrare care trebuie să fie o constantă sau egală cu NULL. În acest caz, procedura poate fi efectuată fără a specifica valoarea argumentului corespunzător. Utilizarea unei parametri de intrare opțional.
Este de asemenea posibil să se precizeze parametrii de ieșire (IEȘIRE marcată), permițând o procedură stocată pentru a returna una sau mai multe valori scalare la subrutina de la care a fost numit. Când creați o procedură, puteți specifica trei parametri. Când creați o procedură cu un parametru CRIPTARE SQL Server criptează procedura de identificare. Dacă utilizați recompilați SQL Server va recompilarea o procedură stocată de fiecare dată când îl rulați. EXECUTE ca parametru determină contextul de securitate pentru procedura.
În definirea procedurilor stocate după cuvântul cheie ca trebuie să fie introdusă în mod direct corp procedură (sql_statements) un cod de la una sau mai multe instrucțiuni de limbaj T-SQL.
instrucțiune DROP șterge o procedură stocată din baza de date. ALTER modifică conținutul întregii proceduri stocate. Pentru a efectua modificări de preferat să folosiți instrucțiunea ALTER, și nu o combinație a instrucțiunilor de eliminare și crearea, ca ultimă metodă elimină toate permisiunile.
Procedura EXEMPLU stocată fără parametri
CREATE PROCEDURE spr_getOrders
SELECT IdOrd, IdCust, OrdDate
Pentru a testa noua procedură, deschide o nouă interogare SQL Server și executați următorul cod.
EXECUTE comanda sau EXEC abreviat execută procedura stocată.
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ă asupra a ceea ce ar trebui să fie făcut acțiuni cu ea. În special, principala problemă cu precedente procedură stocată (spr_getOrders), este static. În cazul în care utilizatorii trebuie să dispună de informații pentru o altă perioadă de timp, atunci această procedură nu le va ajuta. Prin urmare, este necesar să se prevadă posibilitatea de a transfera în ea parametrii de intrare relevanți, care ar schimba în mod dinamic perioada de eșantionare.
@parameter_name [AS] datatype [= implicit | NULL]
În exemplul următor, o procedură stocate spr_getOrders completate doi parametri de intrare, care permit să se precizeze în mod explicit perioada de eșantionare.
ALTER PROCEDURĂ [DBO]. [Spr_getOrders]
SELECT IdOrd, IdCust, OrdDate
UNDE (OrdDate ÎNTRE @dateBegin ȘI @dateEnd)
Următoarele trei exemple demonstrează stocate apelurile de procedură și a le transfera la parametrii folosind comanda inițială și nume:
Aplicarea parametrilor de ieșire
Parametrii de ieșire permit procedura stocată pentru a reveni date la programul de asteptare. Pentru a determina parametrii de ieșire a cuvântului cheie OUT [PUT], care este necesară ca și în cadrul procedurii de evaluare, și atunci când este chemat. În cazul procedurii de cele mai multe stocate parametrii de ieșire sunt variabile locale. Procedură sau de ieșire de apelare variabile ale pachetului trebuie să fie predeterminat, pentru a obține valoarea rezultată. În cazul în care procedura stocată este finalizată, setarea curentă va fi transferată apelantului variabilă locale.
În exemplul următor, parametrul de ieșire este utilizat pentru a returna un identificator unic al produsului nou adăugat.
CREATE PROCEDURA spr_addProduct
nvarchar @Description (100),
INSERT Produs ([Descriere], instock)
VALORI (@Description, @InStock)
SET @IdProd = @@ IDENTITY
DECLAR @IdProd int
EXEC spr_addProduct
@Description = N 'Sosiri noi'
@IdProd = @IdProd IEȘIRE
SELECT @IdProd ca N '@IdProd'
Rețineți că valorile pentru toți parametrii care nu au trecut în apelul de procedură. Parametrul instock este opțională, deoarece este setat implicit în formă de la zero, care va fi utilizat, în cazul în care nu li se acordă în mod explicit un sens diferit pentru el. Astfel, în cazul în care apelul la valorile și procedura de transmitere stocate are loc folosind parametrii de poziție, ar trebui să completeze fiecare poziție în lista de parametri, cel puțin, înainte ca acesta din urmă să îndeplinească parametrul pentru care trebuie să fie furnizate valoarea.
Confirmarea unei finalizarea cu succes sau nereușită a lucrării utilizând valorile de returnare. Utilizarea komandyRETURN.
Orice cauzat de a executa o procedură stocată returnează o valoare, indiferent dacă sunt sau nu valoarea returnată le-a furnizat. În mod implicit, după finalizarea cu succes a procedurilor de baze de date SQL Server returnează automat o valoare de zero.
Pentru a trimite o valoare returnată din procedura stocată înapoi la codul de apel, este suficient să se aplice instrucțiunea RETURN:
RETURN [<Целое число>]
Rețineți că valoarea returnată trebuie să fie întotdeauna un număr întreg.
Valorile returnate sunt destinate numai pentru a indica succesul sau eșecul procedurii stocate și permite chiar să desemneze gradul sau natura de succes sau de eșec. Folosind valoarea returnată pentru a reveni la datele reale, cum ar fi valoarea de identificare sau datele privind numărul de rânduri afectate procedură stocată este considerată ca fiind practica de programare nevalidă. Valoarea returnata de la 0 indică o execuție cu succes a procedurii și setarea implicită. Microsoft a rezervat valorile de la -99 la -1 numai pentru uz intern. Programator pentru revenirea unei stări de eroare utilizatorul este încurajat să utilizeze valorile -100 sau mai puțin.
Una dintre cele mai importante caracteristici ale instrucțiunii RETURN este faptul că punerea în aplicare a acestuia duce la încetarea necondiționată și ieșirea din procedura stocată. Acest lucru înseamnă că, indiferent de locația declarației RETURN în codul unei proceduri stocate, după performanța sa nu va mai fi efectuată fie o singură linie de cod. Sub închidere absolută a însemnat că efectul furnizat de operatorul RETURN, efectuat indiferent unde în codul este detectat. Pe de altă parte, toleranța de cod stocate în procedura de mai mulți operatori de returnare, precum și executarea acestor operatori au loc numai în cazul în care acest lucru conduce la o structură convențională a procesului de management al codului.
În exemplul anterior, atunci când încercați să adăugați la informații tabelul de produse pe noul produs cu o eroare de nume duplicat ar putea să apară, deoarece numele mărfurilor a organizat o constrângere unică. Extinderea procedurii de spr_addProduct stocate, oferind o verificare preliminară cu privire la disponibilitatea acestui produs în baza de date, precum și o indicație a succesului operațiunii prin parametrul de ieșire.
ALTER PROCEDURĂ [DBO]. [Spr_addProduct]
nvarchar @Description (100),
Dacă există (SELECT * FROM Produs unde [Descriere] = @Description)
INSERT Produs ([Descriere], instock)
VALORI (@Description, @InStock)
SET @IdProd = @@ IDENTITY
Când se apelează o procedură stocată în cazul în care valoarea de ieșire de așteptat, comanda EXEC pentru a utiliza o variabilă număr întreg:
EXEC @ lokalnaya_peremennaya = sp_name;
DECLAR @return_value int,
EXEC @return_value = spr_addProduct
@Description = N 'Sosiri noi'
@IdProd = @IdProd IEȘIRE
IF @return_value = 0
The Print „Acest produs a fost adăugat cu succes“
SELECT @IdProd ca N '@IdProd'
The Print „A apărut o eroare în timp ce adăugarea produsului“
SELECT 'Revenire Valoare' = @return_value
Exerciții: Creați proceduri stocate care implementează următoarele:
- Returnează o listă cu toate comenzile care conțin un anumit produs (pentru IdProd).
- Determinarea numărului de clienți care nu au nici o ordine. Rezultatul ar trebui să fie returnate printr-un parametru de ieșire.
- Ștergerea dintr-o bază de date de informații despre un anumit client (de IdCust). Dacă acest client are înregistrări legate (comenzi) mutarea trebuie să fie anulată. Valoarea returnată trebuie să determine succesul operațiunii.
În general, acest lucru este tot ce am vrut să împărtășesc pe SQL. Personal, mi-a placut foarte mult acest curs, el a fost antrenat de el. Surprinzător, cunoașterea SQL mine foarte util. Mult noroc în dezvoltarea în continuare a limbii, să învețe, nu există încă nimic! =)
