Se ia în considerare o situație în care procedurile stocate pot degrada performanța interogării.
La compilarea procedurilor stocate în MS SQL Server 2000, procedurile stocate sunt plasate în memoria cache procedurală, ceea ce poate îmbunătăți performanța în timpul execuției lor prin eliminarea nevoii 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 operatori care alcătuiesc codul procedurii este compilat, respectiv, dacă procedura stocată compilată este stocată în cache, atunci planul de execuție al acesteia este stocat î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.
Să creăm o bază de date separată pentru experiment.
CREAȚI BAZĂ DE DATE test_sp_perf
ACTIVAT (NAME = „date_test”, FILENAME = „c: \ temp \ date_test”, SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
LOG ON (NUME = „test_log”, FILENAME = „c: \ temp \ test_log”, SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1 Mb)
PASUL 2. Crearea unui tabel.
CREATE TABLE sp_perf_test (column1 int, column2 char (5000))
PASUL 3. Umplerea tabelului cu linii de testare. Rândurile duplicat sunt adăugate în mod intenționat la tabel. 10.000 de linii numerotate de la 1 la 10.000 și 10.000 de rânduri numerotate 50.000.
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 caracter (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 (*) DIN 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 la fel pentru toate apelurile.
CREATE INDEX NONCLUSTERED CL_perf_test ON sp_perf_test (coloana1)
Merge
PASUL 5. Crearea unei proceduri stocate. Procedura pur și simplu execută o instrucțiune SELECT cu o condiție.
CREATE PROC proc1 (@param int)
LA FEL DE
SELECTAȚI coloana1, coloana2 FROM sp_perf_test WHERE [email protected]
Merge
PASUL 6. Rularea unei proceduri stocate. La pornirea unei proceduri vulnerabile, se folosește în mod special un parametru selectiv. Ca rezultat al procedurii, obținem 1 linie. Planul de execuție indică utilizarea unui index non-cluster deoarece interogarea este selectivă și este cea mai bună modalitate de a prelua un rând. O procedură optimizată pentru preluarea unui rând este stocată în memoria cache procedurală.
EXEC proc1 1234
Merge
PASUL 7. Rularea unei proceduri stocate cu un parametru neselectiv. Ca parametru este folosită valoarea 50.000. Liniile cu o astfel de valoare a primei coloane de aproximativ 10.000, respectiv, folosind un index neclustrat și operația de căutare a marcajelor este ineficientă, dar deoarece codul compilat cu planul de execuție este stocat în cache procedural, va fi folosit. Planul de execuție arată acest lucru, precum și faptul că operația de căutare a marcajelor a fost efectuată pentru 9999 de linii.
EXEC proc1 50.000
Merge
PASUL 8. Preluarea rândurilor cu primul câmp egal cu 50 000. Executarea unei interogări separate va optimiza și compila interogarea cu valoarea specifică a primei coloane. Ca rezultat, optimizatorul de interogări detectează că câmpul este duplicat de multe ori și decide să utilizeze operația de scanare a tabelului, care în acest caz este mult mai eficientă decât utilizarea unui index neclustered.
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 procedurile stocate care operează pe rezultate cu un număr variabil de linii și folosesc planuri de execuție diferite.
Puteți utiliza scriptul pentru a repeta experimentul pe un nou server MS SQL.
Tipuri de proceduri stocate
Există mai multe tipuri în SQL Server proceduri stocate.
- Sistemică proceduri stocate sunt 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 atât ale utilizatorilor, cât ș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 efectua anumite actiuni. Proceduri stocate- un obiect de bază de date cu drepturi depline. Drept urmare, fiecare procedură stocată se află într-o anumită bază de date, unde este executat.
- Temporar proceduri stocate există doar de ceva 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 au fost create. Când creați o astfel de procedură, trebuie să îi dați un nume care începe cu un singur caracter #. Ca toate obiectele temporare, proceduri stocate de acest tip sunt șterse automat atunci 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, trebuie doar să îi dai un nume care începe cu simbolurile ##. Aceste proceduri sunt șterse atunci când serverul este repornit sau oprit, sau când conexiunea în contextul căreia au fost create este închisă.
Crearea, modificarea și ștergerea procedurilor stocate
Creare procedură stocată presupune rezolvarea următoarelor sarcini:
- definirea tipului de creat procedură stocată: temporar sau personalizat. În plus, vă puteți crea propriul sistem procedură stocată dându-i un nume prefixat cu sp_ și plasându-l în baza de date a sistemului. Această procedură va fi disponibilă în contextul oricărei baze de date de pe serverul local;
- planificarea drepturilor de acces. În timp ce creați procedură stocată trebuie avut în vedere că va avea aceleași drepturi de acces la obiectele bazei de date ca și utilizatorul care a creat-o;
- definiție parametrii procedurii stocate... Ca și procedurile găsite în majoritatea limbajelor de programare, proceduri stocate poate avea parametri de intrare și de ieșire;
- dezvoltarea codului procedură stocată... Codul de procedură poate conține o secvență de orice comenzi SQL, inclusiv apeluri către alte persoane. proceduri stocate.
Crearea unuia nou și modificarea unuia existent procedură stocată se face cu următoarea comandă:
<определение_процедуры>:: = (CREATE | ALTER) procedure_name [; număr] [(@ parameter_name datatype) [= implicit]] [, ... n] AS sql_operator [... n]
Să luăm în considerare parametrii acestei comenzi.
Folosind prefixele sp_, #, ##, procedura creată poate fi definită ca sistem sau temporară. După cum puteți vedea din sintaxa comenzii, nu este permis să specificați numele proprietarului care va deține procedura creată, precum și numele bazei de date unde ar trebui să fie localizată. Astfel, pentru a plasa creatul procedură stocată pe o anumită bază de date, trebuie să rulați comanda CREATE PROCEDURE în contextul acelei baze de date. La manipularea în afara corpului procedură stocată puteți folosi nume prescurtate pentru obiectele aceleiași baze de date, adică fără a specifica numele bazei de date. Când trebuie să vă referiți la obiecte aflate în alte baze de date, este necesară specificarea numelui bazei de date.
Numărul din nume este numărul de identificare procedură stocată, care îl identifică î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 transfera datele de intrare și de ieșire în formatul 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ă pot fi specificați mulți parametri, separați prin virgule. Corpul unei proceduri nu ar trebui să utilizeze variabile locale ale căror nume sunt aceleași cu numele parametrilor acestei proceduri.
Pentru a determina tipul de date care vor avea corespunzătoare parametrul procedurii stocate, orice tipuri de date SQL, inclusiv cele definite de utilizator, vor funcționa. Cu toate acestea, tipul de date CURSOR poate fi folosit doar ca parametrul de ieșire procedură stocată, adică specificând 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ă procedură stocată atribuiți valoarea curentă a parametrului variabilei locale care a fost specificată ca valoare a parametrului la apelarea procedurii. Rețineți că atunci când este specificat cuvântul cheie OUTPUT, valoarea parametrului corespunzător la apelarea unei proceduri poate fi setată numai folosind o variabilă locală. Nu aveți voie să utilizați expresii sau constante care sunt valide pentru parametri normali.
Cuvântul cheie VARYING este folosit împreună cu
Procedura stocată (ing. procedura stocată) este un obiect de program de bază de date numit. Există mai multe tipuri de proceduri stocate în SQL Server.
Proceduri stocate de sistem (ing. sistem stocat procedura) sunt furnizate de dezvoltatorii DBMS și sunt utilizate pentru a efectua acțiuni cu catalogul de sistem sau pentru a obține informații despre sistem. Numele lor încep de obicei cu prefixul „sp_”. Procedurile stocate de toate tipurile sunt invocate cu comanda EXECUTE, care poate fi abreviată ca EXEC. De exemplu, procedura stocată sp_helplogins, rulată fără parametri, generează două rapoarte privind numele conturilor (ing. login) și utilizatorii corespunzători acestora din fiecare bază de date (ing. utilizatori).
EXEC sp_helplogins;
Pentru a da o idee despre acțiunile efectuate prin utilizarea procedurilor stocate în sistem, în tabel. 10.6 oferă câteva exemple. Există peste o mie de proceduri stocate de sistem în SQL Server.
Tabelul 10.6
Exemple de proceduri stocate de sistem SQL Server
Utilizatorul poate crea proceduri stocate în bazele de date utilizator și în bazele de date pentru obiecte temporare. În acest din urmă caz, procedura stocată va fi temporal. Ca și în cazul tabelelor temporare, numele unei proceduri stocate temporare trebuie să înceapă cu un prefix „#” dacă este o procedură stocată temporară locală, sau cu „##” dacă este una globală. Procedura temporară locală poate fi utilizată numai în cadrul conexiunii în care a fost creată, cea globală - și în cadrul altor conexiuni.
Obiectele programabile SQL Server pot fi create folosind instrumente sau ansambluri Transact-SQL (ing. asamblare) în Common Language Runtime (CRL) al Microsoft .Net Framework. În acest tutorial, va fi luată în considerare doar prima metodă.
Procedurile stocate sunt create folosind instrucțiunea CREATE PROCEDURE (poate fi prescurtată în PROC), al cărei format este prezentat mai jos:
CREATE (PROC I PROCEDURE) proc_name [; număr]
[(gparameter data_type)
[„Implicit] |
[CU
[PENTRU REPLICAȚIE]
AS ([BEGIN] sql_statement [;] [... n] [END])
Dacă o procedură stocată (sau declanșator, funcție, vizualizare) este creată cu opțiunea CRIPTARE, codul acesteia este convertit astfel încât textul să devină ilizibil. În același timp, așa cum s-a menționat în, algoritmul utilizat a fost transferat din versiunile anterioare ale SQL Server și nu poate fi considerat un algoritm de protecție de încredere - există utilități care vă permit să efectuați rapid conversia inversă.
Opțiunea RECOMPILE indică sistemului să recompileze textul de fiecare dată când procedura este apelată. În mod normal, procedura compilată la prima pornire este salvată în cache, ceea ce poate îmbunătăți performanța.
EXECUTE AS definește contextul de securitate în care ar trebui să fie executată procedura. Apoi una dintre valorile f CALLER | SINE | PROPRIETAR | "nume de utilizator"). CALLER este implicit și înseamnă că codul va fi executat în contextul de securitate al utilizatorului care apelează acest modul. În consecință, utilizatorul trebuie să aibă permisiuni nu numai pentru obiectul programat în sine, ci și pentru alte obiecte de bază de date afectate de acesta. EXECUTE AS SELF înseamnă utilizarea contextului utilizatorului pentru a crea sau a modifica un obiect programabil. OWNER indică faptul că codul va fi executat în contextul actualului proprietar al procedurii. Dacă nu este definit niciun proprietar pentru acesta, atunci se înțelege proprietarul schemei căreia îi aparține. EXECUTE AS „nume_utilizator” vă permite să specificați în mod explicit numele de utilizator (între ghilimele simple).
Parametrii pot fi specificați pentru procedură. Acestea sunt variabile locale utilizate pentru a transmite valori către procedură. Dacă un parametru este declarat cu cuvântul cheie OUTPUT (sau pe scurt OUT), acesta este un parametru de ieșire: valoarea specificată în procedură după încheierea acesteia poate fi folosită de programul care a apelat procedura. Cuvântul cheie READONLY înseamnă că valoarea parametrului nu poate fi modificată în cadrul procedurii stocate.
Parametrilor li se pot atribui valori, dar implicit, care vor fi folosite dacă valoarea parametrului nu este specificată în mod explicit atunci când procedura este apelată. Să luăm în considerare un exemplu:
CREATE PROC surma (@ a int, @b int = 0,
© rezultat int IEȘIRE) AS
SET @ rezultat = 0a + 0b
Am creat o procedură cu trei parametri, iar parametrul @b are valoarea implicită 0, iar parametrul @result este un parametru de ieșire: prin intermediul acestuia, valoarea este returnată programului apelant. Acțiunile efectuate sunt destul de simple - parametrul de ieșire primește valoarea sumei celor două intrări.
Când lucrați în SQL Server Management Studio, procedura stocată creată poate fi găsită în secțiunea Obiecte DB programabile (ing. Programabilitate) în subsecțiunea pentru procedurile stocate (Figura 10.2).
Când apelați o procedură, puteți utiliza atât variabile, cât și constante ca parametri de intrare. Să ne uităm la două exemple. În primul, parametrii de intrare ai procedurii sunt setați explicit prin constante; cuvântul cheie OUTPUT este specificat pentru parametrul de ieșire din apel. În a doua opțiune, valoarea variabilei este folosită ca prim parametru de intrare, iar pentru al doilea parametru, folosind cuvântul cheie DEFAULT, se indică faptul că trebuie utilizată valoarea implicită:
Orez. 10.2.
DECLARE @ cu int;
EXEC summa 10.5, @ c IEȘIRE;
PRINT 0c; - 15 vor fi afișate
DECLARE Gi int = 5;
- atunci când apelați, utilizați valoarea implicită
EXEC summa Gi, DEFAULT, 0с IEȘIRE;
PRINT 0c; - 5 vor fi afișate
Să luăm acum în considerare un exemplu cu analiza codului de returnare cu care se încheie procedura. Să fie necesar să se calculeze câte cărți din tabelul Bookl au fost publicate într-un anumit interval de ani. În plus, dacă anul inițial s-a dovedit a fi mai mare decât anul final, procedura returnează „1” și nu contează, în caz contrar, numărăm numărul de cărți și returnăm 0:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
DACĂ 0FirsYear> 0LastYear RETURN 1
SET @ rezultat = (SELECTARE CONT (*) FROM dbo.Bookl
UNDE ÎNTRE 0FirsYear SI 0LastYear);
Luați în considerare o variantă de apelare a acestei proceduri, în care codul de retur este stocat în variabila întreagă 0ret, după care este analizată valoarea acesteia (în acest caz, va fi 1). Funcția CAST utilizată în operatorul PRINT este utilizată pentru a converti valoarea variabilei întregi Gres într-un tip șir:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret = l PRINT „Anul de început este mai mare decât anul de sfârșit”
PRINT „Numărul de cărți” + CAST (Gres ca varchar (20))
Procedurile stocate nu pot doar să citească date dintr-un tabel, ci și să modifice date și chiar să creeze tabele și o serie de alte obiecte de bază de date.
Cu toate acestea, nu puteți crea scheme, funcții, declanșatoare, proceduri și vederi dintr-o procedură stocată.
Următorul exemplu ilustrează atât aceste capabilități, cât și domeniul de aplicare pentru obiectele temporare. Procedura stocată de mai jos verifică existența tabelului temporar # thab2; dacă acest tabel nu există, atunci îl creează. După aceea, valorile a două coloane sunt introduse în tabelul # Tab2, iar conținutul tabelului este afișat prin instrucțiunea SELECT:
CREATE PROC My_Procl (@id int, @name varchar (30))
DACĂ OBJECT_ID ("tempdb.dbo. # Tab21) ESTE NULL
INSERT INTO dbo. # Tab2 (id, name) VALUES (0id, 0name)
SELECTAȚI * FROM dbo. # Tab2 –№1
Înainte de primul apel al procedurii stocate, vom crea tabelul temporar # Thab2 folosit în ea. Acordați atenție operatorului EXEC. În exemplele anterioare, parametrii au fost trecuți la procedura „prin poziție”, dar în acest caz, se folosește un alt format pentru transmiterea parametrilor - „prin nume”, numele parametrului și valoarea acestuia sunt indicate în mod explicit:
CREATE TABLE dbo. # Tab2 (id int, nume varchar (30));
EXEC My_Procl 0name = "lvan", 0id = 2;
SELECTAȚI * FROM dbo. # Tab2; –№2
În exemplul de mai sus, instrucțiunea SELECT va fi executată de două ori: prima dată - în cadrul procedurii, a doua oară - din fragmentul de cod apelant (marcat cu comentariul „# 2”).
Înainte de al doilea apel al procedurii, vom șterge tabelul temporar # Tab2. Apoi tabelul temporar cu același nume va fi creat din procedura stocată:
DROP TABLE dbo. # Tab2;
EXEC My_Procl 0name = "Ivan", 0id = 2;
SELECTAȚI * FROM dbo. # Tab2; –№2
În acest caz, numai instrucțiunea SELECT din cadrul procedurii (cu comentariul „Xa 1”) va afișa datele. SELECT „# 2” va avea ca rezultat o eroare, deoarece tabelul temporar creat în procedura stocată a fost deja șters din tempdb în momentul în care procedura revine.
Puteți renunța la o procedură stocată folosind instrucțiunea DROP PROCEDURE. Formatul său este prezentat mai jos. Un operator poate șterge mai multe proceduri stocate, listându-le separate prin virgule:
DROP (PROCEDURA PROC I) (procedura) [
De exemplu, să eliminăm procedura creată anterior suma:
DROP PROC summa;
Puteți face modificări la o procedură existentă (și, de fapt, o puteți modifica) folosind instrucțiunea ALTER PROCEDURE (admisibil
abrevierea PROC). Cu excepția cuvântului cheie ALTER, formatul instrucțiunii este similar cu cel al CREATE PROCEDURE. De exemplu, să schimbăm procedura dbo. rownum setându-i o opțiune de execuție în contextul de securitate al proprietarului:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int IEȘIRE)
WITH EXECUTE AS Owner este o opțiune instalabilă
DACĂ 0FirsYear> 0LastYear RETURN 1 ELSE BEGIN
SET 0result = (SELECTARE CONT (*) FROM dbo.Bookl
UNDE ÎNTRE SFirsYear ȘI SLlastYear);
În unele cazuri, poate fi necesar să se formeze dinamic o comandă și să o execute pe serverul bazei de date. Această sarcină poate fi rezolvată și folosind operatorul EXEC. Exemplul de mai jos preia înregistrări din tabelul Bookl cu condiția ca atributul An să fie egal cu valoarea setată folosind o variabilă:
DECLARE 0y int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE =" [email protected]) ;
Executarea instrucțiunilor generate dinamic creează condiții prealabile pentru implementarea atacurilor computerizate, cum ar fi „injectarea SQL” (ing. injecție SQL). Esența atacului este că atacatorul își injectează propriul cod SQL într-o interogare generată dinamic. Acest lucru se întâmplă de obicei atunci când parametrii înlocuiți sunt preluați din rezultatele introduse de utilizator.
Să schimbăm puțin exemplul anterior:
DECLARE 0y varchar (100);
SET 0y = "2OOO"; - Am primit asta de la utilizator
Dacă presupunem că am primit valoarea șirului atribuită în instrucțiunea SET de la utilizator (indiferent cum, de exemplu, printr-o aplicație web), atunci exemplul ilustrează comportamentul „regulat” al codului nostru.
DECLARE 0y varchar (100);
SET 0y = "2000; DELETE FROM dbo.Book2"; - injectare
EXEC ("SELECT * FROM dbo.Book2 WHERE =" + 0y);
În astfel de cazuri, este recomandat să utilizați procedura stocată de sistem sp_executcsql ori de câte ori este posibil, care vă permite să controlați tipul de parametri, care este una dintre barierele în calea injectării SQL. Fără să luăm în considerare formatul său în detaliu, să analizăm un exemplu similar cu cel prezentat mai devreme:
EXECUTE sp_executesql
N „SELECT * FROM dbo.Bookl WHERE = 0y”,
Acesta specifică în mod explicit tipul de parametru utilizat în interogare și va fi monitorizat de SQL Server în timpul execuției. Litera „N” din fața ghilimelelor indică faptul că aceasta este o constantă literală Unicode, așa cum este cerut de procedură. Un parametru i se poate atribui nu numai o valoare constantă, ci și valoarea unei alte variabile.
Procedură stocată este un tip special de lot de instrucțiuni Transact-SQL create 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 menținute pe partea serverului pentru a îmbunătăți performanța și repetabilitatea.
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 accepta argumentele adecvate 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 asociat.
Procedura 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ă un beneficiu important prin faptul că elimină (în aproape toate cazurile) recompilarea procedurii și oferă îmbunătățiri corespunzătoare de performanță. 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ă care are o dimensiune 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 acțiuni cu tabele de baze de date.
Utilizarea procedurilor stocate oferă un nivel de control al securității care depășește semnificativ nivelul de securitate 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 într-o procedură stocată dată, așa cum este descris în secțiunea următoare.
Procedurile stocate care generează jurnalele pentru scrieri și/sau citiri pe tabele oferă securitate suplimentară pentru baza de date. Utilizând aceste 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ă ca proprietar al procedurii stocate generate. Parametrul proc_name specifică numele procedurii stocate. Parametrul @ param1 este un parametru de procedură (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 definește valoarea implicită pentru parametrul de procedură corespunzător. (Valoarea implicită poate fi, de asemenea, NULL.)
Opțiunea IEȘIRE indică faptul că un parametru de procedură este un parametru returnabil care 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, procedura stocată trebuie compilată de fiecare dată când este apelată, atunci când declarați procedura, utilizați optiunea CU RECOMPILE... Utilizarea opțiunii WITH RECOMPILE anulează unul dintre cele mai importante avantaje ale procedurilor stocate: îmbunătățirea performanței dintr-o singură compilare. Prin urmare, opțiunea WITH RECOMPILE ar trebui utilizată numai atunci când se efectuează modificări frecvente la obiectele bazei de date utilizate de procedura stocată.
clauza EXECUTE AS definește contextul de securitate în care procedura stocată ar trebui să se execute după ce este apelată. Prin setarea acestui context, puteți utiliza Motorul de bază de date pentru a 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. Dar 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 * @ procent / 100;
După cum am menționat mai devreme, pentru a separa două pachete, utilizați GO instrucțiune... Instrucțiunea CREATE PROCEDURE nu poate fi combinată cu alte instrucțiuni Transact-SQL din același pachet. Procedura stocată IncreaseBudget crește bugetele pentru toate proiectele cu un anumit număr de procente, specificat de parametrul @percent. Procedura definește, de asemenea, o valoare procentuală implicită (5), care este utilizată dacă acest argument nu este prezent în timpul 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 adecvate sau chiar să vă conectați 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. Un motiv pentru crearea procedurilor stocate temporare poate fi evitarea execuției 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 aceasta, numele procedurii locale este specificat cu un singur simbol # (#proc_name), iar numele procedurii globale este specificat cu un 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ă. Procedura temporară globală poate fi executată de toți utilizatorii, dar numai până la încheierea ultimei conexiuni în care este executată (de obicei conexiunea creatorului procedurii).
Ciclul de viață al unei proceduri stocate constă din două etape: crearea și executarea acesteia. Fiecare procedură este creată o dată și executată în mod repetat. Procedura stocată este executată de EXECUTE declarații un utilizator care deține o procedură sau are permisiunea EXECUTE pentru a accesa procedura respectivă. 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 semnificație logică ca și parametrii cu același nume pentru instrucțiunea CREATE PROCEDURE. 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 nenumiți trebuie furnizate în ordinea în care sunt definiți în instrucțiunea CREATE PROCEDURE.
clauza DEFAULT furnizează valori implicite pentru un parametru de procedură care a fost specificat în definiția procedurii. O eroare apare atunci când o procedură așteaptă o valoare pentru un parametru pentru care nu a fost definită nicio valoare implicită și nu este specificat niciun parametru sau este specificat cuvântul cheie DEFAULT.
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 pentru toate proiectele cu 10%.
Exemplul de mai jos 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 procedură stocată similară poate fi utilizată în cadrul unei definiții de declanșare care impune de fapt integritatea referențială.
Exemplul de mai jos 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, @ [email protected] IEȘIRE; PRINT N "Angajați șterși:" + convert (nvarchar (30), @quantityDeleteEmployee);
Această procedură numără numărul de proiecte la care este ocupat angajatul cu număr de personal @empId și atribuie valoarea rezultată parametrului © contor. 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ă o valoare sistemului. Prin urmare, parametrul @counter trebuie specificat atât în opțiunea OUTPUT la declararea unei proceduri, cât și în instrucțiunea EXECUTE la apelarea acesteia.
Clauza WITH RESULTS SETS a instrucțiunii EXECUTE
În SQL Server 2012, este introdusă instrucțiunea EXECUTE WITH RESULTS SETS clauza, prin care, atunci când sunt îndeplinite anumite condiții, puteți modifica 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 acestuia. Efectuarea acestei proceduri afișează un tabel cu două coloane, antetele cărora coincid cu numele coloanelor corespunzătoare din tabelul bazei de date, adică. Id și Nume. SQL Server 2012 utilizează noua clauză WITH RESULTS SETS pentru a modifica titlurile coloanelor rezultate (precum și tipul lor de date). Aplicarea acestei propuneri 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 procedurii stocate folosind clauza WITH RESULT SETS din instrucțiunea EXECUTE vă permite să schimbați numele și tipul de date al coloanelor din setul de rezultate returnat 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. ALTER PROCEDURE este de obicei folosită 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 cu același nume pentru instrucțiunea CREATE PROCEDURE. Scopul principal al utilizării acestei declarații este de a evita suprascrierea drepturilor existente de procedură stocată.
Motorul bazei de date acceptă tip de date CURSOR... Acest tip de date este folosit pentru a declara cursoarele în procedurile stocate. Cursor este o construcție 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 linie cu linie.
Pentru a șterge una sau un grup de proceduri stocate, utilizați Declarația DROP PROCEDURE... Doar proprietarul sau membrii rolurilor fixe db_owner și sysadmin pot șterge o procedură stocată.
Proceduri stocate și CLR
SQL Server acceptă Common Language Runtime (CLR), care vă permite să dezvoltați diferite obiecte de bază de date (proceduri stocate, UDF-uri, declanșatoare, UDF-uri și UDD-uri) folosind C # și Visual Basic. CLR vă permite, de asemenea, să executați aceste obiecte folosind sistemul de rulare comun.
CLR este permis și refuzat de opțiune clr_enabled procedura de sistem sp_configure, care este pornit pentru execuție de către instrucțiune RECONFIGURAȚI... Exemplul de mai jos arată cum poate fi activat CLR utilizând procedura de sistem sp_configure:
UTILIZAȚI SampleDb; EXEC sp_configure „clr_enabled”, 1 RECONFIGURĂ
Pentru a crea, compila și salva o procedură folosind CLR, trebuie să parcurgeți următoarea secvență de pași, în ordine:
Creați o procedură stocată în C # sau Visual Basic și apoi compilați-o folosind compilatorul corespunzător.
Folosind instrucțiunea CREAȚI ANSAMBLU, creați fișierul executabil corespunzător.
Executați procedura utilizând instrucțiunea EXECUTE.
Figura de mai jos prezintă o diagramă a pașilor descriși anterior. Mai jos este o descriere mai detaliată a acestui proces.
Mai întâi, creați programul necesar î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 salvat într-un fișier de bibliotecă de legături dinamice (.dll), care este sursa instrucțiunii CREATE ASSEMBLY, care generează cod intermediar executabil. Apoi, rulați instrucțiunea CREATE PROCEDURE pentru a salva codul executabil 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ă pentru o procedură stocată în C #:
Utilizarea System.Data.SqlClient; folosind Microsoft.SqlServer.Server; clasă parțială publică StoredProcedures (public static int CountEmployees () (int rows; SqlConnection connection = new SqlConnection ("Context Connection = true"); connection.Open (); SqlCommand cmd = connection.CreateCommand (); cmd.CommandText = "selectați contorizează (*) ca „Număr de angajați” „+” de la Angajat „; rows = (int) cmd.ExecuteScalar (); connection.Close (); return rows;))
Această procedură implementează o interogare pentru a număra numărul de rânduri din tabelul Employee. Directivele de utilizare de la începutul programului specifică spațiile de nume necesare pentru a executa programul. 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ă. Metoda CountEmployees () este definită în codul clasei. 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 acelei instanțe. A Metoda CreateCommand (). vă permite să accesați o instanță a clasei SqlCommnd căruia i se transmite comanda SQL necesară.
În următorul fragment de cod:
Cmd.CommandText = "selectați numărul (*) 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 (). o instanță SqlCommand. Această metodă returnează o valoare scalară care este convertită în tipul de date int și atribuită rândurilor.
Acum puteți compila acest cod folosind Visual Studio. Am adăugat această clasă la proiectul numit 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 executabil. Înainte de a rula 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; CREATE ASEMBLEA 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, UDF-uri și declanșatoare. Această declarație are următoarea sintaxă:
CREATE ASSEMBLY nume_asamblare [AUTHORIZATION owner_name] FROM (dll_file) Convenții de sintaxă
Assembly_name specifică numele ansamblului. Clauza opțională AUTHORIZATION specifică numele rolului 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 trebuie inclusă întotdeauna. 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 privilegii EXTERNAL_ACCESS permite codului de asamblare să acceseze anumite resurse externe ale sistemului, în timp ce setul de privilegii UNSAFE oferă acces nerestricționat la resurse, atât în interiorul, cât și în afara sistemului bazei de date.
Utilizatorul trebuie să fie capabil să execute instrucțiunea CREATE ASSEMBLY pentru a păstra informații despre codul de asamblare. Un ansamblu este deținut de utilizatorul (sau rolul) care execută această instrucțiune. Puteți face proprietarul ansamblului un alt utilizator 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ă declarație 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ă.
Exemplul de mai jos arată cum să creați o procedură stocată pe baza codului gestionat pe care l-ați 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 de mai devreme prin faptul că conține Parametrul NUME EXTERN... Acest parametru indică faptul că codul este generat de CLR. Numele din această propoziție are trei părți:
nume_asamblare.nume_clase.nume_metodă
assembly_name Specifică numele ansamblului.
class_name - indică numele clasei generale;
method_name - opțional, specifică numele metodei care este specificată î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.
În Microsoft SQL Server, pentru a implementa și automatiza proprii algoritmi ( calcule), 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, vă recomand să citiți și cartea - The T-SQL Programmer's Way. Tutorial pentru limbajul Transact-SQL.
Ce sunt procedurile stocate în T-SQL?
Proceduri stocate Sunt obiecte de bază de date care conțin un algoritm 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 salva codul reutilizabil pe server, de exemplu, ați scris un anumit algoritm, un calcul secvenţial sau o instrucţiune SQL în mai multe etape, iar pentru a nu executa de fiecare dată toate instrucţiunile incluse în acest algoritm, puteți proiecta aceasta ca o procedură stocată. În același timp, atunci când creați o procedură SQL, serverul compilează codul și apoi, de fiecare dată când rulați această procedură SQL, serverul nu o va mai compila.
Pentru a rula o procedură stocată în SQL Server, este necesar să scrieți comanda EXECUTE înaintea numelui acesteia, sau este posibilă și comanda EXEC prescurtată. Apelarea unei proceduri stocate într-o instrucțiune SELECT, de exemplu, ca o funcție nu va mai funcționa, de exemplu. procedurile rulează separat.
În procedurile stocate, spre deosebire de funcții, puteți efectua deja operațiuni de modificare a datelor precum: UNSERT, UPDATE, DELETE. De asemenea, în proceduri, puteți utiliza instrucțiuni SQL de aproape orice tip, 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 de scheme ș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, nu poate returna nimic, doar executa 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. operatorul PIVOT. Pentru a facilita formarea de interogări cu acest operator ( după cum știți, sintaxa lui 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 despre modul în care această caracteristică poate fi implementată 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 test și să adăugăm mai multe înregistrări la acesta, să spunem că este un tabel care conține o listă de produse cu prețurile acestora.
Instrucțiune pentru a crea un tabel CREATE TABLE TestTable (INT IDENTITY (1,1) NOT NULL, INT NOT NULL, VARCHAR (100) NOT NULL, MONEY NULL) GO - Instrucțiune pentru adăugarea datelor INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (1 , „Mouse”, 100), (1, „Tastatură”, 200), (2, „Telefon”, 400) GO --Selectați cererea SELECT * FROM TestTable
Datele sunt acolo, acum să trecem la crearea procedurilor stocate.
Crearea unei proceduri stocate T-SQL - instrucțiunea 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 blocul 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. produs nou la graficul nostru 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 se va putea trece la procedura ( de exemplu, nu știm încă prețul), pentru aceasta în definiția sa vom seta valoarea implicită. Acești parametri se află în corpul procedurii, de exemplu. în blocul BEGIN ... END puteți utiliza același mod ca variabilele obișnuite ( după cum știți, variabilele sunt notate cu @). Dacă trebuie să specificați parametrii de ieșire, apoi 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, precum și o instrucțiune SELECT la sfârșitul procedurii, astfel încât procedura stocată să returneze date tabelare despre produsele din categoria specificată, ținând cont de noul , tocmai a adăugat produsul. Tot în această procedură stocată am adăugat procesarea parametrului de intrare și anume eliminarea spațiilor în plus la începutul și la sfârșitul liniei de text pentru a evita situațiile în care mai multe spații au fost introduse accidental.
Iată codul pentru această procedură ( Am comentat si eu).
Creați o procedură CREATE PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY = 0) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Manevrați parametrii primiți --Eliminați spațiile suplimentare la început și la sfârșitul liniei de text SET @ProductName = LTRIM (RTRIM (@ProductName)); --Adăugați o nouă înregistrare INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) - Returnează datele SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Rularea unei proceduri stocate T-SQL - comanda EXECUTE
Puteți începe o procedură stocată, așa cum am observat deja, folosind comanda EXECUTE sau EXEC. Parametrii primiți sunt trecuți procedurilor prin simpla listă a acestora și specificând valorile corespunzătoare după numele procedurii ( pentru parametrii de ieșire trebuie să specificați și comanda OUTPUT). Cu toate acestea, numele parametrilor poate fi omis, 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).
Parametrii care au valori implicite nu trebuie să fie specificați, aceștia sunt așa-numiții parametri opționali.
Iată câteva modalități 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 indicația de preț EXEC TestProcedure @CategoryId = 1, @ProductName = „Test produs 2”, @Price = 300 --3. Apelăm procedura fără a specifica numele parametrilor EXEC TestProcedure 1, "Test Product 3", 400
Schimbarea unei proceduri stocate în instrucțiunea T-SQL - ALTER PROCEDURE
Puteți face modificări ale algoritmului procedurii folosind instrucțiunile PROCEDURA DE ALTERARE... Cu alte cuvinte, pentru a schimba o procedură existentă, trebuie doar să scrieți ALTER PROCEDURE în loc de CREATE PROCEDURE și să modificați totul 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ă.
Schimbăm ALTER PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Manevrarea parametrilor de intrare - Eliminarea spațiilor inutile la început și la sfârșitul liniilor de text SET @ProductName = LTRIM (RTRIM (@ProductName)); --Adăugați o nouă înregistrare INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO înregistrare
Eliminarea unei proceduri stocate T-SQL - instrucțiunea 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 deoarece procedura la care au făcut referire nu mai există.
Am de toate, sper ca materialul ti-a fost interesant si de folos, pana acum!
