NULL を別の値に置換、リストから最初の NULL 以外の値を探す
NVL ( expr1 , expr2 )
COALESCE ( expr_list )  、
return [ 第一引数の型、NULL、または、数値なら優先順位の高い数値型 ]
| expr1 | NULL を設定可能な式（数値式、文字列式、日付式、etc） | | expr2 | 数値式、文字列式、日付式 | | expr_list | カンマで区切った式のリスト（2つ以上の値リスト） |
- 式 expr1 が NULL なら 式 expr2 の値、または、expr_list で最初の NULL 以外の値
NVL 関数の内容
式 expr1 が NULL なら expr2 の値を戻す。Null Value Logic の略
COALESCE 関数の内容 、
NVL 関数を一般化した関数で引数に含まれる最初の 「非 NULL値」 を戻す。
COALESCE( expr1,…, exprN ) のように可変長の引数をもつ。
⇒ コアレス：空き領域を連結してより大きな空き領域に変換
COALESCE 関数の追加説明と注意事項
COALESCE 関数の引数の数 (N) が 1 の場合にはエラーを戻す。
N = 2 の場合 … NVL(expr1, expr2) と同じ
N > 2 の場合 … CASE WHEN expr1 IS NOT NULL then COALESCE( expr2,..., exprN) end と自己参照した内容と同じになる。
ちなみに NVL( expr1, NVL( expr2, ( ... ( NVL( exprN-1, exprN)...) の記述の仕方は関数の最深部から評価されていくので無駄な処理が多くなるので控えた方がよい。
COALESCE(col1, '<値なし'>) ⇒ NVL(col1, '<値なし>') と同じ意味
注意点
COALESCE の expr_list の引数の最小および最大数は 2つ以上 256 以下である。
組み込み SQL 関数の可変長引数は、おそらく上限が 255、または、256 以下まで(※)である。DECODE 関数 は上限が 255 個 となる関数の一例。
(※) PL/SQL における引数は上限 65536 であるが SQL関数 では 256 個を超える引数をもてる関数は見たことがない。
NVL の使用例
文字列の場合
SQL> select vc2, NVL(vc2, 'IS NULL'), dump(NVL(vc2, 'IS NULL'), 1016) dump_code
2 from nvl_sample;
VC2 NVL(VC2,'ISNULL') DUMP_CODE
---------------- ---------------------- ------------------------------------------
あいう あいう Typ=1 Len=6 CharacterSet=JA16SJISTILDE: ..
<NULL> IS NULL Typ=1 Len=7 CharacterSet=JA16SJISTILDE: ..
⇒ DUMP 関数
異なるキャラクタセット、数値型による NVL
NVL の引数として異なるキャラクタセットの文字列を使用すると第一引数の型で評価して戻される。
数値型の場合には BINATY_DOUBLE > BINATY_FLOAT > NUMBER の優先順位で型変換が行なわれる。
引数１ expr1 を NVARCHAR2(AL16UFT16)、引数２ expr2 をデータベースキャラクタセットで NVL を実行したサンプル
SQL> select nvc2, NVL(nvc2, 'これはNULLです(SJIS)') nvl_nvchar2,
2 dump(NVL(nvc2, 'これはNULLです(SJIS)'), 1016) dump_code
3 from nvl_sample;
NVC2 NVL_NVCHAR2 DUMP_CODE
---------------- ---------------------- ------------------------------------------
あいう(UTF16) あいう(UTF16) Typ=1 Len=20 CharacterSet=AL16UTF16: 30,42..
<NULL> これはNULLです(SJIS) Typ=1 Len=30 CharacterSet=AL16UTF16: 30,53..
^^^^^^^^^^
SQL> select nvc2, NVL(nvc2, n'これはNULLです(UTF16)') nvl_nvchar2,
2 dump(NVL(nvc2, n'これはNULLです(UTF16)'), 1016) dump_code
3 from nvl_sample;
NVC2 NVL_NVCHAR2 DUMP_CODE
---------------- ---------------------- ------------------------------------------
あいう(UTF16) あいう(UTF16) Typ=1 Len=20 CharacterSet=AL16UTF16: 30,42..
<NULL> これはNULLです(UTF16) Typ=1 Len=32 CharacterSet=AL16UTF16: 30,53..
NUMBER と BINAY_DOUBLE の NVL 例
SQL> select num, dump(NVL(num, 0d)) from nvl_sample2;
NUM DUMP(NVL(NUM,0D))
---------- ----------------------------------------
1 Typ=101 Len=8: 191,240,0,0,0,0,0,0
<NULL> Typ=101 Len=8: 128,0,0,0,0,0,0,0
-- Typ=101 : TO_BINARY_DOUBLE をあらわす
SQL> select d, dump(NVL(d, 0)) from nvl_sample2;
D DUMP(NVL(D,0))
---------- ----------------------------------------
1.0E+000 Typ=101 Len=8: 191,240,0,0,0,0,0,0
<NULL> Typ=101 Len=8: 128,0,0,0,0,0,0,0
COALESCE の使用例
SQL> select COALESCE(null, null, 'NOT NULL', null) from dual;
COALESCE(NULL,NU
----------------
NOT NULL
リストのすべてが NULL になった場合
SQL> select COALESCE(null, null) all_null from dual;
ALL_NULL
--------------------
<NULL>
リストは2つ以上で構成されていること
SQL> select COALESCE(null) from dual;
*
行1でエラーが発生しました。:
ORA-00938: 関数の引数が不足しています。
SQL 関数 - NVL,COALESCE 関連
|
