oracle数据处理经验1

--start------注意----------------------------------------------------------------------------------------------------------------------------------------------
  -- 1)前提必须先初始化网点表数据,网点数据每天凌晨0:00点从旧CMS网点表(tb_product_agency)抽到网点表(t_network)
  -- 2)经销商渠道关系同步表(t_dealer_channel_rel_sync)和渠道负责人关系同步表(t_channel_charge_rel_sync)数据来源是信息中心提供的"渠道_生产商_品牌_负责人关系表.xls"
  -- 3)经销商用户同步表(t_dealer_user_sync)和经销商开发员同步表(t_dealer_developer_sync)数据来源是内部库dbo.JXX_Dealer_Info表,使用ETL工个抽数
  -- 4)cas.sys_user表tel和email字段长度都修改为200
--end--------注意----------------------------------------------------------------------------------------------------------------------------------------------


--[1]----satrt--------根据经销商编码,把网点表的城市、省份等字段同步到经销商表--------
  merge into t_dealer a
  using t_network b 
  on(a.DEALER_CODE=b.DEALER_CODE)
  when matched then
  update set a.province_id = b.province_id
             ,a.city_id = b.city_id
             ,a.county_id = b.county_id
             ,a.manf_id = b.manf_id
             ,a.brand_id = b.brand_id
             ,a.shop_name = b.dealer_full_name
             ,a.address = b.dealer_full_addr
             ,a.sale_tel = b.sale_tel
             ,a.dealer_nature_val = b.dealer_nature_val;
--[1]----end--------根据经销商编码,把网点表的城市、省份等字段同步到经销商表--------




--[2]----satrt--------根据经销商编码,创建经销商渠道关系--------
  /*--经销商渠道关系同步表
  create table t_dealer_channel_rel_sync (
    dealer_code varchar2(300),
    channel_id  number
  )*/


  merge into t_dealer a
  using t_dealer_channel_rel_sync b
  on(a.dealer_code = b.dealer_code)
  when matched then
  update set a.channel_id = b.channel_id;


--把没有"经销商渠道关系"的渠道ID都置为-1
--update t_dealer t set t.channel_id = -1 where t.channel_id is null;
--[2]----end--------根据经销商编码,创建经销商渠道关系--------




--[3]----satrt--------初始化渠道负责人关系(价格/终端、商务政策)--------
  /*--渠道负责人关系同步表
  create table t_channel_charge_rel_sync(
     CHANNEL_ID number,
     PRICE      number,
     TERMINAL   number,
     BUSINESS   number,
      COLLECTOR_NAME varchar2(100),
     ASSESSOR_NAME varchar2(100),
     BUSINESS_NAME varchar2(100),
     COLLECTOR_ID varchar2(32),
     ASSESSOR_ID varchar2(32),
     BUSINESS_ID varchar2(32)
  )*/


  --同步采集员ID(同步临时表)
  merge into t_channel_charge_rel_sync a
  using cas.sys_user b
  on (a.COLLECTOR_NAME = b.user_name )
  when matched then
  update set a.COLLECTOR_ID = b.user_id;
        


  --同步审核员ID(同步临时表)           
  merge into t_channel_charge_rel_sync a
  using cas.sys_user b
  on (a.ASSESSOR_NAME = b.user_name)
  when matched then
  update set a.ASSESSOR_ID = b.user_id;
                      


  --同步商务政策负责人ID(同步临时表)           
  merge into t_channel_charge_rel_sync a
  using cas.sys_user b
  on (a.BUSINESS_NAME = b.user_name)
  when matched then
  update set a.BUSINESS_ID = b.user_id;


  --初始化渠道负责人关系(价格/终端)
  insert into t_channel_charge_rel
  select sys_guid()
         ,t.channel_id
         ,1 as PRODUCT_TYPE_VAL
         ,t.collector_id
         ,t.assessor_id
         ,''
         ,sysdate
         ,''
         ,sysdate
    from t_channel_charge_rel_sync t
   where t.price = 1 or t.terminal = 1
    
  --初始化渠道负责人关系(商务政策)
  insert into t_channel_charge_rel
  select sys_guid()
         ,t.channel_id
         ,2 as PRODUCT_TYPE_VAL
         ,t.business_id
         ,t.business_id
         ,''
         ,sysdate
         ,''
         ,sysdate
    from t_channel_charge_rel_sync t
   where t.business = 1;


   select * from t_channel_charge_rel_main;   
   truncate table t_channel_charge_rel_main
   insert into t_channel_charge_rel_main select * from t_channel_charge_rel
--[3]----end--------初始化渠道负责人关系(价格/终端、商务政策)--------




--[4]----start--------根据经销商编码,把网点表的营运状态、集团ID字段同步到经销商背景信息表--------
  merge into t_dealer_background a
  using t_network b 
  on(a.DEALER_CODE=b.DEALER_CODE)
  when matched then
  update set a.group_id = b.group_id
             ,a.operation_state_val = b.operation_state_val;


--处理营运状态不为数字的都为20
--update t_dealer_background b set b.operation_state_val = 20 where b.operation_state_val != '20'           
--[4]----end--------根据经销商编码,把网点表的营运状态、集团ID字段同步到经销商背景信息表--------
       


--[5]----start--------根据码表(t_code_table)更新"删除详细原因"字段,初始化时此字段为中文--------
  update t_dealer d
     set d.del_detail_reason = (select a.code_val
                                  from t_code_table a
                                 where a.p_code =
                                       'PARAM_DEALER_DEL_DETAIL_REASON'
                                   and d.del_detail_reason = a.code_name)


--更新后,"删除详细原因"字段还是空的数据都置为6(其他)
  update t_dealer d
     set d.del_detail_reason = 6
   where d.del_detail_reason is null;
--[5]----end--------根据码表(t_code_table)更新"删除详细原因"字段,初始化时此字段为中文--------




--[6]----start--------根据码表(t_code_table)更新"银行"字段,初始化时此字段为中文--------                                 
  update t_dealer_account d
     set d.bank_val = (select a.code_val
                                  from t_code_table a
                                 where a.p_code =
                                       'PARAM_BANK'
                                   and d.bank_val = a.code_name)
  --更新后,"银行"字段还是空的数据都置为44(其他)
  update t_dealer_account d
     set d.bank_val = 44
   where d.bank_val is null;
--[6]----end--------根据码表(t_code_table)更新"银行"字段,初始化时此字段为中文--------




--[7]----start--------添加经销商用户并分配角色--------                                      
  /*--创建经销商用户同步临时表
  create table t_dealer_user_sync(
     dealer_user_id   varchar2(32),
     dealer_user_name varchar2(100),
     GENDER           number,
     TEL              varchar2(100),
     EMAIL            varchar2(100)  
     
  )*/


  /*
  --创建md5函数(用于密码加密)
  CREATE OR REPLACE FUNCTION MD5(passwd IN VARCHAR2) RETURN VARCHAR2 IS retval varchar2(32);
  BEGIN
         retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;
         RETURN retval;
  END;


  --测试md5函数
  select md5(123456) from dual;
  --随机6位小数
  select substr(abs(dbms_random.random),1,6) from dual;
  
  */
  select * from t_dealer_user_sync;


  -- 1)删除原来的经销商用户
  delete from cas.sys_user c where c.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c';
  -- 2)使用ETL工具从内部库抽数到t_dealer_user_sync
  -- 3)t_dealer_user_sync表添加uuid主键
  update t_dealer_user_sync t set t.dealer_user_id = sys_guid()


  -- 4)往cas.sys_user表里插入经销商用户数据
  insert into cas.sys_user
  select t.dealer_user_id as USER_ID
         ,substr(abs(dbms_random.random),1,6) as LOGIN_ID
         ,md5(123456) as PASSWORD
         ,t.dealer_user_name as USER_NAME
         ,t.gender
         ,t.tel
         --,''
         ,''
         ,sysdate
         ,add_months(sysdate,240)
         ,''
         ,''
         ,sysdate
         ,''
         ,sysdate
         ,1
         ,1
         ,''
         ,t.email
        -- ,''
         ,'728eac62b54f4f4aa3aaacff70fbf97c'
         ,0
         ,t.dealer_user_name
    from t_dealer_user_sync t;


   /* select * from cas.sys_user u where u.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c'
    delete from cas.sys_user u where u.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c'


    --truncate table t_dealer_user_sync;
    select * from t_dealer_user_sync t where t.dealer_user_name = '薛天宇'*/


  -- 5)往cas.sys_user_role表里插入经销商用户角色数据
  insert into cas.sys_user_role
  select sys_guid()
         ,t.dealer_user_id
         ,'b2371031e6af42b3b6bf155336d50e00'
         ,''
    from t_dealer_user_sync t
    
  -- 6)往更新t_dealer表“联系人ID”
  update t_dealer a
     set a.CONTACTS_ID = (select b.DEALER_USER_ID
                             from t_dealer_user_sync b
                            where b.dealer_user_name = a.CONTACTS_ID) 
    
/*  select * from t_dealer_user_sync;
  select * from t_dealer;*/
  
--[7]----end--------添加经销商用户并分配角色--------         




--[8]----start--------添加经销商开发员并分配角色、更新经销商背景表"开发员ID"--------     
  -- 1)单独为"管理员"添加用户数据 OA_58是收集部
  insert into cas.sys_user
  select sys_guid() as USER_ID
         ,'admin' as LOGIN_ID
         ,md5(123456) as PASSWORD
         ,'管理员' as USER_NAME
         ,1
         ,t.tel
         ,''
         ,sysdate
         ,add_months(sysdate,240)
         ,''
         ,''
         ,sysdate
         ,''
         ,sysdate
         ,1
         ,1
         ,''
         ,t.email
         ,'OA_58'
         ,0
         ,'管理员'
    from dual;


  -- 2)查询"管理员"用户ID
  select * from cas.sys_user u where u.user_name = '管理员'
  -- 3)单独为"管理员"添加用户角色数据
  insert into cas.sys_user_role
  select sys_guid()
         ,'0EB90118D51647CDE050A8C01B018B69'
         ,'3e34f0c3acd24e32ba91808896efa0c4'
         ,''
    from t_dealer_user_sync t


 /* --3e34f0c3acd24e32ba91808896efa0c4 开发员角色ID  
  select * from cas.sys_user_role r where r.role_id = '3e34f0c3acd24e32ba91808896efa0c4';


                                 
  --创建经销商开发员同步临时表
  create table t_dealer_developer_sync(
     dealer_developer_id   varchar2(32),
     dealer_developer_name varchar2(100),
     inner_developer_id    varchar2(32)
  )
*/


  -- 4)根据中文名称查询用户ID
  merge into t_dealer_developer_sync a
  using cas.sys_user b
  on (a.DEALER_DEVELOPER_NAME = b.user_name)
  when matched then
  update set a.DEALER_DEVELOPER_ID = b.user_id;


  -- 5)删除用户ID为空的(即已离职)
  delete from t_dealer_developer_sync t where t.dealer_developer_id is null;


  -- 6)往cas.sys_user_role表里插入经销商开发员角色数据
  insert into cas.sys_user_role
  select sys_guid()
         ,t.dealer_developer_id
         ,'3e34f0c3acd24e32ba91808896efa0c4'
         ,''
    from t_dealer_developer_sync t


  -- 7)更新经销商背景表"开发员ID"
  update t_dealer_background a
     set a.developer_id = (select b.dealer_developer_id
                             from t_dealer_developer_sync b
                            where b.inner_developer_id = a.developer_id)
                            
select * from t_dealer_developer_sync;                            
--[8]----end--------添加经销商开发员并分配角色、更新经销商背景表"开发员ID"--------   


--[9]----start--------分配采集员角色--------
 insert into cas.sys_user_role
  select sys_guid()
         ,t.collector_id 
         ,(select t.role_id from cas.sys_role t where t.role_name = '收集员' and t.role_code = 'ROLE_DEALER_COLLECTOR')
         ,''
    from (
            select distinct t.collector_id 
              from t_channel_charge_rel t
             where t.collector_id is not null
   ) t


select * from cas.sys_role;


--[9]----start--------分配审核员角色--------
 insert into cas.sys_user_role
  select sys_guid()
         ,t.ASSESSOR_ID 
         ,(select t.role_id from cas.sys_role t where t.role_name = '审核员' and t.role_code = 'ROLE_DEALER_ASSESSOR')
         ,''
    from (
            select distinct t.ASSESSOR_ID 
              from t_channel_charge_rel t
             where t.ASSESSOR_ID is not null
   ) t


select * from cas.sys_role;




--[9]----end--------分配审核员角色--------
---------------------------------------------------------------------------------------------------------------
------------------------------------定时任务---------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
/*  --删除JOB 
  begin 
    dbms_job.remove(54); 
    commit;
  end; 


  --查看job信息
  select * from user_jobs ; 
  select * from all_jobs ;*/
  
----start--------经销商信息JOB(开发中)----------
  --每天凌晨3:00
  declare
    job number;
  begin
    --sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440');  --每分钟
    sys.dbms_job.submit(job, 'proc_dealer_auto_sync;', sysdate, 'TRUNC(sysdate)+1+3/24');
    commit;
  end;
----end--------经销商信息JOB(开发中)------------------------




----start--------旧CMS网点表同步到经销商网点表----------
  --每天0:00
  declare
    job number;
  begin
    sys.dbms_job.submit(job, 'proc_network_auto_sync;', sysdate, 'TRUNC(sysdate)+1');
    commit;
  end;
----end--------旧CMS网点表同步到经销商网点表------------------------




----start--------跟踪表JOB------------------------
  --每月10号00:00点
  declare
    job number;
  begin
    --sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440');  --每分钟
    sys.dbms_job.submit(job, 'proc_trace_table_bat;', sysdate, 'TRUNC(LAST_DAY(SYSDATE)+10)');
    commit;
  end;


  --每月24号00:00点
  declare
    job number;
  begin
    --sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440');  --每分钟
    sys.dbms_job.submit(job, 'proc_trace_table_bat;', sysdate, 'TRUNC(LAST_DAY(SYSDATE)+24)');
    commit;
  end;
----end--------跟踪表JOB------------------------ 


----start--------年度开发计划城市表JOB----------
  --每年12月31号23:00点
  declare
    job number;
  begin
    --sys.dbms_job.submit(job, 'proc_year_city_auto_bat;', sysdate, 'sysdate+1/1440');  --每分钟
    --sys.dbms_job.submit(job, 'proc_year_city_auto_bat;', sysdate, 'add_months(trunc(sysdate,'yyyy'),11) + 30 +(23*60)/(24*60)');
    commit;
  end;  
----end--------年度开发计划城市表JOB------------------------