Funkcje użytkownika (UDF) w SQL - CREATE FUNCTION (T-SQL)

CREATE FUNCTION fnPelneImie(@i NVARCHAR(50), @n NVARCHAR(50))
RETURNS NVARCHAR(101) AS
BEGIN
  RETURN @i + ' ' + @n;
END;

Funkcje użytkownika (UDF – user-defined functions) mogą wykonywać zapytania i obliczenia oraz zwracać zarówno wartości skalarne, jak i zbiory danych w tabelach. Funkcje takie mają jednak pewne ograniczenia, przykładowo nie mogą korzystać z niedeterministycznych funkcji systemowych ani wykonywać wyrażeń z grupy DML i DDL, przez co nie mogą wprowadzać modyfikacji do struktury ani zawartości bazy danych. Nie mogą też wykonywać dynamicznych zapytań SQL ani zmieniać stanu bazy danych.

Funkcje tak jak i procedury tworzymy za pomocą poleceń CREATE, modifukujemy poleceniami ALTER i kasujemy przy użyciu DROP.

Przy tworzeniu funkcji, musimy od razu zadeklarować co taka funkcja będzie zwracała – jaki typ danych, należy przy tym dobrze przewidzieć wielkość wyniku, ponieważ jeśli damy za mało, to wynik zostanie ucięty (w przypadku liczb np. do wartości całkowitej, jeśli na wyjściu obiecamy

INT, zaś w przypadku łańcuchów znaków do obiecanej ilości znaków)

Funkcje skalarne

Funkcje skalarne są to funkcje które zwracają jedną wartość, mogą zwrócić dowolny (prosty np. INT, VARCHAR, DATETIME itp.) typ danych.

Poniżej przykład takiej funkcji, która jako parametr wejściowy przyjmie promień koła i zwóci jego pole:

Wywołanie takiej funkcji następuje w klauzuli SELECT lub np. wynik funkcji możemy przypisać do zmiennej.

Funkcja może również wywołać sama siebie (rekurencja), dla przykładu funkcja obliczająca silnie dla liczby podanej jako parametr wejściowy:

Sprawdzenie funkcji:

I szybka walidacja czy wynik jest prawidłowy:

W SQL Server jesteśmy ograniczeni do maksymalnie 32 poziomów rekurencji.

W funkcji ObliczSilnie pojawił się dopisek "WITH RETURNS NULL ON NULL INPUT", oznacza on, że jeśli uzytkownik poda jako parametr wejściowy wartość null, to wtedy funkcja ta ma nic nie obliczać, tylko odgórnie zwrócić wartość null.

Funkcje tabelaryczne

Funkcje tabelaryczne najprościej ujmując zwracają wynik w formie tabelarycznej.

W MS SQL Server mamy do czynienia z dwoma rodzajami takich funkcji, pierwszy z nich to multi-statement table-value. Wtedy w ciele funkcji definiujemy jako typ tabelę jaka będzie zwracana jako wynik jej działania.

Do wyniku takiej funkcji odwołujemy się jak do zwykłej tabeli:

Co więcej, wiersze zwracane przez taką funkcję możemy filtrować za pomocą zwykłej klauzuli WHERE:

Innym rodzajem funkcji jaka zwróci nam wynik w formie tabelarycznej jest funkcja typu inline table-valued. Korzysta się z niej podobnie, jednakże sposób rozpisania funkcji jest nieco inny. Cały wynik zapisujemy za pomocą klauzuli SELECT umieszczając ją w klauzuli RETURN.

Poniżej przykład takiej funkcji która zwróci kilku (sami ustalimy ilu) pracowników o najwyższej pensji, dla danego managera, którego nazwisko podamy przy uruchamianiu takiej funkcji:

I próba uruchomienia tego typu funkcji, za pomocą kaluzuli SELECT:

Jakie są zatem różnice pomiędzy procedurami a funkcjami tabelarycznymi (table-valued functions), skoro i jedne i drugie mogą działać i zwracać podobne rezultaty, poza tym, że oczywiście wywołujemy/uruchamiamy je w inny sposób.

Funkcje:

-są łatwiejsze w użyciu

-ich wynik można przypisać do zmiennej

-nie mogą modyfikować danych

-często powodują problemy wydajnościowe.

Procedury:

-potrafią modyfikować dane

-potrafią wykonywać dynamiczny kod SQL statements

-potrafią przechwytywać błędy

-mogą zwracać wiele wyników.

Szkolenie Kompleksowe SQL w Microsoft SQL Server →

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5