带有科学符号的Oracle圆形函数

Question

Dpes有人知道如何在Oracle中实现这一点吗？该值是表中的值，我需要将其四舍五入到2位：

Select round(3.00345678908765E+60, 2) from dual

Answer 1

您需要将四舍五入移动到小数点后60位(因此从+2到-58(：

Select round( 3.00345678908765E+60, -58) from dual

输出：

|ROUND(3.00345678908765E+60，-58(||------------------------------------------------------------：||3000000000000000000000000000000000000000000000000|

如果要四舍五入到3个有效数字：

SELECT ROUND( value, 2 - FLOOR( LOG( 10, value ) ) ) FROM table_name;

对于样本数据：

CREATE TABLE table_name ( value ) AS
SELECT 3.00345678908765E+60 FROM DUAL UNION ALL
SELECT 3.00555678908765E+60 FROM DUAL UNION ALL
SELECT 3.05345678908765E+58 FROM DUAL;

输出：

|ROUND(VALUE，2-FLOOR(LOG(10，VALUE((||------------------------------------------------------------：||3000000000000000000000000000000000000000000000000||3010000000000000000000000000000000000000000000000000000000000000000||30500000000000000000000000000000000000000000000000000000000000000|

或者您可以使用TO_CHAR将值转换为以科学记数法取整的字符串：

SELECT TO_CHAR( value, 'FM0.00EEEE' ) AS as_string,
TO_NUMBER(TO_CHAR( value, 'FM0.00EEEE' )) AS as_number
FROM   table_name;

输出：

AS_STRING|AS_NUMBER：---------|------------------------------------------------------------：3.00E+60|30000000000000000000000000000000000000000000000003.01E+60 | 301000000000000000000000000000000000000000000000000000000000000000003.05E+58|30500000000000000000000000000000000000000000000000000000000000000

db<gt；小提琴这里