做学校网站的济南公司,双11销售数据,邯郸市民网,阿里云服务器添加网站摘要#xff1a;接着之前的问题#xff0c;在Oracle数据库中通过Function#xff0c;存储过程#xff0c;触发器实现解析数据表中CLOB大数据字段中存在的xml字符串有时#xff0c;有个特殊的时间字符串要在数据库格式化处理#xff0c;之前解析过来的时间字符串是#x…摘要接着之前的问题在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
