在插入数据库之前截断值

发布于 2025-01-07 04:58:17 字数 1083 浏览 1 评论 0原文

我们正在尝试修改数据库中现有列的精度。那些已经定义为NUMBER的，我们想将其更改为NUMBER(14,2)。

但是，由于 NUMBER 的默认精度为 38，因此数据库中存在小数点后 10 位以上的值。因此，当我们创建附加列并尝试从临时表复制时，这会导致错误。

 select count(*) into countCol from USER_TAB_COLUMNS where TABLE_NAME = 'EVAPP_INTERFACE' and COLUMN_NAME = 'RESERVE_RATE_NUM' and DATA_SCALE is null; 
 IF (countCol <> 0) then   

 execute immediate 'alter table EVAPP_INTERFACE add RESERVE_RATE_NUM_TMP NUMBER(6,3)' ; 

 execute immediate 'update EVAPP_INTERFACE set RESERVE_RATE_NUM_TMP = RESERVE_RATE_NUM' ; 

 execute immediate 'alter table EVAPP_INTERFACE drop column RESERVE_RATE_NUM' ; 

 execute immediate 'alter table EVAPP_INTERFACE rename column RESERVE_RATE_NUM_TMP to RESERVE_RATE_NUM' ; 


 DBMS_OUTPUT.put_line('This column EVAPP_INTERFACE.RESERVE_RATE_NUM has been modified to the required precision');

有没有办法截断列中的所有值？

就像说一列

43.8052201822
21.1610909091
76.4761223618
75.8535613657

我希望它们全部更改为

“编辑”：我知道“截断”这个词使用错误，但我不知道有更好的术语来削减精度。

原文

We are trying to modify the precision on existing columns in the database. Those which have been defined as NUMBER, we want to change it to NUMBER(14,2).

But, since NUMBER has a default precision of 38, there exist values in the database which run into more than 10 decimal places. So, when we create an additional column and try to copy over from a temp table, this results in errors.

 select count(*) into countCol from USER_TAB_COLUMNS where TABLE_NAME = 'EVAPP_INTERFACE' and COLUMN_NAME = 'RESERVE_RATE_NUM' and DATA_SCALE is null; 
 IF (countCol <> 0) then   

 execute immediate 'alter table EVAPP_INTERFACE add RESERVE_RATE_NUM_TMP NUMBER(6,3)' ; 

 execute immediate 'update EVAPP_INTERFACE set RESERVE_RATE_NUM_TMP = RESERVE_RATE_NUM' ; 

 execute immediate 'alter table EVAPP_INTERFACE drop column RESERVE_RATE_NUM' ; 

 execute immediate 'alter table EVAPP_INTERFACE rename column RESERVE_RATE_NUM_TMP to RESERVE_RATE_NUM' ; 


 DBMS_OUTPUT.put_line('This column EVAPP_INTERFACE.RESERVE_RATE_NUM has been modified to the required precision');

Is there any way to truncate all values in a column?

Like say a column has

43.8052201822
21.1610909091
76.4761223618
75.8535613657

I want them all changed to

EDIT : I know the word Truncate is used wrongly, but I don't know of a better term to shaving off precision.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

洛阳烟雨空心柳 2025-01-14 04:58:17

完全没有错，请参阅：TRUNC(number)。

从下面的示例中您可以看到截断和舍入的区别：

create table foo(n number);

insert all
into foo values (1.111)
into foo values (5.555)
into foo values (9.999)
select * from dual;

select n, round(n,2), trunc(n, 2) from foo;

         N ROUND(N,2) TRUNC(N,2)
---------- ---------- ----------
     1.111       1.11       1.11
     5.555       5.56       5.55
     9.999         10       9.99

Not a wrong word at all, see: TRUNC(number).

From the example below you can see the difference of truncating and rounding:

create table foo(n number);

insert all
into foo values (1.111)
into foo values (5.555)
into foo values (9.999)
select * from dual;

select n, round(n,2), trunc(n, 2) from foo;

         N ROUND(N,2) TRUNC(N,2)
---------- ---------- ----------
     1.111       1.11       1.11
     5.555       5.56       5.55
     9.999         10       9.99

回复收藏 0 原文