迷彩风情

记录日常工作和生活点滴 QQ交流群:715224124

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

摘要:之前在项目中使用到了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的语句!

阅读更多
版权声明:本文为博主原创文章,转载请加上原文链接,谢谢! https://blog.csdn.net/sxdtzhaoxinguo/article/details/44648331
个人分类: 数据库技术
上一篇养成让自己进步的10个习惯
下一篇Maven配置nexus
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭