А поскольку в операторе предусмотрена также конструкция ELSE, то вместо любого значения, не соответствующего одному из указанных в конструкциях WHEN, возвращается значение, предусмотренное в конструкции ELSE. Если бы конструкция ELSE была исключена, то вместо любого такого значения было бы возвращено NULL-значение.

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

USE AdventureWorks GO

SELECT TOP 10 SalesOrderlD % 10 AS OrderLastDigit, ProductID % 10 AS ProductLastDigit , How Close? = CASE SalesOrderlD % 10

WHEN ProductID % 1 THEN Exact Match!

WHEN ProductID % 1 - 1 THEN Within 1

WHEN ProductID % 1 + 1 THEN Within 1

ELSE More Than One Apart

FROM Sales.SalesOrderDetail ORDER BY SalesOrderlD DESC

Обратите внимание на то, что в данном примере выражения используются не только там, где обычно, но и в конструкциях WHEN, и все же созданный в итоге оператор функционирует успешно: OrderLastDigit ProductLastDigit How Close?

2 5 More Than One Apart

3 2 More Than One Apart 3 9 More Than One Apart 3 8 More Than One Apart 2 2 More Than One Apart 2 8 More Than One Apart 1 7 Within 1

1 0 Within 1

1 1 Within 1

0 2 Exact Match1

(10 row(s) affected)

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

Поисковый оператор case

Оператор CASE этого типа действует в основном так же, как простой оператор CASE, если не считать двух небольшрхх описанных ниже отличий.

В нем отсутствует входное выражение (напомним, что в простом операторе CASE входное выражение находится между ключевым словом CASE и первым оператором WHEN).

Вычисление выражения в конструкции WHEN должно приводить к получению булева значения (напомним, что в примерах применения простого оператора CASE, приведенных выше, использовались такие значения, как 1, 3 и ProductID + 1).

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

1Сак обычно, рассмотрим пример, который позволяет лучше всего проиллюстрировать особенности работы оператора:

SELECT TOP 10 SalesOrderlD % 10 AS OrderLastDigit, ProductID % 10 AS ProductLastDigit, How Close? = CASE

WHEN (SalesOrderlD % 10) < 3 THEN Ends With Less Than Three WHEN ProductID = 6 THEN ProductID is 6

WHEN ABS(SalesOrderlD % 10 - ProductID) <= 1 THEN Within 1 ELSE More Than One Apart

FROM Sales.SalesOrderDetail ORDER BY SalesOrderlD DESC

Этот пример существенно отличается от приведенных выше примеров применения простых операторов CASE, но его выполнение приводит к успешному получению желаемых результатов:

OrderLastDigit ProductLastDigit How Close?

2 5 Ends With Less Than Three

3 2 More Than One Apart 3 9 More Than One Apart 3 8 More Than One Apart

2 2 Ends With Less Than Three

2 8 Ends With Less Than Three

1 7 Ends With Less Than Three

1 0 Ends With Less Than Three

1 1 Ends With Less Than Three

0 2 Ends With Less Than Three

(10 row(s) affected)

При изучении того, как именно происходит вычисление выражений в СУБД SQL Server, необходимо обратить особое внимание на описанные ниже нюансы.

Даже если значение TRUE может быть получено при вычислении выражений в нескольких конструкциях WHEN, учитывается только результат вычисления в первой по порядку конструкции. Например, предпоследняя строка соответ ствует условию и в первой конструкции WHEN (последняя цифра меньше 3), и в третьей конструкции (последняя цифра отличается от значения ProductID на 1). Таким же образом действуют аналогичные операторы во многих других языках, включая Visual Basic. Но в языке С выполнение подобного оператора организовано иначе, и об этом должны помнить программисты, работающие на языке С; кроме того, не требуется оператор break, поскольку окончание конструкции THEN всегда обозначается началом следующей конструкции.

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

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

Рассмотрим описанные выше особенности поискового оператора CASE на несколько более сложном примере. В этом примере не рассматриваются комбинации и сочетания значений из разных столбцов; вместо этого используется только один столбец, в котором осуществляется поиск (можно было бы применять для проверки и другие столбцы, но чаще всего необходимость в этом не возникает). Автор продемонстрирует ситуацию из реальной жизни, в которой он помог найти решение для довольно крупного узла электронной коммерции.

Постановка задачи заключается в следующем: в торговле обычно принято назначать легко запоминающиеся цены. Покупателям не нравится, когда, допустим, из-за 10%-ной наценки цена на товары принимает вид $10.13, $23.19 и т.д. Поэтому маркетологи рекомендуют округлять цены так, чтобы они оканчивались цифрами наподобие 49, 75, 95 или 99. В рассматриваемом сценарии было предъявлено требование- подготовить для анализа новый прейскурант с ценами, не выходящими за пределы допустимого, которые должны соответствовать определенным критериям.

Если новая цена оканчивается цифрами, которые не превышают 50 центов (как в приведенном выше примере с ценой $10 .13), то специалисты по маркетингу требуют увеличить эту цену, оставив то же значение в долларах, но указав в конце 49 центов (в данном примере цена должна быть увеличена до $10.4 9). Цены, оканчивающиеся значениями 50-75 центов, должны быть откорректированы так, чтобы оканчивались на 75 центов, а цены, оканчивающиеся значением, превышающим 75 центов, должны быть откорректированы так, чтобы они оканчивались на 95 центов. Некоторые примеры того, как эти требования реализуются на практике, приведены в табл. 10.3.

Таблица 10.3. Примеры цен, соответствующих маркетинговым рекомендациям

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

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

Для реализации кода потребовалось бы применить курсор (а это - неудачное решение!) и обрабатывать таблицу построчно.

В связи с этим указанное решение является неприемлемым.

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