Uskladištene procedure SQL Server-a

Uskladištene procedure su osnova svake klijent/server aplikacije. Uskladištene procedure vam omogućavaju da se obrada podataka obavi na serveru. Prednosti uskladištenih procedura su:

• Omogućavaju da razdvojite klijentsku aplikaciju od strukture baze podataka.
• Pojednostavljuju pravljenje klijentske aplikacije.
• Izvršavaju se na serveru.
• Napisani programski kod, koristi se više puta.
• Greške se obrađuju na serveru.
• Olakšavaju zaštitu podataka.
• Uskladištene procedure se brže izvršavaju jer se kompajliraju.
• Doprinose stabilnosti aplikacije.
• Smanjuju mogućnost zagušenja u računarskoj mreži.
• Kada se pravi uskladištena procedura, pravi se i plan izvršavanja upita. Plan izvršavanja upita sadrži najefikasniji način za izvršavanje uskladištene procedure na osnovu postojećih indeksa.

 

Deklarisanje i korišćenje promenljivih

Promenljive možete da deklarišete u uskladištenim procedurama isto kao što možete da ih deklarišete u podupitima i funkcijama. Promenljiva se definiše rezervisanom rečju DECLARE.

DECLARE @VariableName DataType [(length)], @VariableName DataType [(length)]

 

Primer:

Declare @FirstName VarChar(35)

 

Promenljivima koje nisu inicijalizovane dodeljuje se vrednost Null. Promenljivoj zadajte vrednost pomoću iskaza SELECT.

SELECT @FirstName = ‘Alexis’

 

Promenljiva se koristi na sledeći način:

DECLARE @strCompany varchar (50)
SELECT @strCompany = Upper(CompanyName)
FROM Customer
WHERE CustomerID = ‘ALFKI’
SELECT @strCompany

 

U primeru je deklarisana promenljiva @strCompany. Pomoću programskog koda se upisuje  u promenljivu.

 

Kontrolisanje toka

Kontrolisanje izvršavanja uskladištenih procedura se postiže sa konstruktama:

• BEGIN ... END,
• IF ... ELSE
• GOTO
• RETURN
• CASE
• WHILE

 

Primer konstrukta IF … THEN:

DECLARE @Locale VarChar (20), @Country VarChar(30)
SELECT @Country = Country
FROM Customers
WHERE CustomerID = 'ALFKI'
IF @Country = 'USA'
 BEGIN
 SELECT @Locale = 'Domestic'
 END
ELSE
 BEGIN
 SELECT @Locale = 'Foreign'
 END
SELECT CustomerID as imeid, @locale as Ime
FROM Customers
WHERE CustomerID = 'ALFKI‘

 

Na početku se deklarišu dve promenljive: promenljiva @Locale i promenljiva @Country. Sadržaj polja County za mušteriju ALFKI se dodeljuje promenljivoj @Country. Zatim se proverava sadržaj promenljive @Country, pri čemu se u promenljivu @Locale zapisuje odgovarajuća vrednost. Na kraju se prikazuje CustomerID i sadržaj promenljive @locale za mušteriju za koju je CustomerID jednak ALFKI.

 

Konstrukt IF ... ELSE

Konstrukt IF ... ELSE koristite kako biste unutar uskladištene procedure doneli odluku. Odluka se zasniva na parametrima koji se prosleđuju u uskladištenu proceduru. Definicija konstrukta IF ... ELSE glasi:

IF(Uslov)
 BEGIN
--  Izvršavanje procedure
END

 

Samo se neki iskazi izvršavaju kada je uslov ispunjen.

Definicija konstrukta IF ... ELSE kada treba izvršiti iskaze u slučaju da uslov nije ispunjen:

IF (Uslov)
 BEGIN
-- Izvršavanje procedure

END
 ELSE
   BEGIN

 -- Izvršavanje procedure

 END

 

Primer:

DECLARE @Locale VarChar (20), @Country VarChar(30)
SELECT @Country = Country
FROM Customers
WHERE CustomerID = 'ALFKI'
IF @Country = 'USA'
BEGIN
 SELECT @Locale = 'Domestic'
 PRINT 'Ovo je domestic'
 PRINT ' '
 PRINT 'Hello there'
 END
ELSE
 BEGIN
  SELECT @Locale = 'Foreign'
  PRINT 'This is foreign'
  PRINT '  '
  PRINT 'Hello there'
 END
SELECT CustomerID, @Locale
FROM Customers
WHERE CustomerID = 'ALFKI'

 

Ako je uslov ispunjen, izvršava se iskaz iza IF, a ako uslov nije ispunjen, izvršavaju se iskazi iza ELSE. Kada posle iskaza IF ne biste upotrebili konstrukt  BEGIN ... END, prouzrokovala bi se greška.

 

Konstrukt BEGIN ... END

Omogućava da grupišete niz iskaza. Kada ne bi postojao konstrukt BEGIN ... END, onda bi se izvršio samo prvi iskaz nakon IF ili nakon ELSE.

 

Iskazi GOTO i RETURN i labele

Iskaz GOTO koristite kako biste prešli na labelu unutar uskladištene procedure. Promoću iskaza RETURN bezuslovno se prekida izvršavanje uskladištene procedure.

 

Primer:

IF Month(GetDate()) > 6
BEGIN
Print 'In IF Statement'
GOTO MyLabel
END
 
SELECT CustomerID, CompanyName FROM Customers
MyLabel:
SELECT OrderDate, OrderDate FROM Orders

 

U primeru se proverava da li je mesec tekućeg datuma veći od šest. Ukoliko jeste, prikazuje se rečenica “In IF Statement”, a izvršavanje programskog koda se nastavlja od labele MyLabel. Uskladištena procedura zatim izdvaja podatke iz tabele Orders. Ukoliko je mesec tekućeg datuma manji ili jednak vrednosti šest, uskladištena procedura prvo izdvaja podatke iz tabele Customer. Izvršavanje programskog koda se zatim nastavlja od labele MyLabel i izdvajaju se podaci iz tabele Orders.

 

Bezuslovno prekidanje procedure obezbeđuje iskaz “RETURN”

Primer:

IF Month(GetDate()) > 6
BEGIN
Print 'In IF Statement'
GOTO MyLabel
 END
 
SELECT CustomerID, CompanyName FROM Customers
RETURN
MyLabel:SELECT OrderDate, OrderDate FROM Orders

 

Ako je u primeru mesec tekućeg datuma veći od šest, onda se prikazuje rečenica “In IF Statement” i procedura izdvaja podatke iz tabele Orders. Ukoliko je mesec tekućeg datuma manji ili jednak vrednosti šest, onda se podaci izdvajaju iz tabele Customers i izlazi se iz procedure. Pošto se koristi iskaz RETURN, procedura ne izdvaja podatke iz tabele Orders.

Kada je mesec tekućeg datuma manji ili jednak vrednosti šest, onda se podaci izdvajaju iz tabele Customers i izlazi se iz procedure. Pošto se koristi iskaz RETURN, procedura ne izdvaja podatke iz tabele Orders.

Primer:

IF Month(GetDate()) < 6
BEGIN
Print 'In IF Statement'
GOTO MyLabel
END
SELECT CustomerID, CompanyName FROM Customers
RETURN
MyLabel:
SELECT OrderID,OrderDate, OrderDate FROM Orders

 

Iskaz CASE

Pomoću iskaza CASE vrednost tabele se zamenjuje alternativnom vrednošću.

Definicija iskaza CASE:

Case InputExpression
WhenExpression THEN ResultExpression
[... n  ]
[ELSE ElseResultExpression ]
END

Konstrukt Case se može pisati i na sledeći način:

CASE
 WHEN Expression Then TruePart
 ELSE FalsePart
END

 

Primer 1:

Select OrderID, ShipVIA,OrderDate,
CASE ShipVIA
 WHEN 1 THEN 'UPS'
 WHEN 2 THEN 'U:S: Mail'
 WHEN 3 THEN 'FedEx'
END AS Shipper,
Freight FROM Orders

 

Pomoću iskaza CASE se proverava sadržaj polja ShipVIA i dobija se odgovarajući string u zavisnosti od vrednosti polja ShipVia.

 

Primer 2:

DECLARE @AverageFreight Money
SELECT @AverageFreight = AVG (Freight) FROM Orders
SELECT OrderID, OrderDate, Freight,
 CASE
  WHEN FREIGHT <=  @AverageFreight
  THEN 'Low Freight'
  ELSE 'High Freight'
 END AS Shipper,
 Freight FROM Orders

 

Znači definisali smo promenljivu @AverageFreight Money. Njoj smo dodelili prosečnu količinu iz tabele Orders. CASE proverava da li je količina u tekućem redu manja ili jednaka prosečnoj količini. Ukoliko je manja ili jednaka od prosečne količine, onda se prikazuje „Low Freight“. U suprotnom se prikazuje „Height Freight“. Ta vrednost se kombinuje sa poljima OrderID, OrderDate i Freight koja se izdvajaju iz tabele.

 

Iskaz WHILE

Iskaz While koristite kada želite da napravite ciklus, koji se izvršava sve dok je zadati uslov ispunjen.

.

Definicija Konstrukta WHILE je:

WHILE BooleanExpression
(SQLStatement | SQLBlok)
Here’s an example:
CREATE TABLE MyTable
(
LoopID INT,
LoopText VarChar(25)
)

Primer: Iskaza WHILE

DECLARE @LoopValue INT
DECLARE @LoopText CHAR (25)
SELECT @LoopValue = 1
WHILE (@LoopValue < 100)
BEGIN
 SELECT @LoopText = 'Iteration #' + Convert(VarChar(25), @LoopValue)
 INSERT INTO MyTable(LoopID, LoopText)
  VALUES (@LoopValue, @LoopText)
 SELECT @LoopValue = @LoopValue + 1
END
select * from MyTable

 

Prvo se pravi tabela MyTable. U tabeli postoje dva polja: polje tipa int i polje tipa char. Pomoću rutine se potom deklarišu dve promenljive, promenljiva tipa int i promenljiva tipa char. Promenljivoj @LoopValue se dodeljuje vrednost jedan. Programski kod izvršava ciklus od jedan do sto. Prilikom izvršavanja ciklusa, promenljivoj tipa char se dodeljuje tekst ‘Iteration #’ i sadržaj promenljive @LoopValue koji se prethodno pretvara u tip VarChar. Zatim se sadržaj promenljivih @LoopValue i @LoopText upisuje u tabelu. Na kraju se uvećava vrednost promenljive @LoopValue.

 

Iskaz SET NOCOUNT

Iskaz SET NOCOUNT poboljšava performanse uskladištene procedure.

Primer:

CREATE PROCEDURE [dbo].[procEmployeesgetNoCount1] AS
SET NOCOUNT ON
SELECT EmployeeID, Title, HireDate
FROM dbo.Employees
ORDER BY Title, HireDate

 

Prilikom pokretanja procedure nema poruke „xx row(s) affected“ se ne prikazuje.

 

Korišćenje funkcije @@

Ponašaju se kao funkcije koje proizvode razne tipove informacija o događajima u SQL Server-u.

 

Sistemska promenljiva @@RowCount

Sistemska promenljiva @@RowCount proizvodi broj redova koji se dobija pomoću zadatog iskaza ili broj redova čiji podaci su izmenjeni zadatim iskazom. Sistemska promenljiva @@RowCount ima vrednost nula ukoliko zadati iskaz ne proizvode nijedan red ili ukoliko akcioni upit ne izmeni nijedan red.

Primer:

SELECT EmployeeID, Title
FROM Employees
WHERE Title = 'Sales Representative'
SELECT @@RowCount as Ukupno

 

U primeru se izdvajaju svi zaposleni radnici koji su prodavci.

 

Sistemska promenljiva @@TranCount

Funkciju @@TranCount se upotrebljava kada se koriste transakcije. Pomoću iskaza BEGIN TRAN sistemskoj promenljivoj @@TranCount dodeljujete vrednost jedan. Pomoću iskaza Rollback TRAN vrednost sistemske promenljive @@TranCount umanjujete za jedan. Pomoću iskaza COMMIT TRAN vrednost sistemske promenljive @@TranCount takođe umanjujete za jedan. Kada koristite ugnježdene transakcije, sistemska promenljiva @@TranCount vam pomaže da pratite koliko transakcija čeka na izvršenje.

 

Sistemska promenljiva @@Identity

Funkcija @@Identity proizvodi novu vrednost koja je upisana u tabelu u kojoj postoji kolona identiteta.

Primer:

INSERT INTO Table_Identity
(ime, broj1, broj2)
VALUES ('IT_Akadem', 120,150)
SELECT @@Identity

 

Sistemska promenljiva @@Error

Pomoću funkcije @@Error dobija se broj greške koja se dogodila u iskazu koji je prethodio funkciji.

Primer:

INSERT INTO dbo.CustomerDemographics
 (CustomerTypeID,CustomerDesc)
VALUES ('ITAkade', 'ttt')
SELECT @@Error

 

U primeru se upisuje red u tabelu. Ukoliko je upisivanje reda bilo uspešno, funkcija @@Error vraća vrednost nula. U suprotnom funkcija @@Error vraća broj greške koja se dogodila u programskom redu koji se nalazi odmah ispred programskog reda u kojem se poziva funkcija @@Error.

 

Korišćenje parametara

Neke uskladištene procedure nemaju interfejs. Takve uskladištene procedure možete da pozivate, ali one ne prihvataju i ne vraćaju vrednosti. Druge procedure samo prihvataju vrednosti. Takve procedure imaju ulazne parametre, ali nemaju izlazne parametre. Treći tip uskladištenih procedura ima ulazne i izlazne parametre.

Primer:

CREATE PROCEDURE [dbo].[procEmp2]
@Title nVarChar(50),
@BirthDate DateTime
AS
SELECT EmployeeID, Title, BirthDate, HireDate
FROM Employees
WHERE Title = @Title or
 BIRTHDATE >= @Birthdate
ORDER BY BirthDate

 

Uskladištena procedura ima dva ulazna parametra, paramere @Title i @BirthDate. U uskladištenoj proceduri se ti parametri koriste kao promenljive u odredbi WHERE za opis posla i datum rođenja.

Opcioni parametar pravite tako što SQL Server-u šaljete unapred definisanu vrednost za parametar. U prethodnom primeru parametar @Title je opcioni parametar, a parametar @BirthDate je obavezan parametar. Ukoliko se odlučite da izostavite parametar @title, uskladištenu proceduru pozivate na sledeći način:

procEmployeesGetByTitleAndBirthDateOpt @BirtthDate = ’1/1/1960’

 

U primeru parametar @Birthdate zadaje kao imenovani parametar. Uskladištena procedura proverava vrednost parametra @Title. Ukoliko parametar @title ima vrednost Null, onda se u odredbi WHERE parametar ne koristi u uslovu. Ukoliko korisnik uskladištene procedure zada parametar @Title, uskladištena procedura ga koristi u uslovu u odredbi WHERE.

Primer:

CREATE PROCEDURE [dbo].[procEmployeesPR3]
@Title nVarChar(50) = NULL,
@BirthDate DateTime
AS
IF @Title IS NULL
 BEGIN
 SELECT EmployeeID, Title, BirthDate,
 HireDate
FROM Employees
WHERE BirthDate >= @BirthDate
ORDER BY BirthDate
END
ELSE
 BEGIN
 SELECT EmployeeID, Title, BirthDate,
  HireDate
 FROM Employees
 WHERE Title = @Title AND
  BirthDate >= @BirthDate
 ORDER BY BirthDate
 END

 

Opcioni parametar pravite tako što SQL Server-u šaljete unapred definisanu vrednost za parametar. U prethodnom primeru parametar @Title je opcioni parametar, a parametar @BirthDate je obavezan parametar. Ukoliko se odlučite da izostavite parametar @title, uskladištenu proceduru pozivate na sledeći način:

procEmployeesGetByTitleAndBirthDateOpt @BirtthDate = ’1/1/1960’

 

U primeru parametar @Birthdate se zadaje kao imenovani parametar. Uskladištena procedura proverava vrednost parametra @Title. Ukoliko parametar @Title ima vrednost Null, onda se u odredbi WHERE parametar ne koristi u uslovu. Ukoliko korisnik uskladištene procedure zada parametar @Title, uskladištena procedura ga koristi u uslovu u odredbi WHERE.

 

Izlazni parametri

Za uskladištenu proceduru možete da deklarišete izlazne parametre. SQL Server izlazne parametre šalje pozivaocu uskladištene procedure.

Primer:

CREATE  PROCEDURE dbo.procEmploeesIZ
 @TITLE NvARcHAR(50) = null,
 @Birthdate dateTime,
@MyMessage Varchar (50) = NULL OUTPUT
AS
IF @Title IS NULL
 BEGIN
 SELECT EmployeeID, Title, BirthDate,
  HireDate
 FROM Employees
 WHERE BirthDate >= @BirthDate
 ORDER BY BirthDate
 SELECT @MyMessage = 'No Title'
 END
ELSE
 BEGIN
 SELECT EmployeeID, Title, BirthDate,
  HireDate
FROM Employees
WHERE Title = @Title AND
 BirthDate >= @BirthDate
ORDER BY BirthDate
SELECT @MyMessage = 'Country Supplied'
END
SELECT @MyMessage

 

Uskladištena procedura u primeru prihvata dva parametra, ima izlazni parametar @MyMessage. U iskazu IF u uskladištenoj proceduri se parametru @MyMessage dodeljuje odgovarajuća vrednost 'No Title', ukoliko je vrednost opcionog parametra @Title is Null, odnosno 'Country Supplied', ukoliko vrednost opcionog parametra  @Title nije Null.

 

Menjanje podataka pomoću uskladištenih procedura

Uskladištene procedure se najčešće koriste za menjanje podataka koji se nalaze u bazi podataka. 

 

Upisivanje podataka

Uskladištene procedure pomoću kojih se zapisuju podaci obično imaju nekoliko ulaznih parametara, po jedan za svako polje tabele u koju se podaci upisuju.

Primer:

CREATE PROCEDURE [dbo].[procOrderDetailAddpr]
@SalesOrderID int,
@CarrierTrackingNumber nvarchar (25),
@OrderQty smallint,
@ProductID int,
@SpecialOfferID int,
@UnitPrice money,
@UnitPriceDiscount money
AS
INSERT INTO it_Details
(SalesOrderID, CarrierTrackingNumber, OrderQty,
ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount)
VALUES
(@SalesOrderID, @CarrierTrackingNumber, @OrderQty,
@productID, @SpecialOfferId, @UnitPrice, @UnitPriceDiscount)

 

Procedura prihvata sedam ulaznih parametara, po jedan za svako polje tabele it_Details u koju se podaci upisuju. U iskazu INSERT INTO koriste se ulazni parametri i njihove vrednosti se upisuju u tabelu, izlaznih parametara nema.

Primer uskladištene procedure sa izlaznim parametrima:

CREATE PROCEDURE procOrderDetailAddOutput
@SalesOrderID int,
@CarriertrackingNumber nvarchar(25),
@OrderQty smallint,
@ProductID int,
@SpecialOfferID int,
@unitPrice money,
@unitPriceDiscount money,
@SalesOrderdetailID int = 0 OUTPUT
AS
INSERT INTO it_Details
(SalesOrderID, CarrierTrackingNumber, OrderQty,
ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount)
VALUES
(@SalesOrderID, @CarrierTrackingNumber, @OrderQty,
@ProductID, @SpecialOfferID, @UnitPrice, @UnitPriceDiscount)
SET @SalesOrderDetailID =@@IDENTITY

 

Pomoću programskog koda izlaznom parametru @SalesOrder DetailId se dodeljuje vrednost sistemske funkcije @@Identity.

 

Ažuriranje podataka i uklanjanje podataka

Uskladištene procedure se koriste i za ažuriranje podataka i uklanjanje podataka.

 

Ažuriranje podataka

Primer:

CREATE PROCEDURE [dbo].[procSalesOrderHeaderUpdate]
@RegionID int,
@NewFreight nchar
AS
UPDATE dbo.Region
SET RegionDescription = @NewFreight
WHERE RegionID = @RegionID

 

Procedura ima dva ulazna parametra. Jedan parametar se koristi za polje RegionID. Drugi parametar se koristi za zadavanje nove vrednosti. Pomoću iskaza vrednost parametara @NewFraight zadajete vrednost kao u redovima u kojima se vrednost polja RegionID poklapa sa vrednošću parametara @ RegionID.

 

Uklanjanje podataka

Primer:
 
CREATE PROCEDURE procSalesOrderDetailsDelete
@SalesOrderDetailID int
AS
DELETE FROM Sales.SalesOrderDetails
WHERE SalesOrderDetailID = @SalesOrderDetailID
Primer:
ALTER PROCEDURE [dbo].[IT_delete]
@dept_number int
AS
BEGIN
 SET NOCOUNT ON;
DELETE FROM it_Details
WHERE SalesOrderID= @dept_number
END

 

Procedura prihvata jedan ulazni parametar: SalesOrderdetailID mušterije koju želite da uklonite iz tabele. Vidite da u iskazu DELETE postoji odredba WHERE u kojoj se koristi ulazni parametar @SalesOrderDetailID.

 

Uskladištene procedure i privremene tabele

SQL Server privremene tabele pravi u posebnoj sistemskoj bazi podataka TempDB. SQL Server pravi bazu podataka TempDB, prilikom svakog pokretanja, uklanjanja i kada prekinete njegovo izvršavanje. SQL Server bazu podataka TempDB koristi za zapisivanje mnogih privremenih objekata koje koristi tokom svog izvršavanja. Bazu podataka TempDB možete da koristite za deljenje podataka između uskladištenih procedura ili za obavljanje složenih poslova. Često ćete u uskladištenoj proceduri morati da koristite privremene tabele.

 

Uskladištene procedure i zaštita podataka

Uskladištene procedure, kao i pogledi, odličan su način zaštite podataka aplikacije. Možete da dodelite dozvole za korišćenje uskladištene procedure, a da ne morate da dodelite dozvole za tabele koje se u uskladištenoj proceduri koriste.

Pretpostavite da ste u Acces-u napravili formular pomoću kog korisnici mogu da odaberu razne mušterije. Zatim, izvršavate uskladištene procedure kako biste upisali, ažurirali i uklonili podatke. U tom slučaju korisnicima možete da dodelite dozvole za korišćenje pogleda na tabelu Customers. Ne morate da im dodeljujete dozvole za upisivanje, ažuriranje ili uklanjanje podataka iz tabele. Umesto toga, možete im dodeliti dozvole za izvršavanje odgovarajućih uskladištenih procedura.

Na taj način omogućavate korisnicima da lako naprave upite i izveštaje u kojima se koriste podaci iz tabele Customers (dodeljujete im dozvole za korišćenje pogleda na tabelu). Korisnici mogu samo da upisuju, ažuriraju i uklanjaju podatke pomoću uskladištenih procedura koje pozivaju pomoću programskog koda aplikacije.

 

Pravljenje i korišćenje funkcija koje definišete

Pomoću funkcija se dobija snaga i fleksibilnost koja se ranije nije mogla ostvariti pomoću pogleda i uskladištenih procedura.

Postoje tri tipa funkcija koje definišete: skalarne funkcije, ugrađene funkcije pomoću kojih se dobija sadržaj tabele i funkcije sa više iskaza pomoću kojih se dobija sadržaj tabele.

Primer:

CREATE PROCEDURE [dbo].[procCustomersGetByCountryAndTitle]
 @CountryName Varchar (50),
 @ContactTitle VarChar (50)
AS
SELECT CompanyName, ContactName, ContactTitle, City
FROM Customers
WHERE Country = @CountryName AND
ContactTitle = @ContactTitle
ORDER BY City, CompanyName

 

Uskladištena procedura CustomersGetByCountryAndTitle ima dva parametra. Ti parametri se koriste u odredbi WHERE za polja Country i ContactTitle. Pomoću uskladištene procedure procCustomersGetTemp pravi se privremena tabela u kojoj se nalazi informacije o mušteriji.

U privremenu tabelu se upisuje rezultat izvršavanja uskladištene procedure procCustomersGetByCountryAndTitle, pri čemu joj se prosleđuju vrednosti USA i Owner, a te vrednosti se koriste za državu i opis posla. Najzad, pomoću uskladištene procedure se iz privremene tabele izdvajaju podaci, a rezultat se uređuje po poljima City i CompanyName.

 

Skalarne funkcije

Skalarne funkcije (Scalar Function) proizvode vrednost koja je tipa podataka koji je definisan pomoću odredbe RETURNS. Telo funkcije se nalazi unutar bloka BEGIN i END. Skalarne funkcije mogu da vrate vrednost bilo kojeg tipa podatka, osim tipa podataka text, ntext, image i cursor.

Primer:

CREATE FUNCTION dbo.FullName
(@FirstName nVarChar (10),
@LastName nVarChar(20))
RETURNS nVarChar(35)
BEGIN
 RETURN (@LastName + ', '+@FirstName)
END

 

Prethodna funkcija prihvata dva parametra: @FirstName, @LastName između kojih je izraz i razmak. Funkcija se poziva na sledeći način:

SELECT FirstName, LastName, dbo.FullName (FirstName, LastName) FROM Employees

 

Ugrađene funkcije pomoću kojih se dobija sadržaj tabele

Ugrađene funkcije pomoću kojih se dobija sadržaj tabele (Inline Table-valued Function) daju sadržaj tabele. Ugrađene funkcije pomoću kojih se dobija sadržaj tabele nemaju telo funkcije. Ove funkcije vraćaju rezultate jednostavnog iskaza SELECT.

Primer:

CREATE FUNCTION dbo.CustGetByTitle
(@Title nVarChar(30))
RETURNS Table
AS
RETURN SELECT CustomerID, CompanyName, City, Region
FROM Customers WHERE ContactTitle = @Title

 

Prethodna funkcija prihvata parametar @Title. Funkcija vraća tabelu koja sadrži odabrana polja tabele Customers tako da vrednost polja ContactTitle odgovara vrednosti parametra @Title. Prethodnu funkciju pozivate na sledeći način:

SELECT * FROM dbo.CustGetByTitle ('Owner')

 

Funkcije sa više iskaza pomoću kojih se dobija sadržaj tabele

Funkcije sa više iskaza pomoću kojih se dobija sadržaj tabele (Multi-Statement Table-Valued Function) slične su ugrađenim funkcijama pomoću kojih se dobija sadržaj tabele. Osnovna razlika je što ove funkcije nemaju telo, koje je definisano blokom BEGIN … END. Slično ugrađenim funkcijama pomoću kojih se dobija sadržaj tabele, ove funkcije vraćaju sadržaj tabele.

 

Pravljenje uskladištenih procedura pomoću Query editora

• Otvorite čvor Programmability baze podataka koju koristite kako biste mogli da vidite čvor Stored Procedure (slika 14.1).
• Desnim tasterom miša kliknite na čvor Stored Procedures i odaberite New Stored procedure. U Query Editoru će se prikazati šablon koji se koristi za pravljenje uskladištene procedure (slika 14.2).
  
  
  
  
  Slika 14.1 - Prikaz kreiranja procedure 

 

• Desnim tasterom miša kliknite na čvor Stored Precedures i odaberite New Stored procedure. U Query Editoru će se prikazati šablon koji se koristi za pravljenje uskladištene procedure (slika 14.2).

Slika 14.2. - Prikaz šablona za Stored Procedure

 

Pravljenje uskladištenih procedura pomoću Query editora

• Pomoću miša označite iskaz SELECT koji vidite u šablonu i pritisnite taster Delete kako biste ga uklonili.
• Desnim tasterom miša kliknite isto mesto i odaberite Design Query in Editor. Prikazaće se okvir za dijalog Add Table (Slika 14.3).
• U upit uvrstite tabele tako što ćete ih odabrati i kliknuti na Add.  

 

 

Slika 14.3 - Okvir za obeležavanje tabela

 

• Pošto uvrstite sve tabele, kliknite Close. Pomoću miša odaberite polja koja želite da uvrstite u iskaz SELECT. Pošto odaberete polja, kliknite OK (slika 14.4).
• Izmenite uskladištenu proceduru tako što ćete u nju ugraditi funkcionalnost koja vam je potrebna.
• Pošto napravite uskladištenu proceduru, kliknite Execute kako biste izvršili iskaz CREATE PROCEDURE.
• Uskladištenu proceduru treba da zapišete samo ukoliko želite da T-SQL programski kod uskladištene procedure zapišete u spoljašnju datoteku.

 

Primer - Listanje podataka pomoću uskladištene procedure:

• Desnim tasterom miša kliknite na čvor Stored Precedures i odaberite Execute Stored procedure. U Query Editor-u će se prikazati rezultati uskladištene procedure (slika 14.4).

 

 

Slika 14.4 - Prikaz rezultata uskladištene procedure

• Tehnike za pisanje uskladištenih procedura 1

• Tehnike za pisanje uskladištenih procedura 2

• Tehnike za pisanje uskladištenih procedura 3