求写oracle多个字符替换（有测试数据）

[hkstudio]

求写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)
)

FORMULA_DET列里ELEMENT_ID替换成NAME

数据库版本：oracle 9.2.0.7

[hkstudio]

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');

[hkstudio]

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');

[luoyoumou]

--我帮你两表连接起来了，你自己去更新一下就OK了!
select b.ELEMENT_ID, b.NAME,
       a.PAYOUT_ITEM_CODE, a.FORMULA_DET
 from  a_test a right join b_test b on a.FORMULA_DET LIKE '%{'||b.ELEMENT_ID||'}%'; 

[小灰狼W]

没想出用sql更新的方法，先用个存储过程来实现

declare 
cursor c1 is select * from a_test for update;
begin
for cur1 in c1 loop
  for cur2 in (select * from b_test)loop
    if instr(cur1.formula_det,cur2.element_id)>0 then
      update a_test set formula_det=replace(formula_det,cur2.element_id,cur2.name)
        where current of c1;
    end if;
  end loop;
end loop;
commit;
end;

PAYOUT_ITEM_CODE	FORMULA_DET
30228	({a5}+{a6})*450
30102	({a5}+{a6})*1500
30102	({a5}+{a6})*5000
30102	({a5}+{a6})*2500
30102	({a5}+{a6})*2300
30102	({a5}+{a6})*1150
30104	({a5}+{a6})*300*12
30226	{a47}*2300
30102	({a5}+{a6})*5000
30102	({a5}+{a6})*3000
30102	({a5}+{a6})*1500
30226	{a1}+{a51}+{a2}
30226	{a47}*3800*12
30210	({a20}+{a21}+{a22})*38000+{a23}*23000
30210	({a20}+{a21}+{a22}+{a23})*38000+{a26}*10000
30229	({a5}+{a6})*1400
30228	({a5}+{a6})*450
30216	({a5}+{a6})*1300
30216	({a5}+{a6})*650
30307	({a5}+{a6})*360
30302	{a41}
30302	{a42}
30302	{a43}
30302	{a44}
30302	{a45}
30302	{a46}
30226	{a47}*4000
30226	{a47}*3800
30226	{a47}*100*12
30226	{a47}*500*12
30226	{a50}*0
30226	{a47}/{a47}*150000
30226	{a47}*6000

[luoyoumou]

--楼上很强大!

[小灰狼W]

用sql真难写
想了半天，解决了拼接和顺序问题，却没解决存在、重复和效率问题

update a_test t set t.formula_det=(select replace(max(sys_connect_by_path(newstr,',')),',','')
    from(select  a.formula_det,a.payout_item_code,a.rn,row_number()over(partition by a.rn order by instr(a.formula_det,b.element_id))rn1,
      case when instr(a.formula_det,'}')>instr(a.formula_det,b.element_id) 
        then substr(a.formula_det,1,instr(a.formula_det,b.element_id)-1) end||
      b.name||substr(a.formula_det,instr(a.formula_det,'}',instr(a.formula_det,b.element_id)),
        case when instr(a.formula_det,'{',-1)<instr(a.formula_det,b.element_id) then 100
          else instr(a.formula_det,'{',instr(a.formula_det,b.element_id))-instr(a.formula_det,'}',instr(a.formula_det,b.element_id))+1 end)newstr
    from (select a_test.*,rownum rn from  a_test) a,b_test b
    where instr(a.formula_det,b.element_id)>0
    )
    where payout_item_code=t.payout_item_code
    and formula_det=t.formula_det
    connect by prior rn1=rn1-1 and prior rn=rn
    start with rn1=1
    )

PAYOUT_ITEM_CODE	FORMULA_DET
30228	({a5}+{a6})*450
30102	({a5}+{a6})*1500
30102	({a5}+{a6})*5000
30102	({a5}+{a6})*2500
30102	({a5}+{a6})*2300
30102	({a5}+{a6})*1150
30104	({a5}+{a6})*300*12
30226	{a47}*2300
30102	({a5}+{a6})*5000
30102	({a5}+{a6})*3000
30102	({a5}+{a6})*1500
30226	{a1}+{a51}+{a2}
30226	{a47}*3800*12
30210	({a20}+{a21}+{a22})*38000+{a23}*23000
30210	({a20}+{a21}+{a22}+{a23})*38000+{a26}*10000
30229	({a5}+{a6})*1400
30228	({a5}+{a6})*450
30216	({a5}+{a6})*1300
30216	({a5}+{a6})*650
30307	({a5}+{a6})*360
30302	{a41}
30302	{a42}
30302	{a43}
30302	{a44}
30302	{a45}
30302	{a46}
30226	{a47}*4000
30226	{a47}*3800
30226	{a47}*100*12
30226	{a47}*500*12
30226	{a50}*0
30226	{a47}/{
30226	{a47}*6000

结果里倒数第二条记录，由于30057出现了重复，结果出现错误
性能也比上面写的存储过程差了很多

[hkstudio]

哪位高手能用sql语句实现？？？

[inthirties]

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

[hkstudio]

非常感谢 inthirties、wildwave的帮助。

[小灰狼W]

是我的测试数据出了问题
抱歉。。。

[碧水幽幽泉]

SQL语句：
1.删除字段FORMULA_DET
alter table A_TEST drop column FORMULA_DET;
2.再添加字段那name：
alter table A_TEST add (name varchar2(1000)));

[小灰狼W]

inthirties老兄的代码暂没时间细看，可是有和我上面写的sql一样的问题啊，当一个值里面要替换的字符串出现重复的时候，结果会出错，下面是结果
用字符串拼接的话要解决这个问题很难，要在两表关联的时候就进行相关处理。但是效率实在太低了

GID	PAYOUT_ITEM_CODE	FORMULA_DET	MAX(SUBSTR(TXT,1,LENGTH(TXT)-1
AAASB8AAEAAAAaIAAL	30226	{a1}+{a51}+{a2}}	{a1}+{a51}+{a2}
AAASB8AAEAAAAaIAAO	30210	({a20}+{a21}+{a22}+{a23})*38000+{a26}*10000}	({a20}+{a21}+{a22}+{a23})*38000+{a26}*10000
AAASB8AAEAAAAaIAAP	30229	({a5}+{a6})*1400}	({a5}+{a6})*1400
AAASB8AAEAAAAaIAAT	30307	({a5}+{a6})*360}	({a5}+{a6})*360
AAASB8AAEAAAAaIAAA	30228	({a5}+{a6})*450}	({a5}+{a6})*450
AAASB8AAEAAAAaIAAE	30102	({a5}+{a6})*2300}	({a5}+{a6})*2300
AAASB8AAEAAAAaIAAQ	30228	({a5}+{a6})*450}	({a5}+{a6})*450
AAASB8AAEAAAAaIAAV	30302	{a42}}	{a42}
AAASB8AAEAAAAaIAAZ	30302	{a46}}	{a46}
AAASB8AAEAAAAaIAAb	30226	{a47}*3800}	{a47}*3800
AAASB8AAEAAAAaIAAd	30226	{a47}*500*12}	{a47}*500*12
AAASB8AAEAAAAaIAAY	30302	{a45}}	{a45}
AAASB8AAEAAAAaIAAc	30226	{a47}*100*12}	{a47}*100*12
AAASB8AAEAAAAaIAAg	30226	{a47}*6000}	{a47}*6000
AAASB8AAEAAAAaIAAB	30102	({a5}+{a6})*1500}	({a5}+{a6})*1500
AAASB8AAEAAAAaIAAH	30226	{a47}*2300}	{a47}*2300
AAASB8AAEAAAAaIAAW	30302	{a43}}	{a43}
AAASB8AAEAAAAaIAAC	30102	({a5}+{a6})*5000}	({a5}+{a6})*5000
AAASB8AAEAAAAaIAAJ	30102	({a5}+{a6})*3000}	({a5}+{a6})*3000
AAASB8AAEAAAAaIAAf	30226	{a47}/{}	{a47}/{  --这里
AAASB8AAEAAAAaIAAD	30102	({a5}+{a6})*2500}	({a5}+{a6})*2500
AAASB8AAEAAAAaIAAG	30104	({a5}+{a6})*300*12}	({a5}+{a6})*300*12
AAASB8AAEAAAAaIAAN	30210	({a20}+{a21}+{a22})*38000+{a23}*23000}	({a20}+{a21}+{a22})*38000+{a23}*23000
AAASB8AAEAAAAaIAAR	30216	({a5}+{a6})*1300}	({a5}+{a6})*1300
AAASB8AAEAAAAaIAAS	30216	({a5}+{a6})*650}	({a5}+{a6})*650
AAASB8AAEAAAAaIAAF	30102	({a5}+{a6})*1150}	({a5}+{a6})*1150
AAASB8AAEAAAAaIAAI	30102	({a5}+{a6})*5000}	({a5}+{a6})*5000
AAASB8AAEAAAAaIAAM	30226	{a47}*3800*12}	{a47}*3800*12
AAASB8AAEAAAAaIAAU	30302	{a41}}	{a41}
AAASB8AAEAAAAaIAAX	30302	{a44}}	{a44}
AAASB8AAEAAAAaIAAe	30226	{a50}*0}	{a50}*0
AAASB8AAEAAAAaIAAK	30102	({a5}+{a6})*1500}	({a5}+{a6})*1500
AAASB8AAEAAAAaIAAa	30226	{a47}*4000}	{a47}*4000