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