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

FOREMURA＿uDET列にELINE_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でやるのは難しいです。

著者：三十にして立つ

時間：2009年10月21日17:09:43

オリジナル作品を尊重してください。転載は文章の完全性を維持し、元の著者「inthirties(三十而立)」と出典をハイパーリンクで明記してください。