proceduri stocate modificat

SQL Server – Proceduri stocate

PRO0CEDURI STOCATE

O procedură stocată este un pachet de instrucţiuni SQL memorat pe server şi compilat la utilizare.

Avantaje:- Facilitează actualizarea datelor prin faptul că permit ca toate aplicaţiile ce modifică

datele să acţioneze în acelaşi mod;- Acceptă definirea de parametri şi astfel permit executarea aceloraşi instrucţiuni SQL

cu seturi diferite de parametri;- Utilizarea procedurilor stocate permite diminuarea fluxului de date în reţea micşorând

secvenţele de cod SQL ce sunt transmise serverului;- Deoarece planurile de execuţie sunt păstrate de server, performanţele aplicaţiilor pot fi

îmbunătăţite în mod semnificativ.

Utilizarea procedurilor stocate presupune parcurgerea următoarelor etape:1. Crearea procedurii (prin intermediul comenzii CREATE PROCEDURE)2. Executarea de către utilizator (prin intermediul unei comenzi EXEC)3. Compilarea (în timpul unei comenzi EXEC serverul va compila şi optimiza procedura)4. Executarea de către server (conform planului de execuţie compilat al procedurii)

Pentru a crea o procedură stocată se poate iniţia o nouă interogare în baza de date (New Query) şi se va utiliza comanda:

CREATE PROCEDURE nume_procedura AS instrucţiuni_SQLPentru a modifica o procedură stocată se va utiliza comanda:

ALTER PROCEDURE nume_procedura AS instrucţiuni_SQL

Observaţie:O procedură stocată poate conţine orice instrucţiuni SQL valide cu câteva excepţii dintre care amintim: CREATE PROCEDURE, CREATE VIEW si CREATE TRIGGER (poate însă conţine comenzi de tip CREATE TABLE sau chiar CREATE DATABASE)

Procedurile stocate pot fi create şi prin intermediul interfeţei oferite de Microsoft SQL Server Management Studio. În cadrul ferestrei Object Explorer, procedurile stocate pot fi vizualizate în cadrul colecţiei Programmabilty .

1


Exemplul 1:CREATE PROC ListaAngajati2008 ASSELECT Nume, DataAngajare FROM AngajatiWHERE DataAngajare BETWEEN ‘1/1/2008’ AND ‘12/31/2008’

Observaţie:Lansarea în execuţie a procedurii stocate se poate realiza prin simpla specificare a numelui acesteia, sau prin plasarea instrucţiunii EXEC înaintea numelui procedurii:

EXEC ListaAngajati2008

Proceduri de sistemÎnafara procedurilor definite de utilizatori, SQL server pune la dispoziţia programatorilor o serie de proceduri predefinite ce sunt memorate în baza de date Master. Aceste proceduri permit executarea unor rutine utile şi sunt caracterizate prin prefixul sp_ în faţa numelui de procedură.Câteva exemple de astfel de proceduri:

sp_databases permite afisarea listei bazelor de date de pe serversp_columns permite afişarea informaţiilor privind coloanele unui tabel specificat ca parametrusp_executesql permite executarea unor instrucţiuni SQL specificate ca parametrusp_help afiseaza toate informaţiile disponibile privind un anumit obiect din baza de datesp_rename permite redenumirea obiectelor din baza de datesp_spaceused afiseaza numărul de înregistrări şi spaţiul utilizat de un anumit tabel sau view pe

server

Declararea variabilelorÎn cadrul procedurilor stocate se pot utiliza variabile pentru a facilita prelucrarea datelor.Variabilele se declară în cadrul instrucţiunilor ce urmeză după cuvântul cheie AS din definiţia procedurii stocate prin intermediul unei instrucţiuni DECLARE. Numele de variabile sunt precedate de simbolul @Atribuirea unei valori se poate realiza prin instrucţiunile SET sau SELECT.

Exemplul 2:

CREATE PROCEDURE Impozite ASDECLARE @cota as numeric(3,2)SET @cota = 0.16SELECT Nume, Salariu, Salariu*@Cota As [impozit de plata]FROM ANGAJATI

Parametrizarea procedurilor stocate

Parametrii procedurilor SQL Server sunt de doua tipuri:- Parametri de intrare (Input) - Parametri de ieşire (Output)

2


Parametrii de intrare permit preluarea în cadrul procedurilor stocare a unuia sau mai multor elemente variabile ce pot fi utilizate în cadrul expresiilor.Parametri de ieşire sunt utilizaţi pentru returnarea de rezultate în urma prelucrărilor efectuate de procedura stocata

O sintaxă simplificată a comenzii CREATE PROCEDURE, care permite şi adăugarea de parametri este prezentată în continuare:

CREATE PROC nume_procedura [;număr][ [ @parametru tip_de_date] [OUTPUT] [ , ….n] ]AS { <instrucţiuni SQL> [;] […. n ;]

Procedurile pot utiliza mai mulţi parametri de tip input (tipul implicit de parametru, dacă nu se specifică opţiunea OUT). Parametrii însoţiţi de opţiunea OUT, sunt trataţi ca parametri de ieşire (output) şi sunt utilizaţi pentru a returna valori.

Exemplul 3:Parametrizaţi procedura din exemplul 1 pentru a selecta doar persoanele de la un anumit departament, a căror salarii depăsesc o anumită limită. Limita salarială şi codul departamentului vor fi precizate prin parametri.Intrucât procedura a fost deja creată la exemplul 1 (ListaAngajati2008) vom utiliza comanda ALTER PROC pentru modificare, în loc de CREATE PROC.

Rezolvare

ALTER PROC ListaAngajati2008 @sal money,@Depart Char(3)ASSELECT Nume, DataAngajare FROM AngajatiWHERE DataAngajare BETWEEN '1/1/2008' AND '12/31/2008' AND Salariu>@sal AND CodDepartament=@depart

Pentru a lansa în execuţie o astfel de procedură trebuie atribuite valori parametrilor. Atribuirea de valori se poate realiza prin enumerarea valorilor parametrilor in aceeaşi ordine în care au fost declaraţi în procedură sau prin specificarea exactă a numelui parametrului în faţa fiecărei valori:

EXEC ListaAngajati2008 1900, 'IT'sauEXEC ListaAngajati2008 @sal=1900, @depart='IT'

Comenzile prezentate mai sus vor afişa lista persoanelor angajate in 2008 la departamentul IT care au salarii peste 1900 RON.

In exemplul precedent, nespecificarea valorii pentru unul dintre cei doi parametri va genera o eraore şi imposibilitatea de a executa procedura. Pentru a preîntâmpina astfel de situaţii, parametrilor de intrare li se pot asocia valori implicite care vor fi utilizate atunci când nu se precizează o altă valoare.

3


Pentru a atribui valoarea implicită 1000 parametrului @sal şi valoarea ‘fin’ parametrului @depart se va modifica procedura astfel:

ALTER PROC ListaAngajati2008 @sal money = 1000,@Depart Char(3) = 'fin' AS….

Exemplul 4:

Pentru a exemplifica utilizarea parametrilor de ieşire vom lua în considerare următoarea situaţie: Se doreşte calculul unei prime pentru toţi salariaţii. Prima va fi egală cu 50% din valoarea salariului propriu + 10% din valoarea celui mai mare salariu din firma.

1. Vom crea o pocedura stocata pentru a determina salariul maxim. Procedura va contine un parametru de tip OUTPUT care va prelua valoarea salariului maxim calculat pe ansamblul firmei.

CREATE PROCEDURE AflaSalariuMaxim@SalMax Money OUTPUTASSELECT @SALMAX=MAX(SALARIU) FROM ANGAJATI

2. Pentru a calcula prima fiecărui angajat conform algoritmlui propus este necesar să executăm procedura anterior creată şi să preluăm valoarea parametului de tip Output într-o variabilă de memorie.

DECLARE @VariabilaSalariu AS moneyEXECUTE AflaSalariuMaxim @SalMax=@VariabilaSalariu OUTPUTSELECT Nume, Salariu, Salariu*0.5 + @VariabilaSalariu*0.1 AS PRIMA FROM ANGAJATI

Instrucţiunea RETURN

Prin intermediul comenzii RETURN, se poate forţa întreruperea execţiei unei proceduri stocate. Comenzile ce urmeză după instrucţiunea RETURN nu vor mai fi executate.

Sintaxa instrucţiunii return este RETURN [ expresie de tip intreg ]

După cum se poate observa, opţional, după instrucţiunea RETURN se poate preciza un număr întreg ce poate fi utilizat ulterior în cadrul blocului de instrucţiuni ce a lansat în execuţie procedura.

Exemplu:Să se creeze o procedură stocată pentru a afişa datele unui angajat al cărui CNP este specificat ca parametru. În cazul în care parametrul nu este specificat (ramane NULL) se va afişa un mesaj de eroare.

4


CREATE PROCEDURE DateAngajat @cnp char(13) = NULLASIF @cnp IS NULL

BEGIN PRINT 'NU ATI FURNIZAT UN CNP !' RETURN

ENDELSE SELECT * FROM ANGAJATI WHERE CNP = @cnp

Observaţii:Orice procedură stocată care se execută cu succes va returna valoarea zero. Procedurile stocate care provoacă la execuţie o eroare vor returna un cod negativ (de la -1 la -14)

STRUCTURI DE CONTROL ALE FLUXULUITransact SQL permite realizarea de prelucrări complexe în cadrul procedurilor stocate prin intermediul instrucţiunilor ce controlează fluxul execuţiei în pachetele de comenzi.Instrucţiunile de limbaj pentru controlul fuxului ăn Trasnasct SQL facilitează crearea de structuri alternative sau repetitive complexe. Cele mai imortante instrucţiuni din această categorie sunt:IF …ELSEWHILEGOTOWAITFORBREAKCONTINUE

5

proceduri stocate modificat

Documents