欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-02-03 21:43:04
...

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

二:存储过程.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