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

来源：互联网发布：lte网优日常优化面试编辑：程序博客网时间：2024/06/05 18:06

一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.421240.4212465465432.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.421246543465432.421243244.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.421240.42124654365432.421243244.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.421246543465432.421243244.62124 SQL> select to_char(0.99) from dual; TO_CHAR(0.99)-------------.99