null的数据类型

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