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; --1392792select * from  redmine_841054_1;select * from  redmine_841054_1 where flag1 = 1;--9839select * 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、flag2alter 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更新为1update 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更新为1update 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 onset linesize 120set pagesize 2000set newpage 1set heading offset term offspool C:\Users\wjzuo\Desktop\data1.txtselect 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;

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