OracleでNULLと''(空文字)の違いについて考える。

Oracle9iだけNULLと''(空文字)の定義が変わっています。
8iから9iになった時に一度検証した事が有るんですが、それを10gでやってみたら仕様が元に戻っていて驚いた。

検証プログラム

DECLARE
	a VARCHAR2(8) := NULL;
	b CHAR(8) := NULL;
BEGIN
	a := '';
	b := '';

	IF a IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('VARCHAR2への空文字代入はNULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('VARCHAR2への空文字代入は空文字');
	END IF;

	IF b IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('CHARへの空文字代入はNULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('CHARへの空文字代入は空文字');
		DBMS_OUTPUT.PUT_LINE('b=*' || b || '*');
	END IF;

	a := REPLACE(' ',' ','');
	b := REPLACE(' ',' ','');

	IF a IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('VARCHAR2へのREPLACE代入はNULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('VARCHAR2へのREPLACE代入は空文字');
	END IF;

	IF b IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('CHARへのREPLACE代入はNULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('CHARへのREPLACE代入は空文字');
		DBMS_OUTPUT.PUT_LINE('b=*' || b || '*');
	END IF;

    a := TRIM('');
    b := TRIM('');

	IF a IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('VARCHAR2へのTRIM代入はNULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('VARCHAR2へのTRIM代入は空文字');
	END IF;

	IF b IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('CHARへのTRIM代入はNULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('CHARへのTRIM代入は空文字');
		DBMS_OUTPUT.PUT_LINE('b=*' || b || '*');
	END IF;

    a := NVL(NULL,'');
    b := NVL(NULL,'');

	IF a IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('VARCHAR2へのNVL代入はNULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('VARCHAR2へのNVL代入は空文字');
	END IF;

	IF b IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('CHARへのNVL代入はNULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('CHARへのNVL代入は空文字');
		DBMS_OUTPUT.PUT_LINE('b=*' || b || '*');
	END IF;

END;

これを各バージョンのOracleで動かすと

Oracle8i(8.1.6.0.0 HPUX)

VARCHAR2への空文字代入はNULL
CHARへの空文字代入は空文字
b=*        *
VARCHAR2へのREPLACE代入はNULL
CHARへのREPLACE代入はNULL
VARCHAR2へのTRIM代入はNULL
CHARへのTRIM代入は空文字
b=*        *
VARCHAR2へのNVL代入はNULL
CHARへのNVL代入は空文字
b=*        *

Oracle9i (9.2.0 Linux)

VARCHAR2への空文字代入はNULL
CHARへの空文字代入は空文字  
b=*        *                
VARCHAR2へのREPLACE代入はNULL
CHARへのREPLACE代入は空文字  
b=*        *                 
VARCHAR2へのTRIM代入はNULL   
CHARへのTRIM代入は空文字     
b=*        *                 
VARCHAR2へのNVL代入はNULL    
CHARへのNVL代入は空文字      
b=*        *                 

ここだけ動作が変わっておりCHARへのREPLACE代入が空文字になっている。

Oracle10g (10.2.0.1.0 WINXP)

VARCHAR2への空文字代入はNULL
CHARへの空文字代入は空文字
b=*        *
VARCHAR2へのREPLACE代入はNULL
CHARへのREPLACE代入はNULL
VARCHAR2へのTRIM代入はNULL
CHARへのTRIM代入は空文字
b=*        *
VARCHAR2へのNVL代入はNULL
CHARへのNVL代入は空文字
b=*        *

対策

REPLACEしたりTRIMした変数をCHAR型に突っ込むときぐらいしか問題がはかっくしませんが、NULLに変換したい場合には明示的にNULLをセットするようにしましょう。