Сложные выражения со значением
Использование выражения CASE с условиями поиска
Эффективным способом использования выражения CASE является проводимый по всей таблице поиск тех строк, в которых выполняется определенное условие поиска. Если использовать выражение CASE таким способом, то у него должен быть следующий синтаксис:
CASE WHEN условие 1 THEN результат1 WHEN условие2 THEN результат2 … WHEN условие_n THEN результат_n ELSE результат_х END
Выражение CASE проверяет, является ли истинным условие1 в первой оцениваемой строке (т.е. в первой из тех строк, которые соответствуют условиям предложения WHERE, если только оно имеется). Если да, то выражение CASE принимает значение результата1. А если условие1 не выполняется, строка проверяется на выполнение условия2. Если оно выполняется, то выражение CASE принимает значение результата1 и т.д. А если ни одно из имеющихся условий не выполнено, то CASE принимает значение результат_х. Предложение ELSE не является обязательным. В том случае, если этого предложения нет и не выполняется ни одно из указанных условий, выражение принимает значение NULL. После того как оператор SQL, в котором находится выражение CASE, выполнится по отношению к первой оцениваемой строке таблицы и выполнит соответствующее действие, он приступает к следующей строке. Такая последовательность действий продолжается до тех пор, пока не будет закончена обработка всей таблицы.
Обновление значений на основе условия
Выражение CASE можно поместить почти в любом месте оператора SQL, где только может находиться значение. Поэтому использование этого выражения раскрывает перед вами огромные возможности. Можно использовать CASE внутри оператора UPDATE (обновить), чтобы, например, на основе определенного условия по-разному изменять табличные значения. Проанализируйте следующий пример:
UPDATE FOODS SET RATING = CASE WHEN FAT < 1 THEN 'очень мало жиров' WHEN FAT < 5 THEN 'мало жиров' WHEN FAT < 20 THEN 'среднее количество жиров' WHEN FAT < 50 THEN 'высокое количество жиров' ELSE 'сплошные жиры' END;
Этот оператор проверяет по порядку условия WHEN, пока не встретится первое истинное значение, после чего он игнорирует оставшиеся условия.
В табл. 7.2 было показано содержимое жиров в 100 граммах некоторых продуктов питания. Таблица из базы данных, содержащая эту информацию, может также иметь столбец RATING (оценка), который дает быструю оценку величины содержания жиров. Если запустить предшествующий оператор UPDATE в таблице FOODS (продукты питания) из главы 7, то у спаржи будет оценка "очень мало жиров", у цыплят – "мало жиров", а жареные миндальные орехи попадут в категорию "сплошные жиры".
Обход условий, вызывающих ошибки
Другим ценным применением выражения CASE является обход исключений – проверка условий, которые вызывают ошибки. Проанализируйте выражение CASE, которое определяет размер "компенсации" для продавцов. В компаниях, где работникам компенсируют недополученные комиссионные, часто к комиссионным своих новых работников дают еще и "компенсацию". В следующем примере такую "компенсацию" к своим комиссионным получают новые продавцы, причем по мере роста комиссионных ее размер довольно сильно уменьшается:
UPDATE SALES_COMP SET COMP = COMMISION + CASE WHEN COMMISSION <> 0 THEN DRAW/COMMISSION WHEN COMMISSION = 0 THEN DRAW END;
Если у продавца комиссионных нет, то структура этого примера позволяет избежать операции деления на нуль, которая, как известно, приводит к ошибке. А если продавец все же заработал какие-то комиссионные, то ему выплатят эти комиссионные плюс "компенсацию", которая уменьшается пропорционально их размеру.
Все выражения THEN внутри общего выражения CASE должны быть одного и того же типа – или все числовые, или символьные, или даты-времени. Результат выражения CASE имеет тот же тип.