最近业务部门提出一个需求, 数据库里有些字段中保存的信息里全角和半角字符参杂, 影响市容, 要求做数据订正, 全部统一.
其中英文/数字/英文标点符号 都统一用半角字符, 而日文字符都统一用全角字符. 而且提出订正的字段中还包含一些clob字段.
 
那就函数+游标, 慢慢订正吧, 分析了一下, 要写四个函数:
1. varchar2数据类型的, 全角英文变半角.
2. clob数据类型的, 全角英文变半角.
3. varchar2数据类型的, 半角日文变全角.
4. clob数据类型的, 半角日文变全角.
 
开工:
第一个, 还好, 有现成函数: to_single_byte.
TO_SINGLE_BYTE returns char with all of its multibyte characters converted to their corresponding single-byte characters. char can be of datatype CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned is in the same datatype as char.
Any multibyte characters in char that have no single-byte equivalents appear in the output as multibyte characters. This function is useful only if your database character set contains both single-byte and multibyte characters.
可惜呢, 不支持clob
Note:
This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. Please refer to “Datatype Comparison Rules” for more information.
我把clob输入进去, 发现被自动截取了4000个字节, 然后做了处理, 输出的只有这4000个字节, 后面的字节全丢掉了.
  
第二个, 思路, 循环把clob拆成1000个字符(为什么是1000个字符, 因为我的字符集是UTF8的, 每个字符最多可能占用4个字节, 4000个字节悲观估计就只能容纳1000个字符), 每1000个字符调用to_single_byte, 最后把它们拼起来.
这里有一点要注意的是, 把它门拼起来的时候如果这样写:
v_clob:= v_clob || v_varchr2;
那得到的结果的数据类型还是varchar2, 而不是clob.
正确的写法是:
v_clob:= v_clob || to_clob(v_varchar2);
连接操作符”||”虽然对clob重载了, 但是当这个操作符的两边一边是varchar2另一边是clob时, 它是把clob转化为varchar2.
 
第三个, 惨. 没有现成的函数可以用. 网上查了下又咨询了懂日文的同事, 日文字符中可以用半角来写的字符不超过100个(替自己擦了把汗).
那就用translate吧, 结果发现不行. 因为有几个半角的日文字符, 它对应的全角字符不是一个而是半个,也就是说有些半角字符必须和另一个半角字符连在一起才表示一个全角字符的意思, 而translate函数只能一个字符变一个字符, 不能两个字符变一个字符.
只有用replace了. 思路, 把这几十个字符对应关系保存到一个表里, 然后对每条记录都replace. 效率是低了点, 但是总算可以实现功能.
 
第四个, 惨了, replace函数不支持clob输入.还好网上有现成的replace_clob, 抄下来用吧.
 
总算把四个函数都写出来了.
 
现在写游标来做订正了, 噼里啪啦, 也都写好了.
用的时候发现问题了, 有一个表, 上百万笔记录, clob字段的, 也要做这个订正.
我写的第三/四个函数本来效率就低, 又碰上大数据量的clob, 结果测试库一跑就报错了, 跑到20多万笔的时候把temp表空间撑爆了.
怎么办呢, 20多万笔报错, 那就一次只执行5万笔. 然后多次(30次)调用这个脚本.
终于解决了,脚本跑了7个小时,正确出来了结果.