更改字段类型-将Number修改成varchar2类型

 

实际开发中，添加字段，更改字段类型是常有的事，最近碰到的是数据库中有一个字段ABWORKBYRARATE ，类型为Number(10,5)，需要改成字符串类型varchar2(30)，而且表中已经有很多数据了，直接改报错，改不了。

解决方案如下：

1. 重命名原字段

alter table CDS_IMPORT_FORWARDCHARGE rename column ABWORKBYRARATE to ABWORKBYRARATE123;

2. 增加字段和注释
alter table CDS_IMPORT_FORWARDCHARGE add ABWORKBYRARATE varchar2(30);
comment on column CDS_IMPORT_FORWARDCHARGE.ABWORKBYRARATE is '国外段操作费';

3. 将原字段的值赋值给新字段
update cds_import_forwardcharge t
set t.abworkbyrarate=t.abworkbyrarate123;

4. 删除原字段：
alter table CDS_IMPORT_FORWARDCHARGE drop column ABWORKBYRARATE123;

基本就这样，不过发现一个问题，小于1 的数如0.20000，变成了.20000，小数点前面的0不见了，解决如下：

思路就是将以.开头的值前面加0。

update cds_import_forwardcharge t
set t.abworkbyrarate=(select replace(t.abworkbyrarate,'.','0.')from dual)
where t.abworkbyrarate is not null and ((select instr(t.abworkbyrarate,'.') from dual)=1);

小结一下：新增的字段替换了原字段，与原表相比，新增字段在表中的顺序发生变化，为最后一位，可能会影响存储过程（例如记录的新增）。

不妨试想一下，如果需要更改的字段的类型比较多，怎麽办？

字段级的更改显得有点麻烦，罗嗦了，所以可以考虑上升到表级别来解决问题，步骤如下：

使用客户端工具如PL/SQL Developer做比较容易，部分sql就不再贴出了。

1. 重命名原表

随便加个1就行

2. 创建新表

表名为原表以前的名字

3. 更改新表的字段类型

4. 从原表复制数据到新表

ctrl+c、ctrl+v搞定

5. 为新表添加约束及其他（如外键、索引），如果重名，先重命名原表的约束名称

6. 删除原表

小结一下：与上一种方法相比，表中各字段顺序不变，影响极小。

看看成果吧：

怎么看着不舒服啊，都是字符串，0.00000、0.70000算什么啊，应该是0、0.7啊。

进一步完善成果：

1. 结果格式化

update cds_import_forwardcharge t
set t.ABAIRFREIGHTRATE=to_char(to_number(t.ABAIRFREIGHTRATE),'fm999999999999990.099999'),
t.ABDEVCHARGESRATE=to_char(to_number(t.ABDEVCHARGESRATE),'fm999999999999990.099999'),
t.ABOTHERSRATE=to_char(to_number(t.ABOTHERSRATE),'fm999999999999990.099999'),
t.ABWORKBYTKRATE=to_char(to_number(t.ABWORKBYTKRATE),'fm999999999999990.099999'),
t.FREIGHTRATE=to_char(to_number(t.FREIGHTRATE),'fm999999999999990.099999'),
t.AIRFREIGHTRATE=to_char(to_number(t.AIRFREIGHTRATE),'fm999999999999990.099999'),
t.FUELRATE=to_char(to_number(t.FUELRATE),'fm999999999999990.099999'),
t.CAFRATE=to_char(to_number(t.CAFRATE),'fm999999999999990.099999'),
t.DOFEERATE=to_char(to_number(t.DOFEERATE),'fm999999999999990.099999'),
t.COSTSRATE=to_char(to_number(t.COSTSRATE),'fm999999999999990.099999'),
t.WAREHOUSINGRATE=to_char(to_number(t.WAREHOUSINGRATE),'fm999999999999990.099999'),
t.INOUTRATE=to_char(to_number(t.INOUTRATE),'fm999999999999990.099999'),
t.TALLYINGFEERATE=to_char(to_number(t.TALLYINGFEERATE),'fm999999999999990.099999'),
t.HANDLINGFEERATE=to_char(to_number(t.HANDLINGFEERATE),'fm999999999999990.099999'),
t.DISPATCHFEERATE=to_char(to_number(t.DISPATCHFEERATE),'fm999999999999990.099999'),
t.THCRATE=to_char(to_number(t.THCRATE),'fm999999999999990.099999'),
t.YSFEERATE=to_char(to_number(t.YSFEERATE),'fm999999999999990.099999'),
t.DANGEROUSFEERATE=to_char(to_number(t.DANGEROUSFEERATE),'fm999999999999990.099999');

比刚才看着舒服点吧。

2. 删除最后两位.0

以字段ABDEVCHARGESRATE为例：

update cds_import_forwardcharge t
set t.ABDEVCHARGESRATE=(select replace(t.ABDEVCHARGESRATE,'.0','')from dual)
where t.ABDEVCHARGESRATE is not null and ((select instr(t.ABDEVCHARGESRATE,'.0') from dual)=length(t.ABDEVCHARGESRATE)-1);

OK了，呵呵。