ORALCE中修改列的类型:
方法一:create table 的方式
SQL> desc tt
Name Type Nullable Default Comments
----- ------ -------- ------- --------
ID NUMBER Y
MOUNT NUMBER Y
SQL> alter table tt modify(id varchar2(4));
alter table tt modify(id varchar2(4))
ORA-01439: column to be modified must be empty to change datatype
注释:如果直接修改会报错
SQL> create table t1 as select * from tt where 1=2;
SQL> alter table t1 modify(id varchar2(4));
SQL> insert into t1 select * from tt;
SQL> desc t1
Name Type Nullable Default Comments
----- ----------- -------- ------- --------
ID VARCHAR2(4) Y
MOUNT NUMBER Y
SQL> drop table tt purge;
SQL> rename t1 to tt;
方法2:update的方式
SQL> desc tt
Name Type Nullable Default Comments
----- ------ -------- ------- --------
ID NUMBER Y
MOUNT NUMBER Y
SQL> alter table tt add(id2 varchar2(4));
SQL> update tt set id2=id;
SQL> select * from tt;
ID MOUNT ID2
---------- ---------- ----
1 60 1
2 150 2
3 240 3
4 210 4
5 440 5
SQL> update tt set id=null;
SQL> select * from tt;
ID MOUNT ID2
---------- ---------- ----
60 1
150 2
240 3
210 4
440 5
SQL> alter table tt modify(id varchar2(4));
SQL> update tt set id=id2;
SQL> alter table tt drop column id2;
SQL> desc tt;
Name Type Nullable Default Comments
----- ----------- -------- ------- --------
ID VARCHAR2(4) Y
MOUNT NUMBER Y
SQL> select * from tt;
ID MOUNT
---- ----------
1 60
2 150
3 240
4 210
5 440