当前位置: 首页 > 图文教程 > 数据库 > Oracle > oracle 多个字符替换实现

Oracle
Oracle常用dump命令，记录一下备查。
Oracle存储过程之数据库中获取数据实例
Oracle存储过程入门学习基本语法
java.sql.SQLException: 内部错误: Unable to construct a Datum from the specified input
Oracle 函数大全[字符串函数，数学函数，日期函数]
Oracle 自增(auto increment) 或 标识字段的建立方法
oracle 存储过程加密的方法
Oracle针对数据库某一行进行操作的时候，如何将这一行加行锁
Oracle 忘记密码的找回方法
Oracle 数据库导出(exp)导入(imp)说明
oracle 常见等待事件及处理方法
Oracle9i 动态SGA,PGA特性探索
PDO取Oracle lob大字段，当数据量太大无法取出的问题的解决办法
ORACLE 数据库RMAN备份恢复
用Mimer Validator检查SQL查询
oracle执行cmd的实现方法
ORACLE 正则解决初使化数据格式不一致
Oracle 触发器的使用小结
oracle 时间格式的调整
Oracle 10g的DBA无法登录解决方案

No. « 7 8 9 10 »

技术文章搜索

关键字

Oracle 中的 oracle 多个字符替换实现

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2010-01-10   浏览: 115 ::
收藏到网摘: n/a

CSDN上的一个网友，需要一个sql语句的解决方案需求是这样的求写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