Oracle：如何舍入和更新表中的所有数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19260595/
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: How to round and update all numbers in a table?
提问by florian.isopp
I have a table with several columns, with DATA_TYPE FLOAT, NUMBER. There are whole numbers and decimal digits with decimal places.
e.g.234, 4, 0, 23.000000004, 234,4444, ...
我有一个包含多列的表，其中包含 DATA_TYPE FLOAT、NUMBER。有整数和带小数位的小数位。
例如234, 4, 0, 23.000000004, 234,4444, ...
Assignment:
任务：
I want that the numbers have 2 decimal placesat maximum. If more, then round up resp. off!
The wish is, to execute the .sqlscript via sqldeveloper. Easier approaches are welcome!
我想这些数字有2位小数在最大。如果更多，则四舍五入。离开！
希望是，通过sqldeveloper执行.sql脚本。欢迎使用更简单的方法！
Synopsis:
概要：
- ROUND numbers if they exceed 2 decimal places
- UPDATE the value
- utilization on several choosen columns
- .sql script
- sqldeveloper preferred
- 如果超过 2 位小数，则为圆形数字
- 更新值
- 几个选定列的利用率
- .sql 脚本
- sqldeveloper 首选
回答by Justin Cave
The simplest possible thing would by something like
最简单的事情是像
UPDATE table_name
SET column1_name = round(column1_name, 2 ),
column2_name = round(column2_name, 2 ),
...
columnN_name = round(columnN_name, 2 )
where you enter however many columns you want to modify. If you want to dynamically generate the script, you could write an anonymous PL/SQL block that used the dba|all|user_tab_columnsdata dictionary view to generate the appropriate SQL statement for each table and use EXECUTE IMMEDIATEor DBMS_SQLto execute the dynamically generated SQL statement. That's quite a bit more effort to write, debug, and maintain, though so it's probably only worthwhile if you want it to work automatically in the future when new columns are added to the table.
您可以在其中输入要修改的列数。如果要动态生成脚本，可以编写一个匿名 PL/SQL 块，该块使用dba|all|user_tab_columns数据字典视图为每个表生成适当的 SQL 语句，并使用EXECUTE IMMEDIATE或DBMS_SQL执行动态生成的 SQL 语句。这需要更多的编写、调试和维护工作，但如果您希望它在将来向表中添加新列时自动工作，那么这可能只是值得的。
If you have FLOATcolumns, be aware that floats are inherently imprecise. Even if you round to 2 decimal digits, there is no guarantee that the value that is stored will always be 2 decimal digits. You may find values that are infinitessimally largers or smaller than you'd expect. If you really want to ensure that all numbers have 2 a particular precision, those columns should be defined as numbers not floats.
如果您有FLOAT列，请注意浮点数本质上是不精确的。即使您舍入到 2 个十进制数字，也不能保证存储的值始终是 2 个十进制数字。您可能会发现值比您预期的大或小。如果您真的想确保所有数字都具有 2 特定精度，则应将这些列定义为数字而不是浮点数。
