如何使用表中的现有行修改 Oracle 中的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28791470/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to modify data type in Oracle with existing rows in table
提问by ammar zaheer
How can I change DATA TYPE of a column from numberto varchar2without deleting the table data?
如何number在varchar2不删除表数据的情况下将列的数据类型从 更改为？
回答by Justin Cave
You can't.
你不能。
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.
如果您的代码取决于表中列的位置（您确实不应该这样做），您可以重命名表并使用表的原始名称在表上创建一个视图，以公开列中的列订购您的代码期望，直到您可以修复该错误代码。
回答by Lalit Kumar B
You have to first deal with the existing rowsbefore you modify the column DATA TYPE.
在修改列DATA TYPE之前，您必须首先处理现有行。
You could do the following steps:
您可以执行以下步骤：
- Add the new column with a new name.
- Update the new column from old column.
- Drop the old column.
- Rename the new column with the old column name.
- 添加具有新名称的新列。
- 从旧列更新新列。
- 删除旧列。
- 用旧列名重命名新列。
For example,
例如，
alter table t add (col_new varchar2(50)); update t set col_new = to_char(col_old); alter table t drop column col_old cascade constraints; alter table t rename column col_new to col_old;
Make sure you re-create any required indexeswhich you had.
确保重新创建您拥有的所有必需索引。
You could also try the CTASapproach, i.e. create table as select. But, the above is safe and preferrable.
您也可以尝试CTAS方法，即创建表作为选择。但是，以上是安全和可取的。
回答by BobC
The most efficient way is probably to do a CREATE TABLE ... AS SELECT
(CTAS)
最有效的方法可能是做一个 CREATE TABLE ... AS SELECT
(CTAS)
回答by Amit Jain
alter table table_name modify (column_name VARCHAR2(255));
