ORACLE 中NUMBER类型默认的精度和Scale问题

栏目: 数据库 · Oracle · 发布时间: 7年前

内容简介:ORACLE 中NUMBER类型默认的精度和Scale问题

ORACLE 数据库中, NUMBER(P,S) 是最常见的数字类型,可以存放数据范围为 10^-130~10^126 (不包含此值 ) ,需要 1~22 字节 (BYTE) 不等的存储空间。 PPrecison 的英文缩写,即精度缩写,表示有效数字的位数,最多不能超过 38 个有效数字。 S Scale 的英文缩写,表示从小数点到最低有效数字的位数,它为负数时,表示从最大有效数字到小数点的位数。有时候,我们在创建表的时候, NUMBER 往往没有指定 PS 的值,那么默认情况下, NUMBERPS 的值分别是多少呢?相信这个问题能问倒一大片 DBA 。 在之前,我遇到了一个问题,总结整理在 ORACLE NUMBER类型Scale为0引发的问题 这篇博客当中,当时武断的判断 如果不指定 psNUMBER 类型,它的默认精度值为 38, 默认的 scale 值为 0 ,因为当时参考了官方文档 https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

ORACLE 中NUMBER类型默认的精度和Scale问题

当然文档没有错误 ,文档应该是指在定义字段数据类型为 NUMBER 时,指定了 NUMBER 类型的 P值 ,但是没有指定 S的值 ,那么 Scale 默认就是 0 ,如下测试所示,当时应该是我自己没有完全理解文档意思,当然文档也有误导的嫌疑。

SQL> drop table test;
 
Table dropped.
 
SQL> create table test(id number(38));
 
Table created.
 
SQL> insert into test
  2  select 123 from dual union all
  3  select 123.123 from dual;
 
2 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test;
 
        ID
----------
       123
       123
 
SQL> 

ORACLE 中NUMBER类型默认的精度和Scale问题

当在指定字段类型为 NUMBER 时,如果 PS 都不指定,那么 PS 又是什么值呢?今天特意实验验证了一下,具体实验过程如下:

SQL> drop table test;
 
Table dropped.
 
SQL> create table test(id  number, id1 number(38,4));
 
Table created.
 
SQL> insert into test                                      
  2  select 12, 12 from dual union all
  3  select 12.123456789, 12.123456789 from dual;
 
2 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> col id  for 999999999999.999999999999999999999999999999999999;
SQL> col id1 for 99999999999.9999999999999999999999999999999999999;
SQL> select * from test;
 
                                                ID                                                ID1
-------------------------------------------------- --------------------------------------------------
           12.000000000000000000000000000000000000           12.0000000000000000000000000000000000000
           12.123456789000000000000000000000000000           12.1235000000000000000000000000000000000
 
SQL> 

如上所示,当我插入上面两条记录后,发现如果不指定 psNUMBER 类型,此时的 Scale 至少是 9 ,我们继续测试,插入下面数据

SQL> insert into test
  2  select 12.123456789123456789123456789123456,
  3         12.123456789123456789123456789123456
  4  from dual;
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test;
 
                                                ID                                                ID1
-------------------------------------------------- --------------------------------------------------
           12.000000000000000000000000000000000000           12.0000000000000000000000000000000000000
           12.123456789000000000000000000000000000           12.1235000000000000000000000000000000000
           12.123456789123456789123456789123456000           12.1235000000000000000000000000000000000

如下所示,此时可以看到 Scale 的值 33 了,那么 Scale 的值是否可以继续变大呢?

ORACLE 中NUMBER类型默认的精度和Scale问题

SQL> insert into test
  2  select 12.123456789123456789123456789123456789123,
  3         12.123456789123456789123456789123456789123
  4  from dual;
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test;
 
                                                ID                                                ID1
-------------------------------------------------- --------------------------------------------------
           12.000000000000000000000000000000000000           12.0000000000000000000000000000000000000
           12.123456789000000000000000000000000000           12.1235000000000000000000000000000000000
           12.123456789123456789123456789123456000           12.1235000000000000000000000000000000000
           12.123456789123456789123456789123456789           12.1235000000000000000000000000000000000

如下截图所示,插入的记录为 12.123456789123456789123456789123456789123 ,但是显示的值为 12.123456789123456789123456789123456789 ,总共为 38 位,由于格式化列的缘故,可能导致部分小数位没有显示,

ORACLE 中NUMBER类型默认的精度和Scale问题

我们继续测试,调整格式化列,我们发现值变为了 12.12345678912345678912345678912345678912 ,总共 40 位了, Scale 的值为 38 了。这个是为什么呢?不是数字精度为38,意味着最多是 38 位吗?

SQL> col id  for 999999999999.99999999999999999999999999999999999999999
SQL> col id1 for 99999999999.999999999999999999999999999999999999999999
SQL> select * from test;
 
                                                     ID                                                     ID1
------------------------------------------------------- -------------------------------------------------------
           12.00000000000000000000000000000000000000000           12.000000000000000000000000000000000000000000
           12.12345678900000000000000000000000000000000           12.123500000000000000000000000000000000000000
           12.12345678912345678912345678912345600000000           12.123500000000000000000000000000000000000000
           12.12345678912345678912345678912345678912000           12.123500000000000000000000000000000000000000

ORACLE 中NUMBER类型默认的精度和Scale问题

继续其它测试,我们发现 Sacle 的值会随着小数点前面数字的变化而变化,如下所示:

SQL> insert into test
  2  select 123456789.123456789123456789123456789123456,
  3         123456789.123456789123456789123456789123456
  4  from dual;
 
1 row created.
 
SQL> commit;
 
Commit complete
 
SQL> insert into test
  2  select 123456789123.123456789123456789123456789123456,
  3         123456789123.123456789123456789123456789123456
  4  from dual;
 
1 row created.
 
SQL> commit;
 
Commit complete.
SQL> select * from test;
 
                                                     ID                                                     ID1
------------------------------------------------------- -------------------------------------------------------
           12.00000000000000000000000000000000000000000           12.000000000000000000000000000000000000000000
           12.12345678900000000000000000000000000000000           12.123500000000000000000000000000000000000000
           12.12345678912345678912345678912345600000000           12.123500000000000000000000000000000000000000
           12.12345678912345678912345678912345678912000           12.123500000000000000000000000000000000000000
    123456789.12345678912345678912345678912300000000000    123456789.123500000000000000000000000000000000000000
 123456789123.12345678912345678912345678910000000000000 #######################################################
 
6 rows selected.

ORACLE 中NUMBER类型默认的精度和Scale问题

从上面测试可以看出, Scale 的值是变化的,跟数据值有关系,目前看来,小数点前的数字位数和小数点后的数字位数相加为 40 (有时候又是 39 ),为了测试是否这个规律,我特意用下面案例测试一下

SQL> create table test2(id number);
 
Table created.
 
SQL> insert into test2
  2  select 0.123456789123456789123456789123456789123456789 from dual;
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> col id for 9999999999.9999999999999999999999999999999999999999999999;
SQL> select * from test2;
 
                                                        ID
----------------------------------------------------------
           .1234567891234567891234567891234567891235000000
 
SQL> insert into test2
  2  select 123456789.123456789123456789123456789123456789 from dual;
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test2;
 
                                                        ID
----------------------------------------------------------
           .1234567891234567891234567891234567891235000000
  123456789.1234567891234567891234567891230000000000000000
 
SQL> insert into test2
  2  select 123456789123.123456789123456789123456789123456789 from dual;
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test2;
 
                                                        ID
----------------------------------------------------------
           .1234567891234567891234567891234567891235000000
  123456789.1234567891234567891234567891230000000000000000
##########################################################
 
SQL>  col id for 9999999999999.9999999999999999999999999999999999999999999999;
SQL> select * from test2;
 
                                                           ID
-------------------------------------------------------------
              .1234567891234567891234567891234567891235000000
     123456789.1234567891234567891234567891230000000000000000
  123456789123.1234567891234567891234567891000000000000000000
 
SQL> insert into test2
  2  select 12345678912345.12345678912345678912345678912345 from dual;
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test2;
 
                                                           ID
-------------------------------------------------------------
              .1234567891234567891234567891234567891235000000
     123456789.1234567891234567891234567891230000000000000000
  123456789123.1234567891234567891234567891000000000000000000
#############################################################
 
SQL> col id for 9999999999999999999.99999999999999999999999999999999999999999999;
SP2-0246: Illegal FORMAT string "9999999999999999999.99999999999999999999999999999999999999999999"
SQL> col id for 9999999999999999999.9999999999999999999999999999999999999999
SQL> select * from test2;
 
                                                           ID
-------------------------------------------------------------
                    .1234567891234567891234567891234567891235
           123456789.1234567891234567891234567891230000000000
        123456789123.1234567891234567891234567891000000000000
      12345678912345.1234567891234567891234567900000000000000
 
SQL> 

ORACLE 中NUMBER类型默认的精度和Scale问题

这个问题纠结了很久,不明白为什么是39或40,后面在 Oracle Database SQL Reference 10g Release 2 终于找到解释了,如下所示:

p is the precision, or the total number of significant decimal digits, where the most

significant digit is the left-most nonzero digit, and the least significant digit is the

right-most known digit. Oracle guarantees the portability of numbers with

precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits

depending on the position of the decimal point.

p 是精度,或是有效十进制数的总位数。最大的有效数字是最左边的非零数字,而最小有效位是最右边的数字。   Oracle 保证数字的可移植性

精度高达 20 base-100 digits ,相当于 39 位或 40 位十进制数字,取决于小数点的位置。


以上所述就是小编给大家介绍的《ORACLE 中NUMBER类型默认的精度和Scale问题》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

两周自制脚本语言

两周自制脚本语言

[日]千叶 滋 / 陈筱烟 / 人民邮电出版社 / 2014-6 / 59.00元

《两周自制脚本语言》是一本优秀的编译原理入门读物。全书穿插了大量轻松风趣的对话,读者可以随书中的人物一起从最简单的语言解释器开始,逐步添加新功能,最终完成一个支持函数、数组、对象等高级功能的语言编译器。本书与众不同的实现方式不仅大幅简化了语言处理器的复杂度,还有助于拓展读者的视野。 《两周自制脚本语言》适合对编译原理及语言处理器设计有兴趣的读者以及正在学习相关课程的大中专院校学生。同时,已经......一起来看看 《两周自制脚本语言》 这本书的介绍吧!

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

MD5 加密
MD5 加密

MD5 加密工具

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具