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

 Kodi Klienti Punonjesi Data Vlera
     


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 Xhiro
USA
......
 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