Encyklopedia SQL - Tworzenie funkcji bazodanowych - CREATE FUNCTION  

Autor: Paweł Wilkosz

Opublikowano: 2012-02-09

W poprzedniej publikacji (Tworzenie procedur bazodanowych - CREATE PROCEDURE) dowiedziałeś się, w jaki sposób wykorzystać procedury składowane do agregacji najczęściej wykonywanych zapytań na bazie danych. Poznałeś również ich zakres zastosowania oraz sposoby dostępu do wbudowanych procedur SQL Server. W tej części cyklu Encyklopedii SQL przybliżymy, bliźniaczy do procedur osadzonych, temat – funkcje bazodanowe. Jej podstawowym celem będzie zaprezentowanie różnic semantycznych pomiędzy PROCEDURE a FUNCTION, techniki tworzenia funkcji oraz wykorzystania już istniejących.

Przed wykonaniem zadań zapoznaj się z następującym materiałem:

• Typy danych w T-SQL
• Wyświetlanie danych, podstawowy SELECT i wybór kolumn
• Klucze główne – PRIMARY KEY i IDENTITY
• Klucze obce – FOREIGN KEY
• Tworzenie procedur bazodanowych - CREATE PROCEDURE

Po wykonaniu zadań będziesz wiedział:

• w jaki sposób efektywnie pracować z funkcjami bazodanowymi.

Polecenie CREATE FUNCTION

Zasadnicza różnica pomiędzy procedurą a funkcją bazodanową leży głównie w semantyce, a właściwie w sposobie wykorzystania tych dwóch obiektów. Syntaktycznie funkcję można odróżnić od procedury tym, iż wymusza ona zadeklarowanie typu zwracanego jako wyniku przetwarzanego zapytania w jej ciele. Co prawda instrukcja SELECT, wykorzystywana w procedurze, również może przekazywać programiście pewną wartość (również skalarną). Dlatego dobre praktyki programowania w T-SQL nakazują, aby wykorzystywać procedury do operacji zapisu, modyfikacji, usuwania rekordów z bazy danych (czyli akcji, które nie zwracają konkretnych wartości), zaś funkcji - do przeprowadzania operacji matematyczno-analitycznych takich jak: liczenie średniej, dodawanie kolumn typu INT, przeliczanie wartości itp.

W najprostszej postaci funkcja bazodanowa, tworzona w języku T-SQL, przyjmuje następującą składnię:

CREATE FUNCTION Nazwa_Funkcji
(
  @Nazwa_parametru1 TYP_PARAMETRU,
  …
  @Nazwa_parametruN TYP_PARAMETRU
)
RETURNS typ_zwracany
AS
Treść_zapytania

RETURN wartość_zwracana

Analiza poniższego przykładu powinna pomóc lepiej zrozumieć zagadnienia tworzenia funkcji. W poprzedniej publikacji wspominałem, iż manager-owie firmy AdventureWorks poprosili programistów o przygotowanie zestawienia wyników sprzedaży za ubiegły rok. Ponieważ rezultaty pozytywnie zaskoczyły kadrę zarządzającą, decyzją kierownika Działu Promocji i Sprzedaży każdy z Sales Managerów otrzyma premię, określoną następującym wzorem:

Premia = 20% ogólnego wyniku sprzedaży za zeszły rok + 30% wyniku indywidualnego.

Pracownicy Działu Księgowości i Rozliczeń poprosili zatem programistów T-SQL o przygotowanie zapytania, które w wyniku będzie zwracało listę w formie: Imię, Nazwisko, Wynik sprzedaży za zeszły rok, premia. Programiści postanowili wykorzystać kod poprzedniej procedury i dopisać do niego następującą funkcję:

CREATE FUNCTION dbo.ComputeBonus(@SalesPersonID INT)
  RETURNS FLOAT 
  AS
  BEGIN
    DECLARE @TOTAL_AVERAGE INT;
    DECLARE @GENERAL FLOAT;
    DECLARE @INDIVIDUAL FLOAT;
    DECLARE @OWN_RESULT INT;
    
      SET @TOTAL_AVERAGE = (SELECT AVG(SalesLastYear)
             FROM Sales.SalesPerson);
    
    SET @OWN_RESULT = (SELECT SalesLastYear
             FROM Sales.SalesPerson
             WHERE SalesPersonID = @SalesPersonID);
             
    SET @GENERAL = 20 * @TOTAL_AVERAGE / 100;
    SET @INDIVIDUAL = 30 * @OWN_RESULT /100;
    
RETURN @GENERAL + @INDIVIDUAL;
END

Następnie zapytaniem:

SELECT c.FirstName, c.LastName, sp.SalesLastYear, dbo.ComputeBonus(sp.SalesPersonID) AS 'Bonus'
 FROM Sales.SalesPerson AS sp
 INNER JOIN HumanResources.Employee AS e
  ON sp.SalesPersonID = e.EmployeeID
 INNER JOIN Person.Contact AS c
  ON e.ContactID = c.ContactID

przygotowali listę premii, zgodnie z życzeniem kolegów z działu zarządzania (Rys. 1.).

Rys. 1. Wynik działania funkcji obliczającej wysokość premii.

Funkcje wbudowane w SQL Server

Oprócz definicji własnych funkcji, użytkownik ma możliwość wykorzystania już istniejących, osadzonych w zasobach SQL Server. W poprzednim podpunkcie niejawnie została użyta jedna z nich – AVG, co, jak nie trudno się domyślić, pomaga w obliczaniu średnich wartości dla wskazanej kolumny. W Tabeli 1. zostały zestawione najczęściej wykorzystywane funkcje, wbudowane w SQL Server.

Tabela 1. Najczęściej wykorzystywane funkcje w SQL Server.

Funkcja	Zastosowanie.
AVG	Obliczanie wartości średniej.
SUM	Sumowanie wartości.
COUNT	Zliczanie ilości (liczba wystąpień).
MAX	Wskazanie wartości maksymalnej.
MIN	Wskazanie wartości minimalnej.
ABS	Obliczanie wartości bezwzględnej wyrażenia.
PI	Liczba PI.
RAND	Wartość losowa z zakresu 0…1.
GETDATE	Zwrócenie bieżącej daty i czasu.
DAY	Zwrócenie dnia miesiąca jako liczby całkowitej.
YEAR	Zwrócenie roku jako liczby całkowitej.
MONTH	Zwrócenie miesiąca jako liczby całkowitej.
VAR	Wariancja wszystkich wartości wyrażenia.

 

Informacja

Zapamiętaj, że: Polecenie CREATE FUNCTION służy to tworzenia funkcji w bazie danych. Z semantycznego punktu widzenia funkcja różni się od procedury tym, iż zwraca wartość zadeklarowanego typu jako wynik wykonanego wewnątrz niej zapytania. W SQL Server istnieje wiele funkcji wbudowanych, realizujących operacje matematyczno-statystyczno-analityczne. Warto je wykorzystywać przez wzgląd na optymalizację oraz efektywność.

Podsumowanie

W niniejszej publikacji przybliżyłem sposoby tworzenia funkcji w bazie danych oraz wskazałem zasadnicze różnice w kwestii wykorzystania ich w przeciwieństwie do procedur osadzonych.

W kolejnej publikacji nauczysz się najciekawszych, a zarazem najprostszych, technik optymalizacji kodu T-SQL względem efektywności wykonania.

Dodatkowo zobacz:

• Język T-SQL - co to jest T-SQL Server, podstawowa składnia języka T-SQL

 

---