はじめに
普段の業務ではNVL関数しか使ったことがありませんが、よく似た名前のNVL2関数について調べる機会があったので、NVL関数と比較しながらまとめてみました。
NVL関数
NVL関数の構文
NVL(expr1,expr2)
- expr1がNULLの場合はexpr2を返し、expr1がNULLでない場合はexpr1を返します。
- expr1とexpr2のデータ型が異なる場合、一方のデータ型が他方のデータ型に暗黙的に変換されます。
- データ型を暗黙的に変換できない場合、エラーが返されます。
NVL関数のサンプルコード（正常に動作するもの）
サンプルコード
-- NVL関数を使って、歩合給がnullの場合に「0」を返している。
SELECT
T1.EMPNO AS 社員番号,
T1.ENAME AS 社員名,
T1.JOB AS 職種,
NVL(T1.COMM, 0) AS 歩合給
FROM
SCOTT.EMP T1,
SCOTT.DEPT T2
WHERE
T1.DEPTNO=T2.DEPTNO
AND T2.DNAME='SALES';
- 上記のサンプルコードの実行結果は以下の通りです。
| 社員番号 | 社員名 | 職種 | 歩合給 |
|---|---|---|---|
| 7521 | WARD | SALESMAN | 500 |
| 7654 | MARTIN | SALESMAN | 1400 |
| 7844 | TURNER | SALESMAN | 0 |
| 7900 | JAMES | CLERK | -1 |
| 7499 | ALLEN | SALESMAN | 300 |
| 7698 | BLAKE | MANAGER | -1 |
NVL関数のサンプルコード（エラーになるもの）
サンプルコード
-- expr1がNUMBER型、expr2が文字リテラル（varchar2型？）なので、暗黙的にデータ型を変換できずにエラーが起きる。
SELECT
T1.EMPNO AS 社員番号,
T1.ENAME AS 社員名,
T1.JOB AS 職種,
NVL(T1.COMM, 'なし') AS 歩合給
FROM
SCOTT.EMP T1,
SCOTT.DEPT T2
WHERE
T1.DEPTNO=T2.DEPTNO
AND T2.DNAME='SALES';
- 上記のサンプルコードの実行結果は以下の通りです。
- 使われているデータ型によってエラーコードは異なると思います。
ORA-01722: invalid number
NVL2関数
NVL2関数の構文
NVL(expr1,expr2,expr3)
- expr1がnullか否かで、異なる値を返すことができます。
- expr1がnullでない時にはexpr2を返します。
- expr1がnullの時にはexpr3を返します。
- expr1は任意のデータ型を指定できますが、expr2とexpr3はLONG型以外のデータ型を指定できます。
- expr2とexpr3のデータ型が異なる場合、一方のデータ型が他方のデータ型に暗黙的に変換されます。
- データ型を暗黙的に変換できない場合、エラーが返されます。
NVL2関数のサンプルコード（正常に動作するもの）
サンプルコード
-- NVL2関数を使って、歩合給がnullの場合に「なし」、nullでない場合に「あり」を返している。
SELECT
T1.EMPNO AS 社員番号,
T1.ENAME AS 社員名,
T1.JOB AS 職種,
NVL2(T1.COMM, 'あり', 'なし') AS 歩合給
FROM
SCOTT.EMP T1,
SCOTT.DEPT T2
WHERE
T1.DEPTNO=T2.DEPTNO
AND T2.DNAME='SALES';
- 上記のサンプルコードの実行結果は以下の通りです。
| 社員番号 | 社員名 | 職種 | 歩合給 |
|---|---|---|---|
| 7521 | WARD | SALESMAN | あり |
| 7654 | MARTIN | SALESMAN | あり |
| 7844 | TURNER | SALESMAN | あり |
| 7900 | JAMES | CLERK | なし |
| 7499 | ALLEN | SALESMAN | あり |
| 7698 | BLAKE | MANAGER | なし |
NVL2関数のサンプルコード（正常に動作するもの - その2）
- こちらの記事を見ると、**「NVL2はSQL文では使用できますが、ストアド（PL/SQL）では使用できないのでご注意ください。（PLS-00201エラーが発生します。）」**と書かれていますが...
- 以下のストアドプロシージャでは、NVL2関数を含みますが正常に実行できました。
- ストアドプロシージャ内であっても、SELECT/UPDATE/DELETEなどのSQL文中であれば正常に動作するのかもしれません。
- NVL2関数をストアド（プロシージャ/ファンクション）で使いたい時は、代わりにCASE文を使うのが一般的なようです。
サンプルコード
-- NVL2関数がストアドプロシージャで使えるかどうかを確認するためのサンプルコード。
CREATE OR REPLACE PROCEDURE print_comm
-- 宣言部
IS
commission VARCHAR2(10);
-- 処理部
BEGIN
SELECT
NVL2(T1.COMM, 'あり', 'なし')
INTO
commission
FROM
SCOTT.EMP T1
WHERE
T1.ENAME='BLAKE';
DBMS_OUTPUT.PUT_LINE(commission);
END;