SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 17 18:02:10 2014Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> show all appinfo is OFF and set to "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autorecovery OFF autotrace OFF blockterminator "." (hex 2e) btitle OFF and is the first few characters of the next SELECT statement cmdsep OFF colsep " " compatibility version NATIVE concat "." (hex 2e) copycommit 0 COPYTYPECHECK is ON define "&" (hex 26) describe DEPTH 1 LINENUM OFF INDENT ON echo OFF editfile "afiedt.buf" embedded OFF escape OFF escchar OFF exitcommit ON FEEDBACK ON for 6 or more rows flagger OFF flush ON heading ON headsep "|" (hex 7c) instance "local" linesize 80 lno 14 loboffset 1 logsource "" long 80 longchunksize 80 up HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;- } h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF newpage 1 null "" numformat "" numwidth 10 pagesize 14 PAUSE is OFF pno 0 recsep WRAP recsepchar " " (hex 20) release 1102000100 repfooter OFF and is NULL repheader OFF and is NULL securedcol is OFF serveroutput OFF shiftinout INVISIBLE showmode OFF spool OFF sqlblanklines OFF sqlcase MIXED sqlcode 0 sqlcontinue "> " sqlnumber ON sqlpluscompatibility 11.2.0 sqlprefix "#" (hex 23) sqlprompt "SQL> " sqlterminator ";" (hex 3b) suffix "sql" tab ON termout ON timing OFF trimout ON trimspool OFF ttitle OFF and is the first few characters of the next SELECT statement underline "-" (hex 2d) USER is "POSZ" verify ON wrap : lines will be wrapped errorlogging is OFF SQL> select sum(l1) from tbl_zzw; SUM(L1) ---------- 10993604SQL>
看看列输出格式: 使用column命令
NUMBER(15,2) 只有对已知列格式化的时候才会出现这种情况。下面的例子是我将c1进行了格式化。。SQL> select sum(a) from test; SUM(A) ---------- 1910.61SQL> select sum(a) c1 from test; c1 ------- 1911
SQL> column COLUMN NAME_COL_PLUS_SHOW_EDITION ON HEADING 'EDITION' FORMAT a30 word_wrap COLUMN result_plus_xquery ON HEADING 'Result Sequence'COLUMN other_plus_exp ON FORMAT a44COLUMN other_tag_plus_exp ON FORMAT a29COLUMN object_node_plus_exp ON FORMAT a8COLUMN plan_plus_exp ON FORMAT a60COLUMN parent_id_plus_exp ON HEADING 'p' FORMAT 990COLUMN id_plus_exp ON HEADING 'i' FORMAT 990COLUMN droptime_plus_show_recyc ON HEADING 'DROP TIME' FORMAT a19COLUMN objtype_plus_show_recyc ON HEADING 'OBJECT TYPE' FORMAT a12COLUMN objectname_plus_show_recyc ON HEADING 'RECYCLEBIN NAME' FORMAT a30COLUMN origname_plus_show_recyc ON HEADING 'ORIGINAL NAME' FORMAT a16COLUMN SID_COL_PLUS_SHOW_SPPARAM ON HEADING 'SID' FORMAT a8 word_wrap COLUMN VALUE_COL_PLUS_SHOW_SPPARAM ON HEADING 'VALUE' FORMAT a28 word_wrap COLUMN NAME_COL_PLUS_SHOW_SPPARAM ON HEADING 'NAME' FORMAT a29 word_wrap COLUMN value_col_plus_show_param ON HEADING 'VALUE' FORMAT a30COLUMN name_col_plus_show_param ON HEADING 'NAME' FORMAT a36COLUMN units_col_plus_show_sga ON FORMAT a15COLUMN name_col_plus_show_sga ON FORMAT a24COLUMN ERROR ON FORMAT A65 word_wrap COLUMN LINE/COL ON FORMAT A8COLUMN ROWLABEL ON FORMAT A15 SQL>
直接执行 select sum(line1) from tblname;
我再plsql developer下测试是没有问题的。。正常显示小数
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
exitcommit ON
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
up HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1102000100
repfooter OFF and is NULL
repheader OFF and is NULL
securedcol is OFF
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 11.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "POSZ"
verify ON
wrap : lines will be wrapped
errorlogging is OFF
SQL> select sum(l1) from tbl_zzw; SUM(L1)
----------
10993604SQL>
使用column命令
只有对已知列格式化的时候才会出现这种情况。下面的例子是我将c1进行了格式化。。SQL> select sum(a) from test; SUM(A)
----------
1910.61SQL> select sum(a) c1 from test; c1
-------
1911
COLUMN NAME_COL_PLUS_SHOW_EDITION ON
HEADING 'EDITION'
FORMAT a30
word_wrap COLUMN result_plus_xquery ON
HEADING 'Result Sequence'COLUMN other_plus_exp ON
FORMAT a44COLUMN other_tag_plus_exp ON
FORMAT a29COLUMN object_node_plus_exp ON
FORMAT a8COLUMN plan_plus_exp ON
FORMAT a60COLUMN parent_id_plus_exp ON
HEADING 'p'
FORMAT 990COLUMN id_plus_exp ON
HEADING 'i'
FORMAT 990COLUMN droptime_plus_show_recyc ON
HEADING 'DROP TIME'
FORMAT a19COLUMN objtype_plus_show_recyc ON
HEADING 'OBJECT TYPE'
FORMAT a12COLUMN objectname_plus_show_recyc ON
HEADING 'RECYCLEBIN NAME'
FORMAT a30COLUMN origname_plus_show_recyc ON
HEADING 'ORIGINAL NAME'
FORMAT a16COLUMN SID_COL_PLUS_SHOW_SPPARAM ON
HEADING 'SID'
FORMAT a8
word_wrap COLUMN VALUE_COL_PLUS_SHOW_SPPARAM ON
HEADING 'VALUE'
FORMAT a28
word_wrap COLUMN NAME_COL_PLUS_SHOW_SPPARAM ON
HEADING 'NAME'
FORMAT a29
word_wrap COLUMN value_col_plus_show_param ON
HEADING 'VALUE'
FORMAT a30COLUMN name_col_plus_show_param ON
HEADING 'NAME'
FORMAT a36COLUMN units_col_plus_show_sga ON
FORMAT a15COLUMN name_col_plus_show_sga ON
FORMAT a24COLUMN ERROR ON
FORMAT A65
word_wrap COLUMN LINE/COL ON
FORMAT A8COLUMN ROWLABEL ON
FORMAT A15
SQL>