在Oracle中更改数字列的精度 [英] Changing precision of numeric column in Oracle

问题描述

当前，我有一列被声明为NUMBER.我想将列的精度更改为NUMBER(14,2).

Currently I have a column that is declared as a NUMBER. I want to change the precision of the column to NUMBER(14,2).

所以，我运行了命令

 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.

推荐答案

假定您最初没有设置精度，则假定为最大值(38).您正在将精度从38更改为14.

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.

处理此问题的最简单方法是重命名列，将数据复制过来，然后删除原始列:

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;

这篇关于在Oracle中更改数字列的精度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！