CASE式を使ってみよう、応用編part1

time 2016/01/14

folder ORACLE

CASE式を使用することで、SQL中で条件制御を行うことが出来ます。
本記事では、更新文（UPDATE）やソート（ORDER BY）を絡めるという応用的な使い方を紹介していきたいと思います。

更新文（UPDATE）での使用

UPDATE文の中で使用することで、更新する値に条件をつけることが出来ます。こうすることで、複数回の更新SQLを1回にまとめる事が出来ます。

例えば、商品価格を以下の条件で更新するとします。

価格10000円以上の商品は、価格を1.2倍する
価格1000円以上10000円未満の商品は、価格を1.1倍する
価格1000円未満の商品は、価格を0.9倍する
端数は切捨て

CASE式を使わない場合

UPDATE test_table
SET    price = TRUNC(price * 1.2)
WHERE  price >= 10000
;

UPDATE test_table
SET    price = TRUNC(price * 1.1)
WHERE  price < 10000 AND price >= 1000
;

UPDATE test_table
SET    price = TRUNC(price * 0.9)
WHERE  price < 1000
;

UPDATE test_table

SET price = TRUNC(price * 1.2)

WHERE price >= 10000

;

UPDATE test_table

SET price = TRUNC(price * 1.1)

WHERE price < 10000 AND price >= 1000

;

UPDATE test_table

SET price = TRUNC(price * 0.9)

WHERE price < 1000

;

CASE式を使う場合

UPDATE test_table
SET    price = CASE
                 WHEN price >= 10000                  THEN TRUNC(price * 1.2)
                 WHEN price < 10000 AND price >= 1000 THEN TRUNC(price * 1.1)
                 WHEN price < 1000                    THEN TRUNC(price * 0.9)
                 ELSE price
               END
;

UPDATE test_table

SET price = CASE

WHEN price >= 10000 THEN TRUNC(price * 1.2)

WHEN price < 10000 AND price >= 1000 THEN TRUNC(price * 1.1)

WHEN price < 1000 THEN TRUNC(price * 0.9)

ELSE price

END

;

更新SQLを1回にまとめられるだけでなく、更新SQLを複数回に分けた場合は、更新する順序に気をつける必要があります。
例えば、上記の例で9900円の商品があった場合で1000円以上10000円未満の商品の更新を先に実施した場合を考えてみましょう。
9900円を1.1倍し結果が10890円になります。さらに10000円以上なので次の更新条件の10000円以上の対象にもなり、10890円を1.2倍し結果が13068円になってしまいます。
本来は、10890円にするのが正しいにも関わらずです。

1回のSQLにまとめることで、こうした心配も不要になります。

ソート（ORDER BY）での使用

ソート（ORDER BY）の中で使用することで、複雑なソート順を実現する事が出来ます。

例えば、商品価格を価格の小さい順でソートするとします。ただし、価格が決まっていない商品は0円で登録されており、その場合はソートの最後尾にします。

SELECT code
      ,price
FROM   test_table
ORDER BY CASE
           WHEN price = 0 THEN 1
           ELSE 0
         END
        ,price
;

CODE    PRICE
----- -------
0002     1000
0004     3500
0003    10000
0001    22000
0005        0