Oracle的NULL代表的含義是不確定，那么不確定的東西也會有確定的

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

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

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

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

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

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 的列

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

下面就是我發(fā)現(xiàn)NULL的數(shù)據(jù)類型的例子，同時說明了如果不注意NULL的數(shù)據(jù)類型可能會出現(xiàn)的問題。

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

SQL> create table t (id number);

表已創(chuàng)建。

SQL> insert into t values (1);

已創(chuàng)建 1 行。

SQL> insert into t values (8);

已創(chuàng)建 1 行。

SQL> insert into t values (0);

已創(chuàng)建 1 行。

SQL> insert into t values (15);

已創(chuàng)建 1 行。

SQL> commit;

提交完成。

需要按照T中的ID的升序顯示數(shù)據(jù)，SQL如下：

SQL> select * from t order by id;

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

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

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

SQL如下：

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

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

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

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

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

15

看看DECODE函數(shù)的結(jié)果，這回明白了，原來DECODE的結(jié)果變?yōu)榱俗址愋汀Ｗ址愋徒Y(jié)果在SQLPLUS顯示左對齊，而數(shù)值類型是右對齊。

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

猜測只是猜測，還需要確切的證據(jù)證明這一點，下面看看標(biāo)準(zhǔn)包中DECODE函數(shù)的定義。

下面的DECODE函數(shù)定義是從STANDARD中摘取出來的部分內(nèi)容：

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;

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

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的返回值的數(shù)據(jù)類型和DECODE函數(shù)中第一個表示返回的參數(shù)的數(shù)據(jù)類型一致。

從這點就可以看出，NULL的默認(rèn)數(shù)量類型是字符類型，這才導(dǎo)致DECODE的結(jié)果變成了字符串，而查詢根據(jù)字符串的排序比較，因此’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

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