oracle数据处理需求02

由于系统原因，导致主数据必需的属性确实，导致订单流程不能正常的流转，本脚本是后期补充确实的属性数据：

--创建临时表
select a.*, a.rowid from ai_zuowj.zwj_temp_0912 a;
--添加过程表字段
alter table ai_zuowj.zwj_temp_0912 add(prod_inst_id number(12),
                                       order_item_id number(12));  
--添加刷新标识
alter table ai_zuowj.zwj_temp_0912 add(modify_flag varchar(10),
                                       modify_msg varchar(160)); 
--初始化刷新标识
update ai_zuowj.zwj_temp_0912 a set a.modify_flag = 0, a.modify_msg = 'C';
--更新过程表数据1
update ai_zuowj.zwj_temp_0912 a
   set a.prod_inst_id =
       (select aa.prod_inst_id
          from iot.bo_serv_order aa
         where aa.product_id = '13409920'
           and aa.order_item_id in
               (select oi.order_item_id
                  from iot.order_item oi
                 where oi.cust_order_id = a.cust_order_id));
--更新过程表数据2
update ai_zuowj.zwj_temp_0912 a
   set a.order_item_id =
       (select aa.order_item_id
          from iot.bo_serv_order aa
         where aa.product_id = '13409920'
           and aa.order_item_id in
               (select oi.order_item_id
                  from iot.order_item oi
                 where oi.cust_order_id = a.cust_order_id));
--执行过程
declare
  cursor temp_data is
    select *
      from ai_zuowj.zwj_temp_0912
     where modify_flag = 0
       and modify_msg = 'C';

  vn_atom_action_id_dwlx   number(12);
  vn_atom_action_id_dwfz   number(12);
  vn_atom_action_id_dwsx   number(12);
  vn_atom_action_id_dwlxsx number(12);
  vn_err_msg               varchar(160);
begin
  for i in temp_data loop
    begin
      select 591760000000 + ai_zuowj.zwj_temp_sequence.nextval
        into vn_atom_action_id_dwlx
        from dual;
      select 592760000000 + ai_zuowj.zwj_temp_sequence.nextval
        into vn_atom_action_id_dwfz
        from dual;
      select 593760000000 + ai_zuowj.zwj_temp_sequence.nextval
        into vn_atom_action_id_dwsx
        from dual;
      select 594760000000 + ai_zuowj.zwj_temp_sequence.nextval
        into vn_atom_action_id_dwlxsx
        from dual;
    
      insert into iot.bo_serv_item
        (ATOM_ACTION_ID,
         ORDER_ITEM_ID,
         PROD_INST_ID,
         ATTR_ID,
         VALUE,
         APP_START_DT,
         APP_END_DT,
         START_DT,
         END_DT,
         STATE,
         ATTR_VALUE_ID)
      values
        (vn_atom_action_id_dwlx,
         i.order_item_id,
         i.prod_inst_id,
         10010076,
         i.dwfz,
         null,
         null,
         null,
         null,
         'ADD',
         null);
      insert into iot.bo_serv_item
        (ATOM_ACTION_ID,
         ORDER_ITEM_ID,
         PROD_INST_ID,
         ATTR_ID,
         VALUE,
         APP_START_DT,
         APP_END_DT,
         START_DT,
         END_DT,
         STATE,
         ATTR_VALUE_ID)
      values
        (vn_atom_action_id_dwfz,
         i.order_item_id,
         i.prod_inst_id,
         10020086,
         10,
         null,
         null,
         null,
         null,
         'ADD',
         null);
      insert into iot.prod_inst_serv_attr
        (PROD_INST_ID,
         ATOM_ACTION_ID,
         ATTR_ID,
         ATTR_VALUE_ID,
         ATTR_VALUE,
         EFF_DATE,
         EXP_DATE,
         STATUS_CD,
         STATUS_DATE,
         CREATE_DATE,
         VERSION,
         COMMON_REGION_ID)
      values
        (i.prod_inst_id,
         vn_atom_action_id_dwsx,
         10010076,
         null,
         i.dwfz,
         sysdate,
         to_date('30000101', 'yyyymmdd'),
         1000,
         sysdate,
         sysdate,
         sysdate,
         8321000);
      insert into iot.prod_inst_serv_attr
        (PROD_INST_ID,
         ATOM_ACTION_ID,
         ATTR_ID,
         ATTR_VALUE_ID,
         ATTR_VALUE,
         EFF_DATE,
         EXP_DATE,
         STATUS_CD,
         STATUS_DATE,
         CREATE_DATE,
         VERSION,
         COMMON_REGION_ID)
      values
        (i.prod_inst_id,
         vn_atom_action_id_dwlxsx,
         10020086,
         null,
         10,
         sysdate,
         to_date('30000101', 'yyyymmdd'),
         1000,
         sysdate,
         sysdate,
         sysdate,
         8321000);
      update ai_zuowj.zwj_temp_0912 a
         set a.modify_flag = 1, a.modify_msg = 'S'
       where a.cust_order_id = i.cust_order_id
         and a.modify_flag = 0;
      commit;
    exception
      when others then
        vn_err_msg := substr(sqlerrm, 1, 150);
        update ai_zuowj.zwj_temp_0912 a
           set a.modify_flag = 2, a.modify_msg = vn_err_msg
         where a.cust_order_id = i.cust_order_id
           and a.modify_flag = 0;
    end;
    commit;
  end loop;
end;