2干的neport

ztbsuper
于 2010-04-28 11:17:31 发布
阅读量106
点赞数
分类专栏：四川移动oss 文章标签： SQL
四川移动oss 专栏收录该内容
10 篇文章 0 订阅
订阅专栏
-----------------改变全角字符
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_USER
SET REMOTE_TRANS_EQU=REPLACE(REMOTE_TRANS_EQU,'－','-' );
-----------------对网元编号提取，存入本端和对端num
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_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);


-----------------根据本端/对端的编号从 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 usr
SET 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 usr
SET 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_USER
set 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_USER
set 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_USER
set 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_USER
set 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 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 
);