Опубликован: 11.12.2006 | Доступ: свободный | Студентов: 5363 / 282 | Оценка: 4.42 / 3.86 | Длительность: 57:15:00

Лекция 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" вы узнаете, как создавать хранимые процедуры, в которых вы можете использовать эти операторы и конструкции.

Константин Дементьев
Константин Дементьев
Россия, г. Мичуринск