Oracle中通过Function，存储过程，触发器，调用实现解析Clob字段中存在的xml字符串

迈不过友情╰ 2024-02-17 15:16 67阅读 0赞

摘要：接着之前的问题，在Oracle数据库中通过Function，存储过程，触发器实现解析数据表中CLOB大数据字段中存在的xml字符串有时，有个特殊的时间字符串要在数据库格式化处理，之前解析过来的时间字符串是，比如：11OCT141024表示:(日月年时分)，这是一种不规则的时间格式，我们要把它格式化成:(年-月-日 时:分)这样的格式，就的再另外写个Function来特殊处理了，下面我把我的方法写上，关于Oracle中解析Clob字段中的xml字符串的方式请看上一篇博客，地址：[http://blog.csdn.net/sxdtzhaoxinguo/article/details/40052783][http_blog.csdn.net_sxdtzhaoxinguo_article_details_40052783]

一：Function.SQL:

CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2)
       RETURN VARCHAR2
    IS
       --定义几个变量，出来解析过来的时间字符串
       --日月年时分(11OCT141024)
       AA       VARCHAR2(32);
       
       DAY      VARCHAR2(32);
       
       MOUNTH   VARCHAR2(32);
       
       YEAR     VARCHAR2(32);
       
       HOUR     VARCHAR2(32);
       
       MINUTE   VARCHAR2(32);
       
       ValueReturn   VARCHAR2 (100);
       
    BEGIN
    
       IF key IS NULL THEN 
       
       ValueReturn := NULL;
       
       RETURN ValueReturn;
       
       ELSE
       
       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;
       
       --日月年时分(11OCT141017)
       ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE;
       
       RETURN ValueReturn;
       
       END IF;
       
    END FormatDateValue;
    /

二：存储过程.SQL:

CREATE OR REPLACE PROCEDURE MIP.MIP_PARSE (xmlStr IN CLOB)
    IS
       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);
       --FFID     VARCHAR2 (100);
       FSTA     VARCHAR2 (100);
       LMDT     VARCHAR2 (100);
       LMUR     VARCHAR2 (100);
       PSTM     VARCHAR2 (100);
       RWAY     VARCHAR2 (100);
       SPOT     VARCHAR2 (100);
       STND     VARCHAR2 (100);
    
       A_TOBT   VARCHAR2 (100);
       A_WEATHER     VARCHAR2 (100);
       --ABNS     VARCHAR2 (100);
       --ACFT     VARCHAR2 (100);
       ASAT     VARCHAR2 (100);
       BCTM     VARCHAR2 (100);
       BOTM     VARCHAR2 (100);
       BSTM     VARCHAR2 (100);
       C_TOBT   VARCHAR2 (100);
       COBT     VARCHAR2 (100);
       CTOT     VARCHAR2 (100);
    
       DINT     VARCHAR2 (100);
       DLAB     VARCHAR2 (100);
       DOUT     VARCHAR2 (100);
       EDDI     VARCHAR2 (100);
       EOBT     VARCHAR2 (100);
       EPGT     VARCHAR2 (100);
       EPOT     VARCHAR2 (100);
       FATD     VARCHAR2 (100);
       --FFID     VARCHAR2 (100);
       FSTD     VARCHAR2 (100);
       --LMDT     VARCHAR2 (100);
       --LMUR     VARCHAR2 (100);
       OFTM     VARCHAR2 (100);
       --RENO     VARCHAR2 (100);
       --RWAY     VARCHAR2 (100);
       STDI     VARCHAR2 (100);
       --STND     VARCHAR2 (100);
       TSAT     VARCHAR2 (100);
       
       --定义出港信息表要格式的时间字段
       A_TOBT_D VARCHAR2 (100);
       ASAT_D   VARCHAR2 (100);
       BCTM_D   VARCHAR2 (100);
       BOTM_D   VARCHAR2 (100);
       BSTM_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);
       
       --定义进港信息表要格式化的时间字段
       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);
       
    BEGIN
       RENO := GetXmlNodeValue (xmlStr, 'RENO');
       AIRLINE := GetXmlNodeValue (xmlStr, 'AIRLINE');
       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');
       FFID := GetXmlNodeValue (xmlStr, 'FFID');
       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');
    
       A_TOBT := GetXmlNodeValue (xmlStr, 'A_TOBT');
       A_WEATHER := GetXmlNodeValue (xmlStr, 'A_WEATHER');
       --ABNS := GetXmlNodeValue (xmlStr, 'ABNS');
       --ACFT := GetXmlNodeValue (xmlStr, 'ACFT');
       ASAT := GetXmlNodeValue (xmlStr, 'ASAT');
       BCTM := GetXmlNodeValue (xmlStr, 'BCTM');
       BOTM := GetXmlNodeValue (xmlStr, 'BOTM');
       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');
       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');
       --LMDT := GetXmlNodeValue (xmlStr, 'LMDT');
       --LMUR := GetXmlNodeValue (xmlStr, 'LMUR');
       OFTM := GetXmlNodeValue (xmlStr, 'OFTM');
       STDI := GetXmlNodeValue (xmlStr, 'STDI');
       TSAT := GetXmlNodeValue (xmlStr, 'TSAT');
       
       --出港信息表中时间字段的时间格式函数的用法
       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');
       BSTM_D := FORMATDATEVALUE (BSTM, 'BSTM_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');
       
       IF INSTR(FFID,'-D-') > 0 THEN
       
        --FFID_D := FFID;
        
        --截取航班号
        FFID_D := SUBSTR(FFID,INSTR(FFID,'-',1)+1,INSTR(FFID,'-',INSTR(FFID,'-',1)+1)-INSTR(FFID,'-',1)-1);
        
        INSERT INTO TB_CMS_FLGTINFO_D (ID,A_TOBT,A_WEATHER,ABNS,ACFT,AIRLINE,ASAT,BCTM,BOTM,BSTM,C_TOBT,COBT,CTOT,DINT,DLAB,DOUT,EDDI,EOBT,EPGT,EPOT,FATD,FFID,FSTD,LMDT,LMUR,OFTM,RENO,RWAY,STDI,STND,TSAT)
         VALUES   (FLGTINFO_D_SEQ.NEXTVAL,
                   A_TOBT_D,
                   A_WEATHER,
                   ABNS,
                   ACFT,
                   AIRLINE,
                   ASAT_D,
                   BCTM_D,
                   BOTM_D,
                   BSTM_D,
                   C_TOBT_D,
                   COBT_D,
                   CTOT_D,
                   DINT_D,
                   DLAB,
                   DOUT_D,
                   EDDI_D,
                   EOBT_D,
                   EPGT_D,
                   EPOT_D,
                   FATD_D,
                   FFID_D,
                   FSTD_D,
                   LMDT_D,
                   LMUR,
                   OFTM_D,
                   RENO,
                   RWAY,
                   STDI_D,
                   STND,
                   TSAT_D);
       
       ELSE
       
        --FFID_A := FFID;
        
         FFID_A := SUBSTR(FFID,INSTR(FFID,'-',1)+1,INSTR(FFID,'-',INSTR(FFID,'-',1)+1)-INSTR(FFID,'-',1)-1);
        
        INSERT INTO TB_CMS_FLGTINFO_A (ID,ABNS,ACFT,AIRLINE,CHDT,FFID,RENO,EIBT,FATA,FETA,FSTA,LMDT,LMUR,PSTM,RWAY,SPOT,STND)
         VALUES   (FLGTINFO_A_SEQ.NEXTVAL,
                   ABNS,
                   ACFT,
                   AIRLINE,
                   CHDT,
                   FFID_A,
                   RENO,
                   EIBT_A,
                   FATA_A,
                   FETA_A,
                   FSTA_A,
                   LMDT_A,
                   LMUR,
                   PSTM_A,
                   RWAY,
                   SPOT_A,
                   STND);
       
       END IF;
    
    
       
        
       
       
      
       COMMIT;
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.PUT_LINE (SQLERRM);
    END MIP_PARSE;
    /

三：以上就是改进后的sql语句;

四：详情请看：[http://blog.csdn.net/sxdtzhaoxinguo/article/details/40052783][http_blog.csdn.net_sxdtzhaoxinguo_article_details_40052783]

[http_blog.csdn.net_sxdtzhaoxinguo_article_details_40052783]: http://blog.csdn.net/sxdtzhaoxinguo/article/details/40052783