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

http://blog.csdn.net/sxdtzhaoxinguo/article/details/40052783 一：Function.SQL: CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2) RETURN VARCHAR2IS --定义几个变量，出来解析过来的时间字符串 --日月年时分(11OCT14102

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;
/

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;
/