Oracle存储过程中使用游标来批量解析CLOB字段里面的xml字符串:

短命女 2024-02-17 15:44 30阅读 0赞

摘要：之前在项目中使用到了Oracle数据库中通过触发器去调用存储过程执行数据解析并Update到对应的数据表中，但是，经过一段时间的测试使用发现，如果job那天停掉了，然后你再重新新建job的话，这时候可能会有很多遗留的数据没有出来，因为之前是通过触发器的方式，来一条数据，解析一条并Update到对应的数据表中，现在一下要执行很多数据，就会报错内存溢出的错误，所以后来经过分析讨论，把之前通过触发器解析的方式改成了通过job来定时调用存储过程解析，并且在存储过程中增加了游标的使用，这样就不会有丢失的数据，同时也能保证在那天job出问题了，再新建job后数据解析出现问题了，具体的SQL如下：

这里我把我用到的所有的存储过程，函数，job的SQL都贴上来，方便大家参考：

一：函数：

CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2)
       RETURN VARCHAR2
    IS
       --定义几个变量，出来解析过来的时间字符串
       --日月年时分(11OCT141024)
       Str      VARCHAR2(32);
       
       AA       VARCHAR2(32);
    
       DAY      VARCHAR2(32);
    
       MOUNTH   VARCHAR2(32);
    
       YEAR     VARCHAR2(32);
    
       HOUR     VARCHAR2(32);
    
       MINUTE   VARCHAR2(32);
    
       ValueReturn   VARCHAR2 (64);
    
    BEGIN
    
       IF key != ' ' THEN
    
       DAY := SUBSTR(key,0,2);
       
       MOUNTH := SUBSTR(key,3,3);
       
       IF INSTR (MOUNTH,'JAN') > 0 THEN
       MOUNTH := 01;
       END IF;
       
       IF INSTR (MOUNTH,'FEB') > 0 THEN
       MOUNTH := 02;
       END IF;
       
       IF INSTR (MOUNTH,'MAR') > 0 THEN
       MOUNTH := 03;
       END IF;
       
       IF INSTR (MOUNTH,'APR') > 0 THEN
       MOUNTH := 04;
       END IF;
       
       IF INSTR (MOUNTH,'MAY') > 0 THEN
       MOUNTH := 05;
       END IF;
       
       IF INSTR (MOUNTH,'JUN') > 0 THEN
       MOUNTH := 06;
       END IF;
       
       IF INSTR (MOUNTH,'JUL') > 0 THEN
       MOUNTH := 07;
       END IF;
       
       IF INSTR (MOUNTH,'AUG') > 0 THEN
       MOUNTH := 08;
       END IF;
       
       IF INSTR (MOUNTH,'SEP') > 0 THEN
       MOUNTH := 09;
       END IF;
       
       IF INSTR (MOUNTH,'OCT') > 0 THEN
       MOUNTH := 10;
       END IF;
       
       IF INSTR (MOUNTH,'NOV') > 0 THEN
       MOUNTH := 11;
       END IF;
       
       IF INSTR (MOUNTH,'DEC') > 0 THEN
       MOUNTH := 12;
       END IF;
       
       YEAR := SUBSTR(key,6,2);
       
       HOUR := SUBSTR(key,8,2);
       
       MINUTE := SUBSTR(key,-2);
       
       AA := 20;
       
       Str := 0;
    
       --日月年时分(11OCT141017)
       IF length(MOUNTH) < 2 THEN
       
       MOUNTH := Str||MOUNTH;
       
       ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE;
       
       ELSE
       
       ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE;
       
       END IF;
       
       --ValueReturn := HOUR || ':' || MINUTE;
    
       RETURN ValueReturn;
    
       ELSE
       
       ValueReturn := ' ';
    
       RETURN ValueReturn;
    
       END IF;
    
    END FormatDateValue;
    /
    
    
    CREATE OR REPLACE FUNCTION MIP.GetXmlNodeValue (xmlStr CLOB, nodeName VARCHAR2)
       RETURN VARCHAR2
    IS
       --创建xml解析器实例xmlparser.Parser
       xmlPar        xmlparser.Parser := xmlparser.newParser;
       
       --定义DOM文档
       xDoc          xmldom.DOMDocument;
       
       --定义item子节点数目变量
       lenItme       INTEGER;
       
       --定义节点列表，存放item节点们
       itemNodes     xmldom.DOMNodeList;
       
       --定义节点，存放单个item节点
       itemNode      xmldom.DOMNode;
       
       ValueReturn   VARCHAR2 (100);
       
       
       
    BEGIN
        
       --解析xmlStr中xml字符串，并存放到xmlPar中
       xmlparser.parseClob (xmlPar, xmlStr);
    
       --将xmlPar中的数据转存到dom文档中
       xDoc := xmlparser.getDocument (xmlPar);
       
       --释放解析器实例
       xmlparser.freeParser (xmlPar);
    
       --获取所有item节点
       itemNodes := xmldom.getElementsByTagName (xDoc, nodeName);
       
       --获取item节点的个数
       lenItme := xmldom.getLength (itemNodes);
        
      --如果无该标签，则返回EMPTY
       IF lenItme = 0 THEN
       
       ValueReturn := ' ';
      
       ELSE
       
       --获取节点列表中的第1个item节点
       itemNode := xmldom.item (itemNodes, 0);
    
       --获取所有子节点的值
       ValueReturn := xmldom.getNodeValue (xmldom.getFirstChild (itemNode));
       
       END IF;   
       
       --释放dom
       xmldom.freeDocument(xDoc);
       
       RETURN ValueReturn;
       
    END GetXmlNodeValue;
    /

二：存储过程：

CREATE OR REPLACE PROCEDURE MIP.JOB_PRO_TEMP
    AS
       TEMP_ID   NUMBER;
    BEGIN
       SELECT   NVL (MAX (ID), 0) INTO TEMP_ID FROM MBINMSGS_TEMP;
    
       INSERT INTO MBINMSGS_TEMP
          SELECT   *
            FROM   MBINMSGS
           WHERE   MBINMSGS.ID > TEMP_ID;
           --WHERE MBINMSGS.mbinmsgs_date_processed > to_date('2015-1-12 12:21:23','yyyy-mm-dd hh24:mi:ss');
    
       COMMIT;
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.PUT_LINE ('Exception happened,data was rollback');
          ROLLBACK;
    END;
    /
    
    
    CREATE OR REPLACE PROCEDURE MIP.LOOP_COPY_PRO_TEMP
    
    AS 
    
    BEGIN
    
        DECLARE
           --定义游标
           CURSOR c_cursor
           
           IS
           --这里查询指定时间内的数据，根据时间判断一下id>那个编号开始
           SELECT ID,MBINMSGS_CLOB_MSG FROM MBINMSGS_TEMP WHERE MBINMSGS_DATE_PROCESSED IS NULL ORDER BY ID DESC;
    
           v_ID MBINMSGS_TEMP.ID%TYPE;
           v_MBINMSGS_CLOB_MSG MBINMSGS_TEMP.MBINMSGS_CLOB_MSG%TYPE;
           
        BEGIN
            
           --打开游标
           OPEN c_cursor;
    
           --提取游标数据
           FETCH c_cursor INTO   v_ID,v_MBINMSGS_CLOB_MSG;
    
           WHILE c_cursor%FOUND
           
           LOOP
               
              DBMS_OUTPUT.put_line (v_ID ||':'||v_MBINMSGS_CLOB_MSG);
    
              MIP_PARSE(v_MBINMSGS_CLOB_MSG);
              
              UPDATE MBINMSGS_TEMP SET MBINMSGS_DATE_PROCESSED = SYSDATE WHERE ID=v_ID;
              
              FETCH c_cursor INTO   v_ID,v_MBINMSGS_CLOB_MSG;
              
           END LOOP;
           --关闭游标
           CLOSE c_cursor;
           
           COMMIT;
           
           EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.PUT_LINE ('Exception happened,data was rollback');
          ROLLBACK;
           
        END;
    
    END;
    /
    
    
    
    CREATE OR REPLACE PROCEDURE MIP.MIP_PARSE (xmlStr IN CLOB)
    IS
       
       STYP     VARCHAR2 (100);
       RENO     VARCHAR2 (100);
       AIRLINE  VARCHAR2 (100);
       FFID     VARCHAR2 (100);
       FFID_A   VARCHAR2 (100);
       FFID_D   VARCHAR2 (100);
       ABNS     VARCHAR2 (100);
       ACFT     VARCHAR2 (100);
       CHDT     VARCHAR2 (100);
       EIBT     VARCHAR2 (100);
       FATA     VARCHAR2 (100);
       FETA     VARCHAR2 (100);
       FSTA     VARCHAR2 (100);
       LMDT     VARCHAR2 (100);
       LMUR     VARCHAR2 (100);
       PSTM     VARCHAR2 (100);
       RWAY     VARCHAR2 (100);
       SPOT     VARCHAR2 (100);
       STND     VARCHAR2 (100);
       SDEC     VARCHAR2 (100);
       A_TOBT   VARCHAR2 (100);
       A_WEATHER     VARCHAR2 (100);
       ASAT     VARCHAR2 (100);
       BCTM     VARCHAR2 (100);
       BOTM     VARCHAR2 (100);
       BETM     VARCHAR2 (100);
       BSTM     VARCHAR2 (100);
       C_TOBT   VARCHAR2 (100);
       COBT     VARCHAR2 (100);
       CTOT     VARCHAR2 (100);
       DINT     VARCHAR2 (100);
       DLAB     VARCHAR2 (100);
       DNAP     VARCHAR2 (100);
       DOUT     VARCHAR2 (100);
       EDDI     VARCHAR2 (100);
       EOBT     VARCHAR2 (100);
       EPGT     VARCHAR2 (100);
       EPOT     VARCHAR2 (100);
       FATD     VARCHAR2 (100);
       FSTD     VARCHAR2 (100);
       OFTM     VARCHAR2 (100);
       STDI     VARCHAR2 (100);
       TSAT     VARCHAR2 (100);
       FLIGHTNUMBER VARCHAR2 (100);
       FLIGHTMARK   VARCHAR2 (100);
       ALAP     VARCHAR2 (100);
       APRT     VARCHAR2 (100);
       DPRT     VARCHAR2 (100);
       PARK     VARCHAR2 (100);
       INTERNALORINTERNATIONAL  VARCHAR2 (100);
       TERMINAL VARCHAR2 (100);
       GROUNDDISTRIBUTION VARCHAR2 (100);
    
       --定义出港信息表要格式的时间字段
       A_TOBT_D VARCHAR2 (100);
       ASAT_D   VARCHAR2 (100);
       BCTM_D   VARCHAR2 (100);
       BOTM_D   VARCHAR2 (100);
       BETM_D   VARCHAR2 (100);
       C_TOBT_D VARCHAR2 (100);
       COBT_D   VARCHAR2 (100);
       CTOT_D   VARCHAR2 (100);
       DINT_D   VARCHAR2 (100);
       DOUT_D   VARCHAR2 (100);
       EDDI_D   VARCHAR2 (100);
       EOBT_D   VARCHAR2 (100);
       EPGT_D   VARCHAR2 (100);
       EPOT_D   VARCHAR2 (100);
       FATD_D   VARCHAR2 (100);
       FSTD_D   VARCHAR2 (100);
       LMDT_D   VARCHAR2 (100);
       OFTM_D   VARCHAR2 (100);
       STDI_D   VARCHAR2 (100);
       TSAT_D   VARCHAR2 (100);
    
       --定义进港信息表要格式化的时间字段
       BSTM_A     VARCHAR2 (100);
       EIBT_A   VARCHAR2 (100);
       FATA_A   VARCHAR2 (100);
       FETA_A   VARCHAR2 (100);
       FSTA_A   VARCHAR2 (100);
       LMDT_A   VARCHAR2 (100);
       PSTM_A   VARCHAR2 (100);
       SPOT_A   VARCHAR2 (100);
    
       COUNTS   NUMBER(36);
    
       --定义出港信息要修改的除时间外的字段
       STND_D   VARCHAR2 (100);
       A_WEATHER_D VARCHAR2 (100);
       ABNS_D   VARCHAR2 (100);
       ACFT_D   VARCHAR2 (100);
       AIRLINE_D VARCHAR2 (100);
       DLAB_D   VARCHAR2 (100);
       DNAP_D   VARCHAR2 (100);
       LMUR_D   VARCHAR2 (100);
       RENO_D   VARCHAR2 (100);
       RWAY_D   VARCHAR2 (100);
       DPRT_D   VARCHAR2 (100);
       PARK_D   VARCHAR2 (100);
       INTERNALORINTERNATIONAL_D  VARCHAR2 (100);
       TERMINAL_D VARCHAR2 (100);
       GROUNDDISTRIBUTION_D VARCHAR2 (100);
    
       --定义进港信息要修改的除时间外的字段
       ABNS_A   VARCHAR2 (100);
       ACFT_A   VARCHAR2 (100);
       AIRLINE_A VARCHAR2 (100);
       ALAP_A   VARCHAR2 (100);
       APRT_A   VARCHAR2 (100);
       CHDT_A   VARCHAR2 (100);
       RENO_A   VARCHAR2 (100);
       LMUR_A   VARCHAR2 (100);
       RWAY_A   VARCHAR2 (100);
       STND_A   VARCHAR2 (100);
       PARK_A     VARCHAR2 (100);
       INTERNALORINTERNATIONAL_A  VARCHAR2 (100);
       TERMINAL_A VARCHAR2 (100);
       GROUNDDISTRIBUTION_A VARCHAR2 (100);
       
    BEGIN
        
       STYP := GetXmlNodeValue (xmlStr, 'STYP');
       RENO := GetXmlNodeValue (xmlStr, 'RENO');
       FFID := GetXmlNodeValue (xmlStr, 'FFID');
       ABNS := GetXmlNodeValue (xmlStr, 'ABNS');
       ACFT := GetXmlNodeValue (xmlStr, 'ACFT');
       CHDT := GetXmlNodeValue (xmlStr, 'CHDT');
       EIBT := GetXmlNodeValue (xmlStr, 'EIBT');
       FATA := GetXmlNodeValue (xmlStr, 'FATA');
       FETA := GetXmlNodeValue (xmlStr, 'FETA');
       FSTA := GetXmlNodeValue (xmlStr, 'FSTA');
       LMDT := GetXmlNodeValue (xmlStr, 'LMDT');
       LMUR := GetXmlNodeValue (xmlStr, 'LMUR');
       PSTM := GetXmlNodeValue (xmlStr, 'PSTM');
       RWAY := GetXmlNodeValue (xmlStr, 'RWAY');
       SPOT := GetXmlNodeValue (xmlStr, 'SPOT');
       STND := GetXmlNodeValue (xmlStr, 'STND');
       SDEC := GetXmlNodeValue (xmlStr, 'STND');
    
       A_TOBT := GetXmlNodeValue (xmlStr, 'A_TOBT');
       A_WEATHER := GetXmlNodeValue (xmlStr, 'A_WEATHER');
       ALAP := GetXmlNodeValue (xmlStr, 'ALAP');
       APRT := GetXmlNodeValue (xmlStr, 'APRT');
       ASAT := GetXmlNodeValue (xmlStr, 'ASAT');
       BCTM := GetXmlNodeValue (xmlStr, 'BCTM');
       BOTM := GetXmlNodeValue (xmlStr, 'BOTM');
       BETM := GetXmlNodeValue (xmlStr, 'BETM');
       BSTM := GetXmlNodeValue (xmlStr, 'BSTM');
       C_TOBT := GetXmlNodeValue (xmlStr, 'C_TOBT');
       COBT := GetXmlNodeValue (xmlStr, 'COBT');
       CTOT := GetXmlNodeValue (xmlStr, 'CTOT');
       DINT := GetXmlNodeValue (xmlStr, 'DINT');
       DLAB := GetXmlNodeValue (xmlStr, 'DLAB');
       DNAP := GetXmlNodeValue (xmlStr, 'DNAP');
       DOUT := GetXmlNodeValue (xmlStr, 'DOUT');
       EDDI := GetXmlNodeValue (xmlStr, 'EDDI');
       EOBT := GetXmlNodeValue (xmlStr, 'EOBT');
       EPGT := GetXmlNodeValue (xmlStr, 'EPGT');
       EPOT := GetXmlNodeValue (xmlStr, 'EPOT');
       FATD := GetXmlNodeValue (xmlStr, 'FATD');
       FSTD := GetXmlNodeValue (xmlStr, 'FSTD');
       OFTM := GetXmlNodeValue (xmlStr, 'OFTM');
       STDI := GetXmlNodeValue (xmlStr, 'STDI');
       TSAT := GetXmlNodeValue (xmlStr, 'TSAT');
       DPRT := GetXmlNodeValue (xmlStr, 'DPRT');
       PARK := GetXmlNodeValue (xmlStr, 'PARK');
       INTERNALORINTERNATIONAL := GetXmlNodeValue (xmlStr, 'INTERNALORINTERNATIONAL');
       TERMINAL := GetXmlNodeValue (xmlStr, 'TERMINAL');
       GROUNDDISTRIBUTION := GetXmlNodeValue (xmlStr, 'GROUNDDISTRIBUTION');
       
       --出港信息表中时间字段的时间格式函数的用法
       A_TOBT_D := FORMATDATEVALUE (A_TOBT, 'A_TOBT_D');
       ASAT_D := FORMATDATEVALUE (ASAT, 'ASAT_D');
       BCTM_D := FORMATDATEVALUE (BCTM, 'BCTM_D');
       BOTM_D := FORMATDATEVALUE (BOTM, 'BOTM_D');
       BETM_D := FORMATDATEVALUE (BETM, 'BETM_D');
       C_TOBT_D := FORMATDATEVALUE (C_TOBT, 'C_TOBT_D');
       COBT_D := FORMATDATEVALUE (COBT, 'COBT_D');
       CTOT_D := FORMATDATEVALUE (CTOT, 'CTOT_D');
       DINT_D := FORMATDATEVALUE (DINT, 'DINT_D');
       DOUT_D := FORMATDATEVALUE (DOUT, 'DOUT_D');
       EDDI_D := FORMATDATEVALUE (EDDI, 'EDDI_D');
       EOBT_D := FORMATDATEVALUE (EOBT, 'EOBT_D');
       EPGT_D := FORMATDATEVALUE (EPGT, 'EPGT_D');
       EPOT_D := FORMATDATEVALUE (EPOT, 'EPOT_D');
       FATD_D := FORMATDATEVALUE (FATD, 'FATD_D');
       FSTD_D := FORMATDATEVALUE (FSTD, 'FSTD_D');
       LMDT_D := FORMATDATEVALUE (LMDT, 'LMDT_D');
       OFTM_D := FORMATDATEVALUE (OFTM, 'OFTM_D');
       STDI_D := FORMATDATEVALUE (STDI, 'STDI_D');
       TSAT_D := FORMATDATEVALUE (TSAT, 'TSAT_D');
       
       --进港信息表中时间字段的时间格式函数的用法
       EIBT_A := FORMATDATEVALUE (EIBT, 'EIBT_A');
       FATA_A := FORMATDATEVALUE (FATA, 'FATA_A');
       FETA_A := FORMATDATEVALUE (FETA, 'FETA_A');
       FSTA_A := FORMATDATEVALUE (FSTA, 'FSTA_A');
       LMDT_A := FORMATDATEVALUE (LMDT, 'LMDT_A');
       PSTM_A := FORMATDATEVALUE (PSTM, 'PSTM_A');
       SPOT_A := FORMATDATEVALUE (SPOT, 'SPOT_A');
       BSTM_A := FORMATDATEVALUE (BSTM, 'BSTM_A');
       
       --出港信息要修改的除时间外的字段
       STND_D := GetXmlNodeValue (xmlStr, 'STND');
       A_WEATHER_D := GetXmlNodeValue (xmlStr, 'A_WEATHER');
       ABNS_D := GetXmlNodeValue (xmlStr, 'ABNS');
       ACFT_D := GetXmlNodeValue (xmlStr, 'ACFT');
       AIRLINE_D := GetXmlNodeValue (xmlStr, 'AIRLINE');
       DLAB_D := GetXmlNodeValue (xmlStr, 'DLAB');
       DNAP_D := GetXmlNodeValue (xmlStr, 'DNAP');
       LMUR_D := GetXmlNodeValue (xmlStr, 'LMUR');
       RENO_D := GetXmlNodeValue (xmlStr, 'RENO');
       RWAY_D := GetXmlNodeValue (xmlStr, 'RWAY');
       DPRT_D := GetXmlNodeValue (xmlStr, 'DPRT');
       PARK_D := GetXmlNodeValue (xmlStr, 'PARK');
       TERMINAL_D := GetXmlNodeValue (xmlStr, 'TERMINAL');
       GROUNDDISTRIBUTION_D := GetXmlNodeValue (xmlStr, 'GROUNDDISTRIBUTION');
       
       --进港信息要修改的除时间外的字段
       ABNS_A := GetXmlNodeValue (xmlStr, 'ABNS');
       ACFT_A := GetXmlNodeValue (xmlStr, 'ACFT');
       AIRLINE_A := GetXmlNodeValue (xmlStr, 'AIRLINE');
       ALAP_A := GetXmlNodeValue (xmlStr, 'ALAP');
       APRT_A := GetXmlNodeValue (xmlStr, 'APRT');
       CHDT_A := GetXmlNodeValue (xmlStr, 'CHDT');
       RENO_A := GetXmlNodeValue (xmlStr, 'RENO');
       LMUR_A := GetXmlNodeValue (xmlStr, 'LMUR');
       RWAY_A := GetXmlNodeValue (xmlStr, 'RWAY');
       STND_A := GetXmlNodeValue (xmlStr, 'STND');
       PARK_A := GetXmlNodeValue (xmlStr, 'PARK');
       TERMINAL_A := GetXmlNodeValue (xmlStr, 'TERMINAL');
       GROUNDDISTRIBUTION_A := GetXmlNodeValue (xmlStr, 'GROUNDDISTRIBUTION');
       
       
       IF STYP = 'FGIS' THEN 
       
       IF INSTR(FFID,'-D-') > 0 THEN
    
        FFID_D := FFID;
        
        --截取航空公司代码
        AIRLINE := SUBSTR(FFID_D,0,2);
    
        --截取航班号
        FLIGHTNUMBER := SUBSTR(FFID_D,INSTR(FFID_D,'-',1)+1,INSTR(FFID_D,'-',INSTR(FFID_D,'-',1)+1)-INSTR(FFID_D,'-',1)-1);
    
        --截取出港标志
    
        FLIGHTMARK := SUBSTR(FFID_D,INSTR(FFID_D,'-',2,2)+1,INSTR(FFID_D,'-',2,3)-1-INSTR(FFID_D,'-',2,2));
        
        --截取进离港标志
        INTERNALORINTERNATIONAL := SUBSTR(FFID_D,-1);
    
        --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE
    
        SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_D where FFID = FFID_D;
    
        IF COUNTS > 0 THEN
        
          IF A_TOBT_D != ' ' THEN
          
          UPDATE TB_CMS_FLGTINFO_D SET A_TOBT = A_TOBT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF A_WEATHER_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET A_WEATHER = A_WEATHER_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF ABNS_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET ABNS = ABNS_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF ACFT_D != ' ' THEN
          
          UPDATE TB_CMS_FLGTINFO_D SET ACFT = ACFT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF ASAT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET ASAT = ASAT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF BCTM_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET BCTM = BCTM_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF BOTM_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET BOTM = BOTM_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF BETM_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET BETM = BETM_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF C_TOBT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET C_TOBT = C_TOBT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF COBT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET COBT = COBT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF CTOT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET CTOT = CTOT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF DINT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DINT = DINT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF DLAB_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DLAB = DLAB_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF DNAP_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DNAP = DNAP_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF DOUT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DOUT = DOUT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF EDDI_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET EDDI = EDDI_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF EOBT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET EOBT = EOBT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF EPGT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET EPGT = EPGT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF EPOT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET EPOT = EPOT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF FATD_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET FATD = FATD_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF FSTD_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET FSTD = FSTD_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF LMDT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET LMDT = LMDT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF LMUR_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET LMUR = LMUR_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF OFTM_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET OFTM = OFTM_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF RENO_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET RENO = RENO_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF RWAY_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET RWAY = RWAY_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF STDI_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET STDI = STDI_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF STND_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET STND = STND_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF TSAT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET TSAT = TSAT_D WHERE FFID = FFID_D;
          
          END IF; 
          
          IF DPRT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DPRT = DPRT_D WHERE FFID = FFID_D;
          
          END IF; 
          
          IF PARK_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET PARK = PARK_D WHERE FFID = FFID_D;
          
          END IF; 
          
          IF TERMINAL_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET TERMINAL = TERMINAL_D WHERE FFID = FFID_D;
          
          END IF; 
          
          IF GROUNDDISTRIBUTION_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET GROUNDDISTRIBUTION = GROUNDDISTRIBUTION_D WHERE FFID = FFID_D;
          
          END IF; 
          
        ELSE
    
        INSERT INTO TB_CMS_FLGTINFO_D (ID,A_TOBT,A_WEATHER,ABNS,ACFT,AIRLINE,ASAT,BCTM,BOTM,BETM,C_TOBT,COBT,CTOT,DINT,DLAB,DNAP,DOUT,DPRT,EDDI,EOBT,EPGT,EPOT,FATD,FFID,FLIGHTNUMBER,
                     FLIGHTMARK,FSTD,GROUNDDISTRIBUTION,INTERNALORINTERNATIONAL,LMDT,LMUR,OFTM,PARK,RENO,RWAY,STDI,STND,TERMINAL,TSAT)
           VALUES   (FLGTINFO_D_SEQ.NEXTVAL,
                     A_TOBT_D,
                     A_WEATHER,
                     ABNS,
                     ACFT,
                     AIRLINE,
                     ASAT_D,
                     BCTM_D,
                     BOTM_D,
                     BETM_D,
                     C_TOBT_D,
                     COBT_D,
                     CTOT_D,
                     DINT_D,
                     DLAB,
                     DNAP,
                     DOUT_D,
                     DPRT,
                     EDDI_D,
                     EOBT_D,
                     EPGT_D,
                     EPOT_D,
                     FATD_D,
                     FFID_D,
                     FLIGHTNUMBER,
                     FLIGHTMARK,
                     FSTD_D,
                     GROUNDDISTRIBUTION,
                     INTERNALORINTERNATIONAL,
                     LMDT_D,
                     LMUR,
                     OFTM_D,
                     PARK,
                     RENO,
                     RWAY,
                     STDI_D,
                     STND,
                     TERMINAL,
                     TSAT_D);
    
        END IF;
    
    
       ELSE
    
         FFID_A := FFID;
         
         --截取航空公司代码
         AIRLINE := SUBSTR(FFID_A,0,2);
    
         --截取航班号
         FLIGHTNUMBER := SUBSTR(FFID_A,INSTR(FFID_A,'-',1)+1,INSTR(FFID_A,'-',INSTR(FFID_A,'-',1)+1)-INSTR(FFID_A,'-',1)-1);
    
         --截取出港标志
         FLIGHTMARK := SUBSTR(FFID_A,INSTR(FFID_A,'-',2,2)+1,INSTR(FFID_A,'-',2,3)-1-INSTR(FFID_A,'-',2,2));
         
         --截取进离港标志
         INTERNALORINTERNATIONAL := SUBSTR(FFID_A,-1);
    
        --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE
    
         SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_A where FFID = FFID_A;
    
         IF COUNTS > 0 THEN
         
             IF ABNS_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET ABNS = ABNS_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF ACFT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET ACFT = ACFT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF ALAP_A != ' ' THEN
             
             UPDATE TB_CMS_FLGTINFO_A SET ALAP = ALAP_A WHERE FFID = FFID_A;
             
             END IF;
             
             IF BSTM_A != ' ' THEN
             
             UPDATE TB_CMS_FLGTINFO_A SET BSTM = BSTM_A WHERE FFID = FFID_A;
             
             END IF;
             
             IF CHDT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET CHDT = CHDT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF RENO_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET RENO = RENO_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF EIBT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET EIBT = EIBT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF FATA_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET FATA = FATA_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF FETA_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET FETA = FETA_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF FSTA_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET FSTA = FSTA_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF LMDT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET LMDT = LMDT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF LMUR_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET LMUR = LMUR_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF PSTM_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET PSTM = PSTM_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF RWAY_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET RWAY = RWAY_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF SPOT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET SPOT = SPOT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF STND_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET STND = STND_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF APRT_A != ' ' THEN
        
             UPDATE TB_CMS_FLGTINFO_A SET APRT = APRT_A WHERE FFID = FFID_A;
                      
             END IF; 
                      
             IF PARK_A != ' ' THEN
                    
             UPDATE TB_CMS_FLGTINFO_A SET PARK = PARK_A WHERE FFID = FFID_A;
                      
             END IF; 
                      
             IF TERMINAL_A != ' ' THEN
                    
             UPDATE TB_CMS_FLGTINFO_A SET TERMINAL = TERMINAL_A WHERE FFID = FFID_A;
                      
             END IF; 
                      
             IF GROUNDDISTRIBUTION_A != ' ' THEN
                    
             UPDATE TB_CMS_FLGTINFO_A SET GROUNDDISTRIBUTION = GROUNDDISTRIBUTION_A WHERE FFID = FFID_A;
                      
             END IF; 
             
         
         ELSE
    
         INSERT INTO TB_CMS_FLGTINFO_A (ID,ABNS,ACFT,AIRLINE,ALAP,BSTM,CHDT,APRT,FFID,FLIGHTNUMBER,FLIGHTMARK,GROUNDDISTRIBUTION,INTERNALORINTERNATIONAL,RENO,EIBT,FATA,FETA,FSTA,LMDT,LMUR,PARK,PSTM,RWAY,SPOT,STND,TERMINAL)
    
         VALUES   (FLGTINFO_A_SEQ.NEXTVAL,
                   ABNS,
                   ACFT,
                   AIRLINE,
                   ALAP,
                   BSTM_A,
                   CHDT,
                   APRT,
                   FFID_A,
                   FLIGHTNUMBER,
                   FLIGHTMARK,
                   GROUNDDISTRIBUTION,
                   INTERNALORINTERNATIONAL,
                   RENO,
                   EIBT_A,
                   FATA_A,
                   FETA_A,
                   FSTA_A,
                   LMDT_A,
                   LMUR,
                   PARK,
                   PSTM_A,
                   RWAY,
                   SPOT_A,
                   STND,
                   TERMINAL);
    
         END IF;
    
       END IF;
       
       ELSE
       
       IF INSTR(FFID,'-D-') > 0 THEN
    
        FFID_D := FFID;
        
        --截取航空公司代码
        AIRLINE := SUBSTR(FFID_D,0,2);
    
        --截取航班号
        FLIGHTNUMBER := SUBSTR(FFID_D,INSTR(FFID_D,'-',1)+1,INSTR(FFID_D,'-',INSTR(FFID_D,'-',1)+1)-INSTR(FFID_D,'-',1)-1);
    
        --截取出港标志
    
        FLIGHTMARK := SUBSTR(FFID_D,INSTR(FFID_D,'-',2,2)+1,INSTR(FFID_D,'-',2,3)-1-INSTR(FFID_D,'-',2,2));
        
        --截取进离港标志
        INTERNALORINTERNATIONAL := SUBSTR(FFID_D,-1);
    
        --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE
    
        SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_D where FFID = FFID_D;
    
        IF COUNTS > 0 THEN
        
          IF A_TOBT_D != ' ' THEN
          
          UPDATE TB_CMS_FLGTINFO_D SET A_TOBT = A_TOBT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF A_WEATHER_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET A_WEATHER = A_WEATHER_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF ABNS_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET ABNS = ABNS_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF ACFT_D != ' ' THEN
          
          UPDATE TB_CMS_FLGTINFO_D SET ACFT = ACFT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF ASAT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET ASAT = ASAT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF BCTM_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET BCTM = BCTM_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF BOTM_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET BOTM = BOTM_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF BETM_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET BETM = BETM_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF C_TOBT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET C_TOBT = C_TOBT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF COBT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET COBT = COBT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF CTOT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET CTOT = CTOT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF DINT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DINT = DINT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF DLAB_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DLAB = DLAB_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF DNAP_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DNAP = DNAP_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF DOUT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DOUT = DOUT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF EDDI_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET EDDI = EDDI_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF EOBT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET EOBT = EOBT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF EPGT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET EPGT = EPGT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF EPOT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET EPOT = EPOT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF FATD_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET FATD = FATD_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF FSTD_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET FSTD = FSTD_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF LMDT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET LMDT = LMDT_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF LMUR_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET LMUR = LMUR_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF OFTM_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET OFTM = OFTM_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF RENO_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET RENO = RENO_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF RWAY_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET RWAY = RWAY_D WHERE FFID = FFID_D;
          
          END IF;
          
          IF STDI_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET STDI = STDI_D WHERE FFID = FFID_D;
          
          END IF;
          
          
          IF SDEC != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET SDEC = SDEC WHERE FFID = FFID_D;
          
          END IF;
          
          IF TSAT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET TSAT = TSAT_D WHERE FFID = FFID_D;
          
          END IF; 
          
          IF DPRT_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET DPRT = DPRT_D WHERE FFID = FFID_D;
          
          END IF; 
          
          IF PARK_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET PARK = PARK_D WHERE FFID = FFID_D;
          
          END IF; 
          
          IF TERMINAL_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET TERMINAL = TERMINAL_D WHERE FFID = FFID_D;
          
          END IF; 
          
          IF GROUNDDISTRIBUTION_D != ' ' THEN
        
          UPDATE TB_CMS_FLGTINFO_D SET GROUNDDISTRIBUTION = GROUNDDISTRIBUTION_D WHERE FFID = FFID_D;
          
          END IF; 
          
        ELSE
    
        INSERT INTO TB_CMS_FLGTINFO_D (ID,A_TOBT,A_WEATHER,ABNS,ACFT,AIRLINE,ASAT,BCTM,BOTM,BETM,C_TOBT,COBT,CTOT,DINT,DLAB,DNAP,DOUT,DPRT,EDDI,EOBT,EPGT,EPOT,FATD,FFID,FLIGHTNUMBER,
                     FLIGHTMARK,FSTD,GROUNDDISTRIBUTION,INTERNALORINTERNATIONAL,LMDT,LMUR,OFTM,PARK,RENO,RWAY,STDI,SDEC,TERMINAL,TSAT)
           VALUES   (FLGTINFO_D_SEQ.NEXTVAL,
                     A_TOBT_D,
                     A_WEATHER,
                     ABNS,
                     ACFT,
                     AIRLINE,
                     ASAT_D,
                     BCTM_D,
                     BOTM_D,
                     BETM_D,
                     C_TOBT_D,
                     COBT_D,
                     CTOT_D,
                     DINT_D,
                     DLAB,
                     DNAP,
                     DOUT_D,
                     DPRT,
                     EDDI_D,
                     EOBT_D,
                     EPGT_D,
                     EPOT_D,
                     FATD_D,
                     FFID_D,
                     FLIGHTNUMBER,
                     FLIGHTMARK,
                     FSTD_D,
                     GROUNDDISTRIBUTION,
                     INTERNALORINTERNATIONAL,
                     LMDT_D,
                     LMUR,
                     OFTM_D,
                     PARK,
                     RENO,
                     RWAY,
                     STDI_D,
                     SDEC,
                     TERMINAL,
                     TSAT_D);
    
        END IF;
    
    
       ELSE
    
         FFID_A := FFID;
         
         --截取航空公司代码
         AIRLINE := SUBSTR(FFID_A,0,2);
    
         --截取航班号
         FLIGHTNUMBER := SUBSTR(FFID_A,INSTR(FFID_A,'-',1)+1,INSTR(FFID_A,'-',INSTR(FFID_A,'-',1)+1)-INSTR(FFID_A,'-',1)-1);
    
         --截取出港标志
         FLIGHTMARK := SUBSTR(FFID_A,INSTR(FFID_A,'-',2,2)+1,INSTR(FFID_A,'-',2,3)-1-INSTR(FFID_A,'-',2,2));
         
         --截取进离港标志
         INTERNALORINTERNATIONAL := SUBSTR(FFID_A,-1);
    
        --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE
    
         SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_A where FFID = FFID_A;
    
         IF COUNTS > 0 THEN
         
             IF ABNS_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET ABNS = ABNS_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF ACFT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET ACFT = ACFT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF ALAP_A != ' ' THEN
             
             UPDATE TB_CMS_FLGTINFO_A SET ALAP = ALAP_A WHERE FFID = FFID_A;
             
             END IF;
             
             IF BSTM_A != ' ' THEN
             
             UPDATE TB_CMS_FLGTINFO_A SET BSTM = BSTM_A WHERE FFID = FFID_A;
             
             END IF;
             
             IF CHDT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET CHDT = CHDT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF RENO_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET RENO = RENO_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF EIBT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET EIBT = EIBT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF FATA_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET FATA = FATA_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF FETA_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET FETA = FETA_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF FSTA_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET FSTA = FSTA_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF LMDT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET LMDT = LMDT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF LMUR_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET LMUR = LMUR_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF PSTM_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET PSTM = PSTM_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF RWAY_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET RWAY = RWAY_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF SPOT_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET SPOT = SPOT_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF STND_A != ' ' THEN
              
             UPDATE TB_CMS_FLGTINFO_A SET STND = STND_A WHERE FFID = FFID_A;
              
             END IF;
             
             IF APRT_A != ' ' THEN
        
             UPDATE TB_CMS_FLGTINFO_A SET APRT = APRT_A WHERE FFID = FFID_A;
                      
             END IF; 
                      
             IF PARK_A != ' ' THEN
                    
             UPDATE TB_CMS_FLGTINFO_A SET PARK = PARK_A WHERE FFID = FFID_A;
                      
             END IF; 
                      
             IF TERMINAL_A != ' ' THEN
                    
             UPDATE TB_CMS_FLGTINFO_A SET TERMINAL = TERMINAL_A WHERE FFID = FFID_A;
                      
             END IF; 
                      
             IF GROUNDDISTRIBUTION_A != ' ' THEN
                    
             UPDATE TB_CMS_FLGTINFO_A SET GROUNDDISTRIBUTION = GROUNDDISTRIBUTION_A WHERE FFID = FFID_A;
                      
             END IF; 
             
         
         ELSE
    
         INSERT INTO TB_CMS_FLGTINFO_A (ID,ABNS,ACFT,AIRLINE,ALAP,BSTM,CHDT,APRT,FFID,FLIGHTNUMBER,FLIGHTMARK,GROUNDDISTRIBUTION,INTERNALORINTERNATIONAL,RENO,EIBT,FATA,FETA,FSTA,LMDT,LMUR,PARK,PSTM,RWAY,SPOT,STND,TERMINAL)
    
         VALUES   (FLGTINFO_A_SEQ.NEXTVAL,
                   ABNS,
                   ACFT,
                   AIRLINE,
                   ALAP,
                   BSTM_A,
                   CHDT,
                   APRT,
                   FFID_A,
                   FLIGHTNUMBER,
                   FLIGHTMARK,
                   GROUNDDISTRIBUTION,
                   INTERNALORINTERNATIONAL,
                   RENO,
                   EIBT_A,
                   FATA_A,
                   FETA_A,
                   FSTA_A,
                   LMDT_A,
                   LMUR,
                   PARK,
                   PSTM_A,
                   RWAY,
                   SPOT_A,
                   STND,
                   TERMINAL);
    
         END IF;
    
       END IF;
       
       END IF;
       
    
       COMMIT;
    
    EXCEPTION
    
       WHEN OTHERS THEN
    
          DBMS_OUTPUT.PUT_LINE (SQLERRM);
    
    END MIP_PARSE;
    /

三：job：

var job_num number;
    begin
        dbms_job.submit(:job_num,'JOB_PRO_TEMP;',sysdate,'sysdate+1/24/60');
    end;
    commit;
    
    var job_num number;
    begin
        dbms_job.submit(:job_num,'LOOP_COPY_PRO_TEMP;',sysdate,'sysdate+1/24/60');
    end;
    commit;

以上就是所有在Oracle中用到的相关的PL-SQL的语句！