Atunci când se foloseşte SQL Server la dezvoltarea aplicaţiilor ( de exemplu
într-o configuraţie client-server) se poate vorbi ,în principiu , despre două opţiuni de bază pentru stocarea şi executarea programelor.
Programele pot fi memorate local la nivelul aplicaţiilor care trimit comenzi către SQL Server şi prelucrează rezultatele returnate de acesta.
A doua opţiune presupune dezvoltarea şi înregistrarea programelor ca proceduri stocate în SQL Server şi crearea de aplicaţii care apelează aceste proceduri şi prelucrează returnate de acestea.
Procedurile stocate din SQL Server sunt similare procedurilor din alte limbaje de programare în sensul că :
acceptă parametrii de intrare de la şi returnează valori prin parametri de ieşire către un program apelant ;
conţin instrucţiunui de programare care efectuează operaţii în baza de date şi pot apela , la rândul lor , alte proceduri stocate ;
returnează către apelant o valoare care indică succesul sau eşecul execuţiei procedurii, eventual cauza eşecului
O procedură stocată se poate lansa în execuţie prin comanda EXECUTE.
Utilizarea procedurilor stocate prezintă mai multe avantaje faţă de varianta programelor stocate la nivelul aplicaţiilor utilizator :
programare modulară : o procedură stocată poate fi creată o singură dată şi apelată de mai multe ori din mai multe aplicaţii . Mai mult, procedura stocată poate fi creată de către o persoană specializată în baze de date şi poate fi modificată independent de aplicaţiile care o apelează
performanţă îmbunătăţită : în cazul programelor cu volum mare de cod sau a celor executate în mod repetat procedurile stocate sunt mai eficiente deoarece compilarea şi optimizarea lor se face o singură dată la crearea procedurii şi sunt memorate într-o formă direct executabilă prin care se evită repetarea fazelor de compilare şi optimizare la fiecare apel al procedurii
reducerea traficului de reţea : o prelucrare care presupune execuţia a sute de linii de cod poate fi realizată printr-o singură linie de comandă care apelează procedura stocată prin care este implementată acea prelucrare.Se evită astfel transmiterea prin reţea a codului respectiv la fiecare executare a acelei prelucrări
oferă un mecanism suplimentar de securitate : utilizatorii nu au acces direct la codul procedurilor stocate, iar dreptul de execuţie al unei proceduri poate fi acordat sau nu în funcţie de statutul fiecărui utilizator de către administratorul bazei de date .
În SQL Server o procedură stocată este creată prin comanda :
CREATE PROCEDURE şi poate fi modificată ulterior prin comanda ALTER PROCEDURE.Definiţia unei proceduri stocate conţine următoarele elemente :
specificarea numelui procedurii şi a parametrilor;
corpul procedurii , care conţine instrucţiunile SQL ce realizează prelucrarea pentru care a fost creată procedura
Instrucţiunea CREATE PROCEDURE
Sintaxa comenzii CREATE PROCEDURE este :
CREATE PROC[EDURE] nume_procedură [;număr]
[
{@parametru tip_de_data} [VARYING] [=valoare_implicita]
[OUTPUT]
][,n]
[WITH
{ RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION }
]
[ FOR REPLICATION ]
AS
instrucţiuni_sql[.n]
unde :
nume_procedura - este numele procedurii stocate care se creează.Prin convenţie , numele unei proceduri temporare locale începe cu simbolul # , iar al unei proceduri temporare globale cu ##.Numele unei proceduri poate avea cel mult 128 caractere.
;numar - întreg opţional folosit pentru a reuni mai multe proceduri cu acelaşi nume într-un grup.Toate procedurile dintr-un grup pot fo şterse cu o singură comandă DROP PROCEDURE având ca parametru numele comun al procedurilor din grup
@parametru - este un parametru al procedurii stocate.O procedură stocată poate avea până la 1024 parametri care se comportă în cadrul procedurii ca orice variabilă locală.La execuţie un parametru poate lua doar o valoare constantă şu nu poate fi folosit în locul numelor de tabele, coloane sau alte obiecte din baza de date .
tip_de_data - este tipul de dată al parametrului.Toate tipurile de dată, inclusiv text şi image, pot fi folosite ca parametru.Totuşi , tipul de dată cursor poate fi folosit doar pentru parametrii de tip OUTPUT (de ieşire).La specificarea unui tip de dată cursor, cuvintele cheie VARYING şi OUTPUT trebuie , de asemenea specificate .
VARYING - specifică relaţia rezultat suportată ca parametru de ieşire.Se aplică numai la parametrii de tip cursor.
valoare_implicita - este valoarea implicită a parametrului.Dacă o asemenea valoare este definită , atunci la apelul procedurii se poate omite specificarea unei valori pentru acest parametru.Valoarea implicită poate fi o constantă ( care poate include caracterele speciale %, _,[], şi [^] atunci când parametrul este utilizat cu operatorul LIKE) sau NULL.
OUTPUT - indică faptul că parametrul curent este unul de ieşire şi poate fi folosit pentru a returna valori dinspre procedură.Tipul de dată text ( sau echivalent) este interzis pentru parametrii de tip OUTPUT.
n - indică faptul că o procedură poate avea mai mulţi parametrii , până la 1024.
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }
- opţiunea RECOMPILE indică faptul că procedura va fi recompilată înainte de fiecare execuţie a sa. Opţiunea ENCRYPTION indică faptul că textul procedurii va fi memorat dub formă încriptată .
FOR REPLICATION - această opţiune se exclude reciproc cu opţiunea WITH RECOMPILE şi specifică faptul că procedura curentă este creată pentru procesul de replicare şi nu poate fi executată decât pe severul curent .
AS - marchează începutul corpului procedurii .
Instricţiuni_sql [.n] - instrucţiunile SQL care formează corpul procedurii,Într-o procedură pot exista mai multe instrucţiuni cu respectarea următoarelor restricţii :
se respectă toate regulile specifice unui batch SQL ( o procedură stocată este o unitate independentă de compilare ) ;
se poate include orice instrucţiune de tip SET cu excepţia : SET
SHOWPLAN_TEXT şi SET SHOWPLAN_ALL ; opţiunile SET
acceptate rămân în vigoare până la ieşirea din procedură ;
dacă procedura stocată urmează să fie folosită şi de alţi utilizatori decât cel care creează procedura , atunci numele de obiecte utilizate în următoarele instrucţiuni :
- ALTER TABLE ;
- CREATE INDEX ;
- CREATE TABLE ;
- Toate instrucţiunile DBCC ;
- DROP TABLE ;
- DROP INDEX ;
- TRUNCATE TABLE ;
- UPDATE STATISTICS
- trebuie prefixate prin numele propietarului obiectului respectiv.
- Următoarele instrucţiuni de tip CREATE nu pot să apară într-o procedură stocată :
- CREATE DEFAULT ;
- CREATE PROCEDURE ;
- CREATE RULE ;
- CREATE TRIGGER ;
- CREATE VIEW
pentru obiectele care se pot crea într-o procedură stocată trebuie respectată condiţia ca acestea să fie referite doar după creare lor
o procedură stocată poate accesa tabele temporare , dar trebuie avut în vedre faptul că o tabelă temporară creată într-o procedură dispare la ieşirea din procedură ;
o procedură apelată de o altă procedură are acces la toate obiectele create de procedura apelantă ( inclusiv tabele temporare !)
Instrucţiunea EXECUTE
În urma executării cu succes a instrcţiunii CREATE PROCEDURE numele şi textul procedurii stocate corespunzătoare sunt înregistrarte în tabelele director ale SQL Server . La primul apel de execuţie a procedurii are loc compilarea acesteia şi construirea unui plan de execuţie optimizat.Lansarea în execuţie a unei proceduri stocate se face cu comanda EXECUTE .
Sintaxa instrucţiunii :
[[EXEC[UTE]]
{
[@valoare_retur=] {nume_procedură [;numar]|@variabila_nume_procedura
}
[[@parametru={valoare | @ variabila [OUTPUT] | [DEFAULT] ] [,n]
[WITH RECOMPILE]
unde :
@valoare_retur - este o variabilă opţională de tip întreg în care se memorează valoarea de retur a procedurii stocate.
nume_procedura - numele procedurii apelate.Dacă se apelează o procedură dintr-o altă bază de date (şi eventual alt server) decât cea curentă ,atunci numele procedurii trebuie prefixat cu numele bazei de date (şi al serverului) în care se găseşte şi unde se execută.Numele unei proceduri stocate este case-sensitive !
;număr - are aceeaşi semnificaţie ca şi în cazul comenziii CREATE PROCEDURE
@variabila_nume_procedura - este numele unei variabile ce conţine numele unei proceduri stocate
@parametru - este numele unui parametru al procedurii aşa cum a fost definit prin instrucţiunea CREATE PROCEDURE.În varianta de apel de forma @parametru = valoare, perechile de forma nume-constantă pot să apară şi într-o ordine diferită faţă de cea dată la crearea procedurii.Totuşi, dacă se foloseşte forma parametru = valoare pentru un parametru , atunci ea trebuie folosită pentru toţi parametrii procedurii
valoare - este valoarea parametrului.Dacă nu se specifică numele parametrului corespunzător, atunci valorile trebuie date în ordinea definirii parametrilor corespunzători
@variabila - este numele unei variabile în care se va returna valoarea unui parametru de tip OUTPUT
OUTPUT - indică faptul că parametrul este de tip ieşire.Un asemenea parametru trebuie asociat la apel cu o variabilă de tipul valorii care se returnează prin parametrul corespunzător ( în forma : @parametru = @variabila sau simplu @variabila ).
DEFAULT - indică faptul că se va lua în considerare valoare implicită a parametrului aşa cum a fost definită la crearea procedurii ( în forma : @parametru = DEFAULT sau simplu DEFAULT).Dacă parametrul nu are defintă o valoare implicită, atunci se va genera o eroare.
WITH RECOMPILE - forţează recompilarea şi crearea unui nou plan de execuţie.Se recomandă folosirea acestei opţiuni dacă au avut loc modificări semnificative ale datelor faţă de momentul creeri procedurii.În acest caz este de asemenea utilă executarea unei comenzi UPDATE STATISTICS înainte de apelul (cu recompilare a ) procedurii.
Instrucţiunea ALTER PROCEDURE
Modifică o procedură stocată creată anterior fără a afecta în vreun fel obiectele dependente (proceduri stocate sau triggere care apelează procedura).Modificarea prin instrucţiunea ALTER PROCEDURE este preferată variantei care presupune ştergerea şi recrearea procedurii deoarece ,în acest caz, procedurile sai triggerele dependente nu nai trebuie recompilate .
Sintaxa :
ALTER PROC[EDURE] nume_procedura [;numar]
[
{ @parametru_tip_de_data } [VARYING] [ = valoare_implicita]
[OUTPUT]
] [,n]
[WITH
{ RECOMPILE
| ENCRYPTION
| RECOMPILE,ENCRYPTION}
]
[ FOR REPLICATION ]
AS
Instrucţiuni_sql[.n]
Unde toate elementele de sinatxă au aceeaşi semnificaţie ca şi în cazul instrucţiunii CREATE PROCEDURE .
Instrucţiunea DROP PROCEDURE
Şterge una sau mai multe proceduri sau grupuri de proceduri din baza de date curentă.Ştergerea unei proceduri înseamnă eliminarea definiţiei din baza de date.
Sintaxa :
DROP PROC[EDURE] nume_procedura [,n]
unde:
nume_procedura - este numele procedurii sau al grupului de proceduri care se şterge.
