2干的NEPORT_2V

-----更改设备
SELECT 'RMS_TRANSCIR_USER->对网元编号提取，存入本端和对端num:' 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_USER
SET REMOTE_TRANS_EQU=REPLACE(REMOTE_TRANS_EQU,'－','-' );

UPDATE RMS_TRANSCIR_USER
SET SOURCE_NUM=SUBSTR(SOURCE_NE,1,INSTR(SOURCE_NE,'-',1)-1);

UPDATE RMS_TRANSCIR_USER
SET REMOTE_NUM=SUBSTR(REMOTE_TRANS_EQU,1,INSTR(REMOTE_TRANS_EQU,'-',1)-1);

UPDATE RMS_TRANSCIR_USER
SET REMOTE_NUM='11074'
WHERE REMOTE_TRANS_EQU='11074广安2扩1';

UPDATE RMS_TRANSCIR_USER
SET REMOTE_NUM='11172'
WHERE REMOTE_TRANS_EQU='11172泸州2-9层扩1';

UPDATE RMS_TRANSCIR_USER
SET REMOTE_NUM='625'
WHERE REMOTE_TRANS_EQU='625南充3扩1';

UPDATE RMS_TRANSCIR_USER
SET SOURCE_NUM='11101'
WHERE SOURCE_NE='11101万年扩2';


--更新网元名称
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
  =(SELECT OLD_NAME
    FROM RMS_TRANSNE_2GX
    WHERE NENO=RMS_TRANSCIR_USER.SOURCE_NUM)
WHERE EXISTS (
  SELECT 1
  FROM RMS_TRANSNE_2GX
  WHERE NENO=RMS_TRANSCIR_USER.SOURCE_NUM);


UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_EQU
  =(SELECT OLD_NAME
    FROM RMS_TRANSNE_2GX
    WHERE NENO=RMS_TRANSCIR_USER.REMOTE_NUM)
WHERE EXISTS ( 
  SELECT 1
  FROM RMS_TRANSNE_2GX
  WHERE NENO=RMS_TRANSCIR_USER.REMOTE_NUM);


--端口处理
SELECT 'RMS_TRANSCIR_USER->G3/上下规整:' AS remark,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') AS deal_time FROM dual;

UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT=REPLACE(SOURCE_TRANS_PORT,'(G3)','' );

UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT=REPLACE(REMOTE_TRANS_PORT,'(G3)','' );

UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT=REPLACE(SOURCE_TRANS_PORT,'1上-','' );

UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT=REPLACE(REMOTE_TRANS_PORT,'1上-','' );

UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT=REPLACE(SOURCE_TRANS_PORT,'1下-','' );

UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT=REPLACE(REMOTE_TRANS_PORT,'1下-','' );

UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT=REPLACE(SOURCE_TRANS_PORT,'2上-','' );

UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT=REPLACE(REMOTE_TRANS_PORT,'2上-','' );

UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT=REPLACE(SOURCE_TRANS_PORT,'2下-','' );

UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT=REPLACE(REMOTE_TRANS_PORT,'2下-','' );

------------------对端口进行规整

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_TRANS_PORT=SUBSTR(SOURCE_TRANS_PORT,1,REGEXP_INSTR(SOURCE_TRANS_PORT,'[a-zA-Z]',1,1)-1)||'-'||SUBSTR(SOURCE_TRANS_PORT,REGEXP_INSTR(SOURCE_TRANS_PORT,'[a-zA-Z]',1,1),REGEXP_INSTR(SOURCE_TRANS_PORT,'-',1,1)-(REGEXP_INSTR(SOURCE_TRANS_PORT,'[a-zA-Z]',1,1)))||SUBSTR(SOURCE_TRANS_PORT,REGEXP_INSTR(SOURCE_TRANS_PORT,'-',1,1))
--WHERE REGEXP_LIKE(SOURCE_TRANS_PORT,'^\d+[A-Za-z]+\d?\-\d+$');
--UPDATE RMS_TRANSCIR_USER
--SET REMOTE_TRANS_PORT=SUBSTR(REMOTE_TRANS_PORT,1,REGEXP_INSTR(REMOTE_TRANS_PORT,'[a-zA-Z]',1,1)-1)||'-'||SUBSTR(REMOTE_TRANS_PORT,REGEXP_INSTR(REMOTE_TRANS_PORT,'[a-zA-Z]',1,1),REGEXP_INSTR(REMOTE_TRANS_PORT,'-',1,1)-(REGEXP_INSTR(REMOTE_TRANS_PORT,'[a-zA-Z]',1,1)))||SUBSTR(REMOTE_TRANS_PORT,REGEXP_INSTR(REMOTE_TRANS_PORT,'-',1,1))
--WHERE REGEXP_LIKE(REMOTE_TRANS_PORT,'^\d+[A-Za-z]+\d?\-\d+$');
----  10PQ1-2
UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT=SUBSTR(SOURCE_TRANS_PORT,1,REGEXP_INSTR(SOURCE_TRANS_PORT,'[a-zA-Z]',1,1)-1)||'-'||SUBSTR(SOURCE_TRANS_PORT,REGEXP_INSTR(SOURCE_TRANS_PORT,'[a-zA-Z]',1,1))
WHERE REGEXP_LIKE(SOURCE_TRANS_PORT,'^\d+[A-Za-z]+\d?\-\d+$');

UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT=SUBSTR(REMOTE_TRANS_PORT,1,REGEXP_INSTR(REMOTE_TRANS_PORT,'[a-zA-Z]',1,1)-1)||'-'||SUBSTR(REMOTE_TRANS_PORT,REGEXP_INSTR(REMOTE_TRANS_PORT,'[a-zA-Z]',1,1))
WHERE REGEXP_LIKE(REMOTE_TRANS_PORT,'^\d+[A-Za-z]+\d?\-\d+$');

----  2-10PQ1-2
UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT= SUBSTR(SOURCE_TRANS_PORT,INSTR(SOURCE_TRANS_PORT,'-',1,1)+1,REGEXP_INSTR(SOURCE_TRANS_PORT,'[a-zA-Z]',1)-INSTR(SOURCE_TRANS_PORT,'-',1,1)-1)||'-'||SUBSTR(SOURCE_TRANS_PORT,REGEXP_INSTR(SOURCE_TRANS_PORT,'[a-zA-Z]',1),INSTR(SOURCE_TRANS_PORT,'-',1,2)-REGEXP_INSTR(SOURCE_TRANS_PORT,'[a-zA-Z]',1,1))||'-'||SUBSTR(SOURCE_TRANS_PORT,INSTR(SOURCE_TRANS_PORT,'-',1,2)+1)
WHERE REGEXP_LIKE(SOURCE_TRANS_PORT,'^\d+\-\d+[A-Za-z]+\d?\-\d+$');

UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT= SUBSTR(REMOTE_TRANS_PORT,INSTR(REMOTE_TRANS_PORT,'-',1,1)+1,REGEXP_INSTR(REMOTE_TRANS_PORT,'[a-zA-Z]',1)-INSTR(REMOTE_TRANS_PORT,'-',1,1)-1)||'-'||SUBSTR(REMOTE_TRANS_PORT,REGEXP_INSTR(REMOTE_TRANS_PORT,'[a-zA-Z]',1),INSTR(REMOTE_TRANS_PORT,'-',1,2)-REGEXP_INSTR(REMOTE_TRANS_PORT,'[a-zA-Z]',1,1))||'-'||SUBSTR(REMOTE_TRANS_PORT,INSTR(REMOTE_TRANS_PORT,'-',1,2)+1)
WHERE REGEXP_LIKE(REMOTE_TRANS_PORT,'^\d+\-\d+[A-Za-z]+\d?\-\d+$');


UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT=REPLACE(SOURCE_TRANS_PORT,'-EFT-','-EFT0-')
WHERE SOURCE_TRANS_PORT LIKE '%-EFT-%';

UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT=REPLACE(REMOTE_TRANS_PORT,'-EFT-','-EFT0-')
WHERE REMOTE_TRANS_PORT LIKE '%-EFT-%';

--更改PQ->PQ1
SELECT 'RMS_TRANSCIR_USER->更改PQ->PQ1:' AS remark,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') AS deal_time FROM dual;

UPDATE RMS_TRANSCIR_USER
SET SOURCE_TRANS_PORT=replace(SOURCE_TRANS_PORT,'-PQ-','-PQ1-')
WHERE SOURCE_TRANS_PORT LIKE '%-PQ-%';
--
UPDATE RMS_TRANSCIR_USER
SET REMOTE_TRANS_PORT=REPLACE(REMOTE_TRANS_PORT,'-PQ-','-PQ1-') 
WHERE REMOTE_TRANS_PORT LIKE '%-PQ-%';


----------------更新端口状态
--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 port
--set port.PORT_STATUS_USER=null;
--
--
--update RMS_NEPORT_2GX port
--set 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 port
--set 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 
--);
--
--UPDATE RMS_NEPORT_2GX PORT 
--SET port.PORT_STATUS_USER='空闲'
--WHERE port.PORT_STATUS_USER is null;
--