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

Oracle
oracle sys_connect_by_path 函数 结果集连接
oracle join on 数据过滤问题
Oracle 当前用户下所有表的记录总数
oracle 树查询 语句
oracle 触发器 实现出入库
Oracle 函数大全
oracle 删除重复数据
ORACLE 最大连接数的问题
oracle 层次化查询（行政区划三级级联）
oracle 查询表名以及表的列名
Oracle 数据显示 横表转纵表
oracle 服务启动，关闭脚本(windows系统下)
ORCLE 表中列的修改
oracle 数据库连接分析
Oracle 实现类似SQL Server中自增字段的一个办法
Oracle 常用的SQL语句
Oracle 数组的学习 小知识也要积累，养成好的学习态度
Oracle 日期的一些简单使用
Oracle 数据库连接查询SQL语句
Oracle DBA常用语句

No. « 10 11 12 13 »

技术文章搜索

关键字

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

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2010-01-10   浏览: 121 ::
收藏到网摘: 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