存储过程"/>
数据迁移存储过程
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;
update radio_cell_g set fdn = null ;
end procedure ;
更多推荐
数据迁移存储过程
发布评论