07. Procedurat dhe Funksionet

Stored procedures jane nje pjese e rendesishme e SQL Server. Ne to inkapsulohet nje pjese e rendeishme e llogjikes se biznesit me te dhenat.

Nje stored procedure eshte nje grup komandash SQL i parakompiluar , dhe qe ruhet ne database (nen nyjen "Stored Procedures" ). Programuesit apo administratoret e database mund ti therrasin ne ekzekutim procedurat nga SQL Server Management Studio or from within an application as required.

Te mirat e Stored Procedurave

Si te krijojme nje Stored Procedure

Komanda SQL per krijimin e nje stoered procedure eshte

CREATE PROCEDURE StoredProcedureName AS ...

Shembulli me poshte krijon nje procedure: "MyStoredProcedure":

CREATE PROCEDURE MyStoredProcedure AS SET ROWCOUNT 10 SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC

Pasi kemi krujar proceduren ajo gjendet nen nujen Programability - Stored Procedures ne Object Explorer.

Si te modifikojme nje Stored Procedure

Mund ta fshijme dhe rikrijojme ose mund te perdorim komanden ALTER.

ALTER PROCEDURE MyStoredProcedure AS ...

Si te ekzekutojme nje Stored Procedure

Nje stored procedure mund te therritet ne executim nepermjet komandes EXECUTE ose EXEC. Psh:

EXEC MyStoredProcedure

Nese emri i procedures ka hapesira boshe midis atehee emri i saj duhet vendosur brenda thonejzave dyshe:

EXEC "My Stored Procedure"

Nese procedura merr argumenta ata vendosen pas emrit te procedures:

EXEC MyStoredProcedure @ParameterName="MyParameter"

Per shembull:

EXEC SalesByCategory @CategoryName ="Beverages"

Te punojme me procedurat nepermjet menuve grafike

Per te punuar me nje procedure ndiqni hapat e meposhtem:

• 1. Shkoni tek stored procedure ne bazen Tuaj te te dhenave nen Object Explorer ne SSMS

  2. Klikoni me te djathetn mbi proceduren e duhur dhe zgjidhni "Execute Stored Procedure...":

3. Do te hapet nje dritare dialogu. Futni parametrat qe pret procedura:

4. Klikoni "OK

5. SQL Server do te gjeneroje kodin SQL dhe do te ekzekutoje stored procedure:

Parametrat

Nje parameter eshte nje vlere qe perdor procedura per te kryer funksionet e saj. Kur shkruani nje procedure, mund te percaktoni cilet parametra duhen vendosur nga perdoruesi. Per shembull, nese shkruani nje procedure per te perzgjedhur adresen e nje personi , procedura duhet te dije adresen e cilit individ duhet te ktheje. Ne kete rast , perdoruesu mund ti jape procedures si parameter nje UserId per ti treguar procedures adresen e cilit person deshiron.

System Stored Procedures

SQL Server ka nje numer te madh procedurash te sistemit qe na asistojne ne adminsitrimin e bazes se te dhenave. Pjesa me e madhe e funksioneve qe mund te realzojme nepermjet SQL Server Management Studio mund te realizohen nepermjet nje stored procedure te sistemit, si psh:

• • te konfigurojme accountet

  • te konfigurojme linked servers

  • te krijojme nje database maintenance plan

  • te krijojme katalogun per full text search

  • te konfigure replikimin

  • etj

-- PROCEDURAT

shembull 1

-- krijojme njE procedure qe kthen emrin e nje punonjesi

CREATE PROCEDURE EMRI

(

@kodi INT,

@emri varchar(100) OUTPUT

)

AS

BEGIN

DECLARE @e as varchar(100)

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

where EmployeeID=@kodi

-- si e kthejme emrin e punonjesit

SELECT @emri=@e

END

shembull 2

DECLARE @doc xml

SET @doc = '<?xml version="1.0" ?>

<Order EmployeeId="2" CustomerId="ALFKI" >

<OrderDetails>

<Product ProdId="1" Quantity="6" Price="100"/>

<Product ProdId="2" Quantity="4" Price="245"/>

<Product ProdId="3" Quantity="4" Price="100"/>

</OrderDetails>

</Order>'

exec dbo.shtoporosi @doc

CREATE PROCEDURE shtoporosi

(@docxml as xml)

AS

BEGIN

BEGIN TRANSACTION neworder

Declare @oid as int

Declare @a as int

exec sp_xml_preparedocument @a OUTPUT,@docxml

INSERT INTO Orders(EmployeeId,CustomerId)

select EmployeeId,CustomerID from

OPENXML(@a,'Order',1)

WITH

(

EmployeeId INT '@EmployeeId',

CustomerId VARCHAR(10) '@CustomerId'

)

select @oid=@@IDENTITY

insert into [Order Details](OrderId,ProductId,UnitPrice,Quantity )

select @oid,ProductId,Price,Quantity from

OPENXML(@a,'Order/OrderDetails/Product',1)

WITH

(

ProductId INT '@ProdId',

Quantity float '@Quantity',

Price money '@Price'

)

If @@ERROR<>0 ROLLBACK TRANSACTION neworder

ELSE COMMIT TRANSACTION neworder

END

Funksion qe kthe nje Shopping Cart

create function neworder

(

@kodi as int

)

returns XML

as

BEGIN

Declare @res as XML

SET @res=

(

SELECT OrderID,EmployeeID,CustomerID,OrderDate,

(

select ProductID,UnitPrice,Quantity

from [Order Details]

where [Order Details].OrderID=Orders.OrderID

for XML RAW('Detail'),ROOT('Details'),TYPE

)

FROM Orders

where OrderID=@kodi

for xml raw('OrderInfo'),ROOT('NewOrder')

)

return @res

END

Procedura qe ben import nje shopping Cart ne XML

neper tabelat Order dhe Order Details.

create procedure ImportOrder

(

@neworder as xml

)

as

begin

begin try

begin transaction

Declare @i as int

Declare @dochandle as INT

exec sp_xml_preparedocument @dochandle OUTPUT,@neworder

insert into Orders(EmployeeID,CustomerID,OrderDate)

SELECT *

from Openxml(@dochandle,'NewOrder/OrderInfo',1)

with

(

punonjesi INT '@EmployeeID',

klienti char(5) '@CustomerID',

data datetime '@OrderDate'

)

set @i=@@IDENTITY

insert into [Order Details](OrderID,ProductID,UnitPrice,Quantity)

select @i,produkti,cmimi,sasia

from

Openxml(@dochandle,'NewOrder/OrderInfo/Details/Detail',1)

with

(

produkti INT '@ProductID',

cmimi money '@UnitPrice',

sasia float '@Quantity'

)

Commit transaction

return 0

end try

begin catch

rollback transaction

return -1

end catch

end

Si ta perdorim proceduren

Declare @tmp as xml

set @tmp=dbo.NewOrder(10252)

exec ImportOrder @tmp

Shembull teze provimi

v

Teze provimi

Ushtrim 1 [20 pike]

Ndertoni nje funksion skalar i cili merr si argument kodin e nje kategorie dhe kthen si rezultat nje XML sipas formatit te meposhtem

Zgjidhje

create function cat(@catId INT)

returns xml as

begin

declare @x xml

set @x=

(

select CategoryID '@Kodi',

CategoryName 'Emri',

(

select top 2

ProductID '@Kodi',

ProductName 'Emri',

(

select top 2

OrderID 'ShiturNePorosine',

UnitPrice 'CmimiShitjes',

Quantity 'Sasia'

From [Order Details]

where Products.ProductID=[Order Details].ProductID

for XML path('Shitje'),TYPE,ROOT('Shitjet')

)

from Products

where Products.CategoryID=Categories.CategoryID

for XML PATH('Produkt'),TYPE,ROOT('Produktet')

)

from Categories

where CategoryID=@catId

for XML PATH('Kategorite'),

ROOT('Shitjet')

)

return @x

end

Ushtrim 2 [30 pike]

Ndertoni nje funksion tabele i cili merr si argument dy data dt1 dhe dt2 dhe kodin e nje punonjesi. Funksioni kthen nje tabele me dy kollona:

Muaji, xhiro_financiare.

Tabela do te permbaje per cdo muaj te periudhes midis dt1 dhe dt2, vleren e xhiros financiare qe ka realizuar ai punonjes ne muajin perkates

Ushtrim 3 [20 pike]

Ndertoni nje funksion tabele i cili kthen nje tabele me te dhenat e punonjesve (Kodin,Emrin,Mbiemrin,Emrin e shefit,Mbiemrin e shefit,Xhiron Financiare) qe ka realizuar ai punonjes.

Zgjidhje

select e1.EmployeeID,e1.FirstName,e1.LastName,e2.FirstName,e2.LastName,

SUM(UnitPrice*(1-Discount)*Quantity),

SUM(UnitPrice*(1-Discount)*Quantity)/COUNT(*)

from Employees e1 inner join Employees e2 on e1.ReportsTo=e2.EmployeeID

inner join Orders on e1.employeeid=orders.EmployeeID

inner join [Order Details] on orders.OrderID=[Order Details].OrderID

group by

e1.EmployeeID,e1.FirstName,e1.LastName,e2.FirstName,e2.LastName

Ushtrim 4 [20 pike]

Ndertoni nje funksion tabele qe merr si argument nje numer dhe kthen listen e kategorive te produkteve dhe numrin e produkteve te shitura nga cdo kategori vetem per kategorite nga te cilat jane shitur me pak se 200 produkte. Per kategorite qe nuk jane produkte te shitura funksioni duhet te ktheje vleren 0.

select Categories.CategoryName,COUNT(Products.ProductID)

from (Orders

inner join [Order Details]

on orders.OrderID=[Order Details].OrderID

inner join

Products

on [Order Details].ProductID=Products.ProductID

)

right outer join Categories

on Products.CategoryID=Categories.CategoryID

group by Categories.CategoryName

having COUNT(Products.ProductID)<200

Ushtrim 5 [30 pike]

Ndertoni nje procedure qe merr si argument nje variabel XML sipas formatit te ushtrimit 1.Procedura duhet te shtoje ne tabelen categories ato kategori qe nuk ekzistojne, tashme. Gjithashtu procedura duhet te shtoje ne tabelen Products produktet qe nuk ndodhen. Si cmim I produkteve te merret cmimi i shitjes se porosise se pare ne te cilen eshte shitur ai produkt.

Procedura duhet te ktheje ne argumenta dales numrin e kategorive te reja te shtuara dhe numrin e produkteve te reja te shtuara ne tabelat perkatese.

[15 pike]

5. Ndertoni nje funksion tabele qe merr si argument nje vlere financiare xhiro te tipit float dhe dy data dt1 dhe dt2. Funksioni kthen nje liste me te dhenat e punonjesve qe kane realizuar nje xhiro financiare me te madhe se argumenti xhiro ne periudhen midis datave dt1 dhe dt2.

Teze provimi

Ushtrim 1 [20 pike]

Ndertoni nje funksion skalar i cili merr si argument kodin e nje kategorie dhe kthen si rezultat nje XML sipas formatit te meposhtem

Zgjidhje

create function cat(@catId INT)

returns xml as

begin

declare @x xml

set @x=

(

select CategoryID '@Kodi',

CategoryName 'Emri',

(

select top 2

ProductID '@Kodi',

ProductName 'Emri',

(

select top 2

OrderID 'ShiturNePorosine',

UnitPrice 'CmimiShitjes',

Quantity 'Sasia'

From [Order Details]

where Products.ProductID=[Order Details].ProductID

for XML path('Shitje'),TYPE,ROOT('Shitjet')

)

from Products

where Products.CategoryID=Categories.CategoryID

for XML PATH('Produkt'),TYPE,ROOT('Produktet')

)

from Categories

where CategoryID=@catId

for XML PATH('Kategorite'),

ROOT('Shitjet')

)

return @x

end

Ushtrim 2 [30 pike]

Ndertoni nje funksion tabele i cili merr si argument dy data dt1 dhe dt2 dhe kodin e nje punonjesi. Funksioni kthen nje tabele me dy kollona:

Muaji, xhiro_financiare.

Tabela do te permbaje per cdo muaj te periudhes midis dt1 dhe dt2, vleren e xhiros financiare qe ka realizuar ai punonjes ne muajin perkates

Ushtrim 3 [20 pike]

Ndertoni nje funksion tabele i cili merr si argument nje vlere financiare dhe kthen nje tabele me te dhenat e punonjesve (Kodin,Emrin,Mbiemrin,Emrin e shefit,Mbiemrin e shefit,Xhiron Financiare) qe ka realizuar ai punonjes dhe vleren mesatare te prosive qe ka trajtuar ai.

Zgjidhje

select e1.EmployeeID,e1.FirstName,e1.LastName,e2.FirstName,e2.LastName,

SUM(UnitPrice*(1-Discount)*Quantity),

SUM(UnitPrice*(1-Discount)*Quantity)/COUNT(*)

from Employees e1 inner join Employees e2 on e1.ReportsTo=e2.EmployeeID

inner join Orders on e1.employeeid=orders.EmployeeID

inner join [Order Details] on orders.OrderID=[Order Details].OrderID

group by

e1.EmployeeID,e1.FirstName,e1.LastName,e2.FirstName,e2.LastName

Ushtrim 4 [20 pike]

Ndertoni nje funksion tabele qe merr si argument nje numer dhe kthen listen e kategorive te produkteve dhe numrin e produkteve te shitura nga cdo kategori vetem per kategorite nga te cilat jane shitur me pak se 200 produkte. Per kategorite qe nuk jane produkte te shitura funksioni duhet te ktheje vleren 0.

select Categories.CategoryName,COUNT(Products.ProductID)

from (Orders

inner join [Order Details]

on orders.OrderID=[Order Details].OrderID

inner join

Products

on [Order Details].ProductID=Products.ProductID

)

right outer join Categories

on Products.CategoryID=Categories.CategoryID

group by Categories.CategoryName

having COUNT(Products.ProductID)<200

Ushtrim 5 [30 pike]

Ndertoni nje procedure qe merr si argument nje variabel XML sipas formatit te ushtrimit 1.Procedura duhet te shtoje ne tabelen categories ato kategori qe nuk ekzistojne, tashme. Gjithashtu procedura duhet te shtoje ne tabelen Products produktet qe nuk ndodhen. Si cmim I produkteve te merret cmimi i shitjes se porosise se pare ne te cilen eshte shitur ai produkt.

Procedura duhet te ktheje ne argumenta dales numrin e kategorive te reja te shtuara dhe numrin e produkteve te reja te shtuara ne tabelat perkatese.

[15 pike]

5. Ndertoni nje funksion tabele qe merr si argument nje vlere financiare xhiro te tipit float dhe dy data dt1 dhe dt2. Funksioni kthen nje liste me te dhenat e punonjesve qe kane realizuar nje xhiro financiare me te madhe se argumenti xhiro ne periudhen midis datave dt1 dhe dt2.

Teze provimi

Ushtrim 1 [20 pike]

Ndertoni nje funksion skalar i cili merr si argument kodin e nje kategorie dhe kthen si rezultat nje XML sipas formatit te meposhtem

Zgjidhje

create function cat(@catId INT)

returns xml as

begin

declare @x xml

set @x=

(

select CategoryID '@Kodi',

CategoryName 'Emri',

(

select top 2

ProductID '@Kodi',

ProductName 'Emri',

(

select top 2

OrderID 'ShiturNePorosine',

UnitPrice 'CmimiShitjes',

Quantity 'Sasia'

From [Order Details]

where Products.ProductID=[Order Details].ProductID

for XML path('Shitje'),TYPE,ROOT('Shitjet')

)

from Products

where Products.CategoryID=Categories.CategoryID

for XML PATH('Produkt'),TYPE,ROOT('Produktet')

)

from Categories

where CategoryID=@catId

for XML PATH('Kategorite'),

ROOT('Shitjet')

)

return @x

end

Ushtrim 2 [30 pike]

Ndertoni nje funksion tabele i cili merr si argument dy data dt1 dhe dt2 dhe kodin e nje punonjesi. Funksioni kthen nje tabele me dy kollona:

Muaji, xhiro_financiare.

Tabela do te permbaje per cdo muaj te periudhes midis dt1 dhe dt2, vleren e xhiros financiare qe ka realizuar ai punonjes ne muajin perkates

Ushtrim 3 [20 pike]

Ndertoni nje funksion tabele i cili merr si argument nje vlere financiare dhe kthen nje tabele me te dhenat e punonjesve (Kodin,Emrin,Mbiemrin,Emrin e shefit,Mbiemrin e shefit,Xhiron Financiare) qe ka realizuar ai punonjes dhe vleren mesatare te prosive qe ka trajtuar ai.

Zgjidhje

select e1.EmployeeID,e1.FirstName,e1.LastName,e2.FirstName,e2.LastName,

SUM(UnitPrice*(1-Discount)*Quantity),

SUM(UnitPrice*(1-Discount)*Quantity)/COUNT(*)

from Employees e1 inner join Employees e2 on e1.ReportsTo=e2.EmployeeID

inner join Orders on e1.employeeid=orders.EmployeeID

inner join [Order Details] on orders.OrderID=[Order Details].OrderID

group by

e1.EmployeeID,e1.FirstName,e1.LastName,e2.FirstName,e2.LastName

Ushtrim 4 [20 pike]

Ndertoni nje funksion tabele qe merr si argument nje numer dhe kthen listen e kategorive te produkteve dhe numrin e produkteve te shitura nga cdo kategori vetem per kategorite nga te cilat jane shitur me pak se 200 produkte. Per kategorite qe nuk jane produkte te shitura funksioni duhet te ktheje vleren 0.

select Categories.CategoryName,COUNT(Products.ProductID)

from (Orders

inner join [Order Details]

on orders.OrderID=[Order Details].OrderID

inner join

Products

on [Order Details].ProductID=Products.ProductID

)

right outer join Categories

on Products.CategoryID=Categories.CategoryID

group by Categories.CategoryName

having COUNT(Products.ProductID)<200

Ushtrim 5 [30 pike]

Ndertoni nje procedure qe merr si argument nje variabel XML sipas formatit te ushtrimit 1.Procedura duhet te shtoje ne tabelen categories ato kategori qe nuk ekzistojne, tashme. Gjithashtu procedura duhet te shtoje ne tabelen Products produktet qe nuk ndodhen. Si cmim I produkteve te merret cmimi i shitjes se porosise se pare ne te cilen eshte shitur ai produkt.

Procedura duhet te ktheje ne argumenta dales numrin e kategorive te reja te shtuara dhe numrin e produkteve te reja te shtuara ne tabelat perkatese.

[15 pike]

5. Ndertoni nje funksion tabele qe merr si argument nje vlere financiare xhiro te tipit float dhe dy data dt1 dhe dt2. Funksioni kthen nje liste me te dhenat e punonjesve qe kane realizuar nje xhiro financiare me te madhe se argumenti xhiro ne periudhen midis datave dt1 dhe dt2.