说时迟那时快,oracle也经过很多过年头了,俗话说世界上没有 没有bug的产品,oracle虽然沉淀了这么多年,依然每个版本或多或少都有bug。
此个bug出现是oracle 11.2.0.4,当然其他版本我没有测试,可能也有这个问题。
BUG:oracle的数据类型number,当字段定义为number(18,4),当然也可以定义为其他长度,只要满足整数类型必须大于12位,且有小数位数,就会触发这个bug。比如定义为number(18,4),当insert数据的时候,插入数据为99999999999999.1的时候,插入的数据并不是这个数据,而是99999999999999.0938。当插入的数据为9999999999999.1的时候,也就是整数位数为13位,实际在数据库中的数据是:9999999999999.0996。当插如的数据整数位数小于或等于12位的时候,数据是正常的。
测试数据库字符集是AL32UTF-8,所以测试有问题。朋友另一台数据库用的字符集是GBK,没有出现这样的问题。初步判断是字符集原因导致的
实际测试结果:
Oracle PL/SQL
SQL> create table jerry_test (a number(18,4));
Table created
SQL> insert into jerry_test(a) values(99999999999999.1);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from jerry_test;
A
--------------------
99999999999999.0938
SQL> insert into jerry_test(a) values(9999999999999.1);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from jerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
SQL>
SQL> insert into jerry_test(a) values(999999999999.1);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from jerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
999999999999.1000
SQL>
SQL> insert into jerry_test(a) values(99999999999999.2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from jerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
999999999999.1000
99999999999999.2031
SQL>
SQL> insert into jerry_test(a) values(99999999999999.33);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from jerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
999999999999.1000
99999999999999.2031
99999999999999.2969
SQL>
SQL> insert into jerry_test(a) values(99999999999999.4444);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from jerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
999999999999.1000
99999999999999.2031
99999999999999.2969
99999999999999.4062
6 rows selected
SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
SQL>createtablejerry_test(anumber(18,4));
Tablecreated
SQL>insertintojerry_test(a)values(99999999999999.1);
1rowinserted
SQL>commit;
Commitcomplete
SQL>select*fromjerry_test;
A
--------------------
99999999999999.0938
SQL>insertintojerry_test(a)values(9999999999999.1);
1rowinserted
SQL>commit;
Commitcomplete
SQL>select*fromjerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
SQL>
SQL>insertintojerry_test(a)values(999999999999.1);
1rowinserted
SQL>commit;
Commitcomplete
SQL>select*fromjerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
999999999999.1000
SQL>
SQL>insertintojerry_test(a)values(99999999999999.2);
1rowinserted
SQL>commit;
Commitcomplete
SQL>select*fromjerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
999999999999.1000
99999999999999.2031
SQL>
SQL>insertintojerry_test(a)values(99999999999999.33);
1rowinserted
SQL>commit;
Commitcomplete
SQL>select*fromjerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
999999999999.1000
99999999999999.2031
99999999999999.2969
SQL>
SQL>insertintojerry_test(a)values(99999999999999.4444);
1rowinserted
SQL>commit;
Commitcomplete
SQL>select*fromjerry_test;
A
--------------------
99999999999999.0938
9999999999999.0996
999999999999.1000
99999999999999.2031
99999999999999.2969
99999999999999.4062
6rowsselected
SQL>
转载请注明: 版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
最后编辑:2016-07-27作者:Jerry
一个积极向上的小青年,热衷于分享--Focus on DB,BI,ETL