将包含数据的 Oracle 列从数字类型更改为 varchar2 类型的 SQL

Question

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

目标: 将表UDA1中列TEST1的类型从number更改为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;

跟索引有关系（为了保留顺序），是吗？

- Din

---

你的最后一个 insert 语句需要改成 update。 - Colin 't Hart

另外，有人告诉我“表格有一个关键字”，我需要导出带有关键字的数据，然后再通过匹配关键字还原回去。这样做正确吗？ - Din

“有人告诉我这个表有一个键”，你知道这个表吗？ - Srini V

请参考@a_horse_with_no_name的答案，那是正确的程序。记得创建索引。由于数字和varchar2不同，顺序可能不同。 - Srini V

请参考手册了解如何创建索引：http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5012.htm#i2062403 - user330315

Answer 1

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;

如果你需要重新创建列，需要在该列上建立索引。

请注意：如果旧列中有大于999的数字，则更新将失败。如果有，您需要调整 varchar 列的最大值。

- user330315

---

你在这里做什么？修改表temp_uda1，添加(test1_new varchar2(3))。 - Din

不应该这样写：alter table UDA1 modify TEST1 VARCHAR2(3); 我想要更改之前的列，而不是添加新的一列。 - Din

@Din：为什么不逐条运行并在每个步骤后检查该表格。然后你就会明白每个语句在做什么。 - user330315

Answer 2

添加一个varchar2类型的新列，将数据复制到这个新列中，删除旧列，将新列重命名为实际的列名：

ALTER TABLE UDA1
ADD (TEST1_temp  VARCHAR2(16));

update UDA1 set TEST1_temp = TEST1;

ALTER TABLE UDA1 DROP COLUMN TEST1;

ALTER TABLE UDA1 
RENAME COLUMN TEST1_temp TO TEST1;

- mkb

Answer 3

看看 Oracle 的 DBMS_REDEFINE 包。如果需要的话，你可以运气好的在线完成无需停机 - 否则你可以：

• 添加新的 VARCHAR2 列
• 使用 update 将 NUMBER 复制到 VARCHAR2
• 删除 NUMBER 列
• 重命名 VARCHAR2 列

- ibre5041

Answer 4

这里是解决方案，它不会影响现有的 NOT NULL 或 Primary key 约束。我将把主键类型从 Number 改为 VARCHAR2(3)，以下是在示例表 employee 上的步骤。

1. 备份表和索引、约束 创建 employee_bkp 表 create table employee_bkp as select * from employee commit;
2. 清空表 truncate table employee
3. 修改表类型 ALTER TABLE employee MODIFY employee_id varchar2(30);
4. 从备份表中复制数据 insert into employee (select * from employee_bkp) commit;
5. 验证

- Kul Bhushan Prasad