[20191001]關於oracle number型別的一些疑惑.txt
[20191001]關於oracle number型別的一些疑惑.txt
--//連結:http://www.itpub.net/thread-2120621-1-1.html討論.
1.問題1:
--//MAX_LENGTH 來自那裡?
1）有關NUMBER的官方文件說明
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檢視）
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20150503]關於oracle的number型別.txtOracle型別
- 關於介面的一些疑惑
- 【NUMBER】Oracle的NUMBER資料型別特點Oracle資料型別
- Oracle的number資料型別Oracle資料型別
- [20160828]number型別.txt型別
- ORACLE NUMBER資料型別Oracle資料型別
- 請教關於ANALYZE 命令的一些疑惑
- Oracle中number型別詳解Oracle型別
- 【NUMBER】有關Oracle NUMBER型別定義中precision和scale的測試和總結Oracle型別
- 關於Oracle字元型別的比較Oracle字元型別
- js關於this的疑惑JS
- [20171203]關於raw型別.txt型別
- EF：oracle的number型別對映為C#的boolean型別Oracle型別C#Boolean
- ORACLE NUMBER型別內部實現Oracle型別
- 關於barryvdh/laravel-ide-helper的一些疑惑LaravelIDE
- [20190930]oracle raw型別轉化number指令碼.txtOracle型別指令碼
- 關於oracle中的sql資料型別OracleSQL資料型別
- 關於引用型別轉化的一些東西型別
- [20190930]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- [20191013]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- oracle集合型別使用的實驗.TXTOracle型別
- 關於解釋型語言和編譯型語言的疑惑，請教編譯
- 關於Application型別的變數一些問題。APP型別變數
- [20191003]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- 關於值物件的理解，疑惑物件
- 關於struts開發的疑惑
- 關於組合模式的疑惑模式
- 淺析number型別的值型別
- 征服number型別的input框型別
- 測試NUMBER型別的效能型別
- 關於oracle的一些命令Oracle
- Go - 關於 protoc 工具的小疑惑Go
- 關於隱式資料型別轉換的一些試驗資料型別
- number型別的顯示問題型別
- 關於Ibatis 轉換 oracle date型別的問題BATOracle型別
- 10_深入解析Oracle number資料型別及os層number解析工具分享Oracle資料型別
- 關於long型別的轉換型別
- 關於tc型別的enqueue等待型別ENQ