条件制御によって特定の値および値域を別の値に変換

syntax

DECODE ( expr , search_and_result_list [ , default ] )
return [ 第3引数(search_and_result_list の最初の result 式) のデータ型 ]

parameter

exprNULL を設定可能な式(数値式、文字列式、日付式、etc)
search_and_result_list検索式と結果式リスト [ search , result ]
default検索式に一致しない場合の結果式default NULL

return

  • expr と一致した 検索式 search をもつ result を戻す。
  • expr が 検索式と結果式リスト に含まれない場合には default_expr を戻す。

avail

  • SQL、埋め込みSQL で使用可能、PL/SQL に組み込まれていない。(Oracle 10g R2 時点)

検索式と結果式リスト

検索式と結果式のリストは [ search , result ] の形式で1セット以上かつ 126、または、127 セット以下の範囲で 検索式 と その結果式 を指定する。
評価はすべてのリストにおいて等号のみで 比較演算子 は選択できない。

DECODE 関数の内容

exprsearch_and_result_list の第1番目の要素の search1 と同値なら result1 を戻す。search2 と同値なら result2 を戻す。これを 検索式と結果式リスト [ search , result ] のある分だけ繰り返す。最後まで一致する要素が見つからない場合にはオプション指定の 式 default の値を戻す。

注意

DECODE の引数は最大 255 個までとなる。もしデフォルト値を使用する場合には 式 exprdefault の要素を 255 から差し引いて 253 個が 検索式と結果式のリストの最大数となる。結果 253/2 ⇒ 126 個までの 検索式 と比較して置換することが可能。
デフォルトを指定しない場合は 127 リストを指定することが可能。そのときの default は NULL となる。

戻り値のデータタイプの選択

戻り値の データ型 は数値型などによる優先順なども無く、必ず 第 3 パラメータのデータタイプが採用される。そのためにデータによって実行時エラーになる可能性がある。TIMESTAMP WITH TIMEZONE Oracle 9i などの新しいデータ型を使用すると CASE 式と同様に型チェックが行なわれているようである。下位互換性でわざとエラーでも無視しているのか?

select dump(DECODE(2, 1, 'ONE', 2, 2)) "STRING" from dual;
⇒ 2 ではなく '2' が戻される
STRING
------------------------------
Typ=1 Len=1: 50

Typ=1 は VARCHAR2 をあらわす タイプ番号 / DUMP 関数

select DECODE(num, 1, SYSDATE, 2, 'TWO', 3, NULL, 'UNKNOWN') from xxx;

num = 1 のとき ⇒ SYSDATE    (DATE 型)
num = 2 のとき ⇒ 'TWO'      (DATE 型...変換不可能で実行時エラー)
num = 3 のとき ⇒ NULL       (DATE 型 : OK)
デフォルト     ⇒ 'UNKNOWN' (DATE 型...変換不可能で実行時エラー)

DECODE の使用例

ローマ数字への変換の一部を DECODE で表現してチェックする。
DECODE 関数では NULL を 検索式 search として使用することができる点がユニークな点である。(TO_CHAR の ローマ数字変換 では、なにげに桁そろえ処理が施されている)

SQL> select num, to_char(num, 'RN'),
  2   DECODE(num, 1, 'I', 10, 'X', 20, 'XX', 30, 'XXX', 40, 'XL',
  3               NULL, '****',
  4               'UNDEFINED') Roman_Num
  5  from decode_sample1;
 
       NUM TO_CHAR(NUM,'RN')  ROMAN_NUM
---------- ------------------ ------------------
         1               I    I
        10               X    X
        20              XX    XX
        21             XXI    UNDEFINED
        30             XXX    XXX
        40              XL    XL
<NULL>     <NULL>             ****

DECODE で数値の等価、大小関係を評価するには SIGN 関数を使用する。

SQL> select num, DECODE(sign(num-10), -1, '10未満', +1, '10以上', 0, '10') X,
  2              DECODE(sign(num-20), -1, '20未満', +1, '20以上', 0, '20') XX,
  3              DECODE(sign(num-30), -1, '30未満', +1, '30以上', 0, '30') XXX,
  4              DECODE(sign(num-40), -1, '40未満', +1, '40以上', 0, '40') XL
  5  from decode_sample1;
 
       NUM X        XX       XXX      XL
---------- -------- -------- -------- --------
         1 10未満   20未満   30未満   40未満
        10 10       20未満   30未満   40未満
        20 10以上   20       30未満   40未満
        21 10以上   20以上   30未満   40未満
        30 10以上   20以上   30       40未満
        40 10以上   20以上   30以上   40
<NULL>     <NULL>   <NULL>   <NULL>   <NULL>

DECODE で文字列の大小関係の一方を評価するには GREATEST、LEAST を使用する。DECODE をネストすると、さらに調べることが可能。

SQL> select str, DECODE(greatest('AAA',str), 'AAA', 'AAA以下') AAA,
  2              DECODE(greatest('BBB',str), 'BBB', 'BBB以下') BBB,
  3              DECODE(least('CCC',str), 'CCC', 'CCC以上') CCC,
  4              DECODE(least('DDD',str), 'DDD', 'DDD以上') DDD
  5  from decode_sample2;
 
STR      AAA      BBB      CCC      DDD
-------- -------- -------- -------- --------
AAA      AAA以下  BBB以下  <NULL>   <NULL>
AAB      <NULL>   BBB以下  <NULL>   <NULL>
BBB      <NULL>   BBB以下  <NULL>   <NULL>
CCC      <NULL>   <NULL>   CCC以上  <NULL>
<NULL>   <NULL>   <NULL>   <NULL>   <NULL>
DDD      <NULL>   <NULL>   CCC以上  DDD以上
EEE      <NULL>   <NULL>   CCC以上  DDD以上

DECODE で関数を駆使して条件制御することは CASE 式の存在しない Oracle8 時代までのテクニックなので 単純CASE式、検索CASE式 が完備されている Oracle 9i 以降であれば、おすすめできない手法である。
見た目にもやさしい 単純CASE式 や 検索CASE 式 を使用するのが望ましい。



SQL 関数 - DECODE 関連

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ オラクルサポートセンター