避免在 DB Float 列中插入指数值

2022-01-09 00:00:00 floating-point sql database insert oracle

我有一张这样的桌子

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)

插入语句为:

Insert into  contact("CONTACT_ID","NAME","EMAIL","ADDRESS","TELEPHONE","AMOUNT")
values ('36','babusailesh1117777','hainatu1@gmail.com','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?

推荐答案

它不是以指数形式存储的，而是以 Oracle 内部数字表示形式存储的.当您查询它时，它会以您会话的当前数字格式显示:

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

您可以在 SQL*Plus 或 SQL Developer 中覆盖:

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.

您也不应该将字符串插入到浮点列中，这只是进行隐式转换；插入中的最后一个参数应该是 0.0000000555559080767 而不是引用的 '0.0000000555559080767'.

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'.

相关文章