Funksionet

Ekzistojne tre lloje funksionesh:

• 1. Funksionet skalare

  2. Funksionet me vlere tabele te thjeshta (Inline Table-Valued functions)

  3. Funksionet me vlere tabele me shume komanda (Multi-statement table-valued functions)

Funksionet skalare

Funksionet skalare kthejne nje vlere te vetme qe do te jete e nje tipi te dhenash qe njohur nga SQL Server si INT, VARCHAR , DATETIME, float, money etj.

Sintaksa e pergjithshme per deklarimin e nje funksioni skalar eshte si me poshte:

CREATE FUNCTION <emri i funksionit> (<lista e argumenave te ndare me presje>)

RETURNS <tipi i te dhenave qe kthen funksioni>

AS

BEGIN

< kodi SQL qe perben llogjiken e funksionit>

RETURN <vlera qe kthen funksioni>

END

Klauzola RETURNS ne fillim te deklarimit te funksionit percakton tipin e te dhenave qe kthen funksioni per shembull INT, VARCHAR etj,

ndersa komanda RETURN ne trupi e funksionit (brenda BEGIN dhe END) shkakton daljen nga funksioni dhe kthimin e vleres se llogaritur brenda trupit te funksionit. Kjo vlere duhet te jete sipas tipit te dhenave te deklaruar nga klauzola RETURNS ne fillim te funksionit, per shembull nese klauzola RETURNS ka percaktuar si INT si tipi te dhenash komanda RETURN do te ktheje patjeter nje numer te plote.

Per shembull

Le te krijojme nje funksion skalar i cili merr si argument kodin e nje punonjesi dhe kthen Emrin dhe Mbiemrin te tij te bashkuar me nje hapesire ne mes.

CREATE FUNCTION EmployeeName (@eid INT)

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @res AS VARCHAR(100)

SELECT @res=Firstname+' '+ Lastname from Employees

WHERE EmployeeId=@eid

RETURN @res

END

Si i perdorim funksionet skalare?

Funksionet skalare mund te therriten kudo ku mund te perdoret nje shprehje skalare.

Disa nga perdorimet e funksionit EmployeeName do te ishin:

Shembull 1

print dbo.EmployeeName(1)

qe do te afishonte

Nancy Davolio

Shembull 2

select employeeid,dbo.EmployeeName(employeeid) 'empname' from Employees

qe do te afishonte nje liste me kodet dhe emrat e punonjesve si ne figuren me poshte:

Shembull 3

select OrderId,Orderdate,dbo.EmployeeName(employeeid) 'empname' from Orders

qe do te afishonte nje liste me porosite si me poshte:

USHTRIME

Ushtrim 1

Te ndertojme nje funksion skalar qe merr si argument kodin e nje porosie dhe kthen vleren financiare te saj

Zgjidhje

CREATE FUNCTION vlereporosie

(

@oid as INT

)

RETURNS money

as

begin

Declare @vlera as money

SELECT @vlera=

SUM(UnitPrice*QUantity)

FROM [Order Details]

where OrderID=@oid

--group by OrderID

return @vlera

end

Ushtrim 2

Te ndertojme nje funksion skalar qe merr si argument kodin e nje klienti dhe kthen xhiron financiare qe ai ka realizuar

Zgjidhje

CREATE FUNCTION xhiroklienti(@cid as char(5))

returns money

as begin

Declare @res as money

select @res=SUM(UnitPrice*Quantity) from [Order Details]

inner join Orders

on [Order Details].OrderID=Orders.OrderID

where CustomerID=@cid

group by CustomerID

return @res

end

Ushtrim 3

Te ndertojme nje funksion skalar qe merr si argument kodin e punonjesit dhe kthen xhiron financiare te tij

Zgjidhje

CREATE FUNCTION xhiropunonjesi(@eid as INT)

returns money

as begin

Declare @res as money

select @res=SUM(UnitPrice*Quantity) from [Order Details]

inner join Orders

on [Order Details].OrderID=Orders.OrderID

where EmployeeID=@eid

group by EmployeeID

return @res

end

Ushtrim 4

Ndertoni nje funksion skalar qe merr kodin e nje punonjesi dhe kthen numrin e porosive qe ka realizuar ai punonjes

Zgjidhje

CREATE FUNCTION porosi_punonjesi(@eid as INT)

returns INT

as begin

Declare @res as INT

select @res=Count(*) from Orders

where EmployeeID=@eid

group by EmployeeID

return @res

end

Ushtrim 5

Ndertoni nje funksion skalar qe merr kodin e nje klienti dhe kthen numrin e porosive qe ka realizuar ai klient

Zgjidhje

CREATE FUNCTION porosi_klienti(@cid as CHAR(5))

returns INT

as begin

Declare @res as INT

select @res=Count(*) from Orders

where CustomerID=@cid

group by CustomerID

return @res

end

Ushtrim 6

Afishoni librin e shitjeve ku vlera e porosise, emri i punonjesit dhe emri i klientit te merren me funksion skalar

Dy funksionet e tjera ndihmese:

CREATE FUNCTION emripun

(

@eid as INT

)

returns varchar(150)

as

begin

DECLARE @res as varchar(150)

SELECT @res=(TitleOfCourtesy+' '+Firstname+' '+Lastname)

from Employees where EmployeeID=@eid

return @res

end

dhe

CREATE FUNCTION EmerKlienti

(

@cid as char(5)

)

RETURNS VARCHAR(70)

AS

BEGIN

Declare @emri as varchar(70)

select @emri=CompanyName from Customers

where CustomerID=@cid

RETURN @emri

END

Query per librin e shitjeve tani shkruhet si me poshte:

select OrderId,

dbo.emripun(Orders.EmployeeID)'Employee',

dbo.EmerKlienti(Orders.CustomerID) 'Customer',

dbo.vlereporosie(Orders.OrderID) 'Amount'

from Orders

Ushtrim - Ndertoni nje funksion skalar qe merr si argument kodin e nje punonjesi dhe dy data dt1 dhe dt2. Funksioni kthen numrin e porosive qe ka trajtuar ky punonjes ne periudhen nga data1 ne daten 2

CREATE FUNCTION dbo.nrporosish

(

@eid INT,

@dt1 as datetime,

@dt2 as datetime

)

returns INT

as

begin

declare @p as INT -- numeri i porosive

SELECT @p=COUNT(*) FROM Orders

where EmployeeID=@eid and ( OrderDate between @dt1 and @dt2)

group by EmployeeID

return 1

end

Ushtrim 7

Ndertoni nje funksion skalar qe merr si argument kodin e nje punonjesi dhe dy data dt1 dhe dt2. Funksioni kthen xhiron financare qe ka realizuar ky punonjes ne periudhen nga data1 ne daten 2

CREATE FUNCTION dbo.xhirofinanciare

(

@eid INT,

@dt1 as datetime,

@dt2 as datetime

)

returns money

as

begin

declare @res as money -- numeri i porosive

SELECT @res=SUM(UnitPrice*Quantity) FROM Orders inner JOIN [Order Details]

On Orders.OrderID=[Order Details].OrderID

where EmployeeID=@eid and ( OrderDate between @dt1 and @dt2)

group by EmployeeID

return @res

end

Ushtrim 8

Te ndertojme nje funksion qe merr si argument nje numer te plote n. Funksioni gjen dhe kthen shumen e n numrave te pare natyrore

Zgjidhje

CREATE FUNCTION shuma

(

@n as int

)

returns int

as

begin

declare @i as int -- deklarim i variablave

declare @s as int

set @i=1 -- vleredhenia

set @s=0

while(@i<=@n) -- cikel

begin -- bllok kodi

set @s=@s+@i

set @i=@i+1

end

return @s

end

print dbo.shuma(10)

print datepart(mm,'1996-1-2')

print datediff(mm,'1996-1-1','1997-1-1')

select OrderID,OrderDate from Orders

FUNKSIONET TABELE TE THJESHTA

Inline Table Valued Functions

Funksionet tabele shpesh konsiderohen si VIEW te parametrizuara. Ne dallim nga funksionet skalare keto funksione kthejne nje bashkesi rreshtash apo nje "tabele".

Trupi i ketyre funksioneve eshte vetem nje komande SELECT e cila nga ana e saj mund te jete komplekse dhe te perfshije JOIN apo nen query te lidhura me te me operatoret IN dhe EXISTS.

Sintaksa e pergjithshme e deklarimit te nje funksioni tabele eshte :

CREATE FUNCTION <emri i funksionit> (<lista e argumenave te ndare me presje>)

RETURNS TABLE

AS

RETURN

(

< komanda SELECT qe perben llogjiken e funksionit>

)

Le ta ilustrojme me nje shembull krijimin e nje funksioni te thjeshte tabele.

Te ndertojme nje funksion i cili merr si argument kodin e nje punonjesi @eid dhe dy data @dt1 dhe @dt2. Funksioni do te ktheje nje liste me porosite e trajtuara nga punonjesi me kodin @eid ne periudhen midis datave @dt1 dfhe @dt2.

CREATE FUNCTION EmpOrders (@eid INT, @dt1 as DATETIME,@dt2 as DATETIME)

RETURNS TABLE

AS

RETURN

(

SELECT * from Orders where EmployeeID=@eid AND (OrderDate between @dt1 AND @dt2)

)

Funksionet tabele therriten nepermjet komandes SELECT kudo ku pritet nje bashkesi rreshtash.

Per shembull funksioni i krijuar me siper do te therritej si me poshte:

SELECT OrderID,CustomerID,EmployeeID,OrderDate

FROM dbo.EmpOrders(1,'1996-1-1','1997-1-1')

dhe do te prodhonte nje rezultat te ngjashem me rezultatin e paraqitur ne figuren me poshte:

shembuj perdorimi

Funksionet tabele ne JOIN

select OrderID,OrderDate,

e.EmployeeID,e.FirstName,e.LastName

from dbo.EmpOrders(1,'1996-1-1','1997-1-1') t

INNER JOIN Employees e

on t.EmployeeID=e.EmployeeID

Funksionet tabele ne Group by

select Month(OrderDate) as MonthofActivity,

e.EmployeeID,e.FirstName,e.LastName, Count(*) as NrOfOrders

from dbo.EmpOrders(1,'1996-1-1','1997-1-1') t

INNER JOIN Employees e

on t.EmployeeID=e.EmployeeID

group by Month(OrderDate),

e.EmployeeID,e.FirstName,e.LastName

Order by MonthofActivity

Ushtrim 1

Ndertoni nje funksion tabele qe merr si argument kodin e nje furnitori dhe kthen listen e produkteve qe blejme nga ai furnitor

Zgjidhje

create function productsbysupplier(@sid int)

returns table

as

return

(

select * from Products where SupplierID=@sid

)

Ushtrim 2

Ndertoni nje funksion tabele qe merr si argument kodin e nje kategorie dhe kthen listen e produkteve qe i perkasin asaj kategorie

Zgjidhje

create function productsbycatewgory(@cid int)

returns table

as

return

(

select * from Products where CategoryID=@cid

)

Ushtrim 3

Ndertoni nje funksion tabele qe merr si argument kodin e nje porosie dhe kthen detajet e asaj porosie

Zgjidhje

create function order_detils(@oid int)

returns table

as

return

(

select * from [Order Details] where OrderID=@oid

)

Ushtrim 4

Ndertoni nje funksion tabele qe merr si argument dy data @dt1 dhe @dt2 funksion kthen librin e shitjeve qe kompanise Northwinnd midis datave @dt1 dhe @dt2 si pas formatit te meposhtem

Zgjidhje

create function salesbydate(@dt1 datetime,@dt2 datetime)

returns table

as

return

(

select OrderID as kodi,

dbo.employeename(EmployeeID) as punonjesi,

dbo.EmerKlienti(CustomerID) as klienti,

Orderdate as data,

dbo.vlereporosie(OrderID) as vlera

from Orders

where OrderDate between @dt1 and @dt2

)

Ushtrim 5

Ndertoni nje funksion tabele te thjeshte

qe merr si argument kodin e nje punonjesi

dhe kthen listen e punonjesve vartes te tij

Zgjidhje

CREATE FUNCTION shefi

(

@eid int

)

returns table

as

return

(

select * from Employees

where ReportsTo=@eid

)

select * from shefi(3)

Ushtrim 6

Te ndertojme nje funksion kompleks tabele qe merr si argument dy data @dt1 dhe @dt2. Funksioni kthen shtetin dhe xhiron financiare te realizuar ne ate shtet ne periudhen midis @dt1 dhe @dt2.

Rezultati do te ishte sipas formatit te meposhtem:

Zgjidhje

CREATE FUNCTION XhiroSipasShtetit

(

@dt1 date,

@dt2 date

)

RETURNS TABLE

AS

RETURN

(

select o.ShipCountry,

SUM(od.Quantity*od.UnitPrice) as xhiro

from Orders o

inner join

[Order Details] od

on o.OrderID=od.OrderID

where o.OrderDate

between @dt1 and @dt2

group by ShipCountry

)

select * from

dbo.XhiroSipasShtetit('1996-6-1','1997-1-1')

FUNKSIONET TABELE KOMPLEKSE

Multi-statement table-valued functions

Funksionet tabele komplekse jane te ngjashme me funksionet e thjeshta tabele , pasi gjithashtu edhe ato kthejne nje vlere te tipit tabele. Dallimi qendron ne faktin qe funksionet tabele komplekse nuk jane te perbere vetem nga nje komande SELECt qe e cila ne disa raste mund te paraqitet si nje kufizim. Funksionet tabele komplekse deklarojne ne kokene tyre strukturen e tabeles qe do te kthejne dhe i vendosin asaj nje emer. Ne trupin e funksionit shtohen rreshta ne kete tabele te deklaruar ne fillim per te pergatitur rezultatin e deshiruar.

Po e ilustrojme krijimin e funksioneve tabele komplekse nepermjet nje shembulli:

CREATE FUNCTION EmployeesByTitle(@title as CHAR(3))

RETURNS @ept TABLE

(

Kodi int PRIMARY KEY,

Emri varchar(50),

Mbiemri varchar(50),

Titulli CHAR(3)

)

AS

BEGIN

INSERT INTO @ept(Kodi,Emri,Mbiemri,Titulli)

SELECT EmployeeID,FirstName,Lastname,TitleOfCourtesy FROM Employees

WHERE TitleOfCourtesy = @title

RETURN

END

Do ta therrisnim kete funksion

SELECT * FROM EmployeesByTitle('Ms.')

CREATE FUNCTION liber(@dt1 DATETIME, @dt2 DATETIME)

RETURNS @L TABLE

(

Kodi INT PRIMARY KEY,

Data DATETIME,

Punonjesi VARCHAR(50),

Klienti VARCHAR(50),

Vlera MONEY

)

AS

BEGIN

INSERT INTO @L(Kodi,Data,Punonjesi,Klienti,Vlera)

SELECT

Orders.OrderID,Orders.OrderDate,

FirstName+' '+LastName,

Customers.CompanyName,

SUM(UnitPrice*Quantity)

FROM Orders inner join [Order Details]

ON Orders.OrderID=[Order Details].OrderID

INNER JOIN Employees

ON Orders.EmployeeID=Employees.EmployeeID

INNER JOIN

Customers

ON Orders.CustomerID=Customers.CustomerID

WHERE OrderDate BETWEEN @dt1 and @dt2

GROUP BY

Orders.OrderID,Orders.OrderDate,

FirstName+' '+LastName,

Customers.CompanyName

RETURN

END

SELECT * FROM dbo.liber('1996-1-1','1997-1-1')

Ushtrim 2

Te ndertojme nje funksion tabele kompleks qe kthen lidhjet e nje punonjesi, si me poshte:

Zgjidhje

CREATE FUNCTION lidhjet(@eid INT)

RETURNS @L TABLE

(

Nr INT Primary key identity(1,1),

Emri varchar(50),

Mbiemri varchar(50),

Kompania varchar(250),

Mardhenia varchar(50)

)

AS

BEGIN

INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)

select e1.FirstName,e1.LastName,'Northwind','Shefi Direkt'

from Employees e1

inner join employees e2

on e1.EmployeeID=e2.reportsto

where e2.EmployeeID=@eid

INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)

select e1.FirstName,e1.LastName,'Northwind','Vartes Direkt'

from Employees e1

where e1.ReportsTo=@eid

INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)

select ContactName,ContactName,

CompanyName,'Klient'

from Customers

where CustomerId IN

(

select distinct CustomerID

from Orders where EmployeeID=@eid

)

INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)

select ContactName,ContactName,

CompanyName,'Furnitor' from Suppliers

where SupplierID in

(

Select supplierID from Products

where productID IN

(

select DISTINCT ProductId from [Order Details]

WHERE

OrderId IN

(

SELECT OrderID from Orders Where EmployeeID=@eid

)

)

)

INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)

SELECT 'not available','not available',

CompanyName,'Shoqeri Transporti'

FROM Shippers

WHERE ShipperID IN

(

select DISTINCT ShipVia from orders

where EmployeeID=@eid

)

RETURN

END

Funksionet deterministe

Funksionet deterministe dhe funksionet deterministe

Ne disa raste eshte e nevojshme te dallojme midis funksionetve deterministe dhe jo deterministe. Nje nga keto raste do te ishte kur kemi ndertar nje VIEW , nje kollone e se ciles merr vlere nga nje funksion. Kjo VIEW ndonese mund te indeksohet pergjithesisht , nese funksioni qe i jep vlere kollones ne fjale eshte jo determinist nuk mund te indeksohet.

Nje funksion quhet determinist ne qofte se per te njejtet argumenta hyres kthen gjithmone te njejten vlere, sa here qe therritet.

Per shembull funksioni shuma i cili merr si argument dy numra te plote dhe kthen shumen e tyre eshte funksion determinist. Funksioni EmployeeName qe ndertuam me lart ne kete leksion eshte funksion determinist.

Ndersa funksioni me poshte:

CREATE FUNCTION mosha(@eid int)

returns INT

as begin

declare @res as int

select @res=DATEDIFF(YYYY,Birthdate,GETDATE()) from Employees

RETURN @res

end

Nuk eshte funksion determinist pasi vlera qe ai kthen per te njejtin argument hyres mund te jete e ndryshme nese funksioni therritet sot dhe pas nje viti.