Лекция 20: Расширенное описание T-SQL
CASE
Ключевое слово CASE используется для оценки списка условий и возврата одного из нескольких возможных результатов. Возвращаемый результат зависит от того, какое условие совпадает с другим указанным условием или является истинным. Наиболее распространенным применением CASE является замена кодового или сокращенного значения на более понятное значение и упорядочивание значений, как будет показано в наших примерах этого раздела. Имеется два формата для конструкции CASE: простой и поисковый. В простом формате на входе после CASE задается в виде выражения значение, которое проверяется на равенство со значением в выражении или выражениях WHEN. В поисковом формате происходит проверка булева выражения на значение TRUE или FALSE, а не проверка на равенство с каким-либо значением. Сначала рассмотрим простой формат. В простом формате предложение CASE имеет следующий синтаксис:
CASE входное_выражение WHEN выражение_для_when THEN результирующее_выражение [WHEN выражение_для_when THEN результирующее_выражение...n] [ELSE выражение_для_else] END
Значение результирующего выражения возвращается в том случае, если значение соответствующего выражения WHEN равно значению входного выражения. Выражения сравниваются в порядке их следования в предложении CASE. Если не обнаружено ни одного совпадения, то возвращается значение результирующего выражения ELSE (если оно задано); в противном случае возвращается значение NULL. Отметим, что в простом формате значение входного выражения CASE и значение выражения WHEN должны иметь одинаковый тип данных или допускать неявное преобразование типов.
В следующем примере используется простой формат предложения CASE внутри оператора SELECT. Колонка payterms (сроки платежей) таблицы sales (продажи) содержит одно из следующих значений для каждой строки: Net 30, Net 60, On invoice или None. С помощью следующего оператора T-SQL в колонке payterms можно выводить на экран альтернативные (более понятные) значения:
SELECT 'Payment Terms' = (сроки платежей)
CASE payterms
WHEN 'Net 30' THEN 'Payable 30 days --к оплате в течение 30 дней
after invoice' --после получения счета-фактуры
WHEN 'Net 60' THEN 'Payable 60 days --к оплате в течение 60 дней
after invoice' --после получения счета-фактуры
WHEN 'On invoice' THEN 'Payable upon --к оплате по
receipt of invoice' --получении счета-фактуры
ELSE 'None'
END,
title_id
FROM sales
ORDER BY payterms
GOВ этом предложении CASE проверяется значение payterms для каждой строки, указанной в операторе SELECT. Значение результирующего выражения возвращается в том случае, если значение выражения WHEN равно значению колонки payterms. Результаты предложения CASE появляются в колонке Payment Terms результирующего набора, как это показано ниже:
Payment Terms title_id ---------------------------------------------------- Payable 30 days after invoice PC8888 Payable 30 days after invoice TC3218 Payable 30 days after invoice TC4203 Payable 30 days after invoice TC7777 Payable 30 days after invoice PS2091 Payable 30 days after invoice MC3021 Payable 30 days after invoice BU1111 Payable 30 days after invoice PC1035 Payable 60 days after invoice PS1372 Payable 60 days after invoice PS2106 Payable 60 days after invoice PS3333 Payable 60 days after invoice PS7777 Payable 60 days after invoice BU7832 Payable 60 days after invoice MC2222 Payable 60 days after invoice PS2091 Payable 60 days after invoice BU1032 Payable 60 days after invoice PS2091 Payable upon receipt of invoice PS2091 Payable upon receipt of invoice BU1032 Payable upon receipt of invoice BU2075 Payable upon receipt of invoice MC3021 (21 row(s) affected)
А теперь рассмотрим второй формат предложения CASE – поисковый формат. В этом формате предложение CASE имеет следующий синтаксис:
CASE WHEN Булево_выражение THEN результирующее_выражение [WHEN Булево_выражение THEN результирующее_выражение...n] [ELSE результирующее_выражение_для_else] END
Предложение CASE в поисковом формате отличается от CASE в простом формате тем, что в поисковом формате после ключевого слова CASE нет входного выражения, а после ключевых слов WHEN следуют булевы выражения, которые проверяются на значение TRUE или FALSE (а не на равенство). В поисковом формате предложение CASE проверяет значения булевых выражений и выводит значение результирующего выражения для первого булева выражения, возвращающего значение TRUE. (Выражения проверяются в порядке их следования.)
Например, предложение CASE внутри следующего оператора SELECT проверяет значение колонки price (цена) каждой строки и возвращает символьную строку, соответствующую диапазону цен (Price Range), в который попадает цена данной книги:
SELECT 'Price Range' =
CASE
WHEN price BETWEEN .01 AND 10.00
THEN 'Inexpensive: $10.00 or less' --дешевые
WHEN price BETWEEN 10.01 AND 20.00
THEN 'Moderate: $10.01 to $20.00' --умеренные
WHEN price BETWEEN 20.01 AND 30.00
THEN 'Semi-expensive: $20.01 to $30.00' --не слишком дорогие
WHEN price BETWEEN 30.01 AND 50.00
THEN 'Expensive: $30.01 to $50.00' --дорогие
WHEN price IS NULL
THEN 'No price listed' --цена не указана
ELSE 'Very expensive!' --очень дорогие
END,
title_id
FROM titles
ORDER BY price
GOРезультирующий набор имеет следующий вид:
Price Range title_id ———————————————— ———— No price listed MC3026 No price listed PC9999 Inexpensive: $10.00 or less MC3021 Inexpensive: $10.00 or less BU2075 Inexpensive: $10.00 or less PS2106 Inexpensive: $10.00 or less PS7777 Moderate: $10.01 to $20.00 PS2091 Moderate: $10.01 to $20.00 BU1111 Moderate: $10.01 to $20.00 TC4203 Moderate: $10.01 to $20.00 TC7777 Moderate: $10.01 to $20.00 BU1032 Moderate: $10.01 to $20.00 BU7832 Moderate: $10.01 to $20.00 MC2222 Moderate: $10.01 to $20.00 PS3333 Moderate: $10.01 to $20.00 PC8888 Semi-expensive: $20.01 to $30.00 TC3218 Semi-expensive: $20.01 to $30.00 PS1372 Semi-expensive: $20.01 to $30.00 PC1035 (18 row(s) affected)
Примечание. В этих двух примерах предложения CASE мы ставили запятую после ключевого слова END, поскольку все предложение CASE было использовано как часть списка_колонок в предложении SELECT вместе с title_id. Иными словами, все предложение CASE было просто входом в список_колонок. Это наиболее распространенное применение ключевого слова CASE.Другие ключевые слова
Ниже приводятся другие ключевые слова, которые можно использовать для управления программной последовательностью:
- GOTO метка. Выполняет передачу управления на метку, определенную в GOTO.
- RETURN. Безусловный выход из запроса или процедуры.
- WAITFOR. Задает задержку или определенное время для выполнения оператора.
Дополнительная информация. Для получения более подробной информации по использования этих ключевых слов найдите "GOTO", "RETURN" и "WAITFOR" в Books Online и просмотрите темы, приведенные в диалоговом окне Topics Found.Заключение
В этой лекции мы рассмотрели использование операторов T-SQL INSERT, UPDATE и DELETE. Мы также рассмотрели ключевые слова T-SQL IF, ELSE, WHILE, BEGIN, END и CASE, которые используются для управления программной последовательностью. В "лекции 21" вы узнаете, как создавать хранимые процедуры, в которых вы можете использовать эти операторы и конструкции.