Self-Learning – видеокурсы и книги по IT

Логическая функция CHOOSE в T-SQL – описание и примеры использования

Всем привет! Сегодня мы поговорим о функции CHOOSE языка T-SQL, которая появилась еще в 2012 версии Microsoft SQL Server. Благодаря этой функции мы можем упрощать SQL запросы, в которых используется условная конструкция CASE, и тем самым повышать читабельность SQL инструкций, не снижая их производительности.

Логическая функция CHOOSE в T-SQL – описание и примеры использования

Функция CHOOSE языка T-SQL

CHOOSE – логическая функция языка T-SQL, которая возвращает элемент по указанному индексу из списка значений.

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

Как было уже отмечено, функцию CHOOSE можно использовать для замены конструкции CASE в SQL запросах, так как принцип ее работы эквивалентен работе конструкции CASE, однако функция CHOOSE может уменьшить количество строк кода и повысить его читабельность, тем самым в целом упрощая программирование с использованием языка T-SQL.

Синтаксис функции CHOOSE

CHOOSE ( index, value_1, value_2 [,value_n ] )

где

  • index – целочисленное выражение, которое представляет отсчитываемый от 1 индекс в списке элементов, следующих за ним. Если указанное значение индекса имеет числовой тип, отличный от типа INT, то значение неявно преобразуется в целое. Если значение индекса выходит за границы массива значений, то функция CHOOSE возвращает значение NULL;
  • value_1, value_2… – список значений любого типа данных, в качестве разделителя выступает запятая.

Функция CHOOSE возвращает тип данных с наивысшим приоритетом из набора типов данных, которые были переданы в функцию.

Интересная статья! Чем отличается инструкция THROW от RAISERROR в T-SQL.

Примеры использования функции CHOOSE в T-SQL

Давайте рассмотрим несколько примеров использования функции CHOOSE в языке T-SQL.

В каждом примере будет представлено два SQL запроса: первый – с использованием функции CHOOSE, а второй, эквивалентный запрос, но только с использованием конструкции CASE, который будет возвращать точно такой же результат, чтобы более наглядно был виден принцип работы функции CHOOSE.

Простой пример использования функции CHOOSE

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

Функция CHOOSE

SELECT CHOOSE (3, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number;

Скриншот 1

В этом примере мы первым параметром передали индекс 3, а затем через запятую перечислили список значений, в результате функция нам вернула третье по счету значение (текст «Три»). Если бы мы передали 1, то нам вернулось бы первое по счету значение, т.е. текст «Один».

Конструкция CASE

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

DECLARE @number INT = 3;

SELECT CASE @number WHEN 1 THEN 'Один'
                    WHEN 2 THEN 'Два'
                    WHEN 3 THEN 'Три'
                    WHEN 4 THEN 'Четыре'
                    WHEN 5 THEN 'Пять'
END AS number;

Скриншот 2

Пример использования функции CHOOSE с участием столбца таблицы

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

Допустим, у нас будет таблица с заказами orders, в которой есть атрибут status, отражающий статус каждого заказа, где:

  • 1 = В обработке
  • 2 = Выполнен
  • 3 = Отменен

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

Исходные данные

Инструкция создания тестовых данных.

CREATE TABLE orders (
   id INT NOT NULL IDENTITY(1,1),
   number INT NOT NULL,
   summa NUMERIC(18,2) NOT NULL,
   status TINYINT NOT NULL
);

INSERT INTO orders (number, summa, status)
   VALUES (32152, 1580, 1),
          (33568, 9600, 2),
          (41289, 3400, 1),
          (42870, 6100, 2),
          (43987, 1900, 3);

SELECT * FROM orders;

Заметка! Если Вы не знаете, что делает указанная выше SQL инструкция, рекомендую пройти онлайн-курс по T-SQL для начинающих, на котором применяется авторская последовательная методика обучения и подробно рассмотрены все базовые конструкции языка T-SQL. Каждый урок на курсе включает онлайн-тестирование для проверки полученных знаний.

Функция CHOOSE

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

SELECT id AS order_number,
       summa AS order_summa,
       status AS order_status_id,
       CHOOSE (status, 'В обработке', 'Выполнен', 'Отменен') AS order_status
FROM orders;

Скриншот 3

Конструкция CASE

Точно такой же результат сформирует и следующий запрос, в котором используется конструкция CASE.

SELECT id AS order_number,
       summa AS order_summa,
       status AS order_status_id,
       CASE status WHEN 1 THEN 'В обработке'
                   WHEN 2 THEN 'Выполнен'
                   WHEN 3 THEN 'Отменен'
       END AS order_status
FROM orders;

Скриншот 4

Заметка! Список и расшифровка версий Microsoft SQL Server.

Примеры некорректного использования функции CHOOSE

При использовании функции CHOOSE стоит помнить о нескольких важных моментах, которые уже упоминались, к ним относятся:

  • Значение индекса должно быть целочисленным;
  • Если значение индекса выходит за границы массива значений, то функция CHOOSE возвращает значение NULL;
  • Если значение индекса имеет числовой тип, отличный от типа INT, то значение неявно преобразуется в целое;
  • Функция CHOOSE возвращает тип данных с наивысшим приоритетом из набора типов данных, которые были переданы в функцию.

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

Пример ошибки преобразования типа данных в целое число

Если в качестве индекса передать текстовую строку, то запрос завершится ошибкой, так как текст нельзя преобразовать в целое число.

SELECT CHOOSE ('Два', 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number;

Скриншот 5

Пример передачи значения вне диапазона

Индекс в функции CHOOSE начинается с числа 1, поэтому если мы передадим в качестве индекса значение 0, то функция вернет NULL.

Функция CHOOSE также вернет NULL, если мы передадим значение индекса вне диапазона массива значений, например, у нас перечислено 5 значений в массиве, и если мы передадим значение индекса 6, то нам вернется NULL.

SELECT CHOOSE (0, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_0,
       CHOOSE (6, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_6;

Скриншот 6

Заметка! Обзор функционала SQL Server Management Studio (SSMS).

Пример преобразования типа данных в целое число

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

В данном случае все значения, которые мы передаем в качестве индекса округляются до 1, и тем самым возвращается первое значение массива.

SELECT CHOOSE (1.4, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_1,
       CHOOSE (1.5, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_2,
       CHOOSE (1.6, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_3,
       CHOOSE (1.9, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_4;

Скриншот 7

Пример ошибки преобразования типа данных в списке значений за счет приоритета типа данных

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

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

Таким образом, если указывать индекс с 1 по 4, то функция отработает корректно, однако если мы укажем индекс 5, т.е. мы хотим получить пятое значение массива, которое имеет текстовый тип данных, то нам вернется ошибка, так как данное текстовое значение не может быть преобразовано к целочисленному типу данных.

SELECT CHOOSE (5, 10, 20, 30, 40, 'Пятьдесят') AS number;

Скриншот 8

Также интересная статья! Как получить последовательность дат в указанном промежутке на T-SQL.

На сегодня это все, надеюсь, материал был вам полезен, пока!

Мы в соцсетях
Следите за выходом новых статей в наших группах в социальных сетях: Подписывайтесь, и Вы не пропустите выход нового материала!
Теги: #
Понравилась статья? Поделитесь с друзьями:
Share on VK
VK
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on Tumblr
Tumblr
Share on Reddit
Reddit
Email this to someone
email

Авторские онлайн-курсы по T-SQL

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Пролистать наверх