欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-24 20:59:52
近期开发一块需求遇到之前用分号拼接保留在一个字段的值需要拆分出来分表保存,所以想了下实现方法数据如下:实现效果如下:单条处理sql:select a.id, a.carcoopercode, substr2(regexp_substr(a.pushmessageinfo, '[^;]+', 1, rownum), 0,instr(regexp_substr(a.pushmessageinfo, '[^;]+', 1, row...

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

数据如下:

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

实现效果如下:

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