Oracle alter table的列等

th2d3o14q5

原文地址：http://www.cnblogs.com/david-zhang-index/archive/2012/04/10/2441015.html
对字段操作 操作方法
更新字段名 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_TEMP
54 ---------- ---------- ----------
55 zyt1                1
56 david               2
57
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               ID
69 ---------- ----------
70 zyt1                1
71 david               2
72
73 SQL> desc zyt;
74  Name                                      Null?    Type
75  ----------------------------------------- -------- ----------------------------
76  NAME                                               VARCHAR2(10)
77  ID                                                 NUMBER(10)
复制代码
备注：第二种方法，是增加一个与被修改的列类型一样的列，之后将要修改列的数据复制到新增的列并置空要修改的列，之后修改数据类型，再从新增列将数据拷贝回来，该过程涉及两次数据复制，如果是数据量很多，会比较慢同时也会产生很多undo和redo；优点是数据不会发生行迁移。