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