Blokkok
A begin end párossal lehet blokkot definiálni. BEGIN { sql_statement | statement_block } ENDTárolt eljárások
Létrehozás parancsa:
CREATE PROCEDURE enev
(@e1 [=ert1] tip1 [OUT],..)
WITH ENCRYPTION | EXECUTE AS user
AS
BEGIN
….
RETURN ertek;
END;
create procedure p1 (@v1 int, @v2 int out) as
begin
select @v2 = count(*) from tabla1
where kod < @v1;
end;
begin
declare @m1 int;
execute dbo.p1 3, @m1 OUT;
select @m1;
end;
Függvények
A felhasználói függvény olyan Transact SQL utasítások sorozata, amelyeket azért csomagolunk egybe, hogy több helyen is felhasználhassuk. Nagyon jól kiegészítik a tárolt eljárásokat, mert minden olyan helyen felhasználhatjuk őket, ahol a beépített függvényeket is, azaz ahol a tárolt eljárásokat legtöbbször nem. A legegyszerűbb példa erre a SELECT-ben való felhasználás. Például, ha van egy osszeadas nevű függvényünk, akkor azt felhasználhatjuk két oszlopban található számok összeadására, a SELECT utasítás részeként. Ennél kevésbé kézenfekvő helyeken is használhatjuk a függvényeinket: WHERE feltételben, HAVING-ben, CHECK CONSTRAINT-ekben, DEFAULT CONSTRAINT-ekben, számított oszlopok képzésében. Mindenhol működnek, ahol a szerver valamilyen kifejezést vár (mint a>b, c=4 vagy 2x2=5).
CREATE FUNCTION osszeadas
(
@a INT,
@b INT
)
RETURNS INT
BEGIN
RETURN @a + @b
END
SELECT osszeadas(Ár, ÁFA), Termék FROM ...
Az SQL Server a függvényeket sokszor tranzakciók, illetve SELECT, UPDATE, satöbbi utasítások kellős közepén hívja
meg. Emiatt rendetlen az a függvény, ami menet közben módosítja egy tábla tartalmát, miközben egy SELECT (ami őt hívta
meg) éppen dolgozik rajta - nos ilyen esetben nagy gond támadhatna. Az SQL Server ezért megpróbálja megkötni a kezüket. Azaz a felhasználói függvényekben nem
tehetünk meg akármit, csak a következőket:
- Definiálhatunk saját változókat és kurzorokat a DECLARE utasítással. Csak lokális kurzorokat készíthetünk így, globálisakat, azaz amelyek a függvény lefutása után is léteznének nem.
- A függvényben deklarált lokális változóknak értéket adhatunk.
- Használhatunk kurzorműveleteket, de csak úgy, hogy a FETCH utasítás eredményeit lokális változókba rakjuk el.
- Bevethetjük a programfolyam-vezérlő utasításokat: if, then, for, while, goto, satöbbi.
- Alkalmazhatjuk az összes adatmódosító utasítást (INSERT, UPDATE, DELETE), ha azok csak lokális táblákon végeznek műveleteket. Ebből következően nem lehet módosítani külső táblákat. Természetesen lekérdezésekben szerepelhetnek.
- Meghívhatunk külső tárolt eljárásokat (Extended Stored Procedure) az EXECUTE utasítással. „Hagyományos” tárolt eljárásokat nem lehet meghívni belőlük, hisz azokból már könnyedén beavatkozhatnánk a „külvilágba”.
- Indexelt számított oszlopokon, azaz, ha olyan oszlopra szeretnénk indexet készíteni, amelynek érékei egy másik (egy vagy több) oszlopból származnak, és a számított érték valamilyen nemdeterminisztikus függvényen alapul.
- Olyan nézetekben, ahol a nézetre clustered indexet szeretnénk használni.
A séma-kötés
A felhasználói függvények igen erősen kötődnek azokhoz a táblákhoz, és egyéb objektumokhoz, amelyekre hivatkoznak. Ha azok módosulnak anélkül, hogy erről a függvény tudna, akkor a kapcsolatuk vége barátságtalan lesz, és a függvény nem fog jól működni. Azért, hogy a jó viszonyban ne következhessen be szakadás, a függvény létrehozásakor (CREATE FUNCTION) megadhatjuk, hogy a függvény legyen hozzákötve az általa használt objektumokhoz. Ezt az SQL Server megjegyzi, és nem engedi módosítani vagy törölni az ily módon leláncolt objektumokat. A kötés jelzését a RETURNS és a függvény törzsét kezdő BEGIN közé kell írni:
...
RETURNS ...
WITH SCHEMABINDING
BEGIN
...
Függvénytípusok
Háromféle felhasználói függvénytípust hozhatunk létre:- Skaláris függvények, melyeknek visszatérési értéke skaláris, azaz egy érték (scalar functions)
- Egy utasításból álló, tábla visszatérési értékű függvények (inline table valued functions)
- Több utasításból álló, tábla visszatérési értékű függvények (multi statement table valued functions)
Skaláris függvények
A skaláris függvények nagyon egyszerűek: kapnak néhány paramétert, azokon végeznek valamilyen műveletet, majd az eredményt egy skaláris értékként visszaadják. Leginkább a procedurális nyelvek függvényeihez hasonlítanak. A CREATE FUNCTION jelzi, hogy ez egy felhasználói függvény lesz. Ezután jön a függvény neve. Általában a függvényeknek vannak paramétereik, ezeket zárójelben soroljuk fel a függvény neve után. A @ nem opcionális, Transact SQL-ben minden változót kötelező @-al kezdeni. A paraméter neve után meg kell adni a típusát. Itt majdnem az összes, a kiszolgáló által támogatott adattípust fel lehet használni, egy-két (image, text vagy cursor) típust kivéve. A RETURNS után kell definiálni a visszatérési érték adattípusát. A kötöttségek ugyanazok, mint a paramétereknél. A függvény törzsét, ahol a funkcionalitást írjuk le, a BEGIN és END kulcsszavak közé kell elhelyezni. A skaláris visszatérési értékű függvényekre minimum 2 tagú névvel kell hivatkozni. Azaz legalább a függvény tulajdonosát meg kell adnunk ahhoz, hogy az SQL Server felismerje a függvényünket.
SELECT dbo.osszeadas(1,4) vagy
SELECT Northwind.dbo.osszeadas(1,4)
Egy utasításból álló, tábla visszatérési értékű függvények
CREATE FUNCTION OrdersByCustomer(
@CustomerID varchar(5))
RETURNS TABLE
AS
RETURN (
SELECT * FROM Orders
WHERE
CustomerID = @CustomerID)
SELECT CustomerID, ShippedDate
FROM OrdersByCustomer('THEBI')
Több utasításból álló, tábla visszatérési értékű függvények
Bonyolultabb esetben a visszatérési érték nem állítható elő egyetlen SELECT utasítás segítségével, ilyenkor kell használnunk ezt a függvénytípust. Mivel ilyenkor már nem egyértelmű, hogy melyik lekérdezés kimenetét szeretnénk visszaadni, explicit deklarálnunk kell a visszatérési értékként szolgáló tábla szerkezetét egy tábla típusú változóként. A változót INSERT utasítások segítségével feltöltjük (akárhány lépésben), és a RETURN utasítás ezt fogja visszaadni a hívónak.
Create function func1 (@p1 int) returns @ret table(col1 int, col2 nvarchar(100))
begin
insert into @ret(col1, col2) values (@p1, ‘a’);
return;
end
Select * from dbo.func1(4)