functii definite de utilizator in sql server

SQL SERVER-Funcţii definite de utilizatori 1

FUNCŢII DEFINITE DE UTILIZATOR IN SQL SERVERFUNCŢ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.

AVANTAJEAVANTAJE

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)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

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

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 moneyAS

BEGIN

RETURNCASE

WHEN @TARA<>'Romania' AND @VALC<1000 THEN @VALC*0.05WHEN @TARA<>'Romania' AND @VALC>=1000 THEN @VALC*0.07ELSE @VALC*0.1

ENDEND

Ulterior putem utiliza funcţia în cadrul unui View după cum se poate observa în figura următoare:

ObservaţiiFuncţ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 intASBEGIN

DECLARE @UltimaData as datetimeSET @UltimaData = (SELECT max(DataFinalizare)

FROM CONTRACTE WHERE CodClient=@CodC)

RETURNCASE

WHEN @UltimaData>=GETDATE() THEN 0ELSE Datediff(week, @UltimaData, getdate())

ENDEND


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 CLIENTIWHERE 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 @Cod1 as char(3) --prima parte din cod (tara)DECLARE @Cod2 as char(3) --a doua parte din cod (nume client)DECLARE @Cod3 as char(2) -- a treia parte din cod (anul)DECLARE @Cod4 as char(3)-- a patra parte din cod (ziua din an)

SET @Cod1=LEFT(@TARA,2)SET @Cod2 = SUBSTRING( @NumeC,2,1)SET @Cod3=RIGHT( CONVERT(CHAR(4), YEAR(@DataC)) , 2)SET @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 CodFROM Clienti INNER Join Contracte on Clienti.Codclient=Contracte.CodClient

Funcţii de tip tabelar (table valued functions)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],


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 TABLEASRETURN (SELECT top 10 CodClient, COUNT(NrContract)

FROM CONTRACTEGROUP BY CodClientORDER BY COUNT(NrContract) DESC)

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],



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)ASBEGIN

INSERT @Tabel_10SELECT top 10 CodClient, COUNT(NrContract)FROM CONTRACTEGROUP BY CodClientORDER 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, AdresaFROM CONTRACTE INNER JOIN CLIENTI ON Contracte.CodClient=Clienti.CodclientWHERE 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:

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

BEGININSERT @ListaFSELECT NrContract, DataFinalizare, CodClient, Valoare/2FROM CONTRACTE WHERE DataFinalizare BETWEEN @Data1 AND @Data2

RETURNEND

Realizarea procedurii stocate:

CREATE PROC Incasari_Final_Contract@CodC AS int,@DataStart AS DateTime,@DataSfarsit AS dateTimeAS

SELECT CodClient, SUM(Incasari)FROM ListaFinalizari(@DataStart, @DataSfarsit)WHERE CodClient=@CodCGROUP 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'

functii definite de utilizator in sql server

Documents