oracle number11,oracle 11.2.0.4 一个还未解决的number数据类型BUG

说时迟那时快，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