oracle 多个字符替换实现

create table A_TEST
(
PAYOUT_ITEM_CODE VARCHAR2(30) not null,
FORMULA_DET VARCHAR2(1000)
)

create table B_TEST
(
ELEMENT_ID VARCHAR2(5) not null,
NAME VARCHAR2(41)
)

insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30228, ({30015}+{30016})*450);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30102, ({30015}+{30016})*1500);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30102, ({30015}+{30016})*5000);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30102, ({30015}+{30016})*2500);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30102, ({30015}+{30016})*2300);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30102, ({30015}+{30016})*1150);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30104, ({30015}+{30016})*300*12);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30057}*2300);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30102, ({30015}+{30016})*5000);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30102, ({30015}+{30016})*3000);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30102, ({30015}+{30016})*1500);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30006}+{30061}+{30008});
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30057}*3800*12);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30210, ({30030}+{30031}+{30032})*38000+{30033}*23000);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30210, ({30030}+{30031}+{30032}+{30033})*38000+{30036}*10000);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30229, ({30015}+{30016})*1400);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30228, ({30015}+{30016})*450);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30216, ({30015}+{30016})*1300);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30216, ({30015}+{30016})*650);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30307, ({30015}+{30016})*360);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30302, {30051});
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30302, {30052});
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30302, {30053});
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30302, {30054});
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30302, {30055});
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30302, {30056});
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30057}*4000);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30057}*3800);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30057}*100*12);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30057}*500*12);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30060}*0);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30057}/{30057}*150000);
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values (30226, {30057}*6000);

insert into b_test (ELEMENT_ID, NAME)
values (30006, a1);
insert into b_test (ELEMENT_ID, NAME)
values (30008, a2);
insert into b_test (ELEMENT_ID, NAME)
values (30009, a3);
insert into b_test (ELEMENT_ID, NAME)
values (30010, a4);
insert into b_test (ELEMENT_ID, NAME)
values (30015, a5);
insert into b_test (ELEMENT_ID, NAME)
values (30016, a6);
insert into b_test (ELEMENT_ID, NAME)
values (30017, a7);
insert into b_test (ELEMENT_ID, NAME)
values (30018, a8);
insert into b_test (ELEMENT_ID, NAME)
values (30019, a9);
insert into b_test (ELEMENT_ID, NAME)
values (30020, a10);
insert into b_test (ELEMENT_ID, NAME)
values (30021, a11);
insert into b_test (ELEMENT_ID, NAME)
values (30022, a12);
insert into b_test (ELEMENT_ID, NAME)
values (30023, a13);
insert into b_test (ELEMENT_ID, NAME)
values (30024, a14);
insert into b_test (ELEMENT_ID, NAME)
values (30025, a15);
insert into b_test (ELEMENT_ID, NAME)
values (30026, a16);
insert into b_test (ELEMENT_ID, NAME)
values (30027, a17);
insert into b_test (ELEMENT_ID, NAME)
values (30028, a18);
insert into b_test (ELEMENT_ID, NAME)
values (30029, a19);
insert into b_test (ELEMENT_ID, NAME)
values (30030, a20);
insert into b_test (ELEMENT_ID, NAME)
values (30031, a21);
insert into b_test (ELEMENT_ID, NAME)
values (30032, a22);
insert into b_test (ELEMENT_ID, NAME)
values (30033, a23);
insert into b_test (ELEMENT_ID, NAME)
values (30034, a24);
insert into b_test (ELEMENT_ID, NAME)
values (30035, a25);
insert into b_test (ELEMENT_ID, NAME)
values (30036, a26);
insert into b_test (ELEMENT_ID, NAME)
values (30037, a27);
insert into b_test (ELEMENT_ID, NAME)
values (30038, a28);
insert into b_test (ELEMENT_ID, NAME)
values (30039, a29);
insert into b_test (ELEMENT_ID, NAME)
values (30040, a30);
insert into b_test (ELEMENT_ID, NAME)
values (30041, a31);
insert into b_test (ELEMENT_ID, NAME)
values (30042, a32);
insert into b_test (ELEMENT_ID, NAME)
values (30043, a33);
insert into b_test (ELEMENT_ID, NAME)
values (30044, a34);
insert into b_test (ELEMENT_ID, NAME)
values (30045, a35);
insert into b_test (ELEMENT_ID, NAME)
values (30046, a36);
insert into b_test (ELEMENT_ID, NAME)
values (30047, a37);
insert into b_test (ELEMENT_ID, NAME)
values (30048, a38);
insert into b_test (ELEMENT_ID, NAME)
values (30049, a39);
insert into b_test (ELEMENT_ID, NAME)
values (30050, a40);
insert into b_test (ELEMENT_ID, NAME)
values (30051, a41);
insert into b_test (ELEMENT_ID, NAME)
values (30052, a42);
insert into b_test (ELEMENT_ID, NAME)
values (30053, a43);
insert into b_test (ELEMENT_ID, NAME)
values (30054, a44);
insert into b_test (ELEMENT_ID, NAME)
values (30055, a45);
insert into b_test (ELEMENT_ID, NAME)
values (30056, a46);
insert into b_test (ELEMENT_ID, NAME)
values (30057, a47);
insert into b_test (ELEMENT_ID, NAME)
values (30058, a48);
insert into b_test (ELEMENT_ID, NAME)
values (30059, a49);
insert into b_test (ELEMENT_ID, NAME)
values (30060, a50);
insert into b_test (ELEMENT_ID, NAME)
values (30061, a51);

select gid, payout_item_code, formula_det, max(substr(txt, 1, length(txt)-1)) from (
select a.gid,
a.payout_item_code,
a.formula_det,
replace(sys_connect_by_path(decode(b.element_id, null, a.signal, replace(signal, b.element_id, b.name)),##), ##, ) txt
from
(select gid, payout_item_code, formula_det, row_number() over(partition by gid order by level) rn,
substr(formula_det, decode(rownum-(allcnt-selfcnt), 1, 1, instr(formula_det, }, 1, rownum-(allcnt-selfcnt)-1)+1), instr(formula_det, }, 1, rownum-(allcnt-selfcnt))-decode(rownum-(allcnt-selfcnt), 1, 0, instr(formula_det, }, 1, rownum-(allcnt-selfcnt)-1))) signal
from (select a.payout_item_code, a.rowid gid,
a.formula_det||} formula_det,
length(a.formula_det) -
length(replace(a.formula_det, }, )) + 1 selfcnt,
sum(length(a.formula_det) - length(replace(a.formula_det, }, ))+1) over(order by rowid) allcnt, sum(length(a.formula_det) - length(replace(a.formula_det, }, ))+1) over() sumcnt
from a_test a) t1
start with (allcnt-selfcnt)=0 connect by rownum < sumcnt+1 and instr(formula_det, }, 1, rownum-(allcnt-selfcnt)) >0) a
left join b_test b on instr(a.signal||}, {||b.element_id||}, 1, 1)>0
start with a.rn = 1 connect by prior a.gid = a.gid and prior a.rn + 1 = a.rn)
group by gid, payout_item_code, formula_det