今天在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)

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

.6 0

果然，詭異的現象發生了。

2、問題分析

一時間還是很唬人的，那麼我們先拋開min函式，單獨看資料列情況。抽絲剝繭吧。

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

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

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

0 0.00

.6 0.60

1 1.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,NU MIN(NUM)

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

0 0

看來原因就在於decode函式使用處理之後，返回資料列是一個字串型別。但是decode函式命名指定了num列，返回值是什麼型別呢？

num是數字肯定沒有什麼問題？難道說疑點出現在null的返回值型別上？繼續實驗。

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

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

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

0 0.00

0.6 0.60

1 1.00

果然，臨時取消掉null，decode返回型別就正常。看來真是受到了null的影響。這個時候，筆者思考一個問題，Oracle Decode函式如何確定返回值型別列呢？

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

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

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

0 0.00

.6 0.60

1 1.00

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

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

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

0 0

.6 .6

1 1

上面的實驗，讓我們得出了和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(NU MIN(NUM)

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

0 0

4、結論

透過這個案例，我們除了重新認識到decode的原理外，還有幾個收穫。

首先是要重視null值，null在Oracle中是一種很特殊的型別。在運算和函式呼叫中，都有很多特殊之處。遇到問題，要注意考慮null的因素，是我們解決問題的思路；

其次就是重視函式的本質。Decode是我們常見的函式，但是我們對一些細節缺乏思考研究。比如Decode返回值型別如何確定？這些都是細節，但是細節也反映了我們的能力和修行。

從一個問題討論到Decode函式返回值

相關文章