SQL SERVER-Funcţii definite de utilizatori
1
FUNCŢII DEFINITE DE UTILIZATOR IN SQL SERVER În afara de setul de funcţii predefinite care au fost prezentate în cursurile precedente, SQL server permite utilizatorilor să definească propriile funcţii prin instrucţiuni TRANSACT SQL. O funcţie SQL, definită de utilizator, grupează in cadrul unui obiect SQL de sine stătător o secvenţă de instrucţiuni SQL încapsulată, ce poate fi reutilizată ori de câte ori este nevoie, in cadrul altor obiecte SQL Server. Funcţiile definite de utilizatori au rolul de a returna un rezultat, calculat conform unui algoritm descris de utilizator şi pot utiliza parametri în cadrul calculelor. În funcţie de tipul de rezultat returnat, funcţiile definite de utilizatori pot fi clasificate în două mari categorii: 1. Funcţii de tip scalar (scalar functions) – care returnează o valoare ce poate fi încadrată în unul dintre tipurile de date SQL Server (de ex. Char, varchar, numeric, datetime, etc.) 2. Funcţii de tip tabelar (table valued functions) – care returnează un set de înregistrări ce poate fi asimilat unui tabel virtual.
AVANTAJE Funcţiile definite de utilizator oferă posibilitatea utilizatorilor de a structura într-o manieră eficientă codul SQL şi pot simplifica prelucrările de date ce implică realizarea unor structuri complexe de programare. O dată create, funcţiile pot fi utilizate în cadrul obiectelor de tip View sau al procedurilor stocate diminuând considerabil dimensiunea codului SQL şi facilitând o mai bună structurare a acestuia. Funcţiile de tip table valued functions pot fi utilizate pentru simularea unor tabele virtuale extrem de utile în programarea procedurilor.
Funcţii de tip scalar (scalar functions) O funcţie de tip scalar poate accepta zero sau mai mulţi parametri şi va returna o singură valoare. Pentru a crea o astfel de funcţii se va utiliza comanda CREATE FUNCTION a cărei comandă simplificată este următoarea: CREATE FUNCTION [numeproprietar.] nume_funcţie ([ @parametru_1 AS tip_de_date], …. [ @parametrul_n AS tip_de_date] ) RETURNS tip_de date AS
2
SQL SERVER-Funcţii definite de utilizatori BEGIN INSTRUCŢIUNI SQL RETURN valoare de returnat END
Pentru modificarea unei funcţii se utilizează sintaxa: ALTER FUNCTION
Pentru ştergerea unei funcţii se utilizează sintaxa: DROP FUNCTION
Observaţii: -Prin intermediul instrucţiunii RETURNS se precizează tipul de date al valorii returnate de funcţie. -Prin intermediul instrucţiunii RETURN se specifică efectiv valoare de returnat rezultată în urma calculelor din comenzile SQL precizate în corpul funcţiei (între BEGIN şi END). În corpul funcţiei, între BEGIN şi END pot fi incluse structuri repetitive, declaraţii de variabile, etc. Valoarea returnată nu poate fi de tip tabel, motiv pentru care nu pot fi utilizate în clauza FROM a unei instrucţiuni SELECT, însă pot fi utilizate în SELECT, WHERE, GROUP BY, HAVING, ORDER BY. - Numele complet al unei funcţii este dat de următorul specificator:
NumeBazaDate.NumeProprietarFunctie.NumeFuncţie La apelarea unei funcţii definite de utilizator dintr-o frază SQL, trebuie precizat în mod obligatoriu proprietarul funcţiei (de exemplu dbo). Numele bazei de date se precizează doar dacă funcţia executată provine din altă bază decât cea unde se face apelul către ea. În cazul în care funcţia trebuie executată de alt utilizator decât proprietarul, acesta trebuie să aibă dreptul de EXECUTE asupra acesteia. -Informatii despre o functie se pot obţine folosind următoarea procedură de sistem:
sp_help functie
EXEMPLUL 1:
Se dau tabelele: CLIENTI (CodClient , Nume, Adresa, Localitate, Tara ) CONTRACTE(NrContract, DataContract, Valoare, DataFinalizare, CodClient ) Se doreşte realizarea unei funcţii pentru calculul unui discount ce se aplica la valoarea contractelor după următoarele criterii: •
Pentru contractele de la clienţii din afara României cu valoare sub 1000 se aplica 5 % la valoare contract
•
Pentru contractele de la clienţii din afara României cu valoare peste 1000 se aplica 7% la valoare contract
SQL SERVER-Funcţii definite de utilizatori •
3
Pentru contractele de la clienţii din România se aplica 10% la valoare contract
Rezolvare:
CREATE FUNCTION DISCOUNT(@VALC AS MONEY, @TARA AS VARCHAR(50)) RETURNS money AS BEGIN RETURN CASE
END
WHEN @TARA<>'Romania' AND @VALC<1000 THEN @VALC*0.05 WHEN @TARA<>'Romania' AND @VALC>=1000 THEN @VALC*0.07 ELSE @VALC*0.1
END Ulterior putem utiliza funcţia în cadrul unui View după cum se poate observa în figura următoare:
Observaţii Funcţiile de tip Scalar se pot utiliza şi în cadrul procedurilor stocate, cel mai uzual în cadrul clauzei SELECT pentru definirea expresiilor, dar şi în cadrul clauzei WHERE pentru a impune restricţii. EXEMPLUL 2
Se doreşte sa se realizeze o funcţie pentru a calcula pentru fiecare dintre clienţii cu care nu mai sunt contracte în derulare câte săptămâni au trecut de la finalizarea ultimului contract. Pentru clienţii la care încă se mai lucrează la ultimul contract (nu s-a ajuns la data de finalizare) funcţia va returna valoarea 0. CREATE FUNCTION Calcul2 (@CodC as int) RETURNS int AS BEGIN DECLARE @UltimaData as datetime SET @UltimaData = (SELECT max(DataFinalizare) FROM CONTRACTE WHERE CodClient=@CodC) RETURN CASE
SQL SERVER-Funcţii definite de utilizatori
END
4
WHEN @UltimaData>=GETDATE() THEN 0 ELSE Datediff(week, @UltimaData, getdate())
END
Ulterior putem utiliza funcţia în cadrul unei proceduri stocate după cum se poate observa în exemplul următor:
--Aceasta procedura selecteaza clientii din tara cu care nu s-a mai -- colaborat de mai mult de 10 saptamani SELECT Codclient, Nume, Adresa, Localitate, Tara , dbo.Ex2(CodClient) as [saptamani de la ultimul contract] FROM CLIENTI WHERE dbo.Ex2(CodClient)>10 And Tara='Romania' EXEMPLUL 3
Se doreşte alocarea unor coduri contractelor pentru realizarea unor clasificări şi verificări ulterioare. Codurile vor fi alcătuite din: primele 3 caractere din denumirea ţării din care provine clientul, urmate de caracterul de pe poziţia a doua din numele clientului, apoi, ultimele doua cifre din anul în care s-a semnat contractul şi numărul zilei din an în care s-a finalizat contractul. CREATE FUNCTION Codificare(@Tara as varchar(50), @NumeC As varchar(50), @DataC as datetime, @DataF as datetime) RETURNS char(11) AS BEGIN --- OBSERVATIE: --- ESTE NECESAR CA DATELE DE TIP NUMERIC SI CALENDARISTIC SA FIE CONVERITE --- IN TIMP SIR DE CARACTERE DECLARE DECLARE DECLARE DECLARE SET SET SET SET
@Cod1 @Cod2 @Cod3 @Cod4
as as as as
char(3) --prima parte din cod (tara) char(3) --a doua parte din cod (nume client) char(2) -- a treia parte din cod (anul) char(3)-- a patra parte din cod (ziua din an)
@Cod1=LEFT(@TARA,2) @Cod2 = SUBSTRING( @NumeC,2,1) @Cod3=RIGHT( CONVERT(CHAR(4), YEAR(@DataC)) , 2) @Cod4 = CONVERT (CHAR(3), DATEPART(dayofyear, @DataF))
RETURN @COD1+@COD2+@COD3+@COD4 END Utilizarea funcţiei pentru a afişa lista codurilor aferente contractelor este exemplificată în interogarea următoare:
SELECT NrContract, Nume, tara, DataContract, DataFinalizare, dbo.Codificare(Tara, Nume, DataContract, DataFinalizare) As Cod FROM Clienti INNER Join Contracte on Clienti.Codclient=Contracte.CodClient
SQL SERVER-Funcţii definite de utilizatori
5
Funcţii de tip tabelar (table valued functions) Funcţiile de tip tabelar se diferenţiază de cele de tip scalar prin faptul că returnează un set de date sub forma unui tabel bidimensional conform modelului relaţional. Având în vedere faptul că sunt sunt asimilate tabelelor (virtuale), pot fi utilizate în cadrul unei fraze SQL, similar modului cum sunt utilizate tabelele bazei de date. Datele rezultate nu sunt memorate sub forma unui tabel în baza de date, ci sunt obţinute dinamic la fiecare apel al funcţiei – similar obiectelor de tip View. Diferenţa majoră faţă de acestea constă în posibilitatea utilizării parametrilor în cadrul funcţiilor, conferindu-le un grad ridicat de flexibilitate. Sintaxa simplificată pentru definirea unei funcţii de tip tabelar este următoarea:
A). varianta INLINE. se returnează un tabel ca rezultat, fără a fi nevoie sa se definească structura acestuia. În această varianta nu sunt permise BEGIN ….END, ci doar o frază SQL de tip SELECT, rezultatele fiind furnizate sub forma unui tabel. Fiecare câmp din interogarea sursă trebuie să aibă un nume, ceea ce înseamnă că expresiile cu mai mulţi operanzi trebuie sa aibă neapărat un alias. În această variantă de utilizare, utilizarea clauzei ORDER BY este permisă numai dacă se utilizează împreună cu TOP în instrucţiunea SELECT. Nu sunt permise prelucrări complexe. CREATE FUNCTION [numepropritar.] nume_funcţie ([ @parametru_1 AS tip_de_date], …. [ @parametrul_n AS tip_de_date] ) RETURNS TABLE As RETURN (fraza_SQL_SELECT)
EXEMPLU Realizaţi o funcţie prin intermediul căreia să se determine care sunt primii 10 clienţi cu cele mai multe contracte. Funcţia va returna codurile clienţilor şi numărul total de contracte.
CREATE FUNCTION CLIENTI_IMPORTANTI() RETURNS TABLE AS RETURN (SELECT top 10 CodClient, COUNT(NrContract) FROM CONTRACTE GROUP BY CodClient ORDER BY COUNT(NrContract) DESC)
SQL SERVER-Funcţii definite de utilizatori
6
B). varianta MULTIINSTRUCŢIUNE. se returnează un tabel rezultat, fiind necesară şi definirea structurii acestuia. Faţă de varianta A, această categorie poate să includă prelucrări complexe. CREATE FUNCTION [numepropritar.] nume_funcţie ([ @parametru_1 AS tip_de_date], …. [ @parametrul_n AS tip_de_date] ) RETURNS @variabila_output TABLE (câmp_1 tip de date, … câmp_N tip de date) AS BEGIN INSTRUCŢIUNI SQL RETURN END
EXEMPLUL 4 Realizaţi o funcţie prin intermediul căreia să se determine care sunt primii 10 clienţi cu cele mai multe contracte. Funcţia va returna codurile clienţilor şi numărul total de contracte.
CREATE FUNCTION CLIENTI_IMPORTANTI() RETURNS @Tabel_10 TABLE (CodClient int, TotalContracte int) AS BEGIN INSERT @Tabel_10 SELECT top 10 CodClient, COUNT(NrContract) FROM CONTRACTE GROUP BY CodClient ORDER BY COUNT(NrContract) DESC RETURN END Putem utiliza funcţia ulterior, în cadrul unei proceduri stocate ce afişează lista contractelor cu clienţii importanţi, contractate de la începutul anului 2008:
SELECT NrContract, DataContract, Valoare, Nume, Adresa FROM CONTRACTE INNER JOIN CLIENTI ON Contracte.CodClient=Clienti.Codclient WHERE CLIENTI.CodClient IN (SELECT CodClient FROM CLIENTI_IMPORTANTI() ) AND DataContract>'1/1/2008' După cum se poate observa, funcţia precedentă nu a necesitat parametri. Pentru exemplificarea unei funcţii cu parametri vom rezolva exemplul următor:
SQL SERVER-Funcţii definite de utilizatori
7
EXEMPLUL 5 Se doreşte realizarea unei funcţii care să returneze lista contractelor finalizate într-o anumită perioadă şi, pentru fiecare dintre acestea, 50% din valoarea contractata. Ulterior se va realiza o procedură stocată pentru a calcula totalul încasărilor din finalizarea contractelor de la un anumit client pe o perioadă de timp (presupunem că la finalizarea contractelor se încasează 50% din valoarea contractată) Realizarea funcţiei:
CREATE FUNCTION ListaFinalizari (@Data1 as datetime, @Data2 As Datetime) RETURNS @ListaF TABLE (NrContract int, DataFinalizare datetime, CodClient datetime, Incasari money) AS BEGIN
INSERT @ListaF SELECT NrContract, DataFinalizare, CodClient, Valoare/2 FROM CONTRACTE WHERE DataFinalizare BETWEEN @Data1 AND @Data2 RETURN
END
Realizarea procedurii stocate:
CREATE PROC Incasari_Final_Contract @CodC AS int, @DataStart AS DateTime, @DataSfarsit AS dateTime AS SELECT CodClient, SUM(Incasari) FROM ListaFinalizari(@DataStart, @DataSfarsit) WHERE CodClient=@CodC GROUP BY Codclient Lansarea în execuţie a procedurii stocate pentru a determina încasările de la clientul cu codul 1000 în intervalul 20 iunie 2007 – 30 mai 2008:
EXEC Incasari_Final_Contract 1000, '20/6/2007', '30/5/2008'