摘要：接着之前的问题，在Oracle数据库中通过Function，存储过程，触发器实现解析数据表中CLOB大数据字段中存在的xml字符串有时，有个特殊的时间字符串要在数据库格式化处理，之前解析过来的时间字符串是，比如：11OCT141024表示:(日月年时分)，这是一种不规则的时间格式，我们要把它格式化成:(年-月-日 时:分)这样的格式，就的再另外写个Function来特殊处理了，下面我把我的方法写上，关于Oracle中解析Clob字段中的xml字符串的方式请看上一篇博客，地址：/sxdtzhaoxinguo/article/details/40052783
一：Function.SQL:
CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2)RETURN VARCHAR2IS--定义几个变量，出来解析过来的时间字符串--日月年时分(11OCT141024)AA VARCHAR2(32);DAYVARCHAR2(32);MOUNTH VARCHAR2(32);YEARVARCHAR2(32);HOURVARCHAR2(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)ISRENOVARCHAR2 (100);AIRLINE VARCHAR2 (100);FFIDVARCHAR2 (100);FFID_A VARCHAR2 (100);FFID_D VARCHAR2 (100);ABNSVARCHAR2 (100);ACFTVARCHAR2 (100);CHDTVARCHAR2 (100);EIBTVARCHAR2 (100);FATAVARCHAR2 (100);FETAVARCHAR2 (100);--FFIDVARCHAR2 (100);FSTAVARCHAR2 (100);LMDTVARCHAR2 (100);LMURVARCHAR2 (100);PSTMVARCHAR2 (100);RWAYVARCHAR2 (100);SPOTVARCHAR2 (100);STNDVARCHAR2 (100);A_TOBT VARCHAR2 (100);A_WEATHERVARCHAR2 (100);--ABNSVARCHAR2 (100);--ACFTVARCHAR2 (100);ASATVARCHAR2 (100);BCTMVARCHAR2 (100);BOTMVARCHAR2 (100);BSTMVARCHAR2 (100);C_TOBT VARCHAR2 (100);COBTVARCHAR2 (100);CTOTVARCHAR2 (100);DINTVARCHAR2 (100);DLABVARCHAR2 (100);DOUTVARCHAR2 (100);EDDIVARCHAR2 (100);EOBTVARCHAR2 (100);EPGTVARCHAR2 (100);EPOTVARCHAR2 (100);FATDVARCHAR2 (100);--FFIDVARCHAR2 (100);FSTDVARCHAR2 (100);--LMDTVARCHAR2 (100);--LMURVARCHAR2 (100);OFTMVARCHAR2 (100);--RENOVARCHAR2 (100);--RWAYVARCHAR2 (100);STDIVARCHAR2 (100);--STNDVARCHAR2 (100);TSATVARCHAR2 (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语句;
四：详情请看：/sxdtzhaoxinguo/article/details/40052783
