CASE 式 と DECODE 関数の違い

標準 SQL の CASE 式 とオラクルの SQL 特有の DECODE 関数は、ほぼ同じ機能をもち、CASE 式DECODE 関数 も短絡評価(※)であるが、この2つは NULL の取り扱い方で大きく異なっている。

(※) 短絡評価(ショートサーキット:short circuit evaluation):条件に該当するものが見つかると後続の条件は評価しない

DECODE 関数

よく知られた非常に便利な変換関数、引数がある値のときに別の値に変換する関数である。
DEOCDE のパラメータ数の上限は 255 であり、当然条件指定はそれ以下(条件と結果で 2 パラメータのため 127 以下)になる。 CASE 式 もほぼ同数の上限がある。

検索 CASE 式 のように評価する式が個々に設定できないため、範囲指定などの条件指定が基本的に行えない。

簡単な不等号レベルであれば

DECODE(SIGN(colA-colB), 1, 'colA>colB', 0, 'colA=colB', -1, 'colA<colB')

として表現できる。応用すれば色々と表現できる。しかし視認性が相当に低下するので CASE 式を使用できる環境においては お勧めできる表記方法ではない。

既に、CASE の劣化版、記述を短くさせるためだけのテクニック?に成り下がってしまったのかと思えば、まだ伝家の宝刀があった。

DECODE の使用例

expr が comparison_expr1 と同値なら ret_expr1 を戻す、comparison_expr2 と同値なら ret_expr2を戻す。それ以外なら default_expr を戻す場合。

DECODE( expr, 
        comparison_expr1, ret_expr1,
        comparison_expr2, ret_expr2,
        default_expr );

CASE式で書き換えるなら

CASE expr
    WHEN comparison_expr1 THEN ret_expr1 
    WHEN comparison_expr2 THEN ret_expr2
    ELSE                   default_expr
END

となる。

今までの使用してきた、ほぼすべての DECODE は CASE 式で表現できる…
しかし、重大な違いは NULL の扱いの違いにある。

単純 CASE 式は NULL との評価はすべて NULL になる。また ret_expr, default_expr に NULL の記述が許されていない、とある。(※)

一方 DECODE は NULL = NULL は True となる。
NULL を直接評価、比較できるのは、NVL シリーズ、COALESCE などの、ごく限られた関数だけである。 以下の書式は DECODE だけに許された記述である(検索 CASE 式 Oracle 9i ならば 書き換え可能である)。

DECODE( nullable_col, 1, 'ONE', 2, 'TWO', NULL, 'EMPTY', 'OTHERS');
DECODE( nullable_col, 1, 'ONE', 2, 'TWO', NULL);

(※) マニュアル上では使用できないと明記されているのだが、実は ret_expr, default_expr については NULL を戻すことができる(注意:単純 CASE の評価式では NULL を直接評価できない)。伝家の宝刀…抜いてはみたけど、あまり役に立たなかったようだ。 ;)

動作も特に問題ないようではあるが、サポートしないという意思表示の可能性がある。DECODE 関数か検索 CASE 式を使用するのが安全。
マニュアル上であえて明記しているということは、標準 SQL に従います、ということなのかもしれない。(Oracle 10g R2 現在)

PL/SQL と DECODE 関数

また DECODE 関数は SELECT 文中で使用が許されているのみで PL/SQL の単独式には使用できない。DUMPBIN_TO_NUMVSIZE なども同様 (SELECT 〜 INTO FROM DUAL にする必要がある)
一方 CASE 式の場合には SQL の式でも PL/SQL の式でも記述が許される。PL/SQL の場合には CASE 文 でも使用できる。
参考: CASE 式 と CASE 文の違い

CREATE OR REPLACE PROCEDURE RIVUS.CASE_OR_DECODE
IS
  vNum  NUMBER;
  vDecode  NUMBER;
  vCase  NUMBER;
BEGIN
  vNum := 2;
  vCase :=  CASE vNum WHEN 1 THEN 100  WHEN 2 THEN 200  ELSE 999 END ;
  -- vDecode :=  DECODE(vNum, 1,100,2,200,999); 
  -- ↑ PLS-00204: 関数または疑似列DECODEはSQL文の中でしか使用できません。
  SELECT DECODE(vNum, 1,100,2,200,999) INTO vDecode FROM DUAL;
  DBMS_OUTPUT.PUT_LINE(vNum || '->' || vCase || '|' || vDecode);
END;
/

この場合 CASE 式は DECODE 関数に比べて SQL の解析やフェッチなどの余計な処理がないので処理時間も短い。

CASE 式

CASE 式は2種類の記法がある。CASE 式とは SQL 文中に記述できる IF や CASE 文のようなものであり、文(statement) ではなく、1つの値を戻す である。

1 つの CASE 式において引数の最大数は 255 に制限される。(ネストは可能)

単純 CASE 式 (simple case expression)

ある式の値を元に分岐するもの

CASE expr
    WHEN comparison_expr1 THEN ret_expr1 
    WHEN comparison_expr2 THEN ret_expr2
    ELSE                    default_expr
END

検索 CASE 式 (searched case expression) Oracle 9i

評価する式が個々に違うもの

CASE 
    WHEN condition1 THEN ret_expr1
    WHEN condition2 THEN ret_expr2
    ELSE              default_expr
END

conditon には、IS NULL など記述も可能

PL/SQL と CASE

PL/SQL および 埋め込みSQL に CASE 式が使用できるのは Oracle 9i 以上 (※)

(※) Oracle 9i より以前の PL/SQL パーサは SQL パーサと異なっている。そのために 埋め込みSQL において一部の SQL 構文がサポートされていない。 (Oracle 9i 以降はパーサが統合されている)



CASE と DECODE 関連事項

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle のライセンスがわからない…
Oracle Direct (ネットで聞いても最後はここで要確認)