SQL和儲存過程的結果不一致——小議Oracle的number精度問題

今天看到一個有趣的問題，SQL得到的查詢結果和儲存過程不一致。

原始問題參加：http://www.itpub.net/showthread.php?s=&threadid=763283

首先模擬一下這個問題：

SQL> CREATE OR REPLACE PROCEDURE P_TEST (P_IN NUMBER, P_OUT OUT NUMBER) IS
2 BEGIN
3 SELECT LOG(2, P_IN) INTO P_OUT FROM DUAL;
4 DBMS_OUTPUT.PUT_LINE(P_OUT);
5 END P_TEST;
6 /

過程已建立。

SQL> SET SERVEROUT ON
SQL> VAR NUM NUMBER
SQL> EXEC P_TEST(4096, :NUM)
11.99999999999999999999999999999999999994

PL/SQL 過程已成功完成。

SQL> SELECT LOG(2, 4096) FROM DUAL;

LOG(2,4096)
------------
12

SQL> PRINT NUM

NUM
------------
12

問題似乎很奇怪，為什麼儲存過程和SQL直接查詢得到的結果不一致？其實問題就是由於資料精度造成的。

看看下面幾個例子就會明白了：

SQL> SELECT DUMP(LOG(2, 4096)) FROM DUAL;

DUMP(LOG(2,4096))
-------------------------------------------------------------------------------
Typ=2 Len=21: 193,12,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,95

SQL> SELECT TO_CHAR(LOG(2, 4096)) FROM DUAL;

TO_CHAR(LOG(2,4096))
----------------------------------------
11.9999999999999999999999999999999999999

從目前的結果可以看到，Oracle實際上得到並非是12，而是一個近似值。實際上是SQLPLUS工具幫我們做了一個四捨五入。

透過DUMP或轉化為字元型別，或者直接在伺服器端列印，都會顯示正確的結果。

為了避免SQLPLUS的干擾，可以簡單的將NUMW設定為40，就可以看到真實的結果：

SQL> SHOW NUMW
numwidth 12
SQL> SET NUMW 40
SQL> SELECT LOG(2, 4096) FROM DUAL;

LOG(2,4096)
----------------------------------------
11.9999999999999999999999999999999999999

LOG函式對精度要求很高，懷疑Oracle中的LOG函式在處理過程中出現了精度不足的問題，因此導致最終的結果不是12。