ORACLE CASE式かDECODE関数か?SQLで条件分岐する方法 ホームページ制作 | 墨田区

ORACLE CASE式かDECODE関数か?SQLで条件分岐する方法

LINEで送る
Pocket

ORACLE SQLで条件式を使うならCASE式(CASE WHEN)かDECODE関数を使用します。
ORACLE CASE式かDECODE関数か?SQLで条件分岐する方法

私がこの業界に入りたての頃、諸先輩方から「ORACLE で条件式なら DECODE やぞ!」と教わりました。そのため今でも DECODE 関数を使っています。他人の作った SQL を見ても、DECODE 関数が多用されていますし、特に歴史ある基幹システムなどには多く見受けられますね。

ただ、以前から「他のデータベースとの互換性がないこと」、「分岐の数が増えるとソースが読みにくくなること」、「サブクエリが使えないこと」に疑問がありました。

今回、あるプロジェクトで ORACLE 用に作られた SQL を別のデータベースでも使うことになりましたが、DECODE 関数を多用していたためにスムーズに移行できず、ちょいと苦労しましたねー^^;

そんな訳で ORACLEのCASE式とDECODE関数 について詳しく調べたので、備忘録的にまとめておきます。




【PR】マジか?!「アレ」してるLINEスタンプっていったい・・・


環境

検証環境は ORACLE 11g です。インストールは下記を参考に。
Oracle 11g を Windows に インストールしよう。

今回使ったテーブルはこちらです。
CREATE TABLE EMPLOYEES
(
  ID NVARCHAR2(255) NOT NULL,
  NAME NVARCHAR2(255) NOT NULL,
  EMAIL NVARCHAR2(255),
  SEX NVARCHAR2(1),
  SALARY NUMBER,
  CREATED_AT DATE NOT NULL,
  UPDATED_AT DATE,
  CONSTRAINT PK_EMPLOYEES PRIMARY KEY (ID) USING INDEX
);

今回使ったサンプルデータはこちら。
INSERT INTO EMPLOYEES VALUES('001','hoge','hoge@example.com','1','30',to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('002','piyo','piyo@example.com','2','50',to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('003','fuga','fuga@example.com',null,null,to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('004','foo','foo@example.com','2','65',to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('005','bar','bar@example.com','1','25',to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('006','baz','baz@example.com','2','70',to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('007','saka','saka@example.com',null,'1000',to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/10/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));

ORACLE CASE式とDECODE関数の違い

どちらも条件式といった意味では同じなので、条件に該当するものが見つかると後続の条件は評価しません。CASE 式と DECODE 関数の大きな違いは NULL の取り扱い方です。

まずは DECODE関数の使い方から見てみましょう。
説明
DECODE ( 式 , 比較値 , 値 [ , 省略値 ] )式と比較値を順番に比較していき、最初に一致した比較値と対になった値を戻す。全ての比較値と一致しなかった場合は省略値(もしくはNULL)を戻す。

DECODE は NULL を直接評価できるため NULL = NULL は True となります。

例えばこんな使い方ができます。
DECODE(sex, '1', '男', '2', '女', null, '不明')

次に CASE 式ですが、これには2種類あります。
説明
単純 CASE 式 (simple case expression) ある式の値を元に分岐するもの。評価される回数は1回。
検索 CASE 式 (searched case expression)評価する式が個々に違うもの。評価される回数は複数回または1回。IS NULL や IN 、LIKE など記述も可能。

単純 CASE 式 (simple case expression) の具体的な使い方はこうです。
CASE sex
  WHEN n'1' THEN '男'
  WHEN n'2' THEN '女'
  ELSE '不明'
END AS conv_sex

ここで「n’1’」って書いているのは、型が NVARCHAR2 だからです。条件判定の ‘1’ は、暗黙的に CHAR となります。明示的 CAST しないと、「ORA-12704:キャラクタ・セットが一致しません。」のエラーが出ます。

DECODE だと出ないんですよねー。DECODE に慣れているとハマりますんでご注意を。

また、単純 CASE 式では、NULL との評価はすべて NULL になります。つまり WHEN NULL THEN ‘不明’ とか書いても NULL になる・・・つまり評価されないということですね。

上記にあるように、DECODE であれば NULL の評価はできます。単純な条件分岐であれば、やはり DECODE が最強といったところでしょうか。

次に、検索 CASE 式 (searched case expression)の具体的な使い方です。
CASE
  WHEN salary <= 30 THEN '少額'
  WHEN salary <= 50 THEN '普通'
  WHEN salary <= 80 THEN '高額'
  WHEN salary IS NULL THEN '未定'
  ELSE '超額'
END AS conv_salary

DECODE 関数では使用できなかった比較条件や演算子が使えます。

これは便利ですねー。

一昔前は FUNCTION を作って SQL に組み込むなどのことをやってた気が・・・。懐かしい話です^^

CASE式を実行してみた

せっかくなんで、上で書いた CASE 式を実行してみましょう。
SELECT
name,
sex,
CASE sex
  WHEN n'1' THEN '男'
  WHEN n'2' THEN '女'
  ELSE '不明'
END AS conv_sex,
salary,
CASE
 WHEN salary <= 30 THEN '少額'
 WHEN salary <= 50 THEN '普通'
 WHEN salary <= 80 THEN '高額'
 WHEN salary IS NULL THEN '未定'
 ELSE '超額'
END AS conv_salary
FROM EMPLOYEES
ORDER BY name

結果はこなりました。
NAMESEXCONV_SEXSALARYCONV_SALARY
bar125少額
baz270高額
foo265高額
fugaNULL不明NULL未定
hoge130少額
piyo250普通
sakaNULL不明1000超額

まとめ

今までの使用してきた DECODE 関数は、ほぼほぼ CASE 式に置き換えられます。ただ、NULL の扱いは大きく違うので注意が必要です。

CASE 式は SQL でできることの幅を広げてくれますね。

DECODE 関数よりもソースがスマートに書けますし、何より可読性がよい!

そんなわけで、これからはできるだけ CASE 式を使っていこうと心に誓いました^^

皆さんも試してみてください。

おつかれさまでした。

LINEで送る
Pocket

この記事がお役に立ちましたら シェア をお願いいたします。

コメントを残す

コメント(必須)

お名前 (必須)
メールアドレス
(アドレスは公開されません)

Trackback URL