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

来源：这里教程网时间：2026-03-03 14:14:55 作者：

[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. --//当然这些仅仅是我的猜测,也许根本不是这样.......