存储过程 ."/>
数据迁移存储过程 .
create procedure update_07_from_06_cell()--------- RADIO_CELL_G 数据迁移--OK--------------------------------------------------------------------------------------------------------update radio_cell_gset 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))))));--okselect * 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;--okselect * 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;--okselect * 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;--okselect * 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;--okselect * 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;--okselect * 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;--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
--noselect 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;
--okselect * 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;
--okselect * 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;
--okselect * 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;
更多推荐
数据迁移存储过程 .
发布评论