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

来源：互联网发布：windows开机不显示壁纸编辑：程序博客网时间：2024/06/05 10:13

摘要：接着之前的问题，在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 VARCHAR2IS   --定义几个变量，出来解析过来的时间字符串   --日月年时分(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

0 0