本帖最后由 645930266 于 2014-7-24 10:29 编辑
测试了一下,看看你是不是LZ需要的
SQL> create table test_list
2 (city_id NUMBER(5),
3 city_name VARCHAR2(30),
4 city_state VARCHAR2(20),
5 city_amount NUMBER(10)
6 )
7 partition by list (city_name)
8 (
9 partition asia VALUES('china','japan'),
10 partition europe VALUES ('germany','italy'),
11 partition africa VALUES('libya','brazil'),
12 partition other VALUES(DEFAULT)
13 );
Table created.
SQL> desc test_list
Name Null? Type
----------------------------------------- -------- ----------------------------
CITY_ID NUMBER(5)
CITY_NAME VARCHAR2(30)
CITY_STATE VARCHAR2(20)
CITY_AMOUNT NUMBER(10)
insert into test_list values(1,'china','asia',100);
insert into test_list values(2,'germany','europe',101);
insert into test_list values(3,'libya','africa',102);
insert into test_list values(4,'liusheng_city','other',103);
SQL> select * from test_list;
CITY_ID CITY_NAME CITY_STATE CITY_AMOUNT
---------- ------------------------------ -------------------- -----------
1 china asia 100
2 germany europe 101
3 libya africa 102
4 liusheng_city other 103
增加一列
SQL> alter table test_list add city_amount_var2 varchar2(10);
Table altered.
SQL> select * from test_list;
CITY_ID CITY_NAME CITY_STATE CITY_AMOUNT CITY_AMOUN
---------- ---------- -------------------- ----------- ----------
1 china asia 100
2 germany europe 101
3 libya africa 102
4 liusheng_c other 103
ity
更新数据
SQL> update test_list set city_amount_var2=(case when city_amount_var2 is null then city_amount else null end);
SQL> commit;
Commit complete.
SQL> select * from test_list;
CITY_ID CITY_NAME CITY_STATE CITY_AMOUNT CITY_AMOUN
---------- ---------- -------------------- ----------- ----------
1 china asia 100 100
2 germany europe 101 101
3 libya africa 102 102
4 liusheng_c other 103 103
ity
删除原有列
SQL> alter table test_list drop column city_amount;
Table altered.
重命名新列为原列名
SQL> alter table test_list rename column city_amount_var2 to city_amount;
Table altered.
SQL> select * from test_list;
CITY_ID CITY_NAME CITY_STATE CITY_AMOUN
---------- ---------- -------------------- ----------
1 china asia 100
2 germany europe 101
3 libya africa 102
4 liusheng_c other 103
ity
SQL> desc test_list;
Name Null? Type
----------------------------------------- -------- ----------------------------
CITY_ID NUMBER(5)
CITY_NAME VARCHAR2(30)
CITY_STATE VARCHAR2(20)
CITY_AMOUNT VARCHAR2(10)