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

Скалярные функции возвращают одно значение и обычно работают с одной строкой данных. Количество входных значений, которое они принимают может быть равно нулю (например, 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;

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

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

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;

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

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

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;

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

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

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

IIF

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

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

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

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

ВЫБЕРИТЕ

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

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

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