Oracle 修改列的数据类型

转自：david_zhang@sh 致谢！

对字段操作操作方法更新字段名alter table TABLE_NAME rename column column_old to column_new;添加字段alter table TABLE_NAME add COLUMN_NAME varchar(10);删除字段alter table TABLE_NAME drop column COLUMN_NAME;添加字段并附值alter table TABLE_NAME ADD COLUMN_NAME NUMBER(1) DEFAULT 1;修改字段值update TABLE_NAME set filedname=value where filedname=value;修改字段数据类型alter table tablename modify filedname varchar2(20);

1 SQL> select * from v$version;2 3 BANNER4 --------------------------------------------------------------------------------5 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production6 PL/SQL Release 11.2.0.1.0 - Production7 CORE    11.2.0.1.0      Production8 TNS for Linux: Version 11.2.0.1.0 - Production9 NLSRTL Version 11.2.0.1.0 - Production

1.修改字段数据类型时，如果该列有数据则报ORA-01439: column to be modified must be empty to change datatype，此时需要通过另外一种方法修改：

 1 SQL> alter table zyt add id_temp varchar2(10); 2  3 Table altered. 4  5 SQL> commit; 6  7 Commit complete. 8  9 SQL> select * from zyt;10 11 NAME               ID ID_TEMP12 ---------- ---------- ----------13 zyt1                114 david               215 16 SQL> alter table zyt rename column id to id_bak;17 18 Table altered.19 20 SQL> select * from zyt;21 22 NAME           ID_BAK ID_TEMP23 ---------- ---------- ----------24 zyt1                125 david               226 27 SQL> desc zyt;28  Name                                      Null?    Type29  ----------------------------------------- -------- ----------------------------30  NAME                                               VARCHAR2(10)31  ID_BAK                                    NOT NULL NUMBER(2)32  ID_TEMP                                            VARCHAR2(10)33 34 SQL> update zyt set ID_TEMP = cast(ID_BAK as varchar2(10));35 36 2 rows updated.37 38 SQL> commit;39 40 Commit complete.41 42 SQL> select * from zyt;43 44 NAME           ID_BAK ID_TEMP45 ---------- ---------- ----------46 zyt1                1 147 david               2 248 49 SQL>  alter table zyt drop column ID_BAK;50 51 Table altered.52 53 SQL> commit;54 55 Commit complete.56 57 SQL> select * from zyt;58 59 NAME       ID_TEMP60 ---------- ----------61 zyt1       162 david      263 64 SQL> desc zyt;65  Name                                      Null?    Type66  ----------------------------------------- -------- ----------------------------67  NAME                                               VARCHAR2(10)68  ID_TEMP                                            VARCHAR2(10)

备注：这种方法能满足需求，因新增字段默认添加到表末尾，有可能发生行迁移,对应用程序会产生影响，同时也涉及复杂数据，不算最好的方法

2.建立一个中间跳板，临时存储数据

 1 SQL> desc zyt; 2  Name                                      Null?    Type 3  ----------------------------------------- -------- ---------------------------- 4  NAME                                               VARCHAR2(10) 5  ID                                                 VARCHAR2(10) 6  7 SQL> select * from zyt; 8  9 NAME       ID10 ---------- ----------11 zyt1       112 david      213 14 SQL> alter table zyt add id_temp VARCHAR2(10)15 16 Table altered.17 18 SQL> select * from zyt;19 20 NAME       ID            ID_TEMP21 ---------- ---------- ----------22 zyt1       123 david      224 25 SQL> update zyt set ID_TEMP=id,id=null;26 27 2 rows updated.28 29 SQL> select * from zyt;30 31 NAME       ID            ID_TEMP32 ---------- ---------- ----------33 zyt1                           134 david                          235 36 SQL> alter table zyt modify id number(10);37 38 Table altered.39 40 SQL> desc zyt;41  Name                                      Null?    Type42  ----------------------------------------- -------- ----------------------------43  NAME                                               VARCHAR2(10)44  ID                                                 NUMBER(10)45  ID_TEMP                                            VARCHAR2(10)46 47 SQL> update zyt set id=ID_TEMP,ID_TEMP=null;48 49 2 rows updated.50 51 SQL> select * from zyt;52 53 NAME               ID    ID_TEMP54 ---------- ---------- ----------55 zyt1                156 david               257 58 SQL> alter table zyt drop column ID_TEMP;59 60 Table altered.61 62 SQL> commit;63 64 Commit complete.65 66 SQL> select * from zyt;67 68 NAME               ID69 ---------- ----------70 zyt1                171 david               272 73 SQL> desc zyt;74  Name                                      Null?    Type75  ----------------------------------------- -------- ----------------------------76  NAME                                               VARCHAR2(10)77  ID                                                 NUMBER(10)

备注：第二种方法，是增加一个与被修改的列类型一样的列，之后将要修改列的数据复制到新增的列并置空要修改的列，之后修改数据类型，再从新增列将数据拷贝回来，该过程涉及两次数据复制，如果是数据量很多，会比较慢同时也会产生很多undo和redo；优点是数据不会发生行迁移。