Korzystanie z funkcji skalarnych

  • 3 min

Funkcje skalarne zwracają pojedynczą wartość i zwykle działają w jednym wierszu danych. Liczba pobieranych wartości wejściowych może wynosić zero (na przykład GETDATE), jeden (na przykład UPPER) lub wielokrotny (na przykład ROUND). Ponieważ funkcje skalarne zawsze zwracają pojedynczą wartość, mogą być używane wszędzie tam, gdzie potrzebna jest pojedyncza wartość (wynik). Są one najczęściej używane w klauzulach SELECT i predykatach klauzul WHERE. Można ich również używać w klauzuli SET instrukcji UPDATE.

Wbudowane funkcje skalarne można podzielić na wiele kategorii, takich jak ciąg, konwersja, logiczny, matematyczny i inne. W tym module przyjrzymy się kilku typowym funkcjom skalarnym.

Niektóre zagadnienia dotyczące używania funkcji skalarnych obejmują:

  • Determinizm: Jeśli funkcja zwraca tę samą wartość dla tego samego stanu danych wejściowych i bazy danych za każdym razem, gdy jest wywoływana, mówimy, że jest deterministyczny. Na przykład funkcja ROUND(1.1, 0) zawsze zwraca wartość 1.0. Wiele wbudowanych funkcji nie jest nieokreślonych. Na przykład funkcja GETDATE() zwraca bieżącą datę i godzinę. Nie można indeksować wyników z nieokreślonych funkcji, co wpływa na zdolność procesora zapytań do utworzenia dobrego planu wykonywania zapytania.
  • Porządkowanie: Które porządkowanie będzie używane podczas stosowania funkcji manipulujących danymi znakowymi? Niektóre funkcje używają sortowania (kolejność sortowania) wartości wejściowej; inne używają sortowania bazy danych, jeśli nie podano sortowania wejściowego.

Przykłady funkcji skalarnych

W momencie pisania dokumentacji technicznej programu SQL Server wymieniono ponad 200 funkcji skalarnych obejmujących wiele kategorii, w tym:

  • Funkcje konfiguracji
  • Funkcje konwersji
  • Funkcje kursora
  • Funkcje daty i godziny
  • Funkcje matematyczne
  • Funkcje metadanych
  • Funkcje zabezpieczeń
  • Funkcje ciągów
  • Funkcje systemowe
  • Funkcje statystyczne systemu
  • Funkcje tekstu i obrazu

W tym kursie nie ma wystarczająco dużo czasu, aby opisać każdą funkcję, ale w poniższych przykładach pokazano niektóre często używane funkcje.

W poniższym hipotetycznym przykładzie użyto kilku funkcji daty i godziny:

SELECT  SalesOrderID,
    OrderDate,
        YEAR(OrderDate) AS OrderYear,
        DATENAME(mm, OrderDate) AS OrderMonth,
        DAY(OrderDate) AS OrderDay,
        DATENAME(dw, OrderDate) AS OrderWeekDay,
        DATEDIFF(yy,OrderDate, GETDATE()) AS YearsSinceOrder
FROM Sales.SalesOrderHeader;

Poniżej przedstawiono częściowe wyniki:

ID Zamówienia Sprzedaży

OrderDate (Data zamówienia)

RokZamówienia

Miesiąc zamówienia

Dzień Zamówienia

OrderWeekDay

LataOdZamówienia

71774

2008-06-01T00:00:00

2008

czerwiec

1

Niedziela

13

...\

...\

...\

...\

...\

...\

...\

W następnym przykładzie przedstawiono niektóre funkcje matematyczne:

SELECT TaxAmt,
       ROUND(TaxAmt, 0) AS Rounded,
       FLOOR(TaxAmt) AS Floor,
       CEILING(TaxAmt) AS Ceiling,
       SQUARE(TaxAmt) AS Squared,
       SQRT(TaxAmt) AS Root,
       LOG(TaxAmt) AS Log,
       TaxAmt * RAND() AS Randomized
FROM Sales.SalesOrderHeader;

Częściowe wyniki:

TaxAmt

Zaokrąglone

Piętro

Pułap

Kwadrat

Element główny

Dziennik

Randomizowanych

70.4279

70.0000

70.0000

71.0000

4960.089098

8.392133221

4.254589491

28.64120429

...\

..

...\

...\

...\

...\

...\

...\

W poniższym przykładzie użyto niektórych funkcji ciągów:

SELECT  CompanyName,
        UPPER(CompanyName) AS UpperCase,
        LOWER(CompanyName) AS LowerCase,
        LEN(CompanyName) AS Length,
        REVERSE(CompanyName) AS Reversed,
        CHARINDEX(' ', CompanyName) AS FirstSpace,
        LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,
        SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName
FROM Sales.Customer;

Częściowe wyniki:

Nazwa firmy

Wielka litera

Małe litery

Długość

Odwrócone

Pierwsza przestrzeń

PierwszeSłowo

RestOfName

Sklep rowerowy

SKLEP ROWEROWY

sklep z rowerami

12

erotS ekiB A

2

A

Sklep rowerowy

Sport progresywny

PROGRESYWNE SPORTY

sport progresywny

18

stropS evissergorP

12

Postępowy

Sporty

Zaawansowane składniki rowerów

ZAAWANSOWANE SKŁADNIKI ROWERÓW

zaawansowane składniki rowerów

24

stnenopmoC ekiB decnavdA

9

Zaawansowany

Składniki roweru

...\

...\

...\

...\

...\

...\

...\

...\

Funkcje logiczne

Inna kategoria funkcji umożliwia określenie, która z kilku wartości ma zostać zwrócona. Funkcje logiczne oceniają wyrażenie wejściowe i zwracają odpowiednią wartość na podstawie wyniku.

IIF

Funkcja IIF ocenia wyrażenie wejściowe wartości logicznej i zwraca określoną wartość, jeśli wyrażenie przyjmie wartość True, a alternatywną wartość, jeśli wyrażenie przyjmie wartość False.

Rozważmy na przykład następujące zapytanie, które ocenia typ adresu klienta. Jeśli wartość to "Main Office", wyrażenie zwraca wartość "Billing". Dla wszystkich innych wartości typu adresu wyrażenie zwraca wartość "Mailing".

SELECT AddressType,
      IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor
FROM Sales.CustomerAddress;

Częściowe wyniki tego zapytania mogą wyglądać następująco:

TypAdresu

UżyjAdresuDla

Biuro główne

Rozliczenia

Wysyłka

Mailingowej

...\

...\

WYBIERAĆ

Funkcja CHOOSE oblicza wyrażenie całkowite i zwraca odpowiednią wartość z listy na podstawie pozycji porządkowej (opartej na 1).

SELECT SalesOrderID, Status,
CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus
FROM Sales.SalesOrderHeader;

Wyniki tego zapytania mogą wyglądać mniej więcej tak:

ID Zamówienia Sprzedaży

Stan

StatusZamówienia

1234

3

Dostarczenia

1235

2

Wysłano

1236

2

Wysłano

1237

1

Zamówiona

...\

...\

...\