如何使用表中的现有行修改 Oracle 中的数据类型

2021-12-30 00:00:00 oracle11g type-conversion oracle

如何在不删除表数据的情况下将列的数据类型从 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.

相关文章