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。