[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190930]oracle raw型別轉化number指令碼.txtOracle型別指令碼
- [20190930]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- [20191003]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- [20191013]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- Oracle的number資料型別Oracle資料型別
- ORACLE NUMBER資料型別Oracle資料型別
- [20201207]關於ORACLE IMU的一些疑問.txtOracle
- 關於barryvdh/laravel-ide-helper的一些疑惑LaravelIDE
- js關於this的疑惑JS
- 10_深入解析Oracle number資料型別及os層number解析工具分享Oracle資料型別
- [20191219]oracle timestamp資料型別的儲存.txtOracle資料型別
- 淺析number型別的值型別
- Go - 關於 protoc 工具的小疑惑Go
- [20180419]關於閃回的一些問題.txt
- [20241009]oracle timestamp with time zone資料型別的儲存.txtOracle資料型別
- 關於Python Number 相關的知識！Python
- 關於Docker中網路效能疑惑Docker
- [20190505]關於latch 一些統計資訊.txt
- 關於 Go 中 Map 型別和 Slice 型別的傳遞Go型別
- [20190311]關於oracle物理與邏輯壞塊.txtOracle
- 關於 PHP 的資料型別 (一)PHP資料型別
- 關於 PHP 的資料型別 (二)PHP資料型別
- 關於 PHP 的資料型別 (三)PHP資料型別
- jsp頁面number型別自動轉為String型別JS型別
- JS -- number資料型別詳解JS資料型別
- JS中其他資料型別轉為number資料型別的方法JS資料型別
- [20191202]關於oracle例項是否使用hugepages問題.txtOracle
- Java 8 peek() 方法的一些疑惑Java
- 關於結構體型別的學習結構體型別
- 幽默：關於型別的觀點演變型別
- 關於不完全型別的認識型別
- 關於php的try catch 使用的疑惑，希望高手指點PHP
- [20180917]關於分析函式的range與rows的區別.txt函式
- [20241121]測試軟軟解析遇到的疑惑.txt
- [20190612]NULL的資料型別.txtNull資料型別
- ORACLE日期型別Oracle型別
- 關於Vue和React區別的一些筆記VueReact筆記
- 關於32位oracle擴充套件SGA的一些問題Oracle套件