oracle小数点前面没有0，纠结解惑

一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