ITPub博客

首页 > 数据库 > Oracle > [20191001]关于oracle number类型的一些疑惑.txt

[20191001]关于oracle number类型的一些疑惑.txt

原创 Oracle 作者:lfree 时间:2019-10-04 20:16:32 0 删除 编辑

[20191001]关于oracle number类型的一些疑惑.txt

--//链接:http://www.itpub.net/thread-2120621-1-1.html讨论.

1.问题1:
--//MAX_LENGTH 来自那里?

1)有关NUMBER的官方文档说明
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref80
NUMBER(p,s)
where:
      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.

2)有关user_tab_columns的DATA_LENGTH字段的官方文档说明(同ALL_TAB_COLUMNS视图)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2094.htm#REFRN20277
ALL_TAB_COLUMNS
... ...
Column        Datatype  NULL      Description
DATA_LENGTH   NUMBER    NOT NULL  Length of the column (in bytes)
... ...
--//P 指精度.

SCOTT@test01p> create table tx ( a number(5,2),b number(5) , c number(5,-2),d number(1,2));
Table created.

SCOTT@test01p> select column_name,data_type,data_length,data_precision,data_scale from user_tab_columns where table_name='TX';
COLUMN_NAME          DATA_TYPE            DATA_LENGTH DATA_PRECISION DATA_SCALE
-------------------- -------------------- ----------- -------------- ----------
A                    NUMBER                        22              5          2
B                    NUMBER                        22              5          0
C                    NUMBER                        22              5         -2
D                    NUMBER                        22              1          2
--//DATA_LENGTH=22.可以看注解描述Length of the column (in bytes),理论将number最大占有空间可以达到22字节,而我实际的测试仅
--//仅21字节.这是我的第一个疑问.顺便测试各种P,S的情况.

--//字段A定义number(5,2),这样保留小数点后2位.小数点前仅仅3位.
SCOTT@test01p> insert into tx (a) values (999.999);
insert into tx (a) values (999.999)
                           *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
--//输入(999.999,因为保留小数点2位,四舍五入的原因变成1000,超出范围.

SCOTT@test01p> insert into tx (a) values (999.994);
1 row created.

SCOTT@test01p> rollback ;
Rollback complete.

--//字段B定义number(5),小数点前仅仅5位.
SCOTT@test01p> insert into tx (b) values (99999.4);
1 row created.

SCOTT@test01p> insert into tx (b) values (99999.5);
insert into tx (b) values (99999.5)
                           *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

--//字段C定义number(5,-2),S=-2,实际上范围发生变化变成0-9999900(指正数).

SCOTT@test01p> insert into tx (c) values (9999949);
1 row created.

SCOTT@test01p> insert into tx (c) values (9999950);
insert into tx (c) values (9999950)
                           *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SCOTT@test01p> select c from tx;
         C
----------
   9999900

--//字段D定义number(1,2),P可以小于S.这样小数点后保留2位.而P=1,这样仅仅能输入0.01-0.09(指正数).
SCOTT@test01p> insert into tx (d) values (0.099);
insert into tx (d) values (0.099)
                           *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SCOTT@test01p> insert into tx (d) values (0.09);
1 row created.

SCOTT@test01p> select d from tx ;
         D
----------
       .09

2.问题2:
--//P最大是多少的问题,按照文档介绍是38,而实际上看链接是可以达到40.
--//解析:21个字符,幂指数占1位,剩下20位保留尾数,这样实际上P可以达到40,而不是38.也就是如果定义number不带参数.
--//是可以突破P=38限制.
SCOTT@test01p> create table ty( a  number(40));
create table ty( a  number(40))
                           *
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)
--//不能定义P>38.

SCOTT@test01p> create table ty ( a number,b number(*),c number(38), d number(38,38) , e number(*,38) ,f int  );
Table created.
--//我的测试不能定义number(*,*).

SCOTT@test01p> select column_name,data_type,data_length,data_precision,data_scale from user_tab_columns where table_name='TY';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- --------- ----------- -------------- ----------
A           NUMBER             22
B           NUMBER             22
C           NUMBER             22             38          0
D           NUMBER             22             38         38
E           NUMBER             22                        38
F           NUMBER             22                         0
6 rows selected.
--//number , number(*) 定义相同. 定义int仅仅S=0.相当于number(*,0).

SCOTT@test01p> insert into ty (a,b,f ) values (to_number( rpad('9',40,'9')),to_number( rpad('9',40,'9')),to_number( rpad('9',40,'9')));
1 row created.

SCOTT@test01p> select dump(a,16) c80,dump(b,16) c80,dump(f,16) c80 from ty
  2  @ prxx
==============================
C80                           : Typ=2 Len=21: d4,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
C80                           : Typ=2 Len=21: d4,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
C80                           : Typ=2 Len=21: d4,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
PL/SQL procedure successfully completed.

SCOTT@test01p> rollback ;
Rollback complete.

SCOTT@test01p> insert into ty (c ) values (to_number( rpad('9',40,'9')));
insert into ty (c ) values (to_number( rpad('9',40,'9')))
                            *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
--//你可以发现字段C就无法插入to_number( rpad('9',40,'9')).

SCOTT@test01p> insert into ty (c ) values (to_number( rpad('9',38,'9')));
1 row created.

SCOTT@test01p> select dump(c,16) c80 from ty ;
C80
-------------------------------------------------------------------------
Typ=2 Len=20: d3,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64

SCOTT@test01p> rollback ;
Rollback complete.

--//继续测试:
SCOTT@test01p> insert into ty (a ) values (to_number( rpad('9',44,'8')));
1 row created.

SCOTT@test01p> select dump(a,16) c80 from ty ;
C80
--------------------------------------------------------------------------------
Typ=2 Len=21: d6,63,59,59,59,59,59,59,59,59,59,59,59,59,59,59,59,59,59,59,5a

--//0x63 = 99
--//0x59 = 89
--//0x5a = 90
--//最后是0x5a,猜测DATA_LENGTH=22是否是为了运算时四舍五入的需要而故意为之.做一个例子验证看看.

3.验证:
SCOTT@test01p> create table tz ( a number,b number,c number  );
Table created.

SCOTT@test01p> insert into tz(a,b) values (1/3,1/3);
1 row created.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> select to_char(a,'fm.'||rpad('9',40,'9')) c50 ,to_char(b,'fm.'||rpad('9',40,'9')) c50 ,to_char(c,'fm.'||rpad('9',40,'9')) c50 from tz;
C50                                                C50                                                C50
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
.3333333333333333333333333333333333333333          .3333333333333333333333333333333333333333

SCOTT@test01p> update tz set c=a+b;
1 row updated.

SCOTT@test01p> select to_char(a,'fm.'||rpad('9',40,'9')) c50 ,to_char(b,'fm.'||rpad('9',40,'9')) c50 ,to_char(c,'fm.'||rpad('9',40,'9')) c50 from tz;
C50                                                C50                                                C50
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
.3333333333333333333333333333333333333333          .3333333333333333333333333333333333333333          .6666666666666666666666666666666666666666

SCOTT@test01p> rollback;
Rollback complete.

SCOTT@test01p> update tz set c=a+b+4.9e-41+4.9e-41;
1 row updated.

SCOTT@test01p> select to_char(a,'fm.'||rpad('9',40,'9')) c50 ,to_char(b,'fm.'||rpad('9',40,'9')) c50 ,to_char(c,'fm.'||rpad('9',40,'9')) c50 from tz;
C50                                                C50                                                C50
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
.3333333333333333333333333333333333333333          .3333333333333333333333333333333333333333          .6666666666666666666666666666666666666666
--//没有变化.我的理解加4.9e-41时,四舍五入时丢弃.再加还是一样.

SCOTT@test01p> update tz set c=a+b+5e-41;
1 row updated.

SCOTT@test01p> select to_char(a,'fm.'||rpad('9',40,'9')) c50 ,to_char(b,'fm.'||rpad('9',40,'9')) c50 ,to_char(c,'fm.'||rpad('9',40,'9')) c50 from tz;
C50                                                C50                                                C50
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
.3333333333333333333333333333333333333333          .3333333333333333333333333333333333333333          .6666666666666666666666666666666666666667

--//字段c最后是7,发生变化.也许这就是DATA_LENGTH=22 number MAX_LENGTH的真正含义.
--//虽然number存储在磁盘最大是21字节.但是在内存里面应该是22字节.再举一个例子:

SCOTT@test01p> update tz set c=a+b+1-1+5e-41+4.9e-41;
1 row updated.

SCOTT@test01p> select to_char(a,'fm.'||rpad('9',40,'9')) c50 ,to_char(b,'fm.'||rpad('9',40,'9')) c50 ,to_char(c,'fm.'||rpad('9',40,'9')) c50 from tz;
C50                                                C50                                                C50
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
.3333333333333333333333333333333333333333          .3333333333333333333333333333333333333333          .6666666666666666666666666666666666666701
--//加1 再-1后变成.66666666666666666666666666666666666667,在加5e-41,变成.6666666666666666666666666666666666666701.
SCOTT@test01p> rollback;
Rollback complete.

SCOTT@test01p> update tz set c=a+b+5e-41+1-1;
1 row updated.

SCOTT@test01p> select to_char(a,'fm.'||rpad('9',40,'9')) c50 ,to_char(b,'fm.'||rpad('9',40,'9')) c50 ,to_char(c,'fm.'||rpad('9',40,'9')) c50 from tz;
C50                                                C50                                                C50
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
.3333333333333333333333333333333333333333          .3333333333333333333333333333333333333333          .66666666666666666666666666666666666667

--//这样就看不到最后的01.当然我个人认为生产系统最后不要使用number不带p,s参数.

SCOTT@test01p> delete from tz;
1 row deleted.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> insert into tz(a,b) values (3*(1/3),3*1/3);
1 row created.

SCOTT@test01p> select to_char(a,'fm.'||rpad('9',40,'9')) c50,a,b,dump(a,16),dump(b,16) from tz
  2  @ prxx
==============================
C50                           : .9999999999999999999999999999999999999999
A                             : 1
B                             : 1
DUMP(A,16)                    : Typ=2 Len=21: c0,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
DUMP(B,16)                    : Typ=2 Len=2: c1,2

PL/SQL procedure successfully completed.
--//sqlplus把A四舍五入了,缺省sqlplus保留小数点6位.但是字段a,b消耗的存储空间不同.
--//前者你可以认为是3*.3333333333333333333333333333333333333333=.9999999999999999999999999999999999999999.
--//当然这些仅仅是我的猜测,也许根本不是这样.......

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2658889/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2506
  • 访问量
    6307419