Oracle字符串截取和拼接应用

今天有人问了个关于Oracle字符串截取和拼接的问题，让我帮他写出SQL，看了下问题描述还比较清晰就试着解决下，利用午休时间把功能实现了，问题看似不难，但思路一定要清晰，不然就乱了，关键大量应用了Oracle的substr 和instr函数。下面贴出问题和脚本：
问题：sql中一个字段值为：1788987565327、768374872394903、21437238740213483874629、23412341234252345。其中顿号间隔的每一组数字位数和尾数不定，现在要使前面这个字段值中顿号前的数字尾数即7、3、9、5都分别加1，变成8、4、0、6输出成1788987565328、768374872394904、21437238740213483874620、23412341234252346。注意其中第三个数，从9加1后，输出成0，而不是10脚本：
declare
targetstr varchar2（2000）;
strlength number;
position number;
maxposition number;
retrunstr varchar2（2000）;
tempstr varchar2（2000）;
endstr number;
begin
targetstr := "1788987565327、768374872394903、21437238740213483874629、23412341234252345";
maxposition := 0;
select LENGTH（targetstr） into strlength from dual;
for i in 1..strlength loop
select instr（str,"、",1,i） into position from （select targetstr as str from dual）;
--dbms_output.PUT_LINE（position）;
if position > 0 then
if maxposition = 0 then
select substr（str,0,instr（str,"、",1,1）-1） into retrunstr from （select targetstr as str from dual）;
select TO_NUMBER（substr（restr,-1）） into endstr from （select retrunstr as restr from dual）;
if endstr = 3 or endstr = 5 or endstr = 7 then
endstr := endstr + 1;
elsif endstr = 9 then
endstr := 0;
end if;
select substr（str,0,instr（str,"、",1,1）-2）||TO_CHAR（endstr） into retrunstr from （select targetstr as str from dual）;
elsif maxposition < position then
select substr（str,instr（str,"、",1,i-1）+1,instr（str,"、",1,i）-instr（str,"、",1,i-1）-1） into tempstr from （select targetstr as str from dual）;
select TO_NUMBER（substr（restr,-1）） into endstr from （select tempstr as restr from dual）;
if endstr = 3 or endstr = 5 or endstr = 7 then
endstr := endstr + 1;
elsif endstr = 9 then
endstr := 0;
end if;
select substr（str,1,length（str）-1） ||TO_CHAR（endstr） into tempstr from （select tempstr as str from dual）;
retrunstr := retrunstr || "、"|| tempstr;
end if;
maxposition := position;
else
if maxposition > position then
--特别处理最后一段
tempstr := "";
select substr（str,maxposition-length（str）） into tempstr from （select targetstr as str from dual）;
select TO_NUMBER（substr（restr,-1）） into endstr from （select tempstr as restr from dual）;
if endstr = 3 or endstr = 5 or endstr = 7 then
endstr := endstr + 1;
elsif endstr = 9 then
endstr := 0;
end if;
select substr（str,1,length（str）-1） ||TO_CHAR（endstr） into tempstr from （select tempstr as str from dual）;
retrunstr := retrunstr || "、"|| tempstr;
end if;
exit;
end if;
end loop;
dbms_output.PUT_LINE（retrunstr）;
end;运行结果如下：
原字符串：1788987565327、768374872394903、21437238740213483874629、23412341234252345
14:03:38 **** SCRIPT STARTED: 02-Apr-2015 14:03:38 ****
14:03:38 declare
14:03:38 targetstr varchar2（2000）;
14:03:38 ...
14:03:39 PL/SQL block executed
1788987565328、768374872394904、21437238740213483874620、23412341234252346
14:03:39 **** SCRIPT ENDED 02-Apr-2015 14:03:39 ****
14:03:39 End Script Execution更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址