oracle数据处理需求01

前两天接到一个数据处理的需求，内容大概是这样：原始数据缺少必要的辅助信息，所以要后期新增这些数据，源数据量为139W+，每条原始记录增加两条辅助记录。以下是数据处理脚本：

--查询出割接数据中接入产品为280000026 且状态为在用有效的数据清单
--drop table redmine_841054;
create table redmine_841054 as select pi.prod_inst_id, pi.product_id, pi.access_nbr from prod_inst pi where pi.product_id ='280000026' and pi.status_cd <> 110000 and pi.item_status_cd = 1000;
select sum(1) from  redmine_841054; --1550360

--以上表中的这些数据为基础在生产库中查找任然在用的数据
drop table redmine_841054_1;
create table redmine_841054_1 as select pi.prod_inst_id, pi.product_id, pi.access_nbr, pi.common_region_id, pi.ext_prod_inst_id from prod_inst@new_mvno_wdb pi where pi.prod_inst_id in(select prod_inst_id from redmine_841054) and pi.status_cd <> 110000 and pi.item_status_cd = 1000;
select sum(1) from  redmine_841054_1; --1392792
select * from  redmine_841054_1;
select * from  redmine_841054_1 where flag1 = 1;--9839
select * from  redmine_841054_1 where flag2 = 1;--9839
--为该临时表添加两个必要的索引
create index IDX_PROD_INSTID on redmine_841054_1 (prod_inst_id);
create index IDX_access_nbr on redmine_841054_1 (access_nbr);
--在该临时表中添加两个功能产品的添加标识flag1、flag2
alter table redmine_841054_1 add(flag1 varchar(10), flag2 varchar(10));
--初始化标识为0，表示该用户没有订购需要的功能产品
update redmine_841054_1 set flag1 = 0, flag2 = 0;
--在该临时表中添加一个数据添加结果标识
alter table redmine_841054_1 add(modify_msg varchar2(160));
--初始化标识为C，表示该数据的相关功能产品还没有添加
update redmine_841054_1 set modify_msg = 'C';


--判断在生产环境中 redmine_841054_1这些用户是否订购了13409347功能产品，如果订购了则将临时表中的flag1更新为1
update redmine_841054_1 aa set aa.flag1 = 1 where exists(select 1 from prod_inst_serv@new_mvno_wdb pis where pis.product_id = '13409347' and pis.acc_prod_inst_id = aa.prod_inst_id and pis.status_cd = 100000 and pis.item_status_cd = 1000);
--判断在生产环境中 redmine_841054_1这些用户是否订购了13409348 功能产品，如果订购了则将临时表中的flag2更新为1
update redmine_841054_1 aa set aa.flag2 = 1 where exists(select 1 from prod_inst_serv@new_mvno_wdb pis where pis.product_id = '13409348' and pis.acc_prod_inst_id = aa.prod_inst_id and pis.status_cd = 100000 and pis.item_status_cd = 1000);

--导出上面临时表的数据为TXT文件格式
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool C:\Users\wjzuo\Desktop\data1.txt
select prod_inst_id || ',' || product_id || ',' || access_nbr || ',' || common_region_id || ',' || ext_prod_inst_id || ',' || flag1 || ',' ||flag2 || ',' || modify_msg
  from redmine_841054_1;
spool off ;

--创建一个序列
CREATE SEQUENCE zwj_temp_sequence  --序列名
INCREMENT BY 2 -- 每次加2个  
START WITH 99  -- 从99开始计数  
NOMAXVALUE     -- 不设置最大值  
NOCYCLE        -- 一直累加，不循环  
CACHE 10;      --(缓冲)定义存放序列的内存块的大小，默认为20


--执行新增存储过程
declare
  cursor get_temp_data is
    select *
      from redmine_841054_1
     where flag1 = 0
       and flag2 = 0;
  --定义变量
  vn_prod_inst_id_1   number(12);
  vn_prod_inst_id_2   number(12);
  vn_acc_prod_inst_id number(12);
  vn_common_region_id varchar(10);
  vn_err_msg varchar2(160);

begin
  for i in get_temp_data loop
    begin
      --为变量赋值
      select 593480000000 + zwj_temp_sequence.nextval into vn_prod_inst_id_1 from dual;
      select 593470000000 + zwj_temp_sequence.nextval into vn_prod_inst_id_2 from dual;
      vn_acc_prod_inst_id = i.prod_inst_id;
      vn_common_region_id = i.common_region_id;
    
      --增加13409348功能产品数据
      insert into prod_inst_serv
        (PROD_INST_ID,
         PRODUCT_ID,
         ADDRESS_ID,
         OWNER_CUST_ID,
         USE_CUST_ID,
         COMMON_REGION_ID,
         PAYMENT_MODE_CD,
         CREATE_DATE,
         BEGIN_RENT_TIME,
         STOP_RENT_TIME,
         FINISH_TIME,
         STATUS_CD,
         ACC_PROD_INST_ID,
         EXT_PROD_INST_ID,
         ADDRESS_DESC,
         ATOM_ACTION_ID,
         ITEM_STATUS_CD,
         STATUS_DATE,
         COMP_PROD_INST_ID,
         VERSION,
         EXT_SYSTEM,
         DISTRIBUTOR_ID,
         PROPERTY)
      values
        (vn_prod_inst_id_1,
         '13409348',
         null,
         null,
         null,
         vn_common_region_id,
         null,
         sysdate,
         sysdate,
         to_date('30000201', 'yyyymmdd'),
         null,
         100000,
         vn_acc_prod_inst_id,
         999900 || vn_prod_inst_id_1,
         null,
         vn_prod_inst_id,
         1000,
         sysdate,
         null,
         sysdate,
         null,
         500000001,
         1);
      --增加13409347功能产品数据
      insert into prod_inst_serv
        (PROD_INST_ID,
         PRODUCT_ID,
         ADDRESS_ID,
         OWNER_CUST_ID,
         USE_CUST_ID,
         COMMON_REGION_ID,
         PAYMENT_MODE_CD,
         CREATE_DATE,
         BEGIN_RENT_TIME,
         STOP_RENT_TIME,
         FINISH_TIME,
         STATUS_CD,
         ACC_PROD_INST_ID,
         EXT_PROD_INST_ID,
         ADDRESS_DESC,
         ATOM_ACTION_ID,
         ITEM_STATUS_CD,
         STATUS_DATE,
         COMP_PROD_INST_ID,
         VERSION,
         EXT_SYSTEM,
         DISTRIBUTOR_ID,
         PROPERTY)
      values
        (vn_prod_inst_id_2,
         '13409347',
         null,
         null,
         null,
         vn_common_region_id,
         null,
         sysdate,
         sysdate,
         to_date('30000201', 'yyyymmdd'),
         null,
         100000,
         vn_acc_prod_inst_id,
         999900 || vn_prod_inst_id_2,
         null,
         vn_prod_inst_id,
         1000,
         sysdate,
         null,
         sysdate,
         null,
         500000001,
         1);
      --更新临时表中的刷新标识flag1，flag2，modify_msg分别为1,1，SUCCESS,标识数据添加成功 
      update redmine_841054_1
         set modify_msg = 'SUCCESS', flag1 = 1, flag2 = 1
       where prod_inst_id = i.prod_inst_id
         and flag1 = 0
         and flag2 = 0;
      commit;
    exception
      when others then
        rollback;
        vn_err_msg := substr(sqlerrm, 1, 150);
        --更新临时表中的刷新标识flag1，flag2，modify_msg分别为2,2，vn_err_msg,标识数据添加失败 
        update redmine_841054_1
           set modify_msg = vn_err_msg, flag1 = 2, flag2 = 2
         where prod_inst_id = i.prod_inst_id
           and flag1 = 0
           and flag2 = 0;
        commit;
    end;
  end loop;
end;

这样写数据处理的格式是我们的以为项目经理教的，很有条理性。基本上每个细节方面都有考虑，值得参考。