SQL 更改 Oracle 中数字列的精度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9233909/
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
Changing precision of numeric column in Oracle
提问by roymustang86
Currently I have a column that is declared as a NUMBER. I want to change the precision of the column to NUMBER(14,2).
目前，我有一个声明为 NUMBER 的列。我想将列的精度更改为 NUMBER(14,2)。
SO, I ran the command
所以，我运行了命令
alter table EVAPP_FEES modify AMOUNT NUMBER(14,2)'
for which, I got an error :
为此，我收到了一个错误：
column to be modified must be empty to decrease precision or scale
I am guessing it wants the column to be empty while it changes the precision and I don't know why it says we want to decrease it while we are increasing it, the data in the columns can't be lost. Is there a short workaround for this? I don't want to copy it into another table and drop it afterwards, or rename a column and copy in between columns, because there is a risk of losing data between the transfers and drops.
我猜它希望该列在更改精度时为空，我不知道为什么它说我们要在增加它的同时减少它，列中的数据不能丢失。是否有一个简短的解决方法？我不想将它复制到另一个表然后删除它，或者重命名一个列并在列之间复制，因为在传输和删除之间存在丢失数据的风险。
回答by Allan
Assuming that you didn't set a precision initially, it's assumed to be the maximum (38). You're reducing the precision because you're changing it from 38 to 14.
假设您最初没有设置精度，则假定为最大值 (38)。您正在降低精度，因为您将其从 38 更改为 14。
The easiest way to handle this is to rename the column, copy the data over, then drop the original column:
处理此问题的最简单方法是重命名列，复制数据，然后删除原始列：
alter table EVAPP_FEES rename column AMOUNT to AMOUNT_OLD;
alter table EVAPP_FEES add AMOUNT NUMBER(14,2);
update EVAPP_FEES set AMOUNT = AMOUNT_OLD;
alter table EVAPP_FEES drop column AMOUNT_OLD;
If you really want to retain the column ordering, you can move the data twice instead:
如果你真的想保留列顺序，你可以移动数据两次：
alter table EVAPP_FEES add AMOUNT_TEMP NUMBER(14,2);
update EVAPP_FEES set AMOUNT_TEMP = AMOUNT;
update EVAPP_FEES set AMOUNT = null;
alter table EVAPP_FEES modify AMOUNT NUMBER(14,2);
update EVAPP_FEES set AMOUNT = AMOUNT_TEMP;
alter table EVAPP_FEES drop column AMOUNT_TEMP;
回答by ron tornambe
By setting the scale, you decrease the precision. Try NUMBER(16,2).
通过设置比例，您会降低精度。尝试 NUMBER(16,2)。
回答by DJ Dev J
If the table is compressed this will work:
如果表被压缩，这将起作用：
alter table EVAPP_FEES add AMOUNT_TEMP NUMBER(14,2);
update EVAPP_FEES set AMOUNT_TEMP = AMOUNT;
update EVAPP_FEES set AMOUNT = null;
alter table EVAPP_FEES modify AMOUNT NUMBER(14,2);
update EVAPP_FEES set AMOUNT = AMOUNT_TEMP;
alter table EVAPP_FEES move nocompress;
alter table EVAPP_FEES drop column AMOUNT_TEMP;
alter table EVAPP_FEES compress;
