[20181207]sqlplus下顯示資料精度.txt

[20181207]sqlplus下顯示資料精度.txt

--//這個是一個細節問題,昨天看了連結:http://nimishgarg.blogspot.com/2018/12/minus-query-giving-results-on-exactly.html

--//以該網站的數字作為測試說明問題.

1.環境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t1 ( id number);

insert into t1 values (1408.921128110362243397676441929671192844);

insert into t1 values (1408.921128110362243397676441929671192846);

insert into t1 values (1408.921128110362243397676441929671192845);

insert into t1 values (1408.92113);

commit;

SCOTT@book> select * from t1 ;

        ID

----------

1408.92113

1408.92113

1408.92113

1408.92113

--//你可以發現在sqlplus下預設僅僅顯示小數點5位.

SCOTT@book> create table t2 as select * from t1 where rownum<=2;

Table created.

SCOTT@book> select * from t1 minus select * from t2;

        ID

----------

1408.92113

1408.92113

SCOTT@book> select rowid,t1.* from t1 where id=1408.92113;

ROWID                      ID

------------------ ----------

AAAWGpAAEAAAAIMAAD 1408.92113

--//你會覺得奇怪僅僅查詢到1條,如果沒有第4條插入,你根本查詢不到.

SCOTT@book> column id format 9999.999999999999999999999999999999999999999999999

SCOTT@book> select rowid,t1.* from t1 ;

ROWID                                                               ID

------------------ ---------------------------------------------------

AAAWGpAAEAAAAIMAAA  1408.921128110362243397676441929671192844000000000

AAAWGpAAEAAAAIMAAB  1408.921128110362243397676441929671192846000000000

AAAWGpAAEAAAAIMAAC  1408.921128110362243397676441929671192845000000000

AAAWGpAAEAAAAIMAAD  1408.921130000000000000000000000000000000000000000

--//如果在toad下就不會存在這個問題.

select * from t1;

ID

1408.921128110362243397676441929671192844

1408.921128110362243397676441929671192846

1408.921128110362243397676441929671192845

1408.92113

--//順便說一下,生產系統資料庫number最好定義精度,不然可能佔用空間很大.有一些數字是計算得來.

SCOTT@book> select rowid,t1.id,dump(id) c80  from t1 where rowid='AAAWGpAAEAAAAIMAAE';

ROWID                                                               ID C80

------------------ --------------------------------------------------- --------------------------------------------------------------------------------

AAAWGpAAEAAAAIMAAE      .333333333333333333333333333333333333333300000 Typ=2 Len=21: 192,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34

--//佔用21位元組.

--//另外可以參考我以前遇到的問題:http://blog.itpub.net/267265/viewspace-1257036/->[20140823]在sqlplus使用copy注意.txt 

--//另外我還遇到的情況:http://blog.itpub.net/267265/viewspace-2148998/->[20171220]toad plsql顯示整形的bug.txt

總結:

1.生產系統number型別最好定義精度.

2.sqlplus下顯示資料型別,預設僅僅精確到小數點5位.在工作中注意.