一1.天前台人找到我,说我们安装的数据库有问题,为什么小数点前面是0就不显示呢,我去看了一下,command窗口要显示。
SQL> create table ml_test (num number);
Table created
SQL> insert into ml_test values(0.42124);
1 row inserted
SQL> insert into ml_test values(0.4212465434);
1 row inserted
SQL> insert into ml_test values(65432.42124324);
1 row inserted
SQL> insert into ml_test values(4.62124);
1 row inserted
SQL> commit;
Commit complete
--2.直接在数据库查询能直接显示
SQL> select * from ml_test;
NUM
----------
0.42124
0.42124654
65432.4212
4.62124
--3.经过测试,怀疑是number转为char时,舍去了小数点前面的0
SQL> select to_char(num,'999999999.999999999') from ml_test;
TO_CHAR(NUM,'999999999.9999999
------------------------------
.421240000
.421246543
65432.421243240
4.621240000
SQL> select to_char(num) from ml_test;
TO_CHAR(NUM)
----------------------------------------
.42124
.4212465434
65432.42124324
4.62124
SQL> select to_char(0.99) from dual;
TO_CHAR(0.99)
-------------
.99
--4.怎么解决,解决方案如下
(1)
SQL> select to_nmber(to_char(0.99)) from dual;
TO_NUMBER(TO_CHAR(0.99))
------------------------
0.99
SQL> select to_char(num,'fm999999990.999999999') from ml_test;
TO_CHAR(NUM,'FM999999990.99999
------------------------------
0.42124
0.421246543
65432.42124324
4.62124
(2)使用case when 或 decode函数,取第一位是否为".", 补0 ,就ok了
SQL> select to_char(num,'999999999.999999999') from ml_test;
TO_CHAR(NUM,'999999999.9999999
------------------------------
.421240000
.421246543
65432.421243240
4.621240000
SQL> select to_char(num) from ml_test;
TO_CHAR(NUM)
----------------------------------------
.42124
.4212465434
65432.42124324
4.62124
SQL> select to_char(0.99) from dual;
TO_CHAR(0.99)
-------------
.99