h1

Implementarea functiilor definite de utilizator (CREATE FUNCTION)

March 1, 2010

User defined functions – pot lua zero sau mai multi parametri si intorc fie o valoare scalara sau o tabela . Parametri de intrare pot fi orice tip de data cu exceptia timestamp, cursor sau tabela. Functiile definite de utilizator nu suporta parametri de iesire! Functii scalare: -opereaza pe o singura valoare si intorc o singura valoare Un exemplu sugestiv poate fi gasit la aceasta adresa: http://msdn.microsoft.com/en-us/library/ms186755.aspx Creating a User-Defined Function (CREATE FUNCTION) se creeaza similar ca si VIEW sau o procedura stocata. Sintaxa generala: CREATE FUNCTION nume_fctie (parametri) Exemplul1: MODIFICARE FUNCTIE UTILIZATOR SI STERGERE (ALTER FUNCTION SI DROP FUNCTION): ALTER FUNCTION dbo.fn_NewRegion <New function content> DROP FUNCTION dbo.fn_NewRegion Exemplul2: Functie scalara definita de utilizator

Exemplul3: Exemplu de tabela cu valoare de functie multi- instructiune Putem crea functii folosind mai multe instructiuni care realizeaza operatii diferite. Acest exemplu creaza o tabela multi-instructiune cu valoare de functie ce intoarce lastname sau atat firstname cat si lastname al unui angajat (employee) depinzand de parametrul furnizat.

Exemplu ce creeaza un in-line table valued function that takes a region value as a parameter.

USE NorthWind

GO

CREATE FUNCTION fn_CustomerNamesInRegion

(@RegionParameter nvarchar(30))

RETURNS TABLE

AS

RETURN

(SELECT CustomerID, CompanyName

FROM Northwind.dbo.Customers

WHERE Region = @RegionParameter

)

Apelul functiei:

SELECT * FROM fn_CustomerNamesInRegion(N’WA’)

In-line functions can greatly increase performance when used with indexed views.

SQL SERVER performs complex aggregation and join operations when the index is created.

SELECT STATEMENT:

SELECT ProductName,UnitPrice,CategoryID,ClassNorthwind.dbo.fn_TaxRate(ProductID) As TaxRate, UnitPrice* ClassNorthwind.dbo.fn_TaxRate(ProductID) AS PriceWithTax

FROM Products

The TaxRate column should contain values of 1.00 1.05 sau 1.10 pt.fiecare produs.

ThePriceWithTax column should contain the UnitPrice multiplied by the TaxRate.

Interogarea de mai jos va crea un tabel in line value user defined function numit fn_LargeFreight care accepta ca paramatru @FreightAmt a tipului de data money si intoarce output-ul pt.urmatorul SELECT:

SELECT S.ShipperID,S.CompanyName,O.OrderID,O.ShipperDate,O.Freight

FROM Shippers AS S JOIN Orders AS O

ON S.ShipperID = O.ShipVia

WHERE O.Freight > @FreightAmt

Urmatorul select selecteaza randurile din functia LargeFreight care au vanzarile in dolari mai mari de 600$:

SELECT *FROM fn_LargeFreight(600)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: