Oracle SQL将包含数据的列类型从数字更改为varchar2 [英] Oracle SQL to change column type from number to varchar2 while it contains data

问题描述

我在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

这篇关于Oracle SQL将包含数据的列类型从数字更改为varchar2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！