SQL 十进制数的 Oracle ceil
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3682781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle ceil for decimal numbers
提问by K Ratnajyothi
When rounding up to 2 decimal places, the value 4.01132141 would be rounded to 4.02 because it exceeds 4.01.
当四舍五入到 2 个小数位时，值 4.01132141 将四舍五入为 4.02，因为它超过了 4.01。
How can you do this in PL/SQL?
你怎么能在 PL/SQL 中做到这一点？
回答by Alex Poole
One way would be to do ceil(value*100)/100, but that seems inelegant. Not sure there's any way to make roundbehave the way you want.
一种方法是 do ceil(value*100)/100，但这似乎不雅。不确定有什么方法可以round按照您想要的方式行事。
回答by Jonathan Leffler
The function to 'round up' is CEIL, but it generates an integer.
“向上舍入”的函数是CEIL，但它生成一个整数。
The function to 'round down' is FLOOR, but it too generates an integer.
“四舍五入”的函数是FLOOR，但它也会生成一个整数。
The function to 'round nearest' is ROUND, and it allows you to specify a number of decimal places (dp).
“舍入最近”的函数是ROUND，它允许您指定小数位数 (dp)。
Note that CEIL rounds to an integer; to round to 2 dp, you'd have to multiply by 100, use CEIL, and divide by 100.
请注意，CEIL 四舍五入为整数；要四舍五入到 2 dp，您必须乘以 100，使用 CEIL，然后除以 100。
To get the answer reasonably directly, use:
要直接合理地获得答案，请使用：
ROUND(value+0.005, 2)
This works because, for the example data of 4.01132141, the value passed to ROUND is 4.01632, and when rounded to 2 dp, that becomes 4.02. If the value started as 4.0593, say, then the value passed to ROUND would be 4.0643, which when rounded to 2 dp becomes 4.06, as required.
这是有效的，因为对于 4.01132141 的示例数据，传递给 ROUND 的值为 4.01632，当四舍五入为 2 dp 时，该值为 4.02。如果值从 4.0593 开始，那么传递给 ROUND 的值将是 4.0643，当四舍五入到 2 dp 时，根据需要变为 4.06。
There are a couple of tricky bits there:
有几个棘手的地方：
- If the number of dp varies, the value to be added (0.005 in the example) varies. You could create a table to hold the number of decimal places in one column and the rounding value to add in the other. Alternatively, you could use an expression with powers of 10, etc.
- Deciding on the correct behaviour for negative numbers. Does -4.01132141 become -4.02 or -4.01? You might need to play with SIGN and ABS functions to get that to work as you want.
- 如果 dp 的数量不同，则要添加的值（示例中为 0.005）也不同。您可以创建一个表格来保存一列中的小数位数和要添加到另一列中的舍入值。或者，您可以使用具有 10 次幂等的表达式。
- 决定负数的正确行为。-4.01132141 会变成 -4.02 还是 -4.01？您可能需要使用 SIGN 和 ABS 功能才能让它按您的意愿工作。
回答by Alex Deux
I faced the same issue and came up with the following statement, it has worked fine so far.
我遇到了同样的问题并提出了以下声明，到目前为止它运行良好。
select 4.01132141+(mod((ceil(4.01132141)-4.01132141)*1000,10)/1000) from dual
回答by Joy Ruhe
select 4.01132141, CEIL(4.01132141*100)/100 from dual
选择 4.01132141, CEIL(4.01132141*100)/100 从双
回答by Jaydip Mehta
You can use this for Rounding up in PLSQL: ROUND( UrNo+ (5 / POWER(10, DecimalPlaces + 1)) , DecimalPlaces)
您可以使用它在 PLSQL 中进行四舍五入： ROUND( UrNo+ (5 / POWER(10, DecimalPlaces + 1)) , DecimalPlaces)
