chapter 09 - Functions

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

Ushtrim 9

Ndertoni nje funksion qe merr si argument kodin e nje produkti dhe kthen sasine e shitur nga ai produkt.

Zgjidhje

CREATE FUNCTION sasi_shitur

(

@pid as int

)

returns INT

AS

BEGIN

DECLARE @sasi INT

select @sasi=SUM(quantity)

from [Order Details] od

where ProductID=@pid

return @sasi

END

Ushtrim 10

Ndertoni nje funksion qe merr si argument kodin e nje produkti dhe kthen te ardhuarat qe ka gjeneruara ai produkt nga shitjet.

Zgjidhje

CREATE FUNCTION vlera_shitur

(

@pid INT

)

RETURNS money

AS

BEGIN

DECLARE @SH money

select @sh= SUM(Quantity*UNitPrice)

from [Order Details]

WHERE ProductID=@pid

RETURN @SH

END

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:

ShipCountry

USA

......

Xhiro

309.09

......

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