oracle 过程 执行字符串sql

CREATE OR REPLACE PROCEDURE NRMS_TB.P_TB_YYS IS
M_CNT NUMBER(1);
V_CNT   NUMBER(2);
V_CITYCODE VARCHAR2(5);
V_FIELDS    VARCHAR2(1000);
V_SQL   VARCHAR2(1000);
V_OLDID    VARCHAR2(100);
--同步库少的或不一样的
CURSOR CR_LESS IS 
SELECT OBJECT_RDN, SYS_ID, SYS_NAME, EMS_ID, EMS, SYS_TYPE, SYS_LEVEL, SYS_TOPO, SYS_RATE, SYS_CHANNEL_NUM,SPACE_ID,SPACE_NAME FROM NRMS_JK.CS_TRANSMISSIONSYS  
MINUS
SELECT OBJECT_RDN, SYS_ID, SYS_NAME, EMS_ID, EMS, SYS_TYPE, SYS_LEVEL, SYS_TOPO, SYS_RATE, SYS_CHANNEL_NUM,SPACE_ID,SPACE_NAME FROM CS_TRANSMISSIONSYS ;
--同步库L多的
CURSOR CR_V_MORE IS
SELECT SYS_ID,SYS_TYPE FROM CS_TRANSMISSIONSYS 
MINUS 
SELECT SYS_ID,SYS_TYPE FROM NRMS_JK.CS_TRANSMISSIONSYS;

BEGIN

    UPDATE NRMS_JK.CS_TRANSMISSIONSYS SET SYS_LEVEL=CASE
        WHEN SYS_LEVEL='1' THEN '221'
        WHEN SYS_LEVEL IN('2','3') THEN '222'
        WHEN SYS_LEVEL='4' THEN '223'
        WHEN SYS_LEVEL='5' THEN '224'
        WHEN SYS_LEVEL='6' THEN '225'
        ELSE SYS_LEVEL END;
        
    UPDATE NRMS_JK.CS_TRANSMISSIONSYS SET SYS_TOPO=CASE
    WHEN SYS_TOPO='1' THEN '1058'
    WHEN SYS_TOPO='2' THEN '1062'
    ELSE SYS_TOPO END;
   
    COMMIT;
 
   FOR C1 IN CR_LESS LOOP
  
    UPDATE CS_TRANSMISSIONSYS SET
        OBJECT_RDN=         C1.OBJECT_RDN, 
        SYS_NAME=           C1.SYS_NAME ,
        EMS_ID=             C1.EMS_ID ,
        EMS=                C1.EMS ,
        SYS_TYPE=           C1.SYS_TYPE ,
        SYS_LEVEL=          C1.SYS_LEVEL,
        SYS_TOPO=           C1.SYS_TOPO,
        SYS_RATE=           C1.SYS_RATE,
        SYS_CHANNEL_NUM=    C1.SYS_CHANNEL_NUM     
      WHERE SYS_ID=C1.SYS_ID;
    M_CNT := SQL%ROWCOUNT;
    --没有有这个记录
    BEGIN
        SELECT FIELDS INTO V_FIELDS FROM SYS_TB_CONFIG WHERE TYPE = 'TransSystem';
        SELECT NRMS_JK.F_REGIONID2CODE(C1.SPACE_ID) INTO V_CITYCODE FROM DUAL;
        IF M_CNT = 0 THEN
           SELECT COUNT(*) INTO V_CNT FROM CS_TRANSMISSIONSYS WHERE SYS_NAME=C1.SYS_NAME;
           IF V_CNT>0 THEN
                SELECT SYS_ID INTO V_OLDID FROM CS_TRANSMISSIONSYS WHERE SYS_NAME = C1.SYS_NAME;    
                
                V_SQL:='UPDATE CS_TRANSMISSIONSYS SET ('||V_FIELDS||') = (SELECT * FROM NRMS_JK.CS_TRANSMISSIONSYS WHERE SYS_ID=:1) WHERE SYS_NAME=:2';
                EXECUTE IMMEDIATE V_SQL USING C1.SYS_ID,C1.SYS_NAME;
                
                P_REFRESH_RES(C1.SYS_TYPE||'_SYS',0,V_OLDID,C1.SYS_ID);
           
           ELSE
              V_SQL:='INSERT INTO CS_TRANSMISSIONSYS ('||V_FIELDS||') SELECT * FROM NRMS_JK.CS_TRANSMISSIONSYS WHERE SYS_ID = :1';
              EXECUTE IMMEDIATE V_SQL USING C1.SYS_ID;
              
              
              UPDATE CS_TRANSMISSIONSYS A SET CITY_CODE = V_CITYCODE WHERE SYS_ID=C1.SYS_ID;
              UPDATE NRMS.S_TRANSMISSIONSYS A SET CITY_CODE = V_CITYCODE WHERE ZRESID=C1.SYS_ID;
            END IF;
          
          
        ELSE--有这一条，但是字段属性不一样
          --其他正式库表的处理，写这里
          V_SQL:='UPDATE CS_TRANSMISSIONSYS SET ('||V_FIELDS||') = (SELECT * FROM NRMS_JK.CS_TRANSMISSIONSYS WHERE SYS_ID=:1) WHERE SYS_ID=:2';
          EXECUTE IMMEDIATE V_SQL USING C1.SYS_ID,C1.SYS_ID;     
         
          P_REFRESH_RES(C1.SYS_TYPE||'_SYS',1,C1.SYS_ID,C1.SYS_ID);
        END IF;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            P_CS_ERROR(C1.SYS_TYPE||'_SYS',C1.SYS_ID,SQLERRM,'CS');
    END;  
  END LOOP; 
   
  
--查出来，本地库比接口多的  
  FOR C1 IN CR_V_MORE  LOOP
    BEGIN
        SELECT CITY_CODE INTO V_CITYCODE FROM CS_TRANSMISSIONSYS WHERE SYS_ID=C1.SYS_ID;
        
        DELETE FROM CS_TRANSMISSIONSYS WHERE SYS_ID=C1.SYS_ID;
        P_DEL_RES(C1.SYS_ID, 'S_TRANSMISSIONSYS', C1.SYS_TYPE||'_SYS');
    EXCEPTION
        WHEN OTHERS THEN
            P_CS_ERROR(C1.SYS_TYPE||'_SYS',C1.SYS_ID,SQLERRM,'CS');
    END;        
  END LOOP;
  COMMIT;
  
  BEGIN
    NRMS.P_AUTO_TB_TRANSSYS();
    COMMIT;
  EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            P_CS_ERROR('SYS',NULL,SQLERRM,'CS');
  END;
  
  EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        P_CS_ERROR('SYS','STOP',SQLERRM,'CS');
END P_TB_YYS ;