Использование скалярных функций

  • 3 мин

Скалярные функции возвращают одно значение и обычно работают с одной строкой данных. Количество входных значений, которое они принимают может быть равно нулю (например, GETDATE) или единице (например, UPPER). Кроме того, возможно несколько значений (например, ROUND). Так как скалярные функции всегда возвращают одно значение, их можно использовать в любом расположении, где необходимо одно значение (результат). Чаще всего они используются в предложениях SELECT и предикатах предложения WHERE. Их также можно использовать в предложении SET инструкции UPDATE.

Встроенные скалярные функции можно объединить в различные категории, например, строковые функции, функции преобразования, логические, математические и другие. В этом модуле рассматриваются несколько распространенных скалярных функций.

При использовании скалярных функций необходимо учитывать следующее:

  • Детерминизм: если функция возвращает одинаковое значение при каждом вызове с определенным набором входных данных и при одном и том же состоянии базы данных, мы говорим, что это детерминированная функция. Например, ROUND (1.1, 0) всегда возвращает значение 1.0. Многие встроенные функции являются недетерминированными. Например, функция GETDATE () возвращает текущую дату и время. Результаты недетерминированных функций нельзя индексировать. Это влияет на возможность обработчика запросов создавать хороший план для выполнения запроса.
  • Параметры сортировки: какие параметры сортировки необходимо использовать при использовании функций, обрабатывающих символьные данные? Некоторые функции используют параметры сортировки (порядок сортировки) входного значения. Другие функции используют параметры сортировки базы данных, если не указаны входные параметры сортировки.

Примеры скалярных функций

На момент написания статьи в технической документации по SQL Server было указано более 200 скалярных функций, охватывающих несколько категорий, а именно:

  • Функции конфигурации
  • Функции преобразования
  • Функции курсора
  • Функции даты и времени
  • Математические функции
  • Функции метаданных
  • Функции безопасности
  • Строковые функции
  • Системные функции
  • Системные статистические функции.
  • Функции работы с текстами и изображениями.

Времени, выделенного для этого курса, недостаточно для описания каждой функции, однако в приведенных ниже примерах показаны некоторые часто используемые функции.

В следующем гипотетическом примере используется несколько функций даты и времени:

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;

Частичные результаты приведены ниже:

SalesOrderID

Датазаказа

OrderYear

OrderMonth

OrderDay

OrderWeekDay

YearsSinceOrder

71774

2008-06-01T00:00:00

2008

июня

1

Воскресенье

13

...

...

...

...

...

...

...

В следующем примере приведены некоторые математические функции:

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;

Частичные результаты:

TaxAmt

Округлено

Этаж

Ceiling

В квадрате

Корневой

Журнал

Случайное

70,4279

70,0000

70,0000

71,0000

4960,089098

8,392133221

4,254589491

28,64120429

...

..

...

...

...

...

...

...

В следующем примере используются некоторые строковые функции:

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;

Частичные результаты:

CompanyName

Заглавная

Нижний регистр

Length

Reversed

FirstSpace

FirstWord

RestOfName

A Bike Store

A BIKE STORE

a bike store

12

erotS ekiB A

2

а

Bike Store

Progressive Sports

PROGRESSIVE SPORTS

progressive sports

18

stropS evissergorP

12

Прогрессивный

Спорт

Advanced Bike Components

ADVANCED BIKE COMPONENTS

advanced bike components

24

stnenopmoC ekiB decnavdA

9

Расширенные

Bike Components

...

...

...

...

...

...

...

...

Логические функции

Другая категория функций позволяет определить, какое из нескольких значений необходимо вернуть. Логические функции вычисляют входное выражение и возвращают соответствующее значение на основе результата.

IIF

Функция IIF вычисляет логическое входное выражение и возвращает указанное значение, если выражение имеет значение True, и альтернативное значение, если выражение имеет значение False.

Например, рассмотрим указанный ниже запрос, который вычисляет тип адреса клиента. Если значение равно "Главный офис", выражение возвращает значение "Выставление счетов". Для всех остальных значений типа адреса выражение возвращает значение "Корреспонденция".

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

Частичные результаты из этого запроса могут выглядеть следующим образом:

AddressType

UseAddressFor

Главный офис

Выставление счетов

На основе доставки

Корреспонденция

...

...

CHOOSE

Функция CHOOSE вычисляет целочисленное выражение и возвращает соответствующее значение из списка на основе его порядкового номера (начиная с 1).

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

Результаты этого запроса могут выглядеть примерно так:

SalesOrderID

Состояние

OrderStatus

1 234

3

Доставлено

1235

2

Доставлено

1236

2

Доставлено

1237

1

Заказано

...

...

...