NULL的資料型別（二）

NULL比較特殊，任何資料型別的空都用NULL來表示，因此NULL可以是任何資料型別。所以在一般情況下，NULL也是不會體現出具體的型別的。

不過有時候還是可以從一些特殊的情況判斷出Oracle給NULL確定的預設型別。

比如newkid給出的CREATE VIEW的例子：

SQL> CREATE VIEW V_NULL AS
  2  SELECT NULL N 
  3  FROM DUAL;

檢視已建立。

SQL> DESC V_NULL
 名稱                                                    是否為空? 型別
 ------------------------------------------------------- -------- --------
 N                                                                VARCHAR2

和我前一篇得出的結果是一樣的，Oracle在某些情況下，會把NULL當作VARCHAR2型別來處理。

而且同時還發現幾個有意思的現象：

SQL> CREATE OR REPLACE VIEW V_TEST AS
  2  SELECT SYSDATE N FROM DUAL
  3  UNION ALL
  4  SELECT NULL FROM DUAL;

檢視已建立。

SQL> DESC V_TEST
 名稱                                                    是否為空? 型別
 ------------------------------------------------------- -------- --------------
 N                                                                DATE

SQL> CREATE OR REPLACE VIEW V_TEST AS
  2  SELECT SYSDATE N FROM DUAL
  3  UNION ALL
  4  SELECT '' FROM DUAL;
SELECT SYSDATE N FROM DUAL
       *
第 2 行出現錯誤:
ORA-01790: 表示式必須具有與對應表示式相同的資料型別

Oracle會檢查UNION ALL包含的兩個查詢結果的資料型別是否匹配，如果不匹配就會出現上面的錯誤。其實在以前的文章中已經提到過，空字串‘‘是NULL的字元型別表現形式，具體的描述可以參考：http://yangtingkun.itpub.net/post/468/245697

由於NULL可以轉換為任意的型別，因此Oracle檢查到NULL後，會根據另一個查詢的型別來確定最終的型別，但是空字串‘‘已經表現出了字元型別，因此和另一個查詢的DATE型別不相符因此報錯。

重點是下面的結果：

SQL> CREATE OR REPLACE VIEW V_TEST AS
  2  SELECT NULL N FROM DUAL
  3  UNION ALL
  4  SELECT '' FROM DUAL;

檢視已建立。

SQL> DESC V_TEST
 名稱                                                    是否為空? 型別
 ------------------------------------------------------- -------- ----------------
 N                                                                VARCHAR2

SQL> CREATE OR REPLACE VIEW V_TEST AS
  2  SELECT '' N FROM DUAL
  3  UNION ALL
  4  SELECT NULL FROM DUAL;

檢視已建立。

SQL> DESC V_TEST
 名稱                                                    是否為空? 型別
 ------------------------------------------------------- -------- ----------------
 N                                                                CHAR

兩個SQL的區別只是UNION ALL兩個查詢的順序，而恰恰就是這個順序決定了最終檢視中欄位的型別。從這一個SQL可以得出三個推論，一、UNION ALL查詢由前面的SQL作為型別的主導；二、空字串’’的預設型別是CHAR；三、NULL的預設型別是VARCHAR2。

除了上面的結果外，TO_NUMBER(NULL)也比較有意思：

SQL> CREATE OR REPLACE VIEW V_TEST AS 
  2  SELECT TO_NUMBER(NULL) N FROM DUAL
  3  UNION ALL
  4  SELECT TO_CHAR(NULL) FROM DUAL;

檢視已建立。

SQL> DESC V_TEST
 名稱                                                    是否為空? 型別
 ------------------------------------------------------- -------- ---------------------
 N                                                                VARCHAR2

SQL> CREATE OR REPLACE VIEW V_TEST AS 
  2  SELECT TO_NUMBER(NULL) N FROM DUAL
  3  UNION ALL
  4  SELECT TO_DATE(NULL) FROM DUAL;

檢視已建立。

SQL> DESC V_TEST
 名稱                                                    是否為空? 型別
 ------------------------------------------------------- -------- ---------------------
 N                                                                DATE

SQL> CREATE OR REPLACE VIEW V_TEST AS 
  2  SELECT TO_CHAR(NULL) N FROM DUAL
  3  UNION ALL
  4  SELECT TO_NUMBER(NULL) FROM DUAL;
SELECT TO_CHAR(NULL) N FROM DUAL
       *
第 2 行出現錯誤:
ORA-01790: 表示式必須具有與對應表示式相同的資料型別

SQL> CREATE OR REPLACE VIEW V_TEST AS 
  2  SELECT TO_CHAR(NULL) N FROM DUAL
  3  UNION ALL
  4  SELECT TO_DATE(NULL) FROM DUAL;
SELECT TO_CHAR(NULL) N FROM DUAL
       *
第 2 行出現錯誤:
ORA-01790: 表示式必須具有與對應表示式相同的資料型別

SQL> CREATE OR REPLACE VIEW V_TEST AS 
  2  SELECT TO_DATE(NULL) N FROM DUAL
  3  UNION ALL
  4  SELECT TO_NUMBER(NULL) FROM DUAL;
SELECT TO_DATE(NULL) N FROM DUAL
       *
第 2 行出現錯誤:
ORA-01790: 表示式必須具有與對應表示式相同的資料型別

SQL> CREATE OR REPLACE VIEW V_TEST AS 
  2  SELECT TO_DATE(NULL) N FROM DUAL
  3  UNION ALL
  4  SELECT TO_CHAR(NULL) FROM DUAL;
SELECT TO_DATE(NULL) N FROM DUAL
       *
第 2 行出現錯誤:
ORA-01790: 表示式必須具有與對應表示式相同的資料型別

似乎在UNION ALL第一個查詢中指定的TO_NUMBER沒有起到確定NUMBER型別的作用，而如果緩衝CAST則不會出現這種情況：

SQL> CREATE OR REPLACE VIEW V_TEST AS 
  2  SELECT CAST(NULL AS NUMBER) N FROM DUAL
  3  UNION ALL
  4  SELECT TO_CHAR(NULL) FROM DUAL;
SELECT CAST(NULL AS NUMBER) N FROM DUAL
       *
第 2 行出現錯誤:
ORA-01790: 表示式必須具有與對應表示式相同的資料型別

但是要是TO_NUMBER一點作用都沒有也不盡然：

SQL> CREATE OR REPLACE VIEW V_TEST AS 
  2  SELECT TO_NUMBER(NULL) N FROM DUAL
  3  UNION ALL
  4  SELECT NULL FROM DUAL;

檢視已建立。

SQL> DESC V_TEST
 名稱                                                    是否為空? 型別
 ------------------------------------------------------- -------- -------------------
 N                                                                NUMBER

總之，NULL總會給我們帶來一些意想不到東西。