[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2658889/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
