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

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

--//以前寫的連結如下:
http://blog.itpub.net/267265/viewspace-2284458/=>[20181207]sqlplus下顯示資料精度.txt
--//才發現自己分析有誤,實際上sqlplus預設最大顯示寬度佔10個字元,是這個問題導致一些精度被擷取.
--//透過例子說明:

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

2.測試:
SCOTT@book> show numwidth
numwidth 10

SCOTT@book> select sum(AMOUNT_SOLD),count(*) from sh.sales;
SUM(AMOUNT_SOLD)   COUNT(*)
---------------- ----------
      98205831.2     918843

--//98205831.2正好佔用10個字元.實際以上sum精確結果是98205831.21,設定numwidth=11看看.

SCOTT@book> set numw 11
SCOTT@book> select sum(AMOUNT_SOLD),count(*) from sh.sales;
SUM(AMOUNT_SOLD)     COUNT(*)
---------------- ------------
     98205831.21       918843

SCOTT@book> select 0.0000000000000000000123456789012 from dual ;
0.0000000000000000000123456789012
---------------------------------
                      1.23457E-20

--//顯示輸出佔11個字元

SCOTT@book> set numw 10
SCOTT@book> select 0.0000000000000000000123456789012 from dual ;
0.0000000000000000000123456789012
---------------------------------
                       1.2346E-20

--//這個問題是一些細節問題,工作中應該引起注意.在toad下只要在
--//Data Grids    Display large numbers in Scientific Notation前面的勾不選上,基本沒有問題,不受顯示寬度限制.