近期開發(fā)一塊需求遇到之前用分號拼接保留在一個字段的值需要拆分出來分表保存，所以想了下實(shí)現(xiàn)方法

數(shù)據(jù)如下：

實(shí)現(xiàn)效果如下：

單條處理sql：

select a.id,a.carcoopercode,substr2(regexp_substr(a.pushmessageinfo, '[^;]+', 1, rownum),0,instr(regexp_substr(a.pushmessageinfo, '[^;]+', 1, rownum),'-') - 1),substr2(regexp_substr(a.pushmessageinfo, '[^;]+', 1, rownum),instr(regexp_substr(a.pushmessageinfo, '[^;]+', 1, rownum),'-') + 1,length(regexp_substr(a.pushmessageinfo, '[^;]+', 1, rownum)))from prpccarcooper_0727 a connect by rownum <= length(a.pushmessageinfo) -length(replace(a.pushmessageinfo, ';', '')) + 1;

批量處理存過：

create or replace package body AHJCARCOOPERPERSON isprocedure CARCOOPERPERSON isv_row_prpccarcooper prpccarcooper%ROWTYPE;TYPE CUR_person IS REF CURSOR;RS_his CUR_person;V_SQL VARCHAR2(1000);person_TYPE prpccarcooperPerson%ROWTYPE;v_id varchar2(20);v_usercode varchar2(20);v_username varchar2(20);cursor cur2 is--查找不為空select *from prpccarcooper a where a.pushmessageinfo is not null ; beginfor v_row_prpccarcooper in cur2 LOOPbeginv_id:=v_row_prpccarcooper.id;delete prpccarcooper_0727 a;insert into prpccarcooper_0727 select * from prpccarcooper a where a.id=v_id;V_SQL:= 'select a.id,a.carcoopercode, regexp_substr(a.pushmessageinfo, ''[^;]+'', 1, rownum),'''' from prpccarcooper_0727 a connect by rownum <= length(a.pushmessageinfo) -length(replace(a.pushmessageinfo, '';'', '''')) + 1';OPEN RS_his FOR V_SQL;LOOPFETCH RS_hisINTO person_TYPE;EXIT WHEN RS_his%NOTFOUND;--截取不為空，則插入if person_TYPE.Pushman is not null thenselect substr2(person_TYPE.Pushman,0,instr(person_TYPE.Pushman,'-')-1),substr2(person_TYPE.Pushman,instr(person_TYPE.Pushman,'-')+1,length(person_TYPE.Pushman))into v_usercode, v_username from dual;EXECUTE IMMEDIATE 'insert into prpccarcooperPerson(id,carcoopercode,pushman,pushmanname) values(:X,:X,:X,:X)'USING person_TYPE.Id,person_TYPE.Carcoopercode, v_usercode,v_username;end if;END LOOP;commit;EXCEPTIONWHEN OTHERS THENROLLBACK;raise;END; end loop; end CARCOOPERPERSON;end AHJCARCOOPERPERSON;

?

總結(jié)

以上是生活随笔為你收集整理的orcal根据分号把一行拆分为多行显示的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。