SQL Server – Proceduri stocate
1
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_SQL
Pentru 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 .
SQL Server – Proceduri stocate
2
Exemplul 1: CREATE PROC ListaAngajati2008 AS SELECT Nume, DataAngajare FROM Angajati WHERE 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 sp_columns sp_executesql sp_help sp_rename sp_spaceused
permite afisarea listei bazelor de date de pe server permite afişarea informaţiilor privind coloanele unui tabel specificat ca parametru permite executarea unor instrucţiuni SQL specificate ca parametru afiseaza toate informaţiile disponibile privind un anumit obiect din baza de date permite redenumirea obiectelor din baza de date 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 AS DECLARE @cota as numeric(3,2) SET @cota = 0.16 SELECT 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)
SQL Server – Proceduri stocate
3
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 { [;] […. 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) AS SELECT Nume, DataAngajare FROM Angajati WHERE 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' sau EXEC 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.
SQL Server – Proceduri stocate
4
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 OUTPUT AS SELECT @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 money EXECUTE AflaSalariuMaxim @SalMax=@VariabilaSalariu OUTPUT SELECT 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.
SQL Server – Proceduri stocate
5
CREATE PROCEDURE DateAngajat @cnp char(13) = NULL AS IF @cnp IS NULL BEGIN PRINT 'NU ATI FURNIZAT UN CNP !' RETURN END ELSE 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 FLUXULUI Transact 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 …ELSE WHILE GOTO WAITFOR BREAK CONTINUE
