Давайте рассмотрим простейший пример с оператором case. Предположим, что мы хотим разделить клиентов на три категории в соответствии с суммами их кредитного лимита- У клиентов категории А лимит кредита превьпиает $60000, у клиентов категории g - $30000, а остальные клиенты относятся к категории С. Если бы в нашем распоря-ясении был только язык SQL1, нам пришлось бы извлечь из базы данных имена и кредитные лимиты клиентов, а приложению - проверить эти лимиты и назначить клиентам категории. Оператор case из SQL2 позволяет возложить всю работу на СУБД:

SELECT COMPANY, CASE WHEN CREDIT LIMIT > 60000 THEN A WHEN CREDIT LIMIT > 30000 THEN B ELSE C

FROM CUSTOMERS

Для каждой строки таблицы результатов запроса СУБД вычисляет выражение в операторе case. Сначала она сравнивает лимит кредита со значением $60000. Если результатом оказывается true, она возвращает во втором столбце значение А. В противном случае лимит кредита сравнивается со значением $30000. Если результатом оказывается true, второй столбец получает значение В, иначе - С.

В этом примере в качестве возможных результатов оператора case были указаны простые литеральные значения, но в общем случае это могут быть любые выражения SQL. Кроме того, совершенно не обязательно, чтобы все проверки, выполняемые в предложениях when, бьши аналогичными, а сам оператор case может находиться не только в предложении select. Рассмотрим пример его использования в предложении where. Предположим, что вам нужны суммы плановых объемов продаж служащих по офисам. Если служащему еще не назначен офис, его сумма должна быть включена в итог по офису его руководителя. Вот запрос, который формирует такие итоги:

SELECT CITY, SUM(SALESREPS.SALES) FROM OFFICES, SALESREPS WHERE OFFICE =

CASE WHEN (REP OFFICE IS NOT NULL) THEN REP OFFICE ELSE (SELECT REP OFFICE

FROM SALESREPS AS MGRS WHERE MGRS.EMPL NUM = MANAGER)

GROUP BY CITY

Стандарт SQL2 определяет еще одну версию оператора case, более удобную для тех случаев, когда нужно сравнить результат проверяемого выражения с несколькими значениями (чаще всего литеральньпми). Синтаксис этой версии представлен на рис. 9.10. Вместо повторения условия вида

проверяемое выражение = значение

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

SELECT NAME, CITY, CASE OFFICE WHEN 11 THEN New York

WHEN 12 THEN Illinois WHEN 13 THEN Georgia

WHEN 21 THEN California WHEN 22 THEN Colorado

FROM OFFICES, SALESREPS WHERE MGR = EMPL NUM

- CASE-провврявмов выражвнив-5-WHEN значение THEN--резупьтирующвв выраженив

- NULL-

L- ELSE-j-peзy77ыnupy/oцe8 8ыpaжвнue-

NULL-

Рис. 9 10 Альтернативней синтаксис оператора CAFE (SOL2j

Оператор COALESCE

Часто оператор case применяется для работы со значениями null Например, когда вы запрашиваете из базы данных набор записей, который хотите представить пользователю, значения шьь в нем обычно лучше заменять некоторьгми литералами, соответствующими их реальному смыслу, вроде слова отсутствует Пусть, к примеру, пользователю нужен отчет о служащих и их плановых объемах продаж Если служащему еще не назначен план, в этой колонке нужно вывести его фактический объем продаж на текущий период Если же по какой-то причине значением столбца sales также оказывается null, тогда во второй колонке отчета нужно вывести ноль Для выполнения этой задачи требуется конструкция if . .then . else, которую прекрасно эмулирует оператор case

SELECT NAME, CASE WHEN (QUOTA IS NOT NULL) THEN QUOTA WHEN (SALES IS NOT NULL) THEN SALES ELSE 0 00

FROM SALESREPS

Поскольку значения null очень часто обрабатываются подобным образом, в cтaндapгSQL2длявьшoлнeнияэтиxдeйcтвийвключeнoпepaтоpcoALESCE (рис 911) - нечто вроде специальной формы оператора case Правила его обработки очень просты СУБД вычисляет первое вьфажение в списке Если его значение не равно null, оно становится результатом всего оператора coalesce В противном случае СУБД переходит ко второму вьфажению и провфяет, равно ли оно null Если нет, оно возвращается в качестве результата, иначе СУБД переходит к третьему выражению и тд Вот как вьппеприведенный пример можно переписать с использованием оператора coalesce

SELECT NAME, COALESCE (QUOTA, SALES, 0 00) FROM SALESREPS

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

I-COALESCE (-у скалярное выражение- ф

Рис. 9. П. Ситпксическа диаграмма оперотори CyAi.ESCE (SQL2}

Оператор NULLIF

Если в одних случаях значения null нужно удалить из набора данных, то в других эти значения нужно, наоборот, создать Во многих приложениях, связанных с обработкой данных (особенно разработанных до появления реляционных СУБД), отсутствующие данные представляются не значениями null, а специальными кодами, которые не могут интерпретироваться как данные Предположим, например, что в нашей учебной базе данных, когда служащий не имеет начальника, то в столбце MANAGER стоит нс null, как это бьшает обычно, а ноль (0) В некоторых случаях требуется выявлять такие коды и менять их на null Это можно сделать с помощью оператора nullif (рис 9 12) Когда СУБД встречает такой оператор, она анализирует первое выражение (обычно это имя столбца) и сравнивает его значение со значением второго выражения (обычно кодового, представляющего отсутствующие данные) Если они равны, результатом оператора nullif становится null, иначе - значение первого выражения

Во г пример запроса, в котором ноль означает отсутствие номера офиса

SELECT CITY, SUM(SALESREPS SALES) FROM OFFICES, SALESREPS WHERE OFFICE = NULLIF (REP OFFICE, 0) GROOP BY CITY

I-NULLIF (скалярное выражение -у)

Рис 912. Смтоксическая диаграмма апбраюра NULLIF ISQL2)

Конечно, операторам case, coalesce и nullif далеко до настоящих логических констрзкций, имеющихся в классических языках программирования, но для выражения логики запросов их возможностей вполне достаточно Одним из их важнейших достоинств является то, что они позволяют возложить на СУБД большую часть обработки данных, чем это бьшо возможно до их появления, разгрузив тем самьпм прикладные программы и освободив от лишней работы пользователей, которые вводят запросы вручную

Выражения с записями таблиц

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