orcal根据分号把一行拆分为多行显示心寒的博客-ora按分号拆分

近期开发一块需求遇到之前用分号拼接保留在一个字段的值需要拆分出来分表保存，所以想了下实现方法

数据如下：

实现效果如下：

单条处理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 is  procedure  CARCOOPERPERSON  is  v_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  ; begin  for v_row_prpccarcooper in cur2 LOOP    begin     v_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;       LOOP         FETCH RS_his           INTO person_TYPE;         EXIT WHEN RS_his%NOTFOUND;         --截取不为空，则插入         if person_TYPE.Pushman is not null then            select 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;    EXCEPTION            WHEN OTHERS THEN                 ROLLBACK;             raise;              END; end loop; end CARCOOPERPERSON;  end AHJCARCOOPERPERSON;