orcal根据分号把一行拆分为多行显示

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

数据如下：

实现效果如下：

单条处理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;

 

本文地址：https://blog.csdn.net/jungeCSND/article/details/107607151