Avoid inserting exponential value in DB Float colu

问题:

I have a table like

CONTACT

Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- -----------
CONTACT_ID                     NOT NULL NUMBER                                                                                                                                                                                        
NAME                           NOT NULL VARCHAR2(45)                                                                                                                                                                                  
EMAIL                          NOT NULL VARCHAR2(45)                                                                                                                                                                                  
ADDRESS                        NOT NULL VARCHAR2(45)                                                                                                                                                                                  
TELEPHONE                      NOT NULL VARCHAR2(45)                                                                                                                                                                                  
AMOUNT                         NOT NULL FLOAT(126) 

and the insert statement is:

Insert into  contact("CONTACT_ID","NAME","EMAIL","ADDRESS","TELEPHONE","AMOUNT")
values ('36','babusailesh1117777','[e-mail]','Vietnam1',
  '0904277091','0.0000000555559080767');

When I see the final value inserted in the database, I see an exponential value.

How can I avoid this at database level for all tables?

回答1:

It isn't stored as an exponential, it's stored in Oracle's internal number representation. When you query it, it's displayed with your sessions's current number format:

select 0.0000000555559080767 from dual;

                  0.0000000555559080767
---------------------------------------
                                5.6E-08

Which you can override in SQL*Plus or SQL Developer:

set numformat 999.9999999999999999999
select 0.0000000555559080767 from dual;

   0.0000000555559080767
------------------------
    .0000000555559080767

Or explicitly format the value as a string, for display only:

set numf ""
select to_char(0.0000000555559080767, '9990.9999999999999999999') from dual;

TO_CHAR(0.000000055555908
-------------------------
    0.0000000555559080767

If you have a client application retrieving and using the value then it should query it as a float, into a suitable data type for the client's language, and then it's up to the client how it's displayed.

---

You also shouldn't be inserting a string into the float column, that just does an implicit conversion; the last argument in your insert should be 0.0000000555559080767 rather than the quoted '0.0000000555559080767'.

回答2:

This is a function of the way the value is displayed, not what is being stored in the database. The database just stores a numeric float value, and whatever retrieves and displays the data decides how to display that. The exponential value you mention is common for displaying such floating point numbers.

You can have Oracle return a string formatted in a way for display using a function such as to_char, that allows you to set formatting. However, it may make more sense to handle this in whatever program or code is using the database.

Also note that floating point numbers have limited precision, so if you need to get that exact number string back, a float is not the way to go.