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...
近期开发一块需求遇到之前用分号拼接保留在一个字段的值需要拆分出来分表保存,所以想了下实现方法
数据如下:
实现效果如下:
单条处理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