条件制御によって特定の値および値域を別の値に変換
DECODE ( expr , search_and_result_list [ , default ] )
return [ 第3引数（search_and_result_list の最初の result 式） のデータ型 ]
| expr | NULL を設定可能な式（数値式、文字列式、日付式、etc） |
| search_and_result_list | 検索式と結果式リスト [ search , result ] |
| default | 検索式に一致しない場合の結果式 | default NULL |
- 式 expr と一致した 検索式 search をもつ result を戻す。
- 式 expr が 検索式と結果式リスト に含まれない場合には default_expr を戻す。
- SQL、埋め込みSQL で使用可能、PL/SQL に組み込まれていない。（Oracle 10g R2 時点）
検索式と結果式リスト
検索式と結果式のリストは [ search , result ] の形式で１セット以上かつ 126、または、127 セット以下の範囲で 検索式 と その結果式 を指定する。
評価はすべてのリストにおいて等号のみで 比較演算子 は選択できない。
DECODE 関数の内容
式 expr が search_and_result_list の第1番目の要素の search1 と同値なら result1 を戻す。search2 と同値なら result2 を戻す。これを 検索式と結果式リスト [ search , result ] のある分だけ繰り返す。最後まで一致する要素が見つからない場合にはオプション指定の 式 default の値を戻す。
注意
DECODE の引数は最大 255 個までとなる。もしデフォルト値を使用する場合には 式 expr と default の要素を 255 から差し引いて 253 個が 検索式と結果式のリストの最大数となる。結果 253/2 ⇒ 126 個までの 検索式 と比較して置換することが可能。
デフォルトを指定しない場合は 127 リストを指定することが可能。そのときの default は NULL となる。
戻り値のデータタイプの選択
戻り値の データ型 は数値型などによる優先順なども無く、必ず 第 3 パラメータのデータタイプが採用される。そのためにデータによって実行時エラーになる可能性がある。TIMESTAMP WITH TIMEZONE 
などの新しいデータ型を使用すると 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 関連
■
■
■
印刷表示