2干的neport
-----------------改变全角字符SELECT 'RMS_TRANSCIR_USER->改变全角字符:' AS remark,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') AS deal_time FROM dual;UPDATE RMS_TRANSCIR_USER SET SOURCE_NE=REPLACE(SOURCE_NE,'－','-' );UPDATE RMS_TRANSCIR_USERSET REMOTE_TRANS_EQU=REPLACE(REMOTE_TRANS_EQU,'－','-' );-----------------对网元编号提取，存入本端和对端numSELECT 'RMS_TRANSCIR_USER->对网元编号提取，存入本端和对端num:' AS remark,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') AS deal_time FROM dual;UPDATE RMS_TRANSCIR_USERSET SOURCE_NUM=SUBSTR(SOURCE_NE,1,INSTR(SOURCE_NE,'-',1)-1);UPDATE RMS_TRANSCIR_USERSET REMOTE_NUM=SUBSTR(REMOTE_TRANS_EQU,1,INSTR(REMOTE_TRANS_EQU,'-',1)-1);-----------------根据本端/对端的编号从 RMS_TRANSNE_2GX 更新网元名SELECT 'RMS_TRANSCIR_USER->更新网元名:' AS remark,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') AS deal_time FROM dual;UPDATE RMS_TRANSCIR_USER usrSET SOURCE_NE= ( select ne.old_name from RMS_TRANSNE_2GX ne where substr(ne.old_name,1,instr(replace(ne.old_name,'－','-'),'-',1)-1)=usr.SOURCE_NUM --and ne.related_ems=usr. --and rownum=1 )where exists ( select 1 from RMS_TRANSNE_2GX ne where substr(ne.old_name,1,instr(replace(ne.old_name,'－','-'),'-',1)-1)=usr.SOURCE_NUM );UPDATE RMS_TRANSCIR_USER usrSET REMOTE_TRANS_EQU= ( select ne.old_name from RMS_TRANSNE_2GX ne where substr(ne.old_name,1,instr(replace(ne.old_name,'－','-'),'-',1)-1)=usr.REMOTE_NUM --and rownum=1 )where exists ( select 1 from RMS_TRANSNE_2GX ne where substr(ne.old_name,1,instr(replace(ne.old_name,'－','-'),'-',1)-1)=usr.REMOTE_NUM );----------------本/对端带扩的PQ拆分SELECT 'RMS_TRANSCIR_USER->带扩的PQ拆分:' AS remark,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') AS deal_time FROM dual;update RMS_TRANSCIR_USERset SOURCE_TRANS_PORT=substr(SOURCE_TRANS_PORT,instr(SOURCE_TRANS_PORT,'-',1)+1,instr(SOURCE_TRANS_PORT,'PQ1',1)-instr(SOURCE_TRANS_PORT,'-',1)-1)||'-'||substr(SOURCE_TRANS_PORT,instr(SOURCE_TRANS_PORT,'PQ1',1))where regexp_like(SOURCE_TRANS_PORT,'\d+\-\d+PQ1*');update RMS_TRANSCIR_USERset REMOTE_TRANS_PORT=substr(REMOTE_TRANS_PORT,instr(REMOTE_TRANS_PORT,'-',1)+1,instr(REMOTE_TRANS_PORT,'PQ1',1)-instr(REMOTE_TRANS_PORT,'-',1)-1)||'-'||substr(REMOTE_TRANS_PORT,instr(REMOTE_TRANS_PORT,'PQ1',1))where regexp_like(REMOTE_TRANS_PORT,'\d+\-\d+PQ1*');----------------本/对端不带扩的PQ拆分SELECT 'RMS_TRANSCIR_USER->不带扩的PQ拆分:' AS remark,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') AS deal_time FROM dual;update RMS_TRANSCIR_USERset SOURCE_TRANS_PORT=substr(SOURCE_TRANS_PORT,1,instr(SOURCE_TRANS_PORT,'PQ1',1)-1)||'-'||substr(SOURCE_TRANS_PORT,instr(SOURCE_TRANS_PORT,'PQ1',1))where regexp_like(SOURCE_TRANS_PORT,'\d+PQ1*');update RMS_TRANSCIR_USERset REMOTE_TRANS_PORT=substr(REMOTE_TRANS_PORT,1,instr(REMOTE_TRANS_PORT,'PQ1',1)-1)||'-'||substr(REMOTE_TRANS_PORT,instr(REMOTE_TRANS_PORT,'PQ1',1))where regexp_like(REMOTE_TRANS_PORT,'\d+PQ1*');----------------更新端口状态SELECT 'RMS_NEPORT_2GX->更新端口状态:' AS remark,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') AS deal_time FROM dual;update RMS_NEPORT_2GX portset port.PORT_STATUS_USER=null;update RMS_NEPORT_2GX portset port.PORT_STATUS_USER='在用'where exists( select 1 from RMS_TRANSCIR_USER where REMOTE_TRANS_PORT=port.OLD_NAME and REMOTE_TRANS_EQU=port.SOURCE_NE_COL);update RMS_NEPORT_2GX portset port.PORT_STATUS_USER='在用'WHERE exists ( SELECT 1 FROM RMS_TRANSCIR_USER usr where usr.SOURCE_TRANS_PORT = port.OLD_NAME and usr.SOURCE_NE= port.SOURCE_NE_COL );
