Wprowadzenie
Artykuł ten zostanie podzielony na trzy osobne podartykuły celem łatwiejszego przyswojenia wiedzy. Pierwszy będzie dotyczył funkcji, drugi procedur składowanych a trzeci transakcji. Dobre praktyki programowania T-SQL nakazują, aby wykorzystywać procedury do operacji zapisu, modyfikacji, usuwania rekordów z bazy danych a funkcji do przeprowadzania operacji matematyczno-analitycznych takich jak: obliczanie średniej czy przeliczanie wartości. Transakcje z kolei to swojego rodzaju wynik zależny od powodzenia wszystkich operacji zawartych w danej transkacji.
Zanim jednak przejdziemy do dalszej cześci artykułu skupimy się przez chwilę na różnicach pomiędzy procedurami a funkcjami. Wydaje mi sie, że takie porównanie zmieni Wasz sposób myślenia i będziecie dokładnie wiedzieli dlaczego do tego typu operacji użyliśmy funkcji a nie procedury.
Funkcje
Funkcja definiowana przez użytkownika to obiekt bazy danych, który zawiera w sobie jedną lub więcej instrukcji języka T-SQL, przygotowany do ponownego użycia. Ta definicja jest podobna do opisu procedury składowanej – dlatego w pierwszej kolejności skupiłem się na zaprezentowaniu różnic tak, aby łatwiej Wam było rozróżniać te elementy języka.
Poniższy przykład funkcji będzie przyjmował dwa parametry wejściowe, które zostaną zsumowane. Użytkownik będzie w stanie zobaczyć wynik takiej operacji:
CREATE FUNCTION SumTwoValues (@val1 INT, @val2 INT)
RETURNS INT
AS
BEGIN
RETURN (@val1 + @val2)
END
Struktura instrukcji CREATE FUNCTION nie jest skomplikowana. Podajesz nazwę obiektu (SumTwoValues), parametry wejściowe (@val1, @val2), typy danych zwracanych przez funkcję oraz operacje do wykonania, które są umieszczone w bloku BEGIN ... END. Następnie za pomocą instrukcji SELECT wywołujesz tak zdefiniowaną funkcję. Należy jednak pamiętać, że w przypadku takiego wywołania wymagana jest dwucześciowa nazwa (owner.object_name):
SELECT dbo.SumTwoValues(1,2)
Po wykonaniu polecenia SELECT wprowadzane są parametry wejściowe a wynikiem w przypadku naszego wywołania jest cyfra 3. Możesz użyć dowolnych wartości, które będą konwertowane na zdefionwany przez Ciebie typ danych (INT) dlatego czasami możesz otrzymać nieoczkiewane rezultaty:
SELECT dbo.SumTwoValues(1.98,2.73)
-- Result: 3
Dzieje się tak dlatego, że część dziesiętna przesłanych parametrów została obcięta w przypadku powyższych obliczeń.
Warto również wiedzieć, że SQL Server pozwala na dokonanie domyślnej konwersji danych dlatego operacje takie jak poniżej zwracają poprawne rezultaty:
SELECT dbo.SumTwoValues('5','7')
-- Result: 12
Jeżeli jednak spróbujesz przekazać do funkcji parametr/parametry, których nie można przekonwertować na INT zobaczysz stosowany komunikat:
SELECT dbo.SumTwoValues('TEST','7')
-- Msg: Conversion failed when converting the varchar value 'TEST' to data type int.
Trzy typu funkcji definiowanych przez użytkownika
Teraz, kiedy już wiesz jak łatwo jest utworzyć i zaimplementować prostą funkcję przejdziemy do przedstawienia wspomnianych typów funkcji definiowanych przez użytkownika.
Funkcje skalarne
Funkcje skalarne zwracają pojedynczą wartość zgodną z typem zdefiniowanym w klauzuli RETRUNS. Zwracane dane mogą być prawie każdego typu za wyjątkiem: text, ntext, image, cursor oraz timestamp.
Przykład pokazany powyżej to funkcja skalarna. Co prawda składał się tylko z jednego bloku BEGIN ... END a warto wiedzieć, że funkcja skalarna może zawierać nieograniczoną liczbę takich bloków o ile jakaś wartość zostanie zwrócona. Poniżej możecie zobaczyć pętle WHILE , która tego dowodzi:
CREATE FUNCTION SumTwoValuesSecondExample (@val1 INT, @val2 INT)
RETURNS INT
AS
BEGIN
WHILE @val1 < 100
BEGIN
SET @val1=@val1+1
END
RETURN @val1 + @val2
END
Oraz wynik wywołania:
SELECT dbo.SumTwoValuesSecondExample (23, 15)
-- Result: 115
Przekazany parametr wejściowy @val1 jest ustawiony jako 23 ale z każdą pętlą jej wartość jest zwiększana jest o 1 aż do wartości 100. Dlatego wynik dodawania w tym przypadku to 115. Pamiętajcie, że wywołania funkcji skalarnych wymagają użycia ich dwucześciowych nazw – nie dotyczy to pozostaych typów funkcji.
Proste funkcje tabelaryczne
Funkcje te zwracają wynik pojedynczej instrukcji SELECT. Ponieważ zwracana wartości pochodzi z instrukcji SELECT w definicji takiej funkcji nie jest wymagany blok BEGIN/END. Nie ma również potrzeby określania nazwy tabeli (lub definicji kolumn) ponieważ struktura jest generowana na podstawie przygotowanej przez nas instrukcji. Ponieważ wyniki pochodzą z naszej instrukcji nie są dopuszczane duplikaty nazw kolumn dlatego muszą mieć one przypisany alias.
Wszelkie operacje wykonywane są na opisywanej wielokrotnie bazie danych AdventureWorks:
CREATE FUNCTION CustomerByCreditRating (@rating INT)
RETURNS TABLE AS
RETURN (SELECT Purchasing.Vendor.BusinessEntityID, Purchasing.Vendor.Name, Purchasing.Vendor.CreditRating
FROM Purchasing.Vendor
WHERE CreditRating = @rating
)
A tak prezentuje się wywołanie funkcji:
select * from CustomerByCreditRating(2)
Złożone funkcje tabelaryczne
Funkcje te są nieco bardziej skomplikowane niż dwa poprzednie przykłady ponieważ wykorzystują wiele instrukcji do tworzenia tabel, która jest następnie zwracana do instrukcji wywołującej. W przeciwieństwie do prostych funkcji tabelarycznych należy jasno zdefiniować i zdeklarować tabelę. Poniższy przykład pokazuje jak zaimplementować złożoną funkcję tabelaryczną, która wypełnia danymi i zwraca tabelę:
USE [AdventureWorks2012]
GO
/****** Object: UserDefinedFunction [dbo].[OrderByModifiedDate] Script Date: 2017-08-04 14:56:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery3.sql|7|0|C:\Users\plukasie\AppData\Local\Temp\~vs6506.sql
ALTER FUNCTION [dbo].[OrderByModifiedDate] (@ModifiedDateStart DateTime, @ModifiedDateEnd DateTime)
RETURNS @OrderByModifiedDateRange TABLE
(
BusinesEntityID INT,
AccountNumber nvarchar(20),
Name nvarchar(40),
ModifiedDate Datetime
)
AS
BEGIN
-- Pierwsza instrukcja
INSERT @OrderByModifiedDateRange
SELECT pv.BusinessEntityID,
pv.AccountNumber,
pv.Name,pv.ModifiedDate
FROM Purchasing.Vendor as pv
WHERE ModifiedDate>@ModifiedDateStart AND ModifiedDate < @ModifiedDateEnd
-- Druga instrukcja
UPDATE @OrderByModifiedDateRange
SET Name = CONCAT(AccountNumber, '_InternalUpdate')
WHERE ModifiedDate>@ModifiedDateStart AND ModifiedDate < @ModifiedDateEnd
RETURN
END
W Twojej głowie może pojawić się pytanie – pisałem przecież na początku artykułu, że w funkcjach można używać jedynie instrukcji SELECT a w powyższym przykładzie zostałą użyta instrukcja UPDATE. Tak... ale instrukcja ta dotyczy zmiennej tabeli, którą przygotowaliśmy i zwracamy. W tym przypadku nie modyfikujemy żadnych danych w żadnej z istniejących tabel.
Przejdźmy jeszcze do wywołania jednej instrukcji oraz właśnie napisanej funkcji.
SELECT * FROM Purchasing.Vendor
-- Result: 104 rows
SELECT * FROM OrderByModifiedDate('2006-01-23 00:00:00.000', '2006-03-05 00:00:00.000')
-- Result: 47 rows
Możemy zobaczyć, że udało nam się zawęzić liczbę wyników zwróconych wierszy. Co równie ważne, każdy ze zwróconych wierszy (47) został nieznacznie zmodyfikowany przez zmianę nazwy kolumny Name. Zmiana ta jednak nie wpłynęła na oryginalną tabelę Purchasing.Vendor.
