Proceduri stocate în mediul server ms sql. Proceduri stocate în T-SQL - Creați, modificați, ștergeți

1. 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.

CREATE PROC dbo.ProcName
LA FEL DE
SETARE NOCOUNT ON;
--Codul de procedură aici
SELECTAȚI coloana1 FROM dbo.TblTable1
--Comutați SET NOCOUNT în starea inițială
SETĂ NOCOUNT OFF;
MERGE

2. Folosiț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.

SELECT * FROM dbo.MyTable --Acesta este o modalitate bună de a face acest lucru
-- În loc de
SELECT * FROM MyTable --Acesta este un lucru rău de făcut
--Apel de procedură
EXEC dbo.MyProc -- Bun din nou
--În loc de
EXEC MyProc --Rău!

3. Nu utilizați prefixul „sp_” în numele procedurilor dvs. 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ă.

4. 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 transmiterii datelor, este mai logic să folosiți „1”, după cum se arată mai jos:

DACĂ EXISTĂ (SELECTARE 1 DIN sysobjects
WHERE nume = „MyTable” ȘI tip = „U”)

5. Folosiți TRY-Catch pentru a detecta erori:Înainte de serverul 2005, după fiecare solicitare, în procedură au fost scrise un număr mare de verificări de eroare. Mai mult cod consumă întotdeauna mai multe resurse și mai mult timp. Începând cu SQL Server 2005, a apărut o modalitate mai corectă și mai convenabilă de a rezolva această problemă:

ÎNCEPE ÎNCERCAȚI
--cod
TERMINARE ÎNCERCARE
ÎNCEPE PRIMIREA
-- cod de captură de eroare
TERMINAT CAPTURĂ

Concluzie
Practic, asta e tot ce am pentru azi. Repet încă o dată că aici sunt doar acele tehnici pe care le-am folosit personal în practica mea și pot garanta eficacitatea lor.

P.S.
Prima mea postare, nu judeca strict.

În Microsoft SQL Server pentru a implementa și automatiza proprii algoritmi ( calculele) puteți folosi proceduri stocate, așa că astăzi vom vorbi despre cum sunt create, modificate și șterse.

Dar mai întâi, puțină teorie, astfel încât să înțelegeți ce sunt procedurile stocate și pentru ce sunt acestea în T-SQL.

Notă! Pentru programatorii începători, recomand următoarele materiale utile pe tema T-SQL:

• Pentru un studiu mai detaliat al limbajului T-SQL, recomand să citești și cartea – The Way of the T-SQL Programmer. Tutorial Transact-SQL.

Ce sunt procedurile stocate în T-SQL?

Proceduri stocate- Acestea sunt obiecte de bază de date în care algoritmul este încorporat sub forma unui set de instrucțiuni SQL. Cu alte cuvinte, putem spune că procedurile stocate sunt programe dintr-o bază de date. Procedurile stocate sunt folosite pentru a stoca cod reutilizabil pe server, de exemplu, ați scris un algoritm, un calcul secvențial sau o instrucțiune SQL în mai mulți pași și, pentru a nu executa de fiecare dată toate instrucțiunile incluse în acest algoritm, puteți aranja aceasta ca o procedură stocată. În acest caz, când creați o procedură SQL, serverul compilează codul și apoi, de fiecare dată când rulați această procedură, serverul SQL nu îl va mai compila din nou.

Pentru a rula o procedură stocată în SQL Server, este necesar să scrieți comanda EXECUTE înaintea numelui acesteia, de asemenea, este posibil să scrieți această comandă EXEC pe scurt. Apelați o procedură stocată într-o instrucțiune SELECT, de exemplu, deoarece o funcție nu va mai funcționa, de exemplu. procedurile se desfășoară separat.

În procedurile stocate, spre deosebire de funcții, este deja posibilă efectuarea unor operațiuni de modificare a datelor precum: UNSERT, UPDATE, DELETE. De asemenea, în proceduri, puteți utiliza aproape orice tip de instrucțiune SQL, de exemplu, CREATE TABLE pentru a crea tabele sau EXECUTE, de exemplu. apelând la alte proceduri. Excepție fac mai multe tipuri de instrucțiuni, cum ar fi: crearea sau modificarea funcțiilor, vizualizărilor, declanșatorilor, crearea schemelor și alte câteva instrucțiuni similare, de exemplu, este imposibil să comutați contextul conexiunii la baza de date (USE) într-o procedură stocată .

O procedură stocată poate avea parametri de intrare și parametri de ieșire, poate returna date tabulare, poate să nu returneze nimic, ci doar să execute instrucțiunile conținute în ea.

Procedurile stocate sunt foarte utile, ne ajută să automatizăm sau să simplificăm multe operațiuni, de exemplu, trebuie să generați în mod constant diverse rapoarte analitice complexe folosind tabele pivot, de ex. operator PIVOT. Pentru a simplifica formarea de interogări cu acest operator ( după cum știți, sintaxa PIVOT este destul de complicată), puteți scrie o procedură care va genera în mod dinamic rapoarte rezumative pentru dvs., de exemplu, în materialul „Dynamic PIVOT în T-SQL”, este prezentat un exemplu de implementare a acestei caracteristici sub forma unei proceduri stocate.

Exemple de lucru cu proceduri stocate în Microsoft SQL Server

Date inițiale pentru exemple

Toate exemplele de mai jos vor fi executate în Microsoft SQL Server 2016 Express. Pentru a demonstra cum funcționează procedurile stocate cu date reale, avem nevoie de aceste date, să le creăm. De exemplu, să creăm un tabel de testare și să adăugăm câteva înregistrări la el, să presupunem că va fi un tabel care conține o listă de produse cu prețul lor.

Instrucțiunea de creare a tabelului CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Adăugați instrucțiunea de date INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , „Mouse”, 100), (1, „Tastatură”, 200), (2, „Telefon”, 400) GO -- SELECT * FROM interogare TestTable

Există date, acum să trecem la crearea procedurilor stocate.

Crearea unei proceduri stocate în instrucțiunea T-SQL - CREATE PROCEDURE

Procedurile stocate sunt create folosind instrucțiunea CREAȚI PROCEDURA, după această instrucțiune trebuie să scrieți numele procedurii dvs., apoi, dacă este necesar, să definiți parametrii de intrare și de ieșire între paranteze. După aceea, scrieți cuvântul cheie AS și deschideți un bloc de instrucțiuni cu cuvântul cheie BEGIN, închideți acest bloc cu cuvântul END. În interiorul acestui bloc, scrii toate instrucțiunile care implementează algoritmul tău sau un fel de calcul secvenţial, cu alte cuvinte, programezi în T-SQL.

De exemplu, să scriem o procedură stocată care va adăuga o nouă înregistrare, de exemplu. element nou în diagrama noastră de testare. Pentru a face acest lucru, vom defini trei parametri de intrare: @CategoryId - identificatorul categoriei de produse, @ProductName - numele produsului și @Preț - prețul produsului, acest parametru va fi opțional pentru noi, adică. nu poate fi trecut la procedura ( de exemplu, nu știm încă prețul), pentru aceasta vom seta valoarea implicită în definiția sa. Acești parametri se află în corpul procedurii, de exemplu. în blocul BEGIN…END poate fi folosit în același mod ca variabilele obișnuite ( după cum știți, variabilele sunt notate cu semnul @). Dacă trebuie să specificați parametrii de ieșire, după numele parametrului, specificați cuvântul cheie OUTPUT ( sau prescurtat OUT).

În blocul BEGIN…END vom scrie o instrucțiune pentru adăugarea datelor, iar la sfârșitul procedurii, o instrucțiune SELECT, astfel încât procedura stocată să returneze date tabelare despre produsele din categoria specificată, ținând cont de noul, doar produs adăugat. Tot în această procedură stocată am adăugat procesarea parametrului de intrare și anume eliminarea spațiilor suplimentare la începutul și la sfârșitul șirului de text pentru a evita situațiile în care au fost introduse accidental mai multe spații.

Iată codul pentru această procedură Am comentat si eu).

Creați procedura CREATE PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Procesarea parametrilor de intrare --Eliminați spațiile suplimentare la început și la sfârșitul șirului de text SET @ProductName = LTRIM(RTRIM(@ProductName)); --Adăugați o intrare nouă INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Return data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Rularea unei proceduri stocate în T-SQL - comanda EXECUTE

Puteți rula o procedură stocată, așa cum am observat deja, folosind comanda EXECUTE sau EXEC. Parametrii primiți sunt trecuți procedurilor prin simpla enumerare a acestora și furnizarea valorilor corespunzătoare după numele procedurii ( pentru parametrii de ieșire, trebuie să specificați și comanda OUTPUT). Cu toate acestea, numele parametrilor poate să nu fie specificat, dar în acest caz este necesar să se urmeze succesiunea de specificare a valorilor, adică. specificați valorile în ordinea în care sunt definiți parametrii de intrare ( acest lucru se aplică și parametrilor de ieșire).

Este posibil ca parametrii care au valori implicite să nu fie specificați, aceștia sunt așa-numiții parametri opționali.

Iată câteva moduri diferite, dar echivalente, de a rula procedurile stocate, în special procedura noastră de testare.

1. Apelați procedura fără a specifica prețul EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Apelăm procedura cu prețul specificat EXEC TestProcedure @CategoryId = 1, @ProductName = „Test produs 2”, @Price = 300 --3. Apelăm procedura fără a specifica numele parametrilor EXEC TestProcedure 1, „Test item 3”, 400

Schimbarea unei proceduri stocate în T-SQL - instrucțiunea ALTER PROCEDURE

Puteți face modificări ale algoritmului procedurii folosind instrucțiunile PROCEDURA DE ALTERARE. Cu alte cuvinte, pentru a schimba o procedură deja existentă, trebuie doar să scrieți ALTER PROCEDURE în loc de CREATE PROCEDURE și să modificați orice altceva după cum este necesar.

Să presupunem că trebuie să facem modificări procedurii noastre de testare, să spunem parametrul @Price, de exemplu. preț, îl vom face obligatoriu, pentru aceasta vom elimina valoarea implicită și, de asemenea, ne imaginăm că nu mai trebuie să obținem setul de date rezultat, pentru aceasta vom elimina pur și simplu instrucțiunea SELECT din procedura stocată.

Modificați procedura ALTER PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Procesarea parametrilor de intrare --Eliminați spațiile suplimentare la început și la sfârșit din liniile de text SET @ProductName = LTRIM(RTRIM(@ProductName)); --Adăugați o înregistrare nouă INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Ștergerea unei proceduri stocate în instrucțiunea T-SQL - DROP PROCEDURE

Dacă este necesar, puteți șterge procedura stocată, aceasta se face folosind instrucțiunea PROCEDURA DE RĂDARE.

De exemplu, să ștergem procedura de testare creată de noi.

DROP PROCEDURE TestProcedure

La ștergerea procedurilor stocate, merită să ne amintim că, dacă procedura este referită prin alte proceduri sau instrucțiuni SQL, după ștergerea acesteia, acestea vor eșua cu o eroare, deoarece procedura la care se referă nu mai există.

Am de toate, sper că materialul v-a fost interesant și util, pa!

Este definit conceptul de proceduri stocate. Sunt date exemple de creare, modificare și utilizare a procedurilor stocate cu parametri. Este dată definiția parametrilor de intrare și de ieșire. Sunt date exemple de creare și apelare a procedurilor stocate.

Conceptul de procedură stocată

Proceduri stocate sunt grupuri de instrucțiuni SQL interconectate, a căror utilizare face munca programatorului mai ușoară și mai flexibilă, deoarece pentru a executa procedură stocată este adesea mult mai simplă decât o secvență de instrucțiuni SQL individuale. Procedurile stocate sunt un set de comenzi constând din una sau mai multe instrucțiuni sau funcții SQL și stocate în baza de date într-o formă compilată. Execuție pe baza de date proceduri stocateÎn loc de instrucțiuni SQL individuale, acesta oferă utilizatorului următoarele beneficii:

• operatorii necesari sunt deja in baza de date;
• au trecut cu toţii de scenă analizareși sunt în format executabil; inainte de executarea unei proceduri stocate SQL Server generează un plan de execuție pentru acesta, îl optimizează și îl compilează;
• proceduri stocate a sustine programare modulară, deoarece vă permit să împărțiți sarcinile mari în părți independente, mai mici și ușor de gestionat;
• proceduri stocate poate cauza altora proceduri stocateși funcții;
• proceduri stocate poate fi apelat din alte tipuri de aplicații;
• de obicei, proceduri stocate sunt executate mai rapid decât o secvență de instrucțiuni individuale;
• proceduri stocate mai ușor de utilizat: pot consta din zeci și sute de comenzi, dar pentru a le rula, este suficient să specificați doar numele comenzii dorite. procedură stocată. Acest lucru vă permite să reduceți dimensiunea cererii trimise de la client la server și, prin urmare, încărcarea în rețea.

Stocarea procedurilor în același loc în care sunt executate reduce cantitatea de date transferate în rețea și îmbunătățește performanța generală a sistemului. Aplicație proceduri stocate simplifică întreținerea sistemelor software și aduce modificări la acestea. De obicei, toate constrângerile de integritate sub formă de reguli și algoritmi de procesare a datelor sunt implementate pe serverul bazei de date și sunt disponibile pentru aplicația finală ca un set proceduri stocate, care reprezintă interfața de procesare a datelor. Pentru a asigura integritatea datelor, precum și în scopuri de securitate, aplicația nu are de obicei acces direct la date - toate lucrările cu acestea se fac apelând unul sau altul. proceduri stocate.

Această abordare face foarte ușoară modificarea algoritmilor de procesare a datelor, care devin imediat disponibili pentru toți utilizatorii rețelei și oferă posibilitatea de a extinde sistemul fără a face modificări aplicației în sine: este suficient să schimbați procedură stocată pe serverul bazei de date. Dezvoltatorul nu trebuie să recompileze aplicația, să creeze copii ale acesteia și, de asemenea, să informeze utilizatorii despre necesitatea de a lucra cu noua versiune. Este posibil ca utilizatorii să nu fie conștienți că au fost făcute modificări în sistem.

Proceduri stocate există independent de tabele sau de orice alte obiecte de bază de date. Sunt chemați de programul client, altul procedură stocată sau declanșatorul. Dezvoltatorul poate gestiona drepturile de acces la procedură stocată, permițând sau interzicând executarea acestuia. Schimbați codul procedură stocată permis numai de proprietarul sau de un membru al rolului fix al bazei de date. Dacă este necesar, puteți transfera dreptul de proprietate asupra acestuia de la un utilizator la altul.

Proceduri stocate în mediul MS SQL Server

Când lucrează cu SQL Server, utilizatorii își pot crea propriile proceduri care implementează anumite acțiuni. Proceduri stocate sunt obiecte de bază de date cu drepturi depline și, prin urmare, fiecare dintre ele este stocat într-o anumită bază de date. Apel direct procedură stocată este posibil doar dacă este executat în contextul bazei de date în care se află procedura.

Tipuri de proceduri stocate

Există mai multe tipuri în SQL Server proceduri stocate.

• Sistemică proceduri stocate concepute pentru a efectua diverse acţiuni administrative. Aproape toate acțiunile de administrare a serverului sunt efectuate cu ajutorul lor. Putem spune că sistemul proceduri stocate 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 ale bazelor de date ale utilizatorilor și ale sistemului. Sistemică proceduri stocate sunt prefixate cu sp_ , sunt stocate în baza de date a sistemului și pot fi apelate în contextul oricărei alte baze de date.
• Personalizat proceduri stocate implementează anumite acțiuni. Proceduri stocate- un obiect de bază de date complet. Ca urmare a acestui fapt, fiecare procedură stocată se află într-o anumită bază de date, unde este executat.
• Temporar proceduri stocate există doar pentru o perioadă scurtă de timp, după care sunt distruse automat de server. Ele sunt împărțite în locale și globale. Local temporar proceduri stocate pot fi apelate doar din conexiunea în care sunt create. La crearea unei astfel de proceduri, trebuie să i se dea un nume care începe cu un singur caracter #. Ca toate obiectele temporare, proceduri stocate de acest tip sunt șterse automat când utilizatorul se deconectează, repornește sau oprește serverul. Global temporar proceduri stocate disponibil pentru orice conexiuni la server care au aceeași procedură. Pentru a-l defini, este suficient să-i dai un nume care să înceapă cu caracterele ## . Aceste proceduri sunt șterse când serverul este repornit sau oprit sau când conexiunea în al cărei context au fost create este închisă.

Crearea, modificarea și ștergerea procedurilor stocate

Creare procedură stocată presupune rezolvarea următoarelor sarcini:

• definirea tipului de procedură stocată: temporar sau personalizat. În plus, vă puteți crea propriul sistem procedură stocată, dându-i un nume cu prefixul sp_ și plasându-l în baza de date a sistemului. O astfel de procedură va fi disponibilă în contextul oricărei baze de date de pe serverul local;
• planificarea accesului. În timp ce creați procedură stocată rețineți că va avea aceleași drepturi de acces la obiectele bazei de date ca și utilizatorul care l-a creat;
• definiție parametrii procedurii stocate. La fel ca procedurile incluse în majoritatea limbajelor de programare, proceduri stocate poate avea parametri de intrare și de ieșire;
• dezvoltarea codului procedură stocată. Codul procedurii poate conține o secvență de orice comenzi SQL, inclusiv apelarea altora. proceduri stocate.

Crearea unuia nou și modificarea unuia existent procedură stocată se face cu următoarea comandă:

<определение_процедуры>::= (CREATE | ALTER ) PROC procedure_name [;număr] [(@parameter_name datatype ) [=default] ][,...n] AS sql_statement [...n]

Luați în considerare parametrii acestei comenzi.

Folosind prefixele sp_ ​​, # , ## , procedura creată poate fi definită ca o procedură de sistem sau temporară. După cum puteți vedea din sintaxa comenzii, nu este permis să specificați numele proprietarului căruia îi va aparține procedura creată, precum și numele bazei de date unde ar trebui plasată. Astfel, pentru a găzdui creatul procedură stocatăîntr-o anumită bază de date, trebuie să rulați comanda CREATE PROCEDURE în contextul acelei baze de date. Când este manevrat din corp procedură stocată Numele scurtate pot fi folosite pentru obiectele din aceeași bază de date, adică fără a specifica numele bazei de date. Când doriți să faceți referire la obiecte aflate în alte baze de date, specificarea numelui bazei de date este obligatorie.

Numărul din nume este numărul de identificare procedură stocată, care îl definește în mod unic într-un grup de proceduri. Pentru comoditatea gestionării procedurilor, logic de același tip proceduri stocate pot fi grupate dându-le același nume, dar numere de identificare diferite.

Pentru a trece datele de intrare și de ieșire în fișierul creat procedură stocată pot fi utilizați parametri, ale căror nume, ca și numele variabilelor locale, trebuie să înceapă cu simbolul @. unu procedură stocată Puteți specifica mai multe opțiuni separate prin virgule. Corpul unei proceduri nu trebuie să utilizeze variabile locale ale căror nume sunt aceleași cu numele parametrilor procedurii.

Pentru a determina tipul de date care corespunde parametrul procedurii stocate, orice tip de date SQL este bine, inclusiv cele definite de utilizator. Cu toate acestea, tipul de date CURSOR poate fi folosit doar ca parametrul de ieșire procedură stocată, adică cu cuvântul cheie OUTPUT .

Prezența cuvântului cheie OUTPUT înseamnă că parametrul corespunzător este destinat să returneze date de la procedură stocată. Cu toate acestea, acest lucru nu înseamnă deloc că parametrul nu este potrivit pentru transmiterea de valori către procedură stocată. Specificarea cuvântului cheie OUTPUT indică serverului să iasă din procedură stocată atribuiți valoarea curentă a parametrului variabilei locale care a fost specificată atunci când procedura a fost apelată ca valoare a parametrului. Rețineți că atunci când specificați cuvântul cheie OUTPUT, valoarea parametrului corespunzător la apelarea procedurii poate fi setată numai folosind o variabilă locală. Orice expresii sau constante permise pentru parametri normali nu sunt permise.

Cuvântul cheie VARYING este folosit împreună cu parametrul OUTPUT, care este de tip CURSOR . Acesta definește asta parametrul de ieșire va fi setul rezultat.

Cuvântul cheie DEFAULT este valoarea corespunzătoare setare implicită. Astfel, atunci când apelați o procedură, nu puteți specifica în mod explicit valoarea parametrului corespunzător.

Deoarece serverul memorează în cache planul de execuție a interogării și codul compilat, data viitoare când procedura este apelată, vor fi utilizate valorile deja pregătite. Cu toate acestea, în unele cazuri este încă necesară recompilarea codului de procedură. Specificarea cuvântului cheie RECOMPILE instruiește sistemul să creeze un plan de execuție procedură stocată de fiecare dată când este chemat.

Parametrul FOR REPLICATION este necesar la replicarea datelor și includerea celor create procedură stocată ca articol într-o publicație.

Cuvântul cheie ENCRYPTION instruiește serverul să cripteze codul procedură stocată, care poate oferi protecție împotriva utilizării algoritmilor de drepturi de autor care implementează lucrarea procedură stocată.

Cuvântul cheie AS este plasat la începutul corpului real procedură stocată, adică un set de comenzi SQL, cu ajutorul cărora va fi implementată cutare sau cutare acțiune. Aproape toate comenzile SQL pot fi folosite în corpul procedurii, tranzacțiile pot fi declarate, blocările pot fi setate și altele pot fi apelate. proceduri stocate. ieșire din procedură stocată se poate face cu comanda RETURN.

Ștergerea unei proceduri stocate efectuat prin comanda:

DROP PROCEDURE (nume_procedură) [,...n]

Executarea unei proceduri stocate

Pentru executarea unei proceduri stocate se foloseste comanda:

[[ EXEC [ UTE] nume_procedură [;număr] [[@nume_parametru=](valoare | @nume_variabilă) |][,...n]

Dacă apelul procedură stocată nu este singura comandă din pachet, atunci este necesară prezența comenzii EXECUTE. Mai mult, această comandă este necesară pentru a apela o procedură din corpul altei proceduri sau declanșatoare.

Utilizarea cuvântului cheie OUTPUT într-un apel de procedură este permisă numai pentru parametrii care au fost declarați când crearea unei proceduri cu cuvântul cheie OUTPUT.

Când cuvântul cheie DEFAULT este specificat pentru un apel de procedură, se va folosi cuvântul cheie DEFAULT. valoare implicită. Desigur, cuvântul DEFAULT specificat este permis numai pentru acei parametri pentru care este definit valoare implicită.

Din sintaxa comenzii EXECUTE, puteți vedea că numele parametrilor pot fi omise la apelarea unei proceduri. Cu toate acestea, în acest caz, utilizatorul trebuie să specifice valorile parametrilor în aceeași ordine în care au fost listați atunci când crearea unei proceduri. Atribuiți parametrului valoare implicită, pur și simplu săriți-l când enumerarea nu este posibilă. Dacă este necesară omiterea parametrilor pentru care valoare implicită, este suficient să specificați în mod explicit numele parametrilor atunci când apelați procedură stocată. Mai mult, în acest fel, puteți lista parametrii și valorile acestora în orice ordine.

Rețineți că atunci când este apelată o procedură, sunt specificate fie nume de parametri cu valori, fie numai valori fără nume de parametru. Combinația lor nu este permisă.

Exemplul 12.1. Procedura fara parametri. Elaborați o procedură pentru obținerea denumirilor și prețurilor bunurilor achiziționate de Ivanov.

CREATE PROC my_proc1 AS SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Articol INNER JOIN Tranzacție ON Item.ItemId=Trade.ItemId) ON Customer.CustomerCode=Trade.CustomerCode WHERE Client .Lastname='Ivanov' Exemplul 12.1. Procedura de obținere a denumirilor și prețurilor bunurilor achiziționate de Ivanov.

Pentru apel la procedura comenzile pot fi folosite:

EXEC my_proc1 sau my_proc1

Procedura returnează un set de date.

Exemplul 12.2. Procedura fara parametri. Creați o procedură pentru a reduce prețul unui articol de clasa întâi cu 10%.

Pentru apel la procedura comenzile pot fi folosite:

EXEC my_proc2 sau my_proc2

Procedura nu returnează date.

Exemplul 12.3. Procedura cu parametrul de intrare. Creați o procedură pentru a obține numele și prețurile articolelor achiziționate de un anumit client.

CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.LastName FROM Customer INNER JOIN (Articol INNER JOIN Trade ON Item.ItemID=Trade.ItemID) ON Client.CustomerID =Deal.CustomerCode WHERE Client.Nume=@k Exemplul 12.3. O procedură pentru obținerea numelor și prețurilor articolelor achiziționate de un anumit client.

Pentru apel la procedura comenzile pot fi folosite:

EXEC my_proc3 "Ivanov" sau my_proc3 @k="Ivanov"

Exemplul 12.4.. Creați o procedură pentru a reduce prețul unui produs de un anumit tip în conformitate cu procentul specificat.

Pentru apel la procedura comenzile pot fi folosite:

EXEC my_proc4 "Waffle", 0.05 sau EXEC my_proc4 @t="Waffle", @p=0.05

Exemplul 12.5. Procedura cu parametrii de intrareși valorile implicite. Creați o procedură pentru a reduce prețul unui produs de un anumit tip în conformitate cu procentul specificat.

CREATE PROC my_proc5 @t VARCHAR(20)='Candy', @p FLOAT=0.1 AS UPDATE Item SET Preț=Preț*(1-@p) WHERE Tip=@t Exemplul 12.5. Procedura cu parametrii de intrare și valorile implicite. Creați o procedură pentru a reduce prețul unui produs de un anumit tip în conformitate cu procentul specificat.

Pentru apel la procedura comenzile pot fi folosite:

EXEC my_proc5 "Waffle", 0.05 sau EXEC my_proc5 @t="Waffle", @p=0.05 sau EXEC my_proc5 @p=0.05

În acest caz, prețul dulciurilor scade (valoarea tipului nu este specificată la apelarea procedurii și este luată implicit).

În acest din urmă caz, ambii parametri (atât tipul, cât și procentul) nu sunt specificați la apelarea procedurii, valorile lor sunt luate implicit.

Exemplul 12.6. Procedura cu parametrii de intrare si iesire. Creați o procedură pentru a determina costul total al mărfurilor vândute într-o anumită lună.

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Item.Price*Trade.Quantity) FROM Item INNER JOIN Trade ON Item.ItemID=Trade.ItemID GROUP BY Month(Trade.Date) HAVING Month( Deal.Date)=@m Exemplul 12.6. Procedura cu parametrii de intrare si iesire. Creați o procedură pentru a determina costul total al mărfurilor vândute într-o anumită lună.

Pentru apel la procedura comenzile pot fi folosite:

DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st

Acest bloc de comenzi vă permite să determinați costul mărfurilor vândute în ianuarie ( parametrul de intrare luna este setată la 1).

Creați o procedură pentru a determina cantitatea totală de bunuri achiziționate de firma în care lucrează un anumit angajat.

În primul rând, vom dezvolta o procedură pentru determinarea companiei în care lucrează angajatul.

Exemplul 12.7. Utilizare proceduri imbricate. Creați o procedură pentru a determina cantitatea totală de bunuri achiziționate de firma în care lucrează un anumit angajat.

Apoi vom crea o procedură care numără cantitatea totală de bunuri achiziționate de firma care ne interesează.

CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Trade.Quantity) FROM Client INNER JOIN Trade ON Client.ClientCode= Deal.ClientCode GROUP BY Client.Company AVÂND Client.Company=@firmă Exemplul 12.7. Creați o procedură pentru a determina cantitatea totală de bunuri achiziționate de firma în care lucrează un anumit angajat.

Procedura este apelată folosind comanda:

DECLARE @k INT EXEC my_proc8 ‘Ivanov’,@k OUTPUT SELECT @k

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 de proceduri și de a obține 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 la 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 în jurnal și/sau citesc î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 procedurii 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 să fie 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.

Exemplul de mai jos 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, de asemenea, 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 unor proceduri stocate temporare poate fi evitarea executării repetitive a unui anumit grup de instrucțiuni atunci când vă conectați 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ă la terminarea ultimei conexiuni pe care rulează (de obicei conexiunea creatorului procedurii).

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 a procesa date în 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; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Angajați șters: " + 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 folosind 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ă în 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ă știți locația fișierului .dll compilat (de obicei situat în folderul Debug al proiectului).

UTILIZAȚI SampleDb; CREAȚI ANSABLUL 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 baze 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_clasă.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.

Declaratie de procedura

CREAȚI PROCEDURA [({IN|OUT|INOUT} [,…])]
[SET DE REZULTATE DINAMIC ]
ÎNCEPE [ATOMIC]

Sfârşit

Cuvinte cheie
. IN (Intrare) – parametru de intrare
. OUT (Ieșire) – parametru de ieșire
. INOUT - intrare și ieșire, precum și un câmp (fără parametri)
. DYNAMIC RESULT SET indică faptul că procedura poate deschide numărul specificat de cursoare care vor rămâne deschise după ce procedura revine

Note
Nu este recomandat să folosiți mulți parametri în procedurile stocate (în primul rând numere mari și șiruri de caractere) din cauza congestionării rețelei și a stivei. În practică, dialectele existente Transact-SQL, PL/SQL și Informix prezintă abateri semnificative de la standard, atât în ​​declararea parametrilor, cât și în utilizarea, declararea variabilelor și invocarea subrutinei. Microsoft recomandă utilizarea următoarei aproximări pentru a estima dimensiunea memoriei cache a procedurilor stocate:
=(utilizatori concurenți maximi)*(dimensiunea cea mai mare a planului de execuție)*1.25. Determinarea dimensiunii planului de execuție în pagini se poate face cu ajutorul comenzii: DBCC MEMUSAGE.

Apel de procedură

În multe SGBD existente, procedurile stocate sunt apelate folosind instrucțiunea:

EXECUTĂ PROCEDURA [(][)]

Notă: Un apel la procedurile stocate poate fi efectuat dintr-o aplicație, o altă procedură stocată sau interactiv.

Exemplu de declarație de procedură

CREATE PROCEDURE Proc1 AS //declararea unei proceduri
DECLARE Cur1 CURSOR FOR SELECT SNname, City FROM SalesPeople WHERE Rating>200 //declara a cursor
OPEN Cur1 //deschide cursorul
FETCH NEXT FROM Cur1 //citește datele de pe cursor
ÎN CAZUL @@Fetch_Status=0
ÎNCEPE
FETCH NEXT FROM Cur1
Sfârşit
CLOSE Cur1 //închide cursorul
DEALLOCATE Cur1
EXECUTE Proc1 //rulați procedura

Polimorfism
Două subprograme cu același nume pot fi create în aceeași schemă dacă parametrii celor două subprograme sunt suficient de diferiți unul de celălalt încât să poată fi distinși. Pentru a face distincția între două rutine cu același nume în aceeași schemă, fiecare primește un nume alternativ și unic (nume specific). Un astfel de nume poate fi specificat în mod explicit atunci când este definită subrutina. Când apelați subrutine cu mai multe nume identice, determinarea subrutinei dorite se realizează în mai mulți pași:
. Inițial, toate procedurile cu numele specificat sunt definite, iar dacă nu există, atunci toate funcțiile cu numele specificat.
. Pentru o analiză ulterioară, rămân doar acele rutine pentru care utilizatorul dat are privilegiul de execuție (EXECUTE).
. Pentru ei, sunt selectați cei al căror număr de parametri corespunde numărului de argumente de apel. Sunt verificate tipurile de date specificate ale parametrilor și pozițiile acestora.
. Dacă au rămas mai multe subrutine, atunci este selectată cea cu numele de calificare mai scurt.
În practică, în Oracle, polimorfismul este suportat pentru funcțiile declarate doar într-un pachet, DB@ - în diferite scheme, iar supraîncărcarea este interzisă în Sybase și MS SQL Server.

Îndepărtarea și modificarea procedurilor
Următoarea instrucțiune este utilizată pentru a șterge o procedură:

Pentru a modifica o procedură, utilizați instrucțiunea:

PROCEDURA DE ALTERARE [([{IN|OUT|INOUT}])]
ÎNCEPE [ATOMIC]

Sfârşit

Privilegii de a executa proceduri

GRANT EXECUTE ON LA |PUBLIC [CU OPȚIUNE DE GRANT]

Proceduri de sistem
Multe SGBD (inclusiv SQL Server) au un anumit set de proceduri stocate în sistem încorporate pe care le puteți utiliza în propriile scopuri.