数据迁移存储过程 .

最新推荐文章于 2022-09-07 14:08:43 发布
larry_lv
最新推荐文章于 2022-09-07 14:08:43 发布
阅读量510
点赞数
分类专栏：数据库
数据库专栏收录该内容
20 篇文章 0 订阅
订阅专栏
create procedure update_07_from_06_cell()
    --------- RADIO_CELL_G 数据迁移--OK--------------------------------------------------------------------------------------------------------
    update radio_cell_g
    set fdn = substr(cuid,1,decode(substr(cuid,24,1),':',23,
                            decode(substr(cuid,25,1),':',24,
                            decode(substr(cuid,26,1),':',25,
                            decode(substr(cuid,27,1),':',26,
                            decode(substr(cuid,28,1),':',27))))));
                          
    --ok
    select * from edis2@edisdb1:bts where ant_dirct_angle is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ANT_AZIMUTH = (select ant_dirct_angle from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    drop table t12;
        --ok
    select * from edis2@edisdb1:bts where ant_height is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ANT_HEIGHT = (select ant_height from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    drop table t12;
        --ok
    select * from edis2@edisdb1:bts where ant_model is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ant_model = (select ant_model from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    drop table t12;
        --ok
    select * from edis2@edisdb1:bts where ant_tilt_angle is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ANT_PITCHING = (select ant_tilt_angle from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    drop table t12;
        --ok
    select * from edis2@edisdb1:bts where ant_gain is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ANT_PLUS = (select ant_gain from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    drop table t12;
        --ok
    select * from edis2@edisdb1:bts where ant_tilt_angle_e is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ANT_TILT_ANGLE_E = (select ant_tilt_angle_e from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    drop table t12;
        --ok
    select * from edis2@edisdb1:bts where ant_tilt_angle_m is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ANT_TILT_ANGLE_M = (select ant_tilt_angle_m from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where ant_type is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ANT_TYPE = (select ant_type from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    -------------------未执行成功，新系统为integer，老系统为字符型
    --drop table t12;
--ok
    --select * from edis2@edisdb1:bts where ant_vendor is not null and confirmed not in (2,5) into temp t12 ;
    --update RADIO_CELL_G SET  ANT_VENDOR = (select ant_vendor from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    --where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where aux_equ_cat is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  AUX_EQU_CAT = (select aux_equ_cat from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where cell_reselect_algo is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CELL_RESELECT_ALGO = (select cell_reselect_algo from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where cell_resel_hyster is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CELL_RESEL_HYSTER = (select cell_resel_hyster from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where CELL_TYPE is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CELL_TYPE = (select CELL_TYPE from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where trim(COVER_AREA_INFO)<>'' and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  COVER_AREA_INFO = (select COVER_AREA_INFO from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where COVER_AREA_TYPE is not null and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  COVER_AREA_TYPE = (select COVER_AREA_TYPE from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where trim(CVR_BEAUTY_SPOT)<>''  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CVR_BEAUTY_SPOT = (select CVR_BEAUTY_SPOT from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where trim(CVR_BUSINESS)<>''  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CVR_BUSINESS = (select CVR_BUSINESS from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where trim(CVR_HIGHWAY)<>''  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CVR_HIGHWAY = (select CVR_HIGHWAY from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where trim(CVR_ROAD )<>''  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CVR_ROAD  = (select CVR_ROAD  from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where trim(CVR_SCHOOL )<>''  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CVR_SCHOOL  = (select CVR_SCHOOL  from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where CVR_TRAF_HINGE is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  CVR_TRAF_HINGE  = (select CVR_TRAF_HINGE  from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where DIVIDE_OUT_TIME  is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  DIVIDE_OUT_TIME   = (select DIVIDE_OUT_TIME   from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where DYNAMIC_PDCH   is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  DYNAMIC_PDCH    = (select DYNAMIC_PDCH    from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where GPRS_TRX_NUM   is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  GPRS_TRX_NUM    = (select GPRS_TRX_NUM    from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where GPRS_TS_NUM   is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  GPRS_TS_NUM    = (select GPRS_TS_NUM    from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where HOPPING_TYPE   is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  HOPPING_TYPE    = (select HOPPING_TYPE    from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where IS_BOUNDARY   is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  IS_BOUNDARY    = (select IS_BOUNDARY    from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where ITA_FLAG   is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ITA_FLAG    = (select ITA_FLAG    from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
     
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where LATITUDE    is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  LATITUDE     = (select LATITUDE     from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select * from edis2@edisdb1:bts where LOCATION_AREA is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  LOCATION_AREA     = (select LOCATION_AREA     from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where LONGITUDE is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  LONGITUDE     = (select LONGITUDE     from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where MAX_PDTCH  is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  MAX_PDTCH      = (select MAX_PDTCH      from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where cell_model  is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  MODEL      = (select cell_model      from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where trim(ne_code)<>''   and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  NECODE       = (select ne_code      from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where Resource_status  is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET ONLINE_STATE      = (select Resource_status      from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where pbcch_broad_para  is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  PBCCH_BROAD_PARA      = (select pbcch_broad_para     from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where pbcch_indicator   is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  PBCCH_INDICATOR      = (select pbcch_indicator      from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where pccch_indicator  is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET PCCCH_INDICATOR      = (select pccch_indicator      from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where divide_in_time   is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET SETUP_TIME        = (select divide_in_time    from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where static_pdch  is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET STATIC_PDCH        = (select static_pdch   from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where bts_trx_power  is not null  and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET TRX_POWER        = (select bts_trx_power   from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where trim(memo  )<>''   and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  REMARK        = (select memo       from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
--ok
    --update RADIO_CELL_G SET  LEN_OF_FEEDLINE       = (select len_of_feedline     from edis2@edisdb1:bts where substr(RADIO_CELL_G.fdn,17) = substr(edis2@edisdb1:bts.ne_rdn,9));
    
    --------------pcu_id类型错误，应该为字符型－－－－－－－－－－－
    drop table t12;
--no
    select substr(sc_in_pcu.ne_rdn,9) pcu_rdn,edis2@edisdb1:bts.* from sc_in_pcu,edis2@edisdb1:bts 
    where edis2@edisdb1:bts.PCU_ID = sc_in_pcu.int_id   and edis2@edisdb1:bts.confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  PCU_ID       = (select pcu.CUID   from t12,pcu where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) and substr(pcu.cuid,8) = t12.pcu_rdn) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where Project_id is not null   and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  RELATED_PROJECT_CUID       = (select Project_id         from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where Machinery_id is not null   and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  RELATED_ROOM_CUID      = (select Machinery_id       from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    
    drop table t12;
--ok
    select *  from edis2@edisdb1:bts where route_area_id is not null   and confirmed not in (2,5) into temp t12 ;
    update RADIO_CELL_G SET  ROUTE_AREA_ID        = (select route_area_id    from t12 where substr(RADIO_CELL_G.fdn,17) = substr(t12.ne_rdn,9) ) 
    where substr(RADIO_CELL_G.fdn,17) in (select substr(t12.ne_rdn,9) from t12);
    
    drop table t12;