NULL を別の値に置換、リストから最初の NULL 以外の値を探す

syntax

NVL ( expr1 , expr2 )
COALESCE ( expr_list ) 標準SQLOracle 9i
return [ 第一引数の型、NULL、または、数値なら優先順位の高い数値型 ]

parameter

expr1NULL を設定可能な式(数値式、文字列式、日付式、etc)
expr2数値式、文字列式、日付式
expr_listカンマで区切った式のリスト(2つ以上の値リスト)

return

  • expr1 が NULL なら 式 expr2 の値、または、expr_list で最初の NULL 以外の値

avail

  • SQL および PL/SQL の両方で使用可能。

NVL 関数の内容

expr1 が NULL なら expr2 の値を戻す。Null Value Logic の略

COALESCE 関数の内容 標準SQLOracle 9i

NVL 関数を一般化した関数で引数に含まれる最初の 「非 NULL値」 を戻す。
COALESCE( expr1,…, exprN ) のように可変長の引数をもつ。
コアレス:空き領域を連結してより大きな空き領域に変換

COALESCE 関数の追加説明と注意事項

COALESCE 関数の引数の数が 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 の優先順位で型変換が行なわれる。

引数1 expr1 を NVARCHAR2(AL16UFT16)、引数2 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 関連

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ 会員制(無料)の公式技術サイト