这样的sql，我却做到了

原创

程序员紫龙 2022-08-12 21:45:38 博主文章分类：Oracle数据库开发专栏 ©著作权

文章标签 sql insert signal oracle null 文章分类 虚拟化云计算

©著作权归作者所有：来自51CTO博客作者程序员紫龙的原创作品，请联系作者获取转载授权，否则将追究法律责任

作者:三十而立

需求是这样的

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

测试数据如下

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

这个如果用function或者是sp做，就没有什么难度了。

但是用sql做就比较难度了

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