Oracle如何修改列的数据类型
对字段操作
操作方法
更新字段名 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 BANNER 4 -------------------------------------------------------------------------------- 5 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 6 PL/SQL Release 11.2.0.1.0 - Production 7 CORE 11.2.0.1.0 Production 8 TNS for Linux: Version 11.2.0.1.0 - Production 9 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_TEMP 12 ---------- ---------- ---------- 13 zyt1 1 14 david 2 15 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_TEMP 23 ---------- ---------- ---------- 24 zyt1 1 25 david 2 26 27 SQL> desc zyt; 28 Name Null Type 29 ----------------------------------------- -------- ---------------------------- 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_TEMP 45 ---------- ---------- ---------- 46 zyt1 1 1 47 david 2 2 48 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_TEMP 60 ---------- ---------- 61 zyt1 1 62 david 2 63 64 SQL> desc zyt; 65 Name Null Type 66 ----------------------------------------- -------- ---------------------------- 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 ID
10 ---------- ----------
11 zyt1 1
12 david 2
13
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_TEMP
21 ---------- ---------- ----------
22 zyt1 1
23 david 2
24
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_TEMP
32 ---------- ---------- ----------
33 zyt1 1
34 david 2
35
36 SQL> alter table zyt modify id number(10);
37
38 Table altered.
39
40 SQL> desc zyt;
41 Name Null Type
42 ----------------------------------------- -------- ----------------------------
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_TE