Oracle存储过程迁移ODPS-02(专有云):循环逻辑修改一例(构造代码表)

本文涉及的产品
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: 总有同学提问,说我原来在oracle的存储过程中的循环现在maxcompute支持了,我的程序怎么迁移。这个问题其实非常难以回答,一般来说99%的oracle的代码都可以通过maxcompute的SQL和函数来替代,这个也包括一般的循环语句要解的问题。
总有同学提问,说我原来在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

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
目录
相关文章
|
2月前
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(上)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
1月前
|
存储 分布式计算 专有云
MaxCompute产品使用问题之阿里公有云的数据如何迁移到阿里专有云
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
36 10
|
5天前
|
机器学习/深度学习 分布式计算 大数据
MaxCompute产品使用合集之表的物理存储量和逻辑存储量分别有什么意义
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
1月前
|
存储 SQL Oracle
oracle 存储过程导出sql语句 导出为文件
oracle 存储过程导出sql语句 导出为文件
130 0
|
2月前
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(下)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
2月前
|
分布式计算 DataWorks Java
DataWorks产品使用合集之阿里云DataWorks专有云环境下,上传MaxCompute的UDF(用户自定义函数)的JAR包的步骤如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
71 0
|
2月前
|
存储 SQL Oracle
Oracle存储过程与自定义函数的调用:异同与实战场景
【4月更文挑战第19天】Oracle的存储过程与自定义函数各有特色,存储过程用于封装复杂SQL操作,常在批量处理和数据维护中使用,通过CALL或EXECUTE调用;而自定义函数则用于简单计算和查询,返回单一值,可直接在SQL语句中调用。了解两者异同,如返回值方式、调用方式和应用场景,能提升数据库管理效率。实战场景包括:使用存储过程定期清理过期数据,用自定义函数在查询中动态计算字段值。
|
2月前
|
存储 SQL Oracle
Oracle存储过程:数据王国的魔法师
【4月更文挑战第19天】Oracle存储过程是封装复杂SQL操作的魔法工具,存储在数据库中以便重复调用。它们提供高效执行和安全,类似于预编译的程序。创建存储过程涉及定义名称和参数,如示例所示,创建一个根据员工ID获取姓名和薪资的`get_employee_info`过程。调用存储过程可提高代码可读性和性能,使数据库管理更为便捷。
|
1天前
|
存储 监控 Oracle
关系型数据库Oracle备份策略建议
【7月更文挑战第21天】
10 6
|
1天前
|
存储 Oracle 关系型数据库

推荐镜像

更多