Luăm în considerare o situație în care procedurile stocate pot degrada performanța interogărilor.
La compilarea procedurilor stocate în MS SQL Server 2000, procedurile stocate sunt plasate în memoria cache a procedurilor, ceea ce poate ajuta la creșterea performanței atunci când sunt executate eliminând nevoia de analiză, optimizare și compilare a codului de procedură stocată.
Pe de altă parte, există capcane în stocarea codului compilat al unei proceduri stocate care pot avea efectul opus.
Faptul este că, la compilarea unei proceduri stocate, planul de execuție al acelor instrucțiuni care alcătuiesc codul procedurii este compilat, respectiv, dacă procedura stocată compilată este stocată în cache, atunci planul de execuție al acesteia este și el în cache și, prin urmare, procedura stocată nu va să fie optimizat pentru o situație specifică și parametrii de interogare.
Voi face un mic experiment pentru a demonstra acest lucru.
PASUL 1. Crearea bazei de date.
Pentru experiment, să creăm o bază de date separată.
CREAȚI BAZĂ DE DATE test_sp_perf
ACTIVAT (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOG ON (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
PASUL 2. Crearea unui tabel.
CREATE TABLE sp_perf_test(coloana1 int, coloana2 char(5000))
PASUL 3. Umplerea tabelului cu șiruri de test. Rândurile duplicat sunt adăugate în mod intenționat la tabel. 10000 de linii cu numere de la 1 la 10000 și 10000 de linii cu numerele 50000.
DECLARE @i int
SET @i=1
In timp ce eu<10000)
ÎNCEPE
INSERT INTO sp_perf_test(coloana1, coloana2) VALUES(@i,"Șir de testare #"+CAST(@i ca char(8)))
INSERT INTO sp_perf_test(coloana1, coloana2) VALUES(50000,"Șir de testare #"+CAST(@i ca char(8)))
SET @i= @i+1
SFÂRȘIT
SELECTAȚI COUNT(*) FROM sp_perf_test
MERGE
PASUL 4. Creați un index non-cluster. Deoarece planul de execuție este memorat în cache cu procedura, indexul va fi utilizat în același mod pentru toate apelurile.
CREATE INDEX NONCLUSTERED CL_perf_test ON sp_perf_test(coloana1)
MERGE
PASUL 5. Creați o procedură stocată. Procedura execută pur și simplu instrucțiunea SELECT cu condiția.
CREATE PROC proc1 (@paramint)
LA FEL DE
SELECTAȚI coloana1, coloana2 FROM sp_perf_test WHERE [email protected]
MERGE
PASUL 6. Rulați o procedură stocată. La pornirea unei proceduri vulnerabile, este utilizat în mod specific un parametru selectiv. Ca rezultat al procedurii, obținem 1 rând. Planul de execuție indică utilizarea unui index neclustrat, ca interogarea este selectivă și acesta este cel mai bun mod de a extrage șirul. O procedură optimizată pentru preluarea unui singur rând este stocată în memoria cache a procedurii.
EXEC proc1 1234
MERGE
PASUL 7. Rularea unei proceduri stocate cu un parametru neselectiv. Ca parametru este folosită valoarea 50000. Rândurile cu o astfel de valoare a primei coloane de aproximativ 10000, respectiv, folosind un index neclustrat și operația de căutare a marcajelor sunt ineficiente, dar deoarece codul compilat cu planul de execuție este stocat în procedura procedurală. cache, va fi folosit. Planul de execuție arată acest lucru, precum și faptul că a fost efectuată o operație de căutare a marcajelor pentru 9999 de rânduri.
EXEC proc1 50000
MERGE
PASUL 8. Efectuarea unei selecții de rânduri cu primul câmp egal cu 50000. Când se execută o interogare separată, interogarea este optimizată și compilată cu o valoare specifică a primei coloane. Ca urmare, optimizatorul de interogări determină că câmpul este duplicat de mai multe ori și decide să folosească operația de scanare a tabelului, care în acest caz este mult mai eficientă decât utilizarea unui index neclustrat.
SELECT coloana1, coloana 2 FROM sp_perf_test WHERE coloana1=50000
MERGE
Astfel, putem concluziona că utilizarea procedurilor stocate nu poate îmbunătăți întotdeauna performanța interogărilor. Ar trebui să fiți foarte atenți la acele proceduri stocate care funcționează cu rezultate cu un număr variabil de rânduri și folosesc planuri de execuție diferite.
Puteți utiliza scriptul pentru a repeta experimentul pe propriul server MS SQL.
Includeți în procedurile dumneavoastră linia - SET NOCOUNT ON:
Cu fiecare instrucțiune DML, serverul SQL ne returnează cu atenție un mesaj care conține numărul de înregistrări procesate. Aceste informații ne pot fi utile în timp ce depanăm codul, dar după aceea va fi complet inutilă. Scriind SET NOCOUNT ON, dezactivăm această funcție. Pentru procedurile stocate care conțin mai multe expresii sau\și bucle, această acțiune poate oferi o creștere semnificativă a performanței, deoarece cantitatea de trafic va fi redusă semnificativ.
Transact SQL
Utilizați numele schemei cu numele obiectului:
Ei bine, cred că e clar. Această operațiune îi spune serverului unde să caute obiecte și, în loc să scotoci aleatoriu prin coșurile sale, va ști imediat unde trebuie să meargă și ce să ia. Cu un număr mare de baze de date, tabele și proceduri stocate, ne poate economisi în mod semnificativ timpul și nervii.
Transact SQL
SELECT * FROM dbo.MyTable --Acesta este un lucru bun de făcut -- În loc de SELECT * FROM MyTable --Acesta este un lucru rău de făcut --Apelați procedura EXEC dbo.MyProc --Bine din nou --În loc de EXEC MyProc --Rău!
Nu utilizați prefixul „sp_” în numele procedurilor stocate:
Dacă numele procedurii noastre începe cu „sp_”, SQL Server va căuta mai întâi în baza de date principală. Faptul este că acest prefix este utilizat pentru procedurile interne stocate personale ale serverului. Prin urmare, utilizarea acestuia poate duce la costuri suplimentare și chiar la rezultate incorecte dacă în baza sa de date se găsește o procedură cu același nume cu al dumneavoastră.
Folosiți IF EXISTS (SELECT 1) în loc de IF EXISTS (SELECT *):
Pentru a verifica dacă o înregistrare există într-un alt tabel, folosim instrucțiunea IF EXISTS. Această expresie returnează adevărat dacă cel puțin o valoare este returnată din expresia interioară, nu contează „1”, toate coloanele sau un tabel. Datele returnate, în principiu, nu sunt utilizate în niciun fel. Astfel, pentru a comprima traficul în timpul transmisiei de date, este mai logic să folosiți „1”, așa cum se arată mai jos.
Când lucrează cu SQL Server, utilizatorii își pot crea propriile proceduri care implementează anumite acțiuni. Procedurile stocate sunt obiecte de bază de date cu drepturi depline și, prin urmare, fiecare dintre ele este stocată într-o anumită bază de date. Apelarea directă a unei proceduri stocate este posibilă numai dacă este efectuată în contextul bazei de date în care se află procedura.
Tipuri de proceduri stocate
SQL Server are mai multe tipuri de proceduri stocate.
Procedurile stocate de sistem sunt concepute pentru a efectua diverse acțiuni administrative. Aproape toate acțiunile de administrare a serverului sunt efectuate cu ajutorul lor. Se poate spune că procedurile stocate de sistem sunt o interfață care oferă lucru cu tabelele de sistem, care, în cele din urmă, se rezumă la modificarea, adăugarea, ștergerea și preluarea datelor din tabelele de sistem atât ale bazelor de date ale utilizatorului, cât și ale sistemului. Procedurile stocate de sistem sunt prefixate cu sp_, sunt stocate în baza de date a sistemului și pot fi apelate în contextul oricărei alte baze de date.
Procedurile stocate personalizate implementează anumite acțiuni. Procedurile stocate sunt un obiect complet al bazei de date. Ca urmare, fiecare procedură stocată este localizată într-o bază de date specifică, unde este executată.
Procedurile stocate temporar există doar pentru o perioadă scurtă de timp, după care sunt distruse automat de server. Ele sunt împărțite în locale și globale. Procedurile stocate temporare locale pot fi apelate numai de la conexiunea pe care au fost create. La crearea unei astfel de proceduri, trebuie să i se dea un nume care începe cu un singur caracter #. La fel ca toate obiectele temporare, procedurile stocate de acest tip sunt șterse automat atunci când utilizatorul se deconectează, repornește sau oprește serverul. Procedurile stocate temporare globale sunt disponibile pentru orice conexiune pe un server care are aceeași procedură. Pentru a-l defini este suficient să-i dai un nume care să înceapă cu caracterele ##. Aceste proceduri sunt șterse atunci când serverul este repornit sau oprit sau când conexiunea în al cărei context au fost create este închisă.
declanșatoare
declanșatoare sunt una dintre varietățile de proceduri stocate. Execuția lor are loc atunci când o instrucțiune a limbajului de manipulare a datelor (DML) este executată pe tabel. Declanșatorii sunt utilizați pentru a verifica integritatea datelor și, de asemenea, pentru a anula tranzacțiile.
Trigger este o procedură SQL compilată, a cărei execuție este determinată de apariția anumitor evenimente în cadrul unei baze de date relaționale. Utilizarea declanșatorilor este, în cea mai mare parte, foarte convenabilă pentru utilizatorii bazei de date. Cu toate acestea, utilizarea lor este adesea asociată cu costuri suplimentare de resurse pentru operațiunile I/O. Atunci când aceleași rezultate (cu mult mai puțină suprasarcină de resurse) pot fi obținute folosind proceduri sau aplicații stocate, declanșatoarele sunt inadecvate.
declanșatoare este un instrument special de server SQL folosit pentru a menține integritatea datelor dintr-o bază de date. Este posibil ca constrângerile de integritate, regulile și valorile implicite să nu ofere întotdeauna nivelul dorit de funcționalitate. Este adesea necesar să se implementeze algoritmi complexi de validare a datelor pentru a se asigura că aceștia sunt validi și reali. În plus, uneori este necesar să urmăriți modificările la valorile tabelului pentru a modifica datele aferente după cum este necesar. Declanșatoarele pot fi considerate ca un fel de filtre care intră în vigoare după ce toate operațiunile au fost efectuate conform regulilor, valorilor implicite și așa mai departe.
Trigger este un tip special de procedură stocată care este rulată automat de server atunci când încearcă să modifice datele din tabelele cu care sunt asociate declanșatorii. Fiecare Trigger este legat de un anumit tabel. Toate modificările datelor pe care le face sunt tratate ca o singură tranzacție. Dacă este detectată o eroare sau o încălcare a integrității datelor, tranzacția este anulată. Prin urmare, modificările sunt interzise. Orice modificări deja făcute de declanșator sunt, de asemenea, anulate.
Creează declanșatorul doar proprietarul bazei de date. Această restricție vă permite să evitați modificări accidentale în structura tabelelor, modalități de conectare a altor obiecte cu acestea etc.
Trigger este un instrument foarte util și în același timp periculos. Deci, cu o logică greșită a funcționării sale, puteți distruge cu ușurință întreaga bază de date, așa că declanșatoarele trebuie depanate cu mare atenție.
Spre deosebire de o subrutină obișnuită, declanșatorul este executat implicit de fiecare dată când apare evenimentul declanșator și nu are argumente. Activarea acestuia este uneori denumită declanșarea unui declanșator. Declanșatorii ating următoarele obiective:
validarea datelor introduse și implementarea unor constrângeri complexe de integritate a datelor care sunt dificil, dacă nu imposibil, de menținut cu constrângerile de integritate stabilite pe tabel;
emiterea de avertismente care vă amintesc de necesitatea efectuării anumitor acțiuni la actualizarea unui tabel implementat într-un anumit mod;
acumularea de informații de audit prin fixarea informațiilor despre modificările efectuate și despre acele persoane care le-au efectuat;
suport de replicare.
Formatul de bază al comenzii CREATE TRIGGER este prezentat mai jos:
<Определение_триггера>::=
CREATE TRIGGER nume_declanșator
ÎNAINTE | DUPĂ<триггерное_событие>
PE<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Evenimentele de declanșare constau în inserarea, ștergerea și actualizarea rândurilor dintr-un tabel. În acest din urmă caz, pot fi specificate nume specifice de coloane de tabel pentru evenimentul de declanșare. Ora de începere a unui declanșator este definită folosind cuvintele cheie BEFORE ( Trigger incendii înainte de executarea evenimentelor asociate acestuia) sau DUPĂ (după executarea acestora).
Acțiunile efectuate de declanșator sunt setate pentru fiecare rând (PENTRU FIECARE RÂND) acoperit de acest eveniment, sau doar o singură dată pentru fiecare eveniment (PENTRU FIECARE Declarație).
Declanșatoarele scrise incorect pot duce la probleme grave, cum ar fi apariția încuietorilor „moarte”. Declanșatoarele pot bloca multe resurse pentru o perioadă lungă de timp, așa că ar trebui să acordați o atenție deosebită minimizării conflictelor de acces.
Trigger poate fi creat doar în baza de date curentă, dar alte baze de date pot fi accesate în interiorul declanșatorului, inclusiv cele situate pe un server la distanță.
Procedură stocată este un tip special de lot 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 de server pentru a îmbunătăți performanța și consistența sarcinilor repetitive.
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. Astfel, procedura va lua argumentele corespunzătoare de fiecare dată când este apelată. Procedurile stocate pot returna o valoare care conține informații definite de utilizator sau, în cazul unei erori, un mesaj de eroare corespunzător.
O procedură 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) recompilările procedurilor și o îmbunătățire corespunzătoare a performanței. 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, un apel către o procedură stocată 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 semnificativ.
Procedurile stocate pot fi utilizate și în următoarele scopuri:
pentru a crea un jurnal de jurnale despre acțiunile 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ă de 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 scriu și/sau citesc în jurnal în tabele oferă securitate suplimentară pentru baza de date. Utilizând astfel de proceduri, administratorul bazei de date poate urmări modificările aduse bazei de date de către utilizatori sau aplicații.
Crearea și executarea procedurilor stocate
Procedurile stocate sunt create folosind instrucțiunea 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 generate. Parametrul proc_name specifică numele procedurii stocate. Parametrul @param1 este un parametru de procedură (un argument formal) al cărui tip de date este specificat de parametrul type1. Parametrii procedurii sunt locali în cadrul unei proceduri, la fel cum variabilele locale sunt locale într-un pachet. Parametrii de procedură 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 specifică faptul că un parametru de procedură este returnabil ș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 procedurii este stocată în baza de date și utilizată de fiecare dată când este apelată. Dacă, dintr-un motiv oarecare, o procedură stocată trebuie compilată de fiecare dată când este apelată, declarația procedurii folosește optiunea CU 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 dacă există modificări frecvente la obiectele bazei de date utilizate de procedura stocată.
EXECUTĂ ca ofertă definește contextul de securitate în care procedura stocată trebuie executată după ce a fost apelată. Prin setarea acestui context, Motorul de baze de date poate controla selecția conturilor de utilizator pentru verificarea permisiunilor de acces la obiectele la care se face referire prin această procedură stocată.
În mod implicit, numai membrii rolului de server fix sysadmin și rolului de bază de date fix db_owner sau db_ddladmin pot folosi instrucțiunea CREATE PROCEDURE. Cu toate acestea, membrii acestor roluri pot atribui acest drept altor utilizatori folosind instrucțiunile PROCEDURA DE CREARE A GRANT.
Următorul exemplu 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 am menționat mai devreme, se folosește separarea a două pachete GO instrucțiune. 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, specificat de parametrul @percent. Procedura definește și o valoare procentuală implicită (5) care se aplică dacă acest argument nu este prezent în timpul execuției procedurii.
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 corespunzătoare și fără a vă conecta măcar la serverul de destinație.
Spre deosebire de procedurile stocate de bază, care sunt întotdeauna stocate în baza de date curentă, este posibil să se creeze proceduri stocate temporare, care sunt întotdeauna plasate în baza de date temporară a sistemului tempdb. Unul dintre motivele pentru crearea procedurilor stocate temporare poate fi evitarea execuției repetitive a unui anumit grup de instrucțiuni la conectarea 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ă doar de utilizatorul care a creat-o și numai în timpul conexiunii la baza de date în care a fost creată. O procedură temporară globală poate fi executată de toți utilizatorii, dar numai până când ultima conexiune pe care rulează (de obicei conexiunea creatorului procedurii) este terminată.
Ciclul de viață al unei proceduri stocate constă din două faze: crearea și executarea acesteia. Fiecare procedură este creată o dată și executată de mai multe ori. Procedura stocată este executată de EXECUTE declarații un utilizator care deține procedura sau are dreptul EXECUTE de a accesa procedura. 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 valoare booleană ca și parametrii instrucțiunii CREATE PROCEDURE cu același nume. Parametrul return_status definește 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 nenumite trebuie furnizate în ordinea în care sunt definite în instrucțiunea CREATE PROCEDURE.
clauza DEFAULT furnizează valori implicite 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ântul cheie DEFAULT, apare o eroare.
Când instrucțiunea EXECUTE este prima instrucțiune dintr-un 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%.
Următorul exemplu arată cum să creați o procedură stocată pentru procesarea datelor din tabelele Employee și Works_on:
Procedura ModifyEmpId din exemplu ilustrează utilizarea procedurilor stocate ca parte a procesului de integritate referențială (în acest caz între tabelele Employee și Works_on). O astfel de procedură stocată poate fi utilizată în interiorul unei definiții de declanșare, care impune de fapt integritatea referențială.
Următorul exemplu arată utilizarea clauzei OUTPUT într-o procedură stocată:
Această procedură stocată poate fi executată folosind următoarele instrucțiuni:
DECLARE @quantityDeleteEmployee INT; EXECUTĂ DeleteEmployee @empId=18316, @ [email protected] IEȘIRE; PRINT N"Angajați șterși: " + convert(nvarchar(30), @quantityDeleteEmployee);
Această procedură numără numărul de proiecte la care lucrează un angajat cu număr de personal @empId și atribuie valoarea rezultată parametrului ©counter. După ștergerea tuturor rândurilor pentru un anumit număr de personal 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, astfel încât procedura stocată returnează valoarea sistemului. Prin urmare, parametrul @counter trebuie specificat atât în opțiunea OUTPUT la declararea procedurii, cât și în instrucțiunea EXECUTE la apelarea acesteia.
Clauza WITH RESULTS SETS a unei instrucțiuni EXECUTE
În SQL Server 2012, instrucțiunea EXECUTE este tastată WITH RESULTS SETS clauza A care, în anumite condiții, poate schimba forma setului de rezultate al procedurii 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 al procedurii și trebuie specificat la apelarea procedurii. Executarea acestei proceduri generează un tabel cu două coloane ale căror titluri se potrivesc cu numele coloanelor corespunzătoare din tabelul bazei de date, de exemplu. id și nume de familie. Pentru a modifica anteturile coloanelor rezultate (precum și tipul lor de date), SQL Server 2012 utilizează noua clauză WITH RESULTS SETS. Aplicarea acestei clauze este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; EXEC EmployeesInDept „d1” CU SETURI DE REZULTATE (( INT NOT NULL, [Last Name] CHAR(20) NOT 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 într-o instrucțiune EXECUTE vă permite să schimbați numele și tipul de date al coloanelor setului de rezultate produse de procedură. Astfel, această nouă funcționalitate oferă mai multă 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, declarația PROCEDURA DE ALTERARE pentru a modifica structura procedurilor stocate. Instrucțiunea ALTER PROCEDURE este de obicei utilizată pentru a modifica instrucțiunile Transact-SQL în cadrul unei proceduri. Toți parametrii instrucțiunii ALTER PROCEDURE au aceeași semnificație ca și parametrii instrucțiunii CREATE PROCEDURE cu același nume. Scopul principal al utilizării acestei instrucțiuni este de a evita suprascrierea permisiunilor de procedură stocată existente.
Motorul bazei 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 elimina una sau un grup de proceduri stocate, utilizați Declarația DROP PROCEDURE. Numai proprietarul procedurii stocate 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 diferite obiecte de bază de date (proceduri stocate, funcții definite de utilizator, declanșatoare, agregate definite de utilizator și tipuri de date definite de utilizator) folosind C# și Visual Basic. Common Language Runtime permite, de asemenea, executarea acestor obiecte folosind sistemul common runtime.
Runtimeul limbajului comun este activat și dezactivat prin intermediul opțiunii clr_enabled procedura de sistem sp_configure, care este lansat pentru execuție de către instrucțiune RECONFIGURAȚI. Următorul exemplu arată cum puteți activa limbajul comun de execuție utilizând procedura de sistem sp_configure:
UTILIZAȚI SampleDb; EXEC sp_configure „clr_enabled”,1 RECONFIGURĂ
Crearea, compilarea și salvarea unei proceduri folosind CLR necesită următoarea secvență de pași, în ordinea listată:
Creați o procedură stocată în C# sau Visual Basic și apoi compilați-o folosind compilatorul corespunzător.
Folosind instrucțiuni CREAȚI ANSAMBLU, creați executabilul corespunzător.
Executați o procedură folosind instrucțiunea EXECUTE.
Figura de mai jos prezintă o diagramă grafică a pașilor subliniați anterior. Mai jos este o descriere mai detaliată a acestui proces.
Mai întâi, creați programul dorit într-un mediu de dezvoltare, cum ar fi Visual Studio. Compilați programul terminat în codul obiect folosind compilatorul C# sau Visual Basic. Acest cod este stocat într-un fișier de bibliotecă de legături dinamice (.dll) care servește drept sursă pentru instrucțiunea CREATE ASSEMBLY, care creează cod executabil intermediar. Apoi, lansați o instrucțiune CREATE PROCEDURE pentru a salva codul care este executat 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ă al procedurii stocate î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 execuția acestuia. Utilizarea acestor directive vă permite să specificați nume de clase în codul sursă fără a specifica în mod explicit spațiile de nume corespunzătoare. În continuare, este definită clasa StoredProcedures, pentru care Atributul SqlProcedure, care informează compilatorul că această clasă este o procedură stocată. În codul clasei, este definită metoda CountEmployees(). Conexiunea la sistemul de baze de date se stabilește 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 este transmisă comanda SQL dorită.
Î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(). Următorul este numit Metoda ExecuteScalar(). instanță SqlCommand. Această metodă returnează o valoare scalară care este convertită la tipul de date întreg int și atribuită variabilei rânduri.
Acum puteți compila acest cod folosind Visual Studio. Am adăugat această clasă la proiect cu numele CLRStoredProcedures, astfel încât Visual Studio va compila ansamblul cu același nume cu extensia *.dll. Exemplul de mai jos arată următorul pas în crearea unei proceduri stocate: crearea codului de rulat. Înainte de a executa codul din acest exemplu, trebuie să cunoașteți locația fișierului .dll compilat (de obicei situat în folderul Debug al proiectului).
UTILIZAȚI SampleDb; CREAȚI ANSAMBLU 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 adecvat pentru care puteți crea proceduri stocate CLR (Common Language Runtime), 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 unui rol 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 ar trebui să fie întotdeauna specificată. Acesta definește setul de drepturi de acces acordate codului de asamblare. Setul de drepturi 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 oferă acces nerestricționat la resurse, atât în interiorul, cât și în afara sistemului bazei de date.
Pentru a stoca informații despre codul de asamblare, utilizatorul trebuie să poată emite o instrucțiune CREATE ASSEMBLY. Ansamblul este deținut de utilizatorul (sau rolul) care execută instrucțiunea. Puteți schimba proprietarul unui ansamblu utilizând clauza AUTHORIZATION a instrucțiunii CREATE SCHEMA.
Motorul de bază de date acceptă, de asemenea, instrucțiunile ALTER ASSEMBLY și DROP ASSEMBLY. Declarație ALTER ASSEMBLY folosit pentru a actualiza un ansamblu la cea mai recentă versiune. Această instrucțiune adaugă sau elimină și fișierele asociate cu ansamblul corespunzător. Declarație DROP ASSEMBLY elimină ansamblul specificat și toate fișierele asociate din baza de date curentă.
Următorul exemplu arată cum să creați o procedură stocată pe baza codului gestionat 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 CLR. Numele din această propoziție este format din trei părți:
nume_asamblare.nume_clase.nume_metodă
assembly_name - specifică numele ansamblului;
class_name - specifică numele clasei generale;
method_name - parte opțională, specifică numele metodei care este setată î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.
