case……when……else用法

CASE表達式可以在SQL中實作if-then-else型的邏輯，而不必使用PL/SQL。CASE的工作方式與DECODE()類似，但應該使用CASE,因為它與ANSI相容。

CASE有兩種表達式:

1. 簡單CASE表達式,使用表達式确定傳回值.

文法:

CASE search_expression

WHEN expression1 THEN result1

WHEN expression2 THEN result2

...

WHEN expressionN THEN resultN

ELSE default_result

END

例:

select product_id,product_type_id,

case product_type_id

when 1 then 'Book'

when 2 then 'Video'

when 3 then 'DVD'

when 4 then 'CD'

else 'Magazine'

end

from products

結果:

PRODUCT_ID PRODUCT_TYPE_ID CASEPROD

---------- --------------- --------

         1               1                             Book

         2               1                             Book

         3               2                              Video

         4               2                             Video

         5               2                             Video

         6               2                            Video

         7               3                             DVD

         8               3                             DVD

         9               4                              CD

        10               4                               CD

        11               4                             CD

        12                                            Magazine

12 rows selected.

2. 搜尋CASE表達式,使用條件确定傳回值.

CASE

WHEN condition1 THEN result1

WHEN condistion2 THEN result2

WHEN condistionN THEN resultN

case

when product_type_id=1 then 'Book'

when product_type_id=2 then 'Video'

when product_type_id=3 then 'DVD'

when product_type_id=4 then 'CD'