Funksionet me vlere tabele te thjeshta (Inline Table-Valued functions)
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
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
