发表于: 2008.05.12 23:39
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/461820
---------------------------------------------------------------
在论坛看到一个有趣的帖子，是关于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存储在数据库中的值是会进行严格的边界检查的，而且科学计数法方式输入的值也不会造成影响。
