Oracle存储过程迁移ODPS-02(专有云):循环逻辑修改一例(构造代码表)
总有同学提问,说我原来在oracle的存储过程中的循环现在maxcompute支持了,我的程序怎么迁移。这个问题其实非常难以回答,一般来说99%的oracle的代码都可以通过maxcompute的SQL和函数来替代,这个也包括一般的循环语句要解的问题。 下面这个例子就是一个例子,原来的存储过程使用循环来为多级组织机构代码构建一个新的代码表,因为有多层级,所以使用了循环。 下面是原来存储过程的代码:
-----------------------使用循环进行更新,需要增加v_pid与v_id两个辅助字段---------------------------- update ZZJG_DM a set v_pid=pid; update ZZJG_DM a set v_id=id; update ZZJG_DM a set JBDM=null; update ZZJG_DM a set JCDM=1; set row_count = -1; while row_count != 0 do update ZZJG_DM a, ZZJG_DM b, (select id,pid,to_char(row_number() over (partition by pid order by id)) rn, row_number() over (partition by id order by pid) rm from ZZJG_DM) c set a.JBDM = concat(lpad(c.rn,4,'0'), nvl(a.JBDM,'')), a.JCDM = a.JCDM+1, a.v_id=a.v_pid, a.v_pid=b.pid where a.v_pid = b.id and a.v_id = c.id and c.rm=1; set row_count=row_count(); end while; update ZZJG_DM set jbdm = '0000' where id = '00000000000';
乍一看挺复杂,好多年没写过存储过程的我看着有点难过。但是当我们对齐一下需求和表数据,就会发现这个问题求解,其实并不会太难。maxcompute一定可以实现。
-------------------------------组织机构代码表(ZZJG_DM)数据---------------------------------- id(编码) pid(上级编码)jcdm(级次代码) jbdm(级别代码) 00000000000 1 0000 00000000010 00000000000 2 0001 00000000011 00000000000 2 0002 00000000012 00000000000 2 0003 00000100000 00000000000 2 0004 17900000000 00000000000 2 0005 17900000001 17900000000 3 0005 0001 17900000002 17900000000 3 0005 0002 17900000003 17900000001 4 0005 0001 0001 17900000004 17900000001 4 0005 0001 0002 17900000005 17900000001 4 0005 0001 0003 17900000006 17900000002 4 0005 0002 0001 17900000007 17900000002 4 0005 0002 0002 17900000008 17900000004 5 0005 0001 0002 0001 17900000009 17900000004 5 0005 0001 0002 0002 17900000011 17900000009 6 0005 0001 0002 0002 0001 17900000012 17900000009 6 0005 0001 0002 0002 0002 17900000013 17900000012 7 0005 0001 0002 0002 0002 0001 17900000014 17900000012 7 0005 0001 0002 0002 0002 0002 17900000015 17900000012 7 0005 0001 0002 0002 0002 0003 17900000016 17900000012 7 0005 0001 0002 0002 0002 0004 ----------------------------------需求------------------------------------------- 根据id 与 pid 上下级关系,逐层更新jcdm 与 jbdm。 jcdm:级别代码,节点所在层级。 jbdm:按pid分组的组内排序序号(不足4位补齐4位) 左边拼接上级的jbdm。
组织机构代码表分7级,原来有一套代码,现在这个程序需要重新构造一个代码结构。而且,需要生成一个表示代码所在层级的列。需求就是jcdm/jbdm这两列。我先从原来存储过程所在的数据库平台,了解数据需求和求解结果。理解了这个代码的输入输出,接下来就是求解。 jcdm就是代码所在的级别。因为原来代码表结构是(级别代码-上级代码)结构,所以,我只要把代码表自关联7次,能关联到第几级代码就是第几个级别。 jbdm是新构造的代码表,我查看明细结果数据后发现其实就是一个按照上级代码分组,按照机构代码排序的序号值,标准化到了4位长度。所以,按照上级代码分组再排序的序号最后拼接在一起,就是我新构造的代码表了。 前后大概折腾了2个小时,代码基本完成。
select id ,pid ,new_jcdm as jcdm ,nrank ,nrank2 ,nrank3 ,nrank4 ,nrank5 ,nrank6 ,nrank7 ,case when new_jcdm=1 then '0000' when new_jcdm=2 then nrank when new_jcdm=3 then concat(nrank2,nrank) when new_jcdm=4 then concat(nrank3,nrank2,nrank) when new_jcdm=5 then concat(nrank4,nrank3,nrank2,nrank) when new_jcdm=6 then concat(nrank5,nrank4,nrank3,nrank2,nrank) when new_jcdm=7 then concat(nrank6,nrank5,nrank4,nrank3,nrank2,nrank) end as jbdm from( SELECT id,pid ,substr(concat('0000',to_char(nrank )),length(to_char(nrank ))+1,4) as nrank ,substr(concat('0000',to_char(nrank2)),length(to_char(nrank2))+1,4) as nrank2 ,substr(concat('0000',to_char(nrank3)),length(to_char(nrank3))+1,4) as nrank3 ,substr(concat('0000',to_char(nrank4)),length(to_char(nrank4))+1,4) as nrank4 ,substr(concat('0000',to_char(nrank5)),length(to_char(nrank5))+1,4) as nrank5 ,substr(concat('0000',to_char(nrank6)),length(to_char(nrank6))+1,4) as nrank6 ,substr(concat('0000',to_char(nrank7)),length(to_char(nrank7))+1,4) as nrank7 ,new_jcdm from( SELECT /*+ mapjoin(t2,t3,t4,t5,t6,t7)*/ t1.id ,t1.pid ,row_number() over(partition by t1.pid order by t1.id) as nrank ,t2.nrank as nrank2 ,t3.nrank as nrank3 ,t4.nrank as nrank4 ,t5.nrank as nrank5 ,t6.nrank as nrank6 ,t7.nrank as nrank7 ,t2.id as id2 ,t3.id as id3 ,t4.id as id4 ,t5.id as id5 ,t6.id as id6 ,t7.id as id7 ,case when t7.id is not null then 7 when t6.id is not null then 6 when t5.id is not null then 5 when t4.id is not null then 4 when t3.id is not null then 3 when t2.id is not null then 2 else 1 end as new_jcdm FROM ZZJG_DM t1 left outer join(select id,pid,row_number() over(partition by pid order by id) as nrank from ZZJG_DM where yfq='201812') t2 on t1.pid=t2.id left outer join(select id,pid,row_number() over(partition by pid order by id) as nrank from ZZJG_DM where yfq='201812') t3 on t2.pid=t3.id left outer join(select id,pid,row_number() over(partition by pid order by id) as nrank from ZZJG_DM where yfq='201812') t4 on t3.pid=t4.id left outer join(select id,pid,row_number() over(partition by pid order by id) as nrank from ZZJG_DM where yfq='201812') t5 on t4.pid=t5.id left outer join(select id,pid,row_number() over(partition by pid order by id) as nrank from ZZJG_DM where yfq='201812') t6 on t5.pid=t6.id left outer join(select id,pid,row_number() over(partition by pid order by id) as nrank from ZZJG_DM where yfq='201812') t7 on t6.pid=t7.id where t1.yfq='201812' )t )tt order by id;
结果就如上,其实就是普通的代码表处理方法,有几级就JOIN几次就可以了。因为一般机构代码的层次不会随便更新,所以,这个方法是可以在实际中使用的。循环可以适配级别的变化,但是在解决这个问题时实际上是没必要非写出循环的。 迁移程序时,遇到问题,一定不要把问题限制在这个方法不支持,我就不会做了。而是要看具体是什么问题,一般一个需求是有多种解法的。
-- 暮角 15901445705 update at 20181224
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
大快搜索城市运河大数据政务管理平台案例解读
大数据在政务当中的应用对于提高问题解决的效率可谓大有帮助,但政务大数据平台的应用开发远不止提高问题解决效率这么简单。当然,作为大数据平台应用的开发者来说,我们要做的是还是从底层的技术层面做好解决方案。关于政务大数据平台的解决方案此前有分享过智慧人社的和城市智慧停车的大数据平台解决方案,本篇给大家分享一个新的政务大数据平台管理案例——大快搜索的城市数据运河政务大数据管理运营平台。 图:DKH大数据通用计算平台的示意图 核心特点介绍: “城市数据运河-政务大数据管理运营平台”通过大快搜索研发的DKH平台提供的大数据框架,实现了政务数据和社会数据的统一的融合。 通过数据交换平台对接上下级数据中心,实现信息资源跨区域、跨层级、跨部门的互联互通、融合共享,为政府统筹规划和科学决策提供支撑。通过大屏、手机、PC等设备,面向社会,图形化动态展示政务信息,为信息惠民、信息消费、数据开放、社会化服务等提供支撑,进一步扩展民生服务和社会管理领域的各种应用,全面提升公共服务保障能力。 行业应用解读2018年,某新区政府与大块搜索合作,在其控制中心部署了“城市数据运河——政务大数据管理运营平台”。客户需求:...
- 下一篇
Oracle存储过程迁移ODPS-03(专有云):ODPS1.0支持exists语法
专有云目前还有不少ODPS1.0版本,主要是应对V3之前的平台版本。这个版本的ODPS不支持exists语法,如何换种写法实现。 -- ODPS1.0 如何实现 exists 语法 -- oracle select col1,col2,col3,col4 from (select t1.col1,t1.col2,t2.col3,t2.col4 from table1 t1 INNER JOIN table2 t2 ON t1.col1 = t2.col1 WHERE (t2.col3 between '14400000000' and '14499000000') and (NOT EXISTS (SELECT 1 FROM table3 t3 WHERE t1.col2 = t3.col2 AND GREATEST(t3.col5,t3.col6,t3.col7) <= sysdate() AND GREATEST(t3.col5,t3.col6,t3.col7) >= sysdate() - interval '1095' day))) foo group by col...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Linux系统CentOS6、CentOS7手动修改IP地址
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- Red5直播服务器,属于Java语言的直播服务器
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16
- Windows10,CentOS7,CentOS8安装Nodejs环境
- SpringBoot2整合Thymeleaf,官方推荐html解决方案
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果