從一個問題討論到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)

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

.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返回值型別如何確定？這些都是細節，但是細節也反映了我們的能力和修行。