在论坛看到一个有趣的帖子，是关于ORACLE的NUMBER类型溢出的。

继续探讨小数的溢出情况。

原文出自：http://www.itpub.net/thread-984938-1-1.html

有趣的数值溢出（一）：http://yangtingkun.itpub.net/post/468/461723

下面继续讨论小数溢出的情况，也就是原文中给出的情况。

SQL> SELECT 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000001
  2  B FROM DUAL;

                                                 B
--------------------------------------------------
1.0000000000000000000000000000000000000000000E-130

SQL> SELECT DUMP(0.000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000001
  2  ) B FROM DUAL;

B
------------------
Typ=2 Len=2: 128,2

SQL> SELECT 1E-130 FROM DUAL;

                                            1E-130
--------------------------------------------------
1.0000000000000000000000000000000000000000000E-130

上面测试的是最小的正数，下面将这个数再缩小10倍：

SQL> SELECT 1E-131 FROM DUAL;

                                            1E-131
--------------------------------------------------
                                                 0

SQL> SELECT 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000001
  2  B FROM DUAL;

                                                 B
--------------------------------------------------
-9.000000000000000000000000000000000000000000E-129

SQL> SELECT DUMP(0.000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000001)
  2  B FROM DUAL;

B
-------------------
Typ=2 Len=2: 127,11

从第一个查询可以看到，对于科学计数法，Oracle可以正确的判断是否超过了最小正数的最小范围，当小于这个范围时，Oracle认为结果是0。

但是，对于SQLPLUS中直接输入位数超过130位的小数，则Oracle没有进行边界判断，造成了数值的“溢出”。

那么为什么正数会变成负数呢，首先看一下下面的结果：

SQL> SELECT DUMP(1) FROM DUAL;

DUMP(1)
------------------
Typ=2 Len=2: 193,2

SQL> SELECT DUMP(0.1) FROM DUAL;

DUMP(0.1)
-------------------
Typ=2 Len=2: 192,11

SQL> SELECT DUMP(0.01) FROM DUAL;

DUMP(0.01)
------------------
Typ=2 Len=2: 192,2

SQL> SELECT DUMP(0.001) FROM DUAL;

DUMP(0.001)
-------------------
Typ=2 Len=2: 191,11

SQL> SELECT DUMP(0.0001) FROM DUAL;

DUMP(0.0001)
------------------
Typ=2 Len=2: 191,2

SQL> SELECT DUMP(0.00001) FROM DUAL;

DUMP(0.00001)
-------------------
Typ=2 Len=2: 190,11

Oracle中1的指数是193，而小于1的则指数从193开始递减，数值每缩小100倍，指数值减少1，因此由于Oracle没有进行最小指数值的判断，当1E-131时，指数值已经减少到了127：

SQL> SELECT 193-CEIL(131/2) FROM DUAL;

                                   193-CEIL(131/2)
--------------------------------------------------
                                               127

而前面已经讨论过了，指数小于127，数值为负，因此由于Oracle没有检查最小正数的边界，而直接导致最小正数的表达“溢出”，正数变成了负数。

随着小数位数的增加，这个值会变得越来越小，直到最小负数值：

SQL> SELECT 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000001
  2  B FROM DUAL;

                                                 B
--------------------------------------------------
-9.900000000000000000000000000000000000000000E-129

SQL> SELECT 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000001
  2  B FROM DUAL;

                                                 B
--------------------------------------------------
-9.900000000000000000000000000000000000000000E-127

SQL> SELECT 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000001
  2  B FROM DUAL;

                                                 B
--------------------------------------------------
                         -.00000000000000000000009

SQL> SELECT
  2  0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000001
  3  B FROM DUAL;

                                                 B
--------------------------------------------------
                                         -.0000009

SQL> SELECT
  2  0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000001
  3  B FROM DUAL;
0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
                                                                                                   
第 2 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

不过当数值超过sqlplus的行长度限制256之后，Oracle就会报错。

SQL> BEGIN
  2  EXECUTE IMMEDIATE 'SELECT ' ||
  3  0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000001
  4  || ' FROM DUAL' INTO :RES;
  5  END;
  6  /

PL/SQL 过程已成功完成。

SQL> PRINT RES

                                               RES
--------------------------------------------------
                                        -.00000099

改用上面的方法就可以避免这个问题。

SQL> BEGIN
  2  EXECUTE IMMEDIATE 'SELECT ' ||
  3  0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
  4  || ' FROM DUAL' INTO :RES;
  5  END;
  6  /

PL/SQL 过程已成功完成。

SQL> PRINT RES

                                               RES
--------------------------------------------------
-9.900000000000000000000000000000000000000000E+125

SQL> SELECT DUMP(:RES) FROM DUAL;

DUMP(:RES)
----------------------------------------------------------------------------------------------------
Typ=2 Len=3: 0,2,102

最终达到了最小负数值，下面再增加位数：

SQL> BEGIN
  2  EXECUTE IMMEDIATE 'SELECT ' ||
  3  0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
  4  || ' FROM DUAL' INTO :RES;
  5  END;
  6  /

PL/SQL 过程已成功完成。

SQL> PRINT RES

                                               RES
--------------------------------------------------
1.0000000000000000000000000000000000000000000E+125

SQL> SELECT DUMP(:RES) FROM DUAL;

DUMP(:RES)
----------------------------------------------------------------------------------------------------
Typ=2 Len=2: 255,11

SQL> BEGIN
  2  EXECUTE IMMEDIATE 'SELECT ' ||
  3  0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
  4  || ' FROM DUAL' INTO :RES;
  5  END;
  6  /

PL/SQL 过程已成功完成。

SQL> PRINT :RES

                                               RES
--------------------------------------------------
1.0000000000000000000000000000000000000000000E+124

SQL> SELECT DUMP(:RES) FROM DUAL;

DUMP(:RES)
----------------------------------------------------------------------------------------------------
Typ=2 Len=2: 255,2

由于缺少边界的检查，Oracle又变成了最大正数并开始递减。

而Oracle最大负数的情况与此类似，只不过将递减变成了递增。

这种情况只会影响直接输入的数值，而Oracle存储在数据库中的值是会进行严格的边界检查的，而且科学计数法方式输入的值也不会造成影响。