Microsoft SQL Server 2018

Domeniu: Informatică, Cibernetică și Programare

Mărime fișier: 87 KB

Lucrarea a fost descărcată: 18 persoane.

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 procesării 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 văzute ca 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ă.

Administrarea procedurilor stocate Procedurile stocate sunt gestionate prin intermediul instrucțiunilor de limbaj 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> [= ] [OUT | OUTPUT]] [. n] AS [BEGIN] [END] . = [ENCRYPȚIE] [RECOMPILE] [EXECUTE_AS_Clause]

Structura acestei instrucțiuni corespunde sintaxei de bază CREATE

, care este baza oricărei instrucțiuni CREATE. Singura caracteristică distinctivă este că este permisă utilizarea cuvântului PROCEDURE sau PROC. Ambele opțiuni sunt valabile: PROC este doar o abreviere pentru PROCEDURE.
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

Pentru a testa noua procedură, deschideți o nouă interogare SQL Server și executați următorul cod.

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] @dateBegin datetime. @dateEnd datetime AS SELECT IdOrd, IdCust, OrdDate FROM [Ordine] WHERE (OrdDate BETWEEN @dateBegin AND @dateEnd)

Următoarele trei exemple demonstrează apelurile la procedurile stocate și transmiterea parametrilor utilizând ordinea și denumirile originale:

Aplicarea parametrilor de ieșire

Parametrii de ieșire permit procedurii stocate să returneze datele către programul de apel. Pentru a determina parametrii de ieșire, se folosește cuvântul cheie OUT [PUT], care este obligatoriu atât pentru determinarea procedurii, cât și pentru numirea acesteia. În procedura stocată, parametrii de ieșire sunt variabile locale. În procedura sau pachetul de apel, variabilele de ieșire trebuie să fie predefinite pentru a obține valorile rezultate. După finalizarea executării procedurii stocate, valoarea curentă a parametrului va fi transmisă la variabila locală a programului apelant.

În următorul exemplu, parametrul de ieșire este utilizat pentru a returna identificatorul unic al produsului nou adăugat.

CREAȚI PROCEDURA spr_addProduct @Description nvarchar (100), @InStock int = 0, @IdProd int OUT AS

INSERT Produs ([Descrierea], InStock) VALUES (@Description, @InStock)

SET @IdProd = @@ IDENTITY

DECLARE @IdProd int

EXEC spr_addProduct @Description = N 'Produs nou', @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.

ALTER PROCEDURE [dbo]. [Spr_addProduct] @Description nvarchar (100), @inStock int = 0, @IdProd int OUT AS

DACĂ EXISTĂ (SELECTĂ * DE PRODUS WHERE [Descriere] = @Description) RETURN -100

INSERT Produs ([Descrierea], InStock) VALUES (@Description, @InStock)

SET @IdProd = @@ IDENTITY

Când se apelează o procedură stocată, dacă se așteaptă o valoare de ieșire, comanda EXEC trebuie să utilizeze o variabilă întreagă:

EXEC @ local_variable = nume_procedeu_procedeu;

DECLARE @ return_value int, @IdProd int

EXEC @return_value = spr_addProduct @Description = N 'Produs nou'. @IdProd = @IdProd OUTPUT

IF @return_value = 0 BEGIN PRINT 'Articol adăugat cu succes' SELECT @IdProd ca N '@IdProd' END

ELSE BEGIN PRINT 'A apărut o eroare în timpul adăugării mărfii' SELECT 'Return Value' = @ return _ value END

Sarcina pentru munca independentă: creați proceduri stocate care pun în aplicare următoarele acțiuni:

Returnează lista tuturor comenzilor care conțin produsul specificat (de IdProd). Determină numărul de clienți care nu au comenzi. Rezultatul trebuie returnat prin parametrul de ieșire. Eliminarea informațiilor despre un anumit client din baza de date (prin IdCust). Dacă există înregistrări asociate (comenzi) cu acest client, ștergerea trebuie anulată. Valoarea returnată trebuie să determine succesul operației.

Scopul lucrării mele este de a studia semnificația numelor copacului meu de familie, pentru a afla cum au trăit, ce au făcut strămoșii mei și cine au fost profesori. Sarcini: aflați cine sunt strămoșii mei; Pentru a stabili unde au trăit și ce au făcut; pentru a studia biografia lor, importanța numelor familiei mele.

Articole similare

Pagina anterioară

Pagina următoare