oracle的decode提示无效数字,从一个问题讨论到Decode函数返回值

今天在QQ上一个朋友发出问题，说min函数返回错误的取值。详细如下：一个数据表列类型为number(6,2)，其中有三行记录，分别为0，0.6和1。用min获取最小值，得到0.6。

min是Oracle SQL的一个基础函数，理论上不会出现这样的Bug之类的。下面一起来模拟下实验环境。

1、环境构建

在实验数据库Oracle 11g环境下，构建实验数据表t。填入实验数据。

SQL> create table t (num number(6,2));

Table created

SQL> insert into t values (0);

1 row inserted

SQL> insert into t values (0.6);

1 row inserted

SQL> insert into t values (1);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from t;

NUM

--------

0.00

0.60

1.00

实验那位兄弟的说法。

SQL> select min(num) from t;

MIN(NUM)

----------

0

SQL> select min(to_number(num)) from t;

MIN(TO_NUMBER(NUM))

-------------------

0

没有什么问题，详细问了一下，获取到了SQL结构如下。

SQL> select min(decode(num,-1,null,num)),min(num) from t;

MIN(DECODE(NUM,-1,NULL,NUM))MIN(NUM)

---------------------------------------- ----------

.60

果然，诡异的现象发生了。

2、问题分析

一时间还是很唬人的，那么我们先抛开min函数，单独看数据列情况。抽丝剥茧吧。

SQL> select decode(num,-1,null,num),num from t;

DECODE(NUM,-1,NULL,NUM)NUM

---------------------------------------- --------

00.00

.60.60

11.00

这里只剩下一个decode函数的使用。从含义上看，当num为-1的时候，返回null值，否则就是原有的num值。但是有两个疑点，首先是0.60是如何转变为.6的呢？其次就是decode函数处理列的列对其方式，数字类型默认是右对齐，只有字符串是左对齐的。难道说经过decode函数处理之后，返回值变成了字符串？

那么，如果decode处理之后，变成了字符串的话，我们调整一个decode的结构，看看是否是由于处理变成字符串造成了问题。

SQL> select min(to_number(decode(num,-1,null,num))),min(num) from t;

MIN(TO_NUMBER(DECODE(NUM,-1,NUMIN(NUM)

------------------------------ ----------

00

看来原因就在于decode函数使用处理之后，返回数据列是一个字符串类型。但是decode函数命名指定了num列，返回值是什么类型呢？

num是数字肯定没有什么问题？难道说疑点出现在null的返回值类型上？继续实验。

SQL> select decode(num,-1,num,num),num from t;

DECODE(NUM,-1,NUM,NUM)NUM

---------------------- --------

00.00

0.60.60

11.00

果然，临时取消掉null，decode返回类型就正常。看来真是受到了null的影响。这个时候，笔者思考一个问题，Oracle Decode函数如何确定返回值类型列呢？

SQL> select decode(num,-1,'d',num),num from t;

DECODE(NUM,-1,'D',NUM)NUM

---------------------------------------- --------

00.00

.60.60

11.00

SQL> select decode(num,-1,'k',num),to_char(num) from t;

DECODE(NUM,-1,'K',NUM)TO_CHAR(NUM)

---------------------------------------- ----------------------------------------

00

.6.6

11

上面的实验，让我们得出了和null值是相同的效果。这样，我们对decode有下面猜想：

üOracle在调用decode函数的时候，是需要预先确定列的类型，因为毕竟出现在相同的列上；

ü确定decode返回值类型，是依据参数中第一个条件返回类型。之后所有的返回类型都依据第一个类型进行强制类型转换；

üOracle在第一个条件返回类型为null的时候，默认将其作为字符串处理；

如果三个假设成立，那么所有问题就得到解释。

那个朋友的SQL中，decode函数第一个可选返回值是null，Oracle识别返回类型为字符类型。之后对所有的其他返回值均使用了to_char方法类似的转换逻辑。

那么，往后想一步，如果Oracle decode函数真是依靠第一条件来确定列类型，其他列进行强行转换，那么如果出现不匹配的时候怎么办？

SQL> select decode(num,1,num,'k') from t;

select decode(num,1,num,'k') from t

ORA-01722:无效数字

这个案例中的decode函数，根据第一个前条件取值num是数字类型，那么其他所有都会被强制转换为数字类型。但是我们写定的其他条件取值是’k’，不能进行强制类型转换。于是报错无效数字。

3、问题解决

了解了问题decode的根源，剩下的就好解释了。min函数可以接受字符串和数字。在数字类型时，依据数字类型的比较规则，选择出0是最小值。当接受字符串时，使用的是二进制对比策略。其中.小数点的排序位最小。于是选择出.6作为结果也就不奇怪了。

解决问题的方法很多，笔者推荐的方式是对null进行数字化处理。让Oracle识别为数字类型。

SQL> select min(decode(num,-1,to_number(null),num)),min(num) from t;

MIN(DECODE(NUM,-1,TO_NUMBER(NUMIN(NUM)

------------------------------ ----------

00

4、结论

通过这个案例，我们除了重新认识到decode的原理外，还有几个收获。

首先是要重视null值，null在Oracle中是一种很特殊的类型。在运算和函数调用中，都有很多特殊之处。遇到问题，要注意考虑null的因素，是我们解决问题的思路；

其次就是重视函数的本质。Decode是我们常见的函数，但是我们对一些细节缺乏思考研究。比如Decode返回值类型如何确定？这些都是细节，但是细节也反映了我们的能力和修行。