如何使用表中的现有行修改Oracle中的数据类型 [英] How to modify data type in Oracle with existing rows in table

问题描述

如何在不删除表数据的情况下将列的数据类型从number更改为varchar2?

How can I change DATA TYPE of a column from number to varchar2 without deleting the table data?

推荐答案

您不能.

但是，您可以使用新的数据类型创建新列，迁移数据，删除旧列并重命名新列.像

You can, however, create a new column with the new data type, migrate the data, drop the old column, and rename the new column. Something like

ALTER TABLE table_name
  ADD( new_column_name varchar2(10) );

UPDATE table_name
   SET new_column_name = to_char(old_column_name, <<some format>>);

ALTER TABLE table_name
 DROP COLUMN old_column_name;

ALTER TABLE table_name
 RENAME COLUMN new_column_name TO old_coulumn_name;

如果您有依赖于表中列位置的代码(您本不应该拥有)，则可以重命名表并在表上创建一个视图，并使用表的原始名称公开按您的代码期望的顺序排列列，直到您可以修复该错误代码为止.

If you have code that depends on the position of the column in the table (which you really shouldn't have), you could rename the table and create a view on the table with the original name of the table that exposes the columns in the order your code expects until you can fix that buggy code.

这篇关于如何使用表中的现有行修改Oracle中的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！