[PL] SQL Server 2014 SP2 - funkcje tabelaryczne i liniowe

Microsoft SQL Server pozwala na tworzenie własnych funkcji. Możemy je postrzegać jako rozwinięcie możliwości oferowanych przez widoki, gdyż - w praktyce -  umożliwiają one zwracanie tych samych danych, ale w trochę inny sposób.

Na przykład, korzystając z widoków możemy określić zapytanie, które zwróci produkty zakupione przez użytkownika o konkretnym identyfikatorze wewnętrznym (np. 1), z kolei korzystając z funkcji możemy ustanowić, że identyfikator użytkownika, o zakupy którego pytamy będzie za każdym razem podawany przy wywoływaniu funkcji.

Na początek przyjrzyjmy się funkcjom tabelarycznym (z angielskiego: Table-Valued User-Defined Functions) - to znaczy takim, które zwracają dane w postaci tabeli.

Zanim zaczniemy część praktyczną dotyczącą funkcji utwórzmy trzy proste tabele, do których będziemy się odwoływać w dalszej części tego materiału. Chodzi o:

• tabelę LUDZIE składającą się z kolumny ID (która jako wartości przyjmuje automatycznie generowane liczby zaczynając od 1 i zwiększając o 1), imie (która jako wartości przyjmuje tekst do 255 znaków) oraz nazwisko (która jako wartości przyjmuje tekst do 255 znaków)
• tabelę KSIAZKI składającą się z kolumny ID (która jako wartości przyjmuje automatycznie generowane liczby zaczynając od 1 i zwiększając o 1), nazwa (która jako wartości przyjmuje tekst do 255 znaków) oraz RokWydania (która jako wartość przyjmuje liczby od -32,768 do 32,767)
• tabelę WypozyczeniaKsiazek składającą się z kolumny ID (która jako wartości przyjmuje automatycznie generowana liczby zaczynając od 1 i zwiększając o 1), RefIdLudzie (która stanowi odwołanie do kolumny ID z tabeli LUDZIE), RefIdKsiazki (która stanowi odwołanie do kolumny ID z tabeli KSIAZKI)

Diagram związków encji dla naszych tabel przedstawia się następująco:


W celu stworzenia wspomnianych tabel i umieszczenia w nich kilku przykładowych wierszy z danymi, skorzystamy z poniższego skryptu. Zwracam uwagę, że na potrzeby szkoleniowe, w podanych przykładach wykorzystujemy bazę danych tempdb, ale dane w niej zawarte są tymczasowe i jest ona przywracana do stanu pierwotnego przy każdym restarcie instancji SQL Server. Jeżeli chcielibyśmy zachować nasze dane na dłuższy czas, należałoby utworzyć i pracować na innej bazie danych.

use tempdb
go
 
CREATE TABLE [dbo].[Ludzie] (
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
imie NVARCHAR(255),
nazwisko NVARCHAR(255)
);
GO
 
CREATE TABLE [dbo].[Ksiazki] (
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
nazwa NVARCHAR(255),
RokWydania SMALLINT
);
GO
 
CREATE TABLE [dbo].[WypozyczeniaKsiazek] (
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
RefIdLudzie INT,
RefIdKsiazki INT,
CONSTRAINT FK_Ksiazki_ID FOREIGN KEY (RefIdKsiazki) 
    REFERENCES [dbo].[Ksiazki] (id),
CONSTRAINT FK_Ludzie_ID FOREIGN KEY (RefIdLudzie) 
    REFERENCES [dbo].[Ludzie] (id),
);
GO
 
INSERT INTO [dbo].[Ludzie] (imie, nazwisko) VALUES (N'Jan',N'Kowalski');
INSERT INTO [dbo].[Ludzie] (imie, nazwisko) VALUES (N'Kacper',N'Krajewski');
INSERT INTO [dbo].[Ludzie] (imie, nazwisko) VALUES (N'Alicja',N'Nowak');
INSERT INTO [dbo].[Ludzie] (imie, nazwisko) VALUES (N'Zofia',N'Sikorska');
INSERT INTO [dbo].[Ludzie] (imie, nazwisko) VALUES (N'Szymon',N'Mróz');
 
INSERT INTO [dbo].[Ksiazki] (nazwa, RokWydania) VALUES (N'Obsesja','2017');
INSERT INTO [dbo].[Ksiazki] (nazwa, RokWydania) VALUES (N'Barcelona','2015');
INSERT INTO [dbo].[Ksiazki] (nazwa, RokWydania) 
VALUES (N'Ameryka po nordycku. W poszukiwaniu lepszego życia','2017');
INSERT INTO [dbo].[Ksiazki] (nazwa, RokWydania) VALUES (N'Światełko','2017');
INSERT INTO [dbo].[Ksiazki] (nazwa, RokWydania) 
VALUES (N'Historia pszczół. Audiobook','2017');
 
INSERT INTO [dbo].[WypozyczeniaKsiazek] (RefIdLudzie, RefIdKsiazki) VALUES (3,3);
INSERT INTO [dbo].[WypozyczeniaKsiazek] (RefIdLudzie, RefIdKsiazki) VALUES (3,5);
INSERT INTO [dbo].[WypozyczeniaKsiazek] (RefIdLudzie, RefIdKsiazki) VALUES (4,1);
INSERT INTO [dbo].[WypozyczeniaKsiazek] (RefIdLudzie, RefIdKsiazki) VALUES (4,2);
INSERT INTO [dbo].[WypozyczeniaKsiazek] (RefIdLudzie, RefIdKsiazki) VALUES (5,1);

Na koniec sprawdźmy, że wszystko przebiegło pomyślnie i zapytajmy SQL Server o wszystkie dane z naszych trzech tabel.

use tempdb
go
 
SELECT * FROM [dbo].[Ludzie]
SELECT * FROM [dbo].[Ksiazki]
SELECT * FROM [dbo].[WypozyczeniaKsiazek]

Powinniśmy otrzymać następujący wynik:

Funkcje typu Table-Valued charakteryzuje to, że zwracają dane tabelaryczne czyli w postaci tabeli.  Aby zademonstrować używanie funkcji tabelarycznych przealizujmy i wykonajmy następujące polecenie.

use tempdb;
go
 
-- na początek podajemy CREATE FUNCTION ponieważ chcemy utworzyć funkcję
-- [dbo].[GetWypozyczenia] jest nazwą funkcji (to jest GetWypozyczenia) 
-- oraz schematu, w którym się ona znajduje (to jest dbo)
-- (@OsobaId INT) oznacza nazwę parametru wejściowego (OsobaId) i jego typ (INT)
CREATE FUNCTION [dbo].[GetWypozyczenia]
    (@OsobaId INT)
-- określamy, że chcemy otrzymać wynik w postaci tabeli 
-- o nazwie tGetWypozyczenia
-- nasza tabela posiadać będzie kolumny o nazwach wymienionych w tym miejscu
-- i w rodzaju wymienionym w tym miejscu  - np. OsobaNazwisko typu NVARCHAR 
-- o długości 255 i pozwala na wartości NULL
RETURNS @tGetWypozyczenia TABLE 
(
[OsobaImie] [NVARCHAR](255) NULL,
[OsobaNazwisko] [NVARCHAR](255) NULL,
[KsiazkaNazwa] [NVARCHAR](255) NULL,
[KsiazkaRokWydania] [SMALLINT] NULL
)
AS 
-- początek tzw. ciała funkcji
BEGIN
 INSERT INTO @tGetWypozyczenia
  SELECT L.imie, L.nazwisko, K.nazwa, K.RokWydania
  FROM [dbo].[WypozyczeniaKsiazek] AS WK
   INNER JOIN [dbo].[Ludzie] AS L
    ON WK.RefIdLudzie = L.id
   INNER JOIN [dbo].[Ksiazki] AS K
    ON WK.RefIdKsiazki = K.id
-- naszą funkcję czyni uniwersalnym to, że 
-- w klauzuli WHERE odwołujemy się 
-- do parametru wejściowego tzn. @OsobaId
  WHERE WK.RefIdLudzie = @OsobaId;
 RETURN;
-- koniec tzw. ciała funkcji
END;

W celu skorzystania z funkcji możemy użyć polecenia SELECT, ale podając w nawiasie wartość na potrzeby parametru wejściowego. Oto przykład:

use tempdb
go
 
SELECT * FROM [dbo].[GetWypozyczenia](5)

gdzie 5 to wartość parametru wejściowego - to znaczy wartość zmiennej @OsobaId - to znaczy identyfikator użytkownika zapisany w tabeli [dbo].[Ludzie]

Wynik działania powyższego polecenia powinien być taki jak na przedstawionym poniżej obrazku.

Na tabeli zwracanej przez funkcję możemy wykonywać różnego rodzaju operacje. Na przykład wynik zapytania:

use tempdb
go
 
SELECT * FROM [dbo].[GetWypozyczenia](4);

zwróci 2 pozycje. Pierwszą - gdzie rok wydania wypożyczonej książki to 2015, drugą - gdzie rok wydania wypożyczonej książki to 2017.

Odwołując się do funkcji, możemy dodać warunek WHERE powodujący zwrócenie jedynie pozycji książkowej wydanej po 2015 r.

use tempdb
go
 
SELECT * FROM [dbo].[GetWypozyczenia](4)
WHERE [KsiazkaRokWydania] > 2015;

Teraz przyjrzymy się funkcjom liniowym (z angielskiego: Inline User-Defined Functions). Są one bardzo podobne do funkcji tabelarycznych, ponieważ również zwracają dane w postaci tabeli, ale odróżnia je to, że 1) nie zawierają tzw. "ciała funkcji" określanego za pomocą BEGIN i END; 2) klauzula RETURN zawiera instrukcję SELECT, której wynik tworzy tabelę zwracaną przez funkcję; 3) klauzula RETURN zawiera tylko słowo TABLE, ponieważ nie można określić formatu zwracanych zmiennych, gdyż są one określone poprzez wynik SELECTa.

Oto przykład funkcji liniowej:

USE tempdb
GO
 
-- na początek podajemy CREATE FUNCTION ponieważ chcemy utworzyć funkcję
-- [dbo].[GetWypozyczenia2] jest nazwą funkcji (to jest GetWypozyczenia2) 
-- oraz schematu, w którym się ona znajduje (to jest dbo)
-- (@OsobaID INT) oznacza nazwę parametru wejściowego (OsobaID) i jego typ (INT)
CREATE FUNCTION [dbo].[GetWypozyczenia2]
    ( @OsobaID  INT )
-- określamy, że chcemy zwrócić dane w postaci tabeli
RETURNS table
AS
RETURN
(
SELECT L.imie, L.nazwisko, K.nazwa, K.RokWydania
  FROM [dbo].[WypozyczeniaKsiazek] AS WK
   INNER JOIN [dbo].[Ludzie] AS L
    ON WK.RefIdLudzie = L.id
   INNER JOIN [dbo].[Ksiazki] AS K
    ON WK.RefIdKsiazki = K.id
-- naszą funkcję czyni uniwersalnym to, że 
-- w klauzuli WHERE odwołujemy się 
-- do parametru wejściowego tzn. @OsobaId
   WHERE WK.RefIdLudzie = @OsobaId
);
GO

Aby wywołać naszą funkcję, możemy skorzystać z instrukcji SELECT i podać w nawiasie parametr, z jakim chcemy ją uruchomić - w naszym przypadku jest to identyfikator użytkownika z tabeli [dbo].[Ludzie]. Oto przykład:

USE tempdb
GO
 
SELECT * FROM [dbo].[GetWypozyczenia2](4)

Wynik zapytania zwróci dwie pozycje.

Podobnie jak w przypadku funkcji tabelarycznych, możemy np. określić w zapytaniu dodatkowe warunki - powodujące na przykład, że otrzymamy informacje o wypożyczonych pozycjach książkowych wypożyczonych, które zostały wydane po 2015 r.:

Na koniec tego artykułu - jako ciekawostkę - chciałbym  podać, że funkcje mogą wywoływać widoki czyniąc je zindeksowanymi widokami sparametryzowanymi. Ktoś mógłby zapytać po co to robić. Otóż może to się przydać w sytuacji, gdy w tabeli, na której pracujemy i w której znajdują się dane jest założony indeks na kolumnę X, a widok mocno wykorzystuje kolumnę Y - wówczas istnieje możliwość założenia indeksu na widoku na kolumnie Y i wywoływania widoku z poziomu funkcji.