摘要：接著之前的問題，在Oracle數據庫中通過Function，存儲過程，觸發器實現解析數據表中CLOB大數據字段中存在的xml字符串有時，有個特殊的時間字符串要在數據庫格式化處理，之前解析過來的時間字符串是，比如：11OCT141024表示:(日月年時分)，這是一種不規則的時間格式，我們要把它格式化成:(年-月-日 時:分)這樣的格式，就的再另外寫個Function來特殊處理了，下面我把我的方法寫上，關于Oracle中解析Clob字段中的xml字符串的方式請看上一篇博客，地址：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);BEGINIF key IS NULL THEN ValueReturn := NULL;RETURN ValueReturn;ELSEDAY := SUBSTR(key,0,2);MOUNTH := SUBSTR(key,3,3);IF INSTR (MOUNTH,'JAN') > 0 THENMOUNTH := 01;END IF;IF INSTR (MOUNTH,'FEB') > 0 THENMOUNTH := 02;END IF;IF INSTR (MOUNTH,'MAR') > 0 THENMOUNTH := 03;END IF;IF INSTR (MOUNTH,'APR') > 0 THENMOUNTH := 04;END IF;IF INSTR (MOUNTH,'MAY') > 0 THENMOUNTH := 05;END IF;IF INSTR (MOUNTH,'JUN') > 0 THENMOUNTH := 06;END IF;IF INSTR (MOUNTH,'JUL') > 0 THENMOUNTH := 07;END IF;IF INSTR (MOUNTH,'AUG') > 0 THENMOUNTH := 08;END IF;IF INSTR (MOUNTH,'SEP') > 0 THENMOUNTH := 09;END IF;IF INSTR (MOUNTH,'OCT') > 0 THENMOUNTH := 10;END IF;IF INSTR (MOUNTH,'NOV') > 0 THENMOUNTH := 11;END IF;IF INSTR (MOUNTH,'DEC') > 0 THENMOUNTH := 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)
ISRENO     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);BEGINRENO := 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;
EXCEPTIONWHEN OTHERSTHENDBMS_OUTPUT.PUT_LINE (SQLERRM);
END MIP_PARSE;
/

三：以上就是改進后的sql語句;

四：詳情請看：http://blog.csdn.net/sxdtzhaoxinguo/article/details/40052783