Web
Analytics

社畜ゲートウェイ

京成・京急を中心に取り上げる阪急ファンのブログです。日本一遅い速報を届けます。

MENU

【スポンサーリンク】

【Oracle】NVLの使い方と仕組み

【スポンサーリンク】

OracleのNVLの使い方です。

NVLとは?

Oracleの独自関数であるNVLは「Null Value Logic」という名称の略です。簡単に言うと、「NULLの時に指定した文字列に変換する」という意味です。ただし、実際のカラムの値を変換しているわけではなく、変換した結果を取得しています。

 

NVLの使い方

NVLの使い方は簡単です。

NVL(文字列,変換文字列)

たったこれだけです。判定してくれるのは文字列だけです。数値を文字列にしたい場合は、第1引数にTO_CHARを使用して下さい。

NVL(TO_CHAR(文字列),変換文字列)

こんな感じ。これだけでは少ないと思いますので、使用例を紹介します。

 

フルーツのデータが入っているテーブルと、フルーツの色が入っているテーブル(色マスタ)を用意しました。データの内容はこんな感じです。

f:id:neko_britannia:20201003175106j:plain

f:id:neko_britannia:20201003175129j:plain

TAB_PRODUCT_FRUITにフルーツの情報、MST_FRUIT_COLORにフルーツの色を保持しています。TAB_PRODUCT_FRUITにMST_FRUIT_COLORをCOLOR_CODEカラムで外部結合して、フルーツの色の名前を取得します。SQLはこの様に書きました。

SELECT TAB.NAME AS FRUIT_NAME
      ,MST.COLOR_NAME AS FRUIT_COLOR_NAME
  FROM TAB_PRODUCT_FRUIT TAB
      ,MST_FRUIT_COLOR MST
 WHERE TAB.FRUIT_COLOR_CD = MST.COLOR_CODE(+)

では、このSQLを実行して、フルーツの色を取得してみましょう。

f:id:neko_britannia:20201003175823j:plain

ちゃんと取得できていますね。しかし、「洋ナシ」の色は取得できていません。取得結果がNULLになっています。何故なら、洋ナシのFRUIT_COLOR_CDは「05」で、MST_FRUIT_COLOR上のCOLOR_CODEに「05」が存在しないからです。もしNULLだった場合に、「色が存在しません」みたいな表示にしたいと思います。そこでNVLを使用します。

SELECT TAB.NAME AS FRUIT_NAME
      ,NVL(MST.COLOR_NAME,'色が存在しません') AS FRUIT_COLOR_NAME
  FROM TAB_PRODUCT_FRUIT TAB
      ,MST_FRUIT_COLOR MST
 WHERE TAB.FRUIT_COLOR_CD = MST.COLOR_CODE(+)

「MST.COLOR_NAME」だったところを、「NVL(MST.COLOR_NAME,'色が存在しません')」としました。では再度実行してみます。

f:id:neko_britannia:20201003180522j:plain

「洋ナシ」の色はMST_FRUIT_COLOR上に存在しないので、取得結果が「色が存在しません」になっています。

 

NVLの仕組み

NVLの仕組みとしては、次の様に考えて貰えばわかりやすいかと思います。

CASE
 WHEN VAL_1 IS NULL THEN 'NULLだよ'
 WHEN VAL_1 IS NOT NULL THEN VAL_1
END

とても簡単なCASE文です。VAL_1というカラムがあったとします。上記のCASE文の意味としては、

  1. VAL_1の値がNULLの場合は「NULL」だよという文字列を使う
  2. VAL_1の値がNULLじゃない場合は、VAL_1の値をそのまま使う

ということです。ちょっと強引ですが、次の様なSQLを用意してみました。

--NVL再現クエリ①
SELECT
 CASE
  WHEN VAL_1 IS NULL THEN 'NULLだよ'
  WHEN VAL_1 IS NOT NULL THEN VAL_1
 END AS RESULT
FROM
 (SELECT NULL AS VAL_1
    FROM DUAL) S01
;
--NVL再現クエリ②
SELECT
 CASE
  WHEN VAL_1 IS NULL THEN 'NULLだよ'
  WHEN VAL_1 IS NOT NULL THEN VAL_1
 END AS RESULT
FROM
 (SELECT '値があるよ' AS VAL_1
    FROM DUAL) S01
;

FROM句にサブクエリS01を用意し、VAL_1カラムの値を、NVL再現クエリ①は強制的にNULL、NVL再現クエリ②は文字列にしています。これをそれぞれ実行してみてください。下図の様に、UNION ALLで一度に実行しても大丈夫です。

f:id:neko_britannia:20201003181135j:plain

実行結果はこんな感じです。それぞれ「NULLだよ」「値があるよ」と出てきたと思います。クエリ①の方はVAL_1がNULLだったので、「WHEN VAL_1 IS NULL THEN」に合致して「NULLだよ」という結果になっています。

クエリ②はVAL_1の値に「値があるよ」という文字列を強制的に与えているので、「WHEN VAL_1 IS NOT NULL THEN」に合致して、「値があるよ」という結果になっています。これがNVLの仕組みです。

 

参考サイト

docs.oracle.com