Oracle SQL 在包含数据时将列类型从 number 更改为 varchar2

2021-12-30 00:00:00 sql oracle11g oracle

我在 Oracle 11g 中有一个表(包含数据)，我需要使用 Oracle SQLPlus 来执行以下操作:

I have a table (that contains data) in Oracle 11g and I need to use Oracle SQLPlus to do the following:

目标:将UDA1表中TEST1列的类型从number改为varchar2.

Target: change the type of column TEST1 in table UDA1 from number to varchar2.

建议的方法:

  1. 备份表
  2. 将列设置为空
  3. 更改数据类型
  4. 恢复值

以下方法无效.

create table temp_uda1 AS (select * from UDA1); 

update UDA1 set TEST1 = null;
commit;

alter table UDA1 modify TEST1 varchar2(3);

insert into UDA1(TEST1)
  select cast(TEST1 as varchar2(3)) from temp_uda1;
commit;

与索引有关(以保持顺序)，对吗?

There is something to do with indexes (to preserve the order), right?

推荐答案

create table temp_uda1 (test1 integer);
insert into temp_uda1 values (1);

alter table temp_uda1 add (test1_new varchar2(3));

update temp_uda1 
   set test1_new = to_char(test1);

alter table temp_uda1 drop column test1 cascade constraints;
alter table temp_uda1 rename column test1_new to test1;

如果列上有索引，您需要重新创建它.

If there was an index on the column you need to re-create it.

请注意，如果旧列中的数字大于 999，则更新将失败.如果这样做，则需要调整 varchar 列的最大值

Note that the update will fail if you have numbers in the old column that are greater than 999. If you do, you need to adjust the maximum value for the varchar column

相关文章