为什么Oracle不显示小数的整数部分 [英] Why oracle doesn't show the integer part of a decimal

问题描述

当我执行下一个SQL时:

when I execute the next SQL:

从双选中选择1/3；

select 1/3 from dual;

结果是，333333333 但我预期的结果是0,333333333

the result is ,333333333 but the result that I expected was 0,333333333

为什么oracle不显示零?有什么参数可以影响它吗? 谢谢

Why doesn't oracle show the zero ? is there any parameter that can affect it? Thanks

我从使用oo4o(用于ole的Oracle对象)的应用程序检查了此行为，并通过sqlDeveloper进行了确认(当我使用F5执行sql时，而不是使用ctrl + intro执行).真正的问题在于oo4o Dynaset对象:当我尝试将两个字段的值求和时，得到的是串联，而不是求和:,3 + ,2 = ,3,2. 我想知道的是，是否有一些参数或配置使oracle引擎返回不带零的数字，所以我可以打开| off以将de 0作为整数部分返回.

I checked this behaviour from an aplication that uses the oo4o (oracle object for ole), and confirmed with the sqlDeveloper (when I execute the sql with F5, not with ctrl+intro). The real problem is with the oo4o Dynaset object: when I try to sum the value of two fields, what I get is a concatenation, not a sum: ,3 + ,2 = ,3,2. What I want to know is if there is some parameter or configuration that makes the oracle engine return the number without the zero, so I can turn on|off in order to return de zero as integer part.

推荐答案

SQL * Plus将默认显示(使用您所在地区的小数点分隔符):

SQL*Plus will show that by default (using your territory's decimal separator):

SQL> select 1/3 from dual;

       1/3
----------
.333333333

您可以使用 set numformat 更改行为:

You could use set numformat to change the behaviour:

SQL> set numformat "0D9999"
SQL> select 1/3 from dual;

    1/3
-------
 0.3333

...其中D代表小数点分隔符.或者，您可以使用带有列别名的列格式:

... where the D represents the decimal separator. Or you could use column formatting, with a column alias:

SQL> set numformat ""
SQL> column answer format 0.000
SQL> select 1/3 as answer from dual;

ANSWER
------
 0.333

其他客户端具有不同的控制默认输出的方式； SQL Developer的行为大致相同，但PL/SQL Developer，Toad等可能不一样.

Other clients have different ways of controlling the default output; SQL Developer behaves much the same, but PL/SQL Developer, Toad etc. might not.

或者您可以将数字格式化为查询的一部分，而不是依赖于客户:

Or you can format the number as part of the query, which isn't client-dpendent:

SQL> select to_char(1/3, '9990D99999') from dual;

TO_CHAR(1/3
-----------
    0.33333

您需要为计算中的整数部分提供足够的数字.小于零的值很简单，但是如果小数点分隔符前的位数过多，则根本不会显示该值:

You need to provide enough digits for the integer part of whatever you're calculating though. Anything less than zero is simple, but if there are too many digits before the decimal separator then it won't display at all:

SQL> select to_char(100000/3, '9990D99999') from dual;

TO_CHAR(100
-----------
###########

这篇关于为什么Oracle不显示小数的整数部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！