1. 首页
  2. 编程与技术

oracle 多个字符替换实现

发布于 2016-03-28 09:59:02 | 150 次阅读 | 评论: 0 | 来源: 网友投递

这里有新鲜出炉的精品教程，程序狗速度看过来！

Oracle关系数据库管理系统

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。

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



最新网友评论  共有(0)条评论 发布评论 返回顶部
推荐阅读
  • 终极炫富——2016 年 iOS 开发者收入调查报告
  • 年度大片：Stack Overflow 2017 开发者调查报告发布！
  • 开发者需要大学学位吗？
  • 程序员多大年纪算高龄，届时该何去何从？
  • 2017 年高薪编程语言排行榜，你的语言上榜没？
  • 各编程语言开发者最常使用的按键，中了没?
  • 伤不起的三十岁，干不动的程序员要何去何从？
  • 2016年中国加班最狠IT公司排行
  • 一句话"面试"漫画：黑了苹果、谷歌、三星
  • 拿代码量算 KPI ……跟程序员们来这套？
  • 程序员遇到 Bug 时的 30 个反应，你是哪一种？
  • 2016年收入最高的5个编程语言 Java和C没在前五?
  • 为什么很少见人傻钱多的程序员炫富？
  • 阿里月薪两万是什么体验？
  • 代码缩进时 程序员更喜欢空格键还是Tab键？
    • 最新资讯
  • Python获取系统所有进程PID及进程名称的方法示例
  • Python简单获取网卡名称及其IP地址的方法【基于psutil模块】
  • Python实现的根据IP地址计算子网掩码位数功能示例
  • 好的Python培训机构应该具备哪些条件
  • 详解Pytorch 使用Pytorch拟合多项式(多项式回归)
  • Python加载带有注释的Json文件实例
  • 对python的文件内注释 help注释方法
  • Python实现判断一行代码是否为注释的方法
  • Oracle数据库表中字段顺序的修改方法
  • ASP.NET MVC 4使用PagedList.Mvc分页的实现代码
  • Oracle实例启动时报错：ORA-32004的解决方法
  • 解读ASP.NET 5 & MVC6系列教程（7）：依赖注入
  • 详解IDEA用maven创建springMVC项目和配置
  • ASP.NET MVC5网站开发之展示层架构（五）
  • SpringBoot创建maven多模块项目实战代码
    • 后端技术
    前端技术
    数据库
    热门框架
    常用IDE
    其他
    Copyright © 2007-2017 PHPERZ.COM All Rights Reserved   冀ICP备14009818号  版权声明  广告服务