Oracle的NULL代表的含義是不確定，那么不確定的東西也會有確定的數據類型嗎？或者換個說法，NULL在Oracle中的默認數據類型是什么，下面就來探討這個問題。 首先公布答案， NULL的默認類型是字符類型，具體是VARCHAR2還是CHAR，這個并不清楚，不過我個人懷疑

Oracle的NULL代表的含義是不確定，那么不確定的東西也會有確定的數據類型嗎？或者換個說法，NULL在Oracle中的默認數據類型是什么，下面就來探討這個問題。

首先公布答案，NULL的默認類型是字符類型，具體是VARCHAR2還是CHAR，這個并不清楚，不過我個人懷疑是VARCHAR2的可能性更大一些。

我們知道一個字段不管是何種類型的，都可以插入NULL值，也就是說，NULL可以隨意的轉換為任意的類型。

而且，絕大部分的函數輸入值為NULL，返回的結果也為NULL，這就阻止了我們通過函數的返回結果判斷NULL的類型的企圖。我們最常用來分析數據的DUMP函數，這回也實效了：

SQL> SELECT DUMP(NULL) FROM DUAL;

DUMP
----
NULL

而且試圖通過CREATE TABLE AS來判定NULL的類型也是不可能的：

SQL> CREATE TABLE T AS SELECT TNAME, NULL COL1 FROM TAB;
CREATE TABLE T AS SELECT TNAME, NULL COL1 FROM TAB
*
ERROR 位于第 1 行:
ORA-01723: 不允許長度為 0 的列

可能有人會產生疑問，既然各種方法的行不通，你是怎么得到NULL的默認類型的？也許還有人會想，既然NULL可以隱式的轉化為任意的類型，討論NULL的默認類型是否有意義呢？

下面就是我發現NULL的數據類型的例子，同時說明了如果不注意NULL的數據類型可能會出現的問題。

由于原始的SQL過于復雜，我這里給出一個簡化的例子。

SQL> create table t (id number);

表已創建。

SQL> insert into t values (1);

已創建 1 行。

SQL> insert into t values (8);

已創建 1 行。

SQL> insert into t values (0);

已創建 1 行。

SQL> insert into t values (15);

已創建 1 行。

SQL> commit;

提交完成。

需要按照T中的ID的升序顯示數據，SQL如下：

SQL> select * from t order by id;

ID
----------
0
1
8
15

需求還有一點點小的要求，對于0值這個比較特殊的值，在所有非0值的后面顯示。當然實現的方法比較多，比如使用UNION ALL將非0值和0值分開，或者將0值轉換為一個很大的數值。

由于ID的最大值不確定，且考慮使用一個簡單的SQL完成，我選擇了在排序的時候將0值轉化為NULL的方法，這樣利用排序時NULL最大的原理，得到我希望的結果。

SQL如下：

SQL> select * from t order by decode(id, 0, null, id);

ID
----------
1
15
8
0

0值確實如我所愿排在了最后，但是結果怎么“不對”了！

SQL> select decode(id, 0, null, id) from t;

DECODE(ID,0,NULL,ID)
----------------------------------------
1
8

15

看看DECODE函數的結果，這回明白了，原來DECODE的結果變為了字符類型。字符類型結果在SQLPLUS顯示左對齊，而數值類型是右對齊。

在DECODE函數中，輸入的4個參數中兩個ID和0都是NUMBER類型，只有NULL這一個輸入值類型不確定，莫非是由于NULL的類型是字符類型？

猜測只是猜測，還需要確切的證據證明這一點，下面看看標準包中DECODE函數的定義。

下面的DECODE函數定義是從STANDARD中摘取出來的部分內容：

function DECODE (expr NUMBER, pat NUMBER, res NUMBER) return NUMBER;
function DECODE (expr NUMBER,
pat NUMBER,
res VARCHAR2 CHARACTER SET ANY_CS)
return VARCHAR2 CHARACTER SET res%CHARSET;
function DECODE (expr NUMBER, pat NUMBER, res DATE) return DATE;
function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET expr%CHARSET,
res NUMBER) return NUMBER;
function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET expr%CHARSET,
res VARCHAR2 CHARACTER SET ANY_CS)
return VARCHAR2 CHARACTER SET res%CHARSET;
function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET expr%CHARSET,
res DATE) return DATE;
function DECODE (expr DATE, pat DATE, res NUMBER) return NUMBER;
function DECODE (expr DATE,
pat DATE,
res VARCHAR2 CHARACTER SET ANY_CS)
return VARCHAR2 CHARACTER SET res%CHARSET;
function DECODE (expr DATE, pat DATE, res DATE) return DATE;

通過觀察上面的定義，我們不難發現，雖然Oracle對DECODE函數進行了大量的重載，且DECODE函數支持各種的數據類型，但是DECODE函數具有一個規律，就是DECODE函數的返回值的類型和DECODE函數的輸入參數中第一個用來返回的參數的數據類型一致。可能不太好理解，舉個簡單的例子：

SQL> select decode(id, 1, '1', 2) from t;

D
-
1
2
2
2

SQL> select decode(id, '1', 1, '2') from t;

DECODE(ID,'1',1,'2')
--------------------
1
2
2
2

從這兩個簡單的例子就可以看出，DECODE的返回值的數據類型和DECODE函數中第一個表示返回的參數的數據類型一致。

從這點就可以看出，NULL的默認數量類型是字符類型，這才導致DECODE的結果變成了字符串，而查詢根據字符串的排序比較，因此’15’小于’8’。

知道了問題的原因，解決的方法就很多了，比如：

SQL> select * from t order by decode(id, 1, 1, 0, null, id);

ID
----------
1
8
15
0

SQL> select * from t order by to_number(decode(id, 0, null, id));

ID
----------
1
8
15
0

SQL> select * from t order by decode(id, 0, cast(null as number), id);

ID
----------
1
8
15
0

SQL> select * from t order by decode(id, 0, to_number(null), id);

ID
----------
1
8
15
0

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com