Oracle中通过游标执行带参数的存储过程实现解析CLOB字段内的xml字符串:
来源：互联网 发布：jk制服淘宝店推荐 编辑：程序博客网 时间：2024/05/17 07:18
摘要：近来之前的项目数据出现了问题，原因是由于之前在设计数据库的时候把时间字段设置成了字符串格式，所以给后期的数据操作带来了很大的麻烦，这里提醒一下各位程序猿，以后在开发项目的时候时间字段一定要是时间字段，不要为了方便操作就设成字符串，这样后期改你带来的麻烦是你想象不到的，接下来就看看我解决这个问题的方法吧！
一：存储数据的零时表：
二：零时表里CLOB字段里面存储的xml字符串格式：
三：存储解析完成的xml的数据表：
四：执行解析CLOB字段里面xml字符串的存储过程SQL：
CREATE OR REPLACE PROCEDURE MIP.MIP_PARSE (xmlStr IN CLOB)IS STYP VARCHAR2 (100); 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); FSTA VARCHAR2 (100); LMDT VARCHAR2 (100); LMUR VARCHAR2 (100); PSTM VARCHAR2 (100); RWAY VARCHAR2 (100); SPOT VARCHAR2 (100); STND VARCHAR2 (100); SDEC VARCHAR2 (100); A_TOBT VARCHAR2 (100); A_WEATHER VARCHAR2 (100); ASAT VARCHAR2 (100); BCTM VARCHAR2 (100); BOTM VARCHAR2 (100); BETM VARCHAR2 (100); BSTM VARCHAR2 (100); C_TOBT VARCHAR2 (100); COBT VARCHAR2 (100); CTOT VARCHAR2 (100); DINT VARCHAR2 (100); DLAB VARCHAR2 (100); DNAP VARCHAR2 (100); DOUT VARCHAR2 (100); EDDI VARCHAR2 (100); EOBT VARCHAR2 (100); EPGT VARCHAR2 (100); EPOT VARCHAR2 (100); FATD VARCHAR2 (100); FSTD VARCHAR2 (100); OFTM VARCHAR2 (100); STDI VARCHAR2 (100); TSAT VARCHAR2 (100); FLIGHTNUMBER VARCHAR2 (100); FLIGHTMARK VARCHAR2 (100); ALAP VARCHAR2 (100); APRT VARCHAR2 (100); DPRT VARCHAR2 (100); PARK VARCHAR2 (100); INTERNALORINTERNATIONAL VARCHAR2 (100); TERMINAL VARCHAR2 (100); GROUNDDISTRIBUTION VARCHAR2 (100); --定义出港信息表要格式的时间字段 A_TOBT_D VARCHAR2 (100); ASAT_D VARCHAR2 (100); BCTM_D VARCHAR2 (100); BOTM_D VARCHAR2 (100); BETM_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); --定义进港信息表要格式化的时间字段 BSTM_A 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); COUNTS NUMBER(36); --定义出港信息要修改的除时间外的字段 STND_D VARCHAR2 (100); A_WEATHER_D VARCHAR2 (100); ABNS_D VARCHAR2 (100); ACFT_D VARCHAR2 (100); AIRLINE_D VARCHAR2 (100); DLAB_D VARCHAR2 (100); DNAP_D VARCHAR2 (100); LMUR_D VARCHAR2 (100); RENO_D VARCHAR2 (100); RWAY_D VARCHAR2 (100); DPRT_D VARCHAR2 (100); PARK_D VARCHAR2 (100); INTERNALORINTERNATIONAL_D VARCHAR2 (100); TERMINAL_D VARCHAR2 (100); GROUNDDISTRIBUTION_D VARCHAR2 (100); --定义进港信息要修改的除时间外的字段 ABNS_A VARCHAR2 (100); ACFT_A VARCHAR2 (100); AIRLINE_A VARCHAR2 (100); ALAP_A VARCHAR2 (100); APRT_A VARCHAR2 (100); CHDT_A VARCHAR2 (100); RENO_A VARCHAR2 (100); LMUR_A VARCHAR2 (100); RWAY_A VARCHAR2 (100); STND_A VARCHAR2 (100); PARK_A VARCHAR2 (100); INTERNALORINTERNATIONAL_A VARCHAR2 (100); TERMINAL_A VARCHAR2 (100); GROUNDDISTRIBUTION_A VARCHAR2 (100); BEGIN STYP := GetXmlNodeValue (xmlStr, 'STYP'); RENO := GetXmlNodeValue (xmlStr, 'RENO'); 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'); 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'); SDEC := GetXmlNodeValue (xmlStr, 'STND'); A_TOBT := GetXmlNodeValue (xmlStr, 'A_TOBT'); A_WEATHER := GetXmlNodeValue (xmlStr, 'A_WEATHER'); ALAP := GetXmlNodeValue (xmlStr, 'ALAP'); APRT := GetXmlNodeValue (xmlStr, 'APRT'); ASAT := GetXmlNodeValue (xmlStr, 'ASAT'); BCTM := GetXmlNodeValue (xmlStr, 'BCTM'); BOTM := GetXmlNodeValue (xmlStr, 'BOTM'); BETM := GetXmlNodeValue (xmlStr, 'BETM'); 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'); DNAP := GetXmlNodeValue (xmlStr, 'DNAP'); 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'); OFTM := GetXmlNodeValue (xmlStr, 'OFTM'); STDI := GetXmlNodeValue (xmlStr, 'STDI'); TSAT := GetXmlNodeValue (xmlStr, 'TSAT'); DPRT := GetXmlNodeValue (xmlStr, 'DPRT'); PARK := GetXmlNodeValue (xmlStr, 'PARK'); INTERNALORINTERNATIONAL := GetXmlNodeValue (xmlStr, 'INTERNALORINTERNATIONAL'); TERMINAL := GetXmlNodeValue (xmlStr, 'TERMINAL'); GROUNDDISTRIBUTION := GetXmlNodeValue (xmlStr, 'GROUNDDISTRIBUTION'); --出港信息表中时间字段的时间格式函数的用法 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'); BETM_D := FORMATDATEVALUE (BETM, 'BETM_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'); BSTM_A := FORMATDATEVALUE (BSTM, 'BSTM_A'); --出港信息要修改的除时间外的字段 STND_D := GetXmlNodeValue (xmlStr, 'STND'); A_WEATHER_D := GetXmlNodeValue (xmlStr, 'A_WEATHER'); ABNS_D := GetXmlNodeValue (xmlStr, 'ABNS'); ACFT_D := GetXmlNodeValue (xmlStr, 'ACFT'); AIRLINE_D := GetXmlNodeValue (xmlStr, 'AIRLINE'); DLAB_D := GetXmlNodeValue (xmlStr, 'DLAB'); DNAP_D := GetXmlNodeValue (xmlStr, 'DNAP'); LMUR_D := GetXmlNodeValue (xmlStr, 'LMUR'); RENO_D := GetXmlNodeValue (xmlStr, 'RENO'); RWAY_D := GetXmlNodeValue (xmlStr, 'RWAY'); DPRT_D := GetXmlNodeValue (xmlStr, 'DPRT'); PARK_D := GetXmlNodeValue (xmlStr, 'PARK'); TERMINAL_D := GetXmlNodeValue (xmlStr, 'TERMINAL'); GROUNDDISTRIBUTION_D := GetXmlNodeValue (xmlStr, 'GROUNDDISTRIBUTION'); --进港信息要修改的除时间外的字段 ABNS_A := GetXmlNodeValue (xmlStr, 'ABNS'); ACFT_A := GetXmlNodeValue (xmlStr, 'ACFT'); AIRLINE_A := GetXmlNodeValue (xmlStr, 'AIRLINE'); ALAP_A := GetXmlNodeValue (xmlStr, 'ALAP'); APRT_A := GetXmlNodeValue (xmlStr, 'APRT'); CHDT_A := GetXmlNodeValue (xmlStr, 'CHDT'); RENO_A := GetXmlNodeValue (xmlStr, 'RENO'); LMUR_A := GetXmlNodeValue (xmlStr, 'LMUR'); RWAY_A := GetXmlNodeValue (xmlStr, 'RWAY'); STND_A := GetXmlNodeValue (xmlStr, 'STND'); PARK_A := GetXmlNodeValue (xmlStr, 'PARK'); TERMINAL_A := GetXmlNodeValue (xmlStr, 'TERMINAL'); GROUNDDISTRIBUTION_A := GetXmlNodeValue (xmlStr, 'GROUNDDISTRIBUTION'); IF STYP = 'FGIS' THEN IF INSTR(FFID,'-D-') > 0 THEN FFID_D := FFID; --截取航空公司代码 AIRLINE := SUBSTR(FFID_D,0,2); --截取航班号 FLIGHTNUMBER := SUBSTR(FFID_D,INSTR(FFID_D,'-',1)+1,INSTR(FFID_D,'-',INSTR(FFID_D,'-',1)+1)-INSTR(FFID_D,'-',1)-1); --截取出港标志 FLIGHTMARK := SUBSTR(FFID_D,INSTR(FFID_D,'-',2,2)+1,INSTR(FFID_D,'-',2,3)-1-INSTR(FFID_D,'-',2,2)); --截取进离港标志 INTERNALORINTERNATIONAL := SUBSTR(FFID_D,-1); --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_D where FFID = FFID_D; IF COUNTS > 0 THEN IF A_TOBT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET A_TOBT = A_TOBT_D WHERE FFID = FFID_D; END IF; IF A_WEATHER_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET A_WEATHER = A_WEATHER_D WHERE FFID = FFID_D; END IF; IF ABNS_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET ABNS = ABNS_D WHERE FFID = FFID_D; END IF; IF ACFT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET ACFT = ACFT_D WHERE FFID = FFID_D; END IF; IF ASAT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET ASAT = ASAT_D WHERE FFID = FFID_D; END IF; IF BCTM_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET BCTM = BCTM_D WHERE FFID = FFID_D; END IF; IF BOTM_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET BOTM = BOTM_D WHERE FFID = FFID_D; END IF; IF BETM_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET BETM = BETM_D WHERE FFID = FFID_D; END IF; IF C_TOBT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET C_TOBT = C_TOBT_D WHERE FFID = FFID_D; END IF; IF COBT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET COBT = COBT_D WHERE FFID = FFID_D; END IF; IF CTOT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET CTOT = CTOT_D WHERE FFID = FFID_D; END IF; IF DINT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DINT = DINT_D WHERE FFID = FFID_D; END IF; IF DLAB_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DLAB = DLAB_D WHERE FFID = FFID_D; END IF; IF DNAP_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DNAP = DNAP_D WHERE FFID = FFID_D; END IF; IF DOUT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DOUT = DOUT_D WHERE FFID = FFID_D; END IF; IF EDDI_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET EDDI = EDDI_D WHERE FFID = FFID_D; END IF; IF EOBT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET EOBT = EOBT_D WHERE FFID = FFID_D; END IF; IF EPGT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET EPGT = EPGT_D WHERE FFID = FFID_D; END IF; IF EPOT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET EPOT = EPOT_D WHERE FFID = FFID_D; END IF; IF FATD_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET FATD = FATD_D WHERE FFID = FFID_D; END IF; IF FSTD_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET FSTD = FSTD_D WHERE FFID = FFID_D; END IF; IF LMDT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET LMDT = LMDT_D WHERE FFID = FFID_D; END IF; IF LMUR_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET LMUR = LMUR_D WHERE FFID = FFID_D; END IF; IF OFTM_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET OFTM = OFTM_D WHERE FFID = FFID_D; END IF; IF RENO_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET RENO = RENO_D WHERE FFID = FFID_D; END IF; IF RWAY_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET RWAY = RWAY_D WHERE FFID = FFID_D; END IF; IF STDI_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET STDI = STDI_D WHERE FFID = FFID_D; END IF; IF STND_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET STND = STND_D WHERE FFID = FFID_D; END IF; IF TSAT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET TSAT = TSAT_D WHERE FFID = FFID_D; END IF; IF DPRT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DPRT = DPRT_D WHERE FFID = FFID_D; END IF; IF PARK_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET PARK = PARK_D WHERE FFID = FFID_D; END IF; IF TERMINAL_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET TERMINAL = TERMINAL_D WHERE FFID = FFID_D; END IF; IF GROUNDDISTRIBUTION_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET GROUNDDISTRIBUTION = GROUNDDISTRIBUTION_D WHERE FFID = FFID_D; END IF; ELSE INSERT INTO TB_CMS_FLGTINFO_D (ID,A_TOBT,A_WEATHER,ABNS,ACFT,AIRLINE,ASAT,BCTM,BOTM,BETM,C_TOBT,COBT,CTOT,DINT,DLAB,DNAP,DOUT,DPRT,EDDI,EOBT,EPGT,EPOT,FATD,FFID,FLIGHTNUMBER, FLIGHTMARK,FSTD,GROUNDDISTRIBUTION,INTERNALORINTERNATIONAL,LMDT,LMUR,OFTM,PARK,RENO,RWAY,STDI,STND,TERMINAL,TSAT) VALUES (FLGTINFO_D_SEQ.NEXTVAL, A_TOBT_D, A_WEATHER, ABNS, ACFT, AIRLINE, ASAT_D, BCTM_D, BOTM_D, BETM_D, C_TOBT_D, COBT_D, CTOT_D, DINT_D, DLAB, DNAP, DOUT_D, DPRT, EDDI_D, EOBT_D, EPGT_D, EPOT_D, FATD_D, FFID_D, FLIGHTNUMBER, FLIGHTMARK, FSTD_D, GROUNDDISTRIBUTION, INTERNALORINTERNATIONAL, LMDT_D, LMUR, OFTM_D, PARK, RENO, RWAY, STDI_D, STND, TERMINAL, TSAT_D); END IF; ELSE FFID_A := FFID; --截取航空公司代码 AIRLINE := SUBSTR(FFID_A,0,2); --截取航班号 FLIGHTNUMBER := SUBSTR(FFID_A,INSTR(FFID_A,'-',1)+1,INSTR(FFID_A,'-',INSTR(FFID_A,'-',1)+1)-INSTR(FFID_A,'-',1)-1); --截取出港标志 FLIGHTMARK := SUBSTR(FFID_A,INSTR(FFID_A,'-',2,2)+1,INSTR(FFID_A,'-',2,3)-1-INSTR(FFID_A,'-',2,2)); --截取进离港标志 INTERNALORINTERNATIONAL := SUBSTR(FFID_A,-1); --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_A where FFID = FFID_A; IF COUNTS > 0 THEN IF ABNS_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET ABNS = ABNS_A WHERE FFID = FFID_A; END IF; IF ACFT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET ACFT = ACFT_A WHERE FFID = FFID_A; END IF; IF ALAP_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET ALAP = ALAP_A WHERE FFID = FFID_A; END IF; IF BSTM_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET BSTM = BSTM_A WHERE FFID = FFID_A; END IF; IF CHDT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET CHDT = CHDT_A WHERE FFID = FFID_A; END IF; IF RENO_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET RENO = RENO_A WHERE FFID = FFID_A; END IF; IF EIBT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET EIBT = EIBT_A WHERE FFID = FFID_A; END IF; IF FATA_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET FATA = FATA_A WHERE FFID = FFID_A; END IF; IF FETA_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET FETA = FETA_A WHERE FFID = FFID_A; END IF; IF FSTA_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET FSTA = FSTA_A WHERE FFID = FFID_A; END IF; IF LMDT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET LMDT = LMDT_A WHERE FFID = FFID_A; END IF; IF LMUR_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET LMUR = LMUR_A WHERE FFID = FFID_A; END IF; IF PSTM_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET PSTM = PSTM_A WHERE FFID = FFID_A; END IF; IF RWAY_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET RWAY = RWAY_A WHERE FFID = FFID_A; END IF; IF SPOT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET SPOT = SPOT_A WHERE FFID = FFID_A; END IF; IF STND_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET STND = STND_A WHERE FFID = FFID_A; END IF; IF APRT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET APRT = APRT_A WHERE FFID = FFID_A; END IF; IF PARK_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET PARK = PARK_A WHERE FFID = FFID_A; END IF; IF TERMINAL_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET TERMINAL = TERMINAL_A WHERE FFID = FFID_A; END IF; IF GROUNDDISTRIBUTION_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET GROUNDDISTRIBUTION = GROUNDDISTRIBUTION_A WHERE FFID = FFID_A; END IF; ELSE INSERT INTO TB_CMS_FLGTINFO_A (ID,ABNS,ACFT,AIRLINE,ALAP,BSTM,CHDT,APRT,FFID,FLIGHTNUMBER,FLIGHTMARK,GROUNDDISTRIBUTION,INTERNALORINTERNATIONAL,RENO,EIBT,FATA,FETA,FSTA,LMDT,LMUR,PARK,PSTM,RWAY,SPOT,STND,TERMINAL) VALUES (FLGTINFO_A_SEQ.NEXTVAL, ABNS, ACFT, AIRLINE, ALAP, BSTM_A, CHDT, APRT, FFID_A, FLIGHTNUMBER, FLIGHTMARK, GROUNDDISTRIBUTION, INTERNALORINTERNATIONAL, RENO, EIBT_A, FATA_A, FETA_A, FSTA_A, LMDT_A, LMUR, PARK, PSTM_A, RWAY, SPOT_A, STND, TERMINAL); END IF; END IF; ELSE IF INSTR(FFID,'-D-') > 0 THEN FFID_D := FFID; --截取航空公司代码 AIRLINE := SUBSTR(FFID_D,0,2); --截取航班号 FLIGHTNUMBER := SUBSTR(FFID_D,INSTR(FFID_D,'-',1)+1,INSTR(FFID_D,'-',INSTR(FFID_D,'-',1)+1)-INSTR(FFID_D,'-',1)-1); --截取出港标志 FLIGHTMARK := SUBSTR(FFID_D,INSTR(FFID_D,'-',2,2)+1,INSTR(FFID_D,'-',2,3)-1-INSTR(FFID_D,'-',2,2)); --截取进离港标志 INTERNALORINTERNATIONAL := SUBSTR(FFID_D,-1); --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_D where FFID = FFID_D; IF COUNTS > 0 THEN IF A_TOBT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET A_TOBT = A_TOBT_D WHERE FFID = FFID_D; END IF; IF A_WEATHER_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET A_WEATHER = A_WEATHER_D WHERE FFID = FFID_D; END IF; IF ABNS_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET ABNS = ABNS_D WHERE FFID = FFID_D; END IF; IF ACFT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET ACFT = ACFT_D WHERE FFID = FFID_D; END IF; IF ASAT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET ASAT = ASAT_D WHERE FFID = FFID_D; END IF; IF BCTM_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET BCTM = BCTM_D WHERE FFID = FFID_D; END IF; IF BOTM_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET BOTM = BOTM_D WHERE FFID = FFID_D; END IF; IF BETM_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET BETM = BETM_D WHERE FFID = FFID_D; END IF; IF C_TOBT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET C_TOBT = C_TOBT_D WHERE FFID = FFID_D; END IF; IF COBT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET COBT = COBT_D WHERE FFID = FFID_D; END IF; IF CTOT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET CTOT = CTOT_D WHERE FFID = FFID_D; END IF; IF DINT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DINT = DINT_D WHERE FFID = FFID_D; END IF; IF DLAB_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DLAB = DLAB_D WHERE FFID = FFID_D; END IF; IF DNAP_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DNAP = DNAP_D WHERE FFID = FFID_D; END IF; IF DOUT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DOUT = DOUT_D WHERE FFID = FFID_D; END IF; IF EDDI_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET EDDI = EDDI_D WHERE FFID = FFID_D; END IF; IF EOBT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET EOBT = EOBT_D WHERE FFID = FFID_D; END IF; IF EPGT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET EPGT = EPGT_D WHERE FFID = FFID_D; END IF; IF EPOT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET EPOT = EPOT_D WHERE FFID = FFID_D; END IF; IF FATD_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET FATD = FATD_D WHERE FFID = FFID_D; END IF; IF FSTD_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET FSTD = FSTD_D WHERE FFID = FFID_D; END IF; IF LMDT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET LMDT = LMDT_D WHERE FFID = FFID_D; END IF; IF LMUR_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET LMUR = LMUR_D WHERE FFID = FFID_D; END IF; IF OFTM_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET OFTM = OFTM_D WHERE FFID = FFID_D; END IF; IF RENO_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET RENO = RENO_D WHERE FFID = FFID_D; END IF; IF RWAY_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET RWAY = RWAY_D WHERE FFID = FFID_D; END IF; IF STDI_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET STDI = STDI_D WHERE FFID = FFID_D; END IF; IF SDEC != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET SDEC = SDEC WHERE FFID = FFID_D; END IF; IF TSAT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET TSAT = TSAT_D WHERE FFID = FFID_D; END IF; IF DPRT_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET DPRT = DPRT_D WHERE FFID = FFID_D; END IF; IF PARK_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET PARK = PARK_D WHERE FFID = FFID_D; END IF; IF TERMINAL_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET TERMINAL = TERMINAL_D WHERE FFID = FFID_D; END IF; IF GROUNDDISTRIBUTION_D != ' ' THEN UPDATE TB_CMS_FLGTINFO_D SET GROUNDDISTRIBUTION = GROUNDDISTRIBUTION_D WHERE FFID = FFID_D; END IF; ELSE INSERT INTO TB_CMS_FLGTINFO_D (ID,A_TOBT,A_WEATHER,ABNS,ACFT,AIRLINE,ASAT,BCTM,BOTM,BETM,C_TOBT,COBT,CTOT,DINT,DLAB,DNAP,DOUT,DPRT,EDDI,EOBT,EPGT,EPOT,FATD,FFID,FLIGHTNUMBER, FLIGHTMARK,FSTD,GROUNDDISTRIBUTION,INTERNALORINTERNATIONAL,LMDT,LMUR,OFTM,PARK,RENO,RWAY,STDI,SDEC,TERMINAL,TSAT) VALUES (FLGTINFO_D_SEQ.NEXTVAL, A_TOBT_D, A_WEATHER, ABNS, ACFT, AIRLINE, ASAT_D, BCTM_D, BOTM_D, BETM_D, C_TOBT_D, COBT_D, CTOT_D, DINT_D, DLAB, DNAP, DOUT_D, DPRT, EDDI_D, EOBT_D, EPGT_D, EPOT_D, FATD_D, FFID_D, FLIGHTNUMBER, FLIGHTMARK, FSTD_D, GROUNDDISTRIBUTION, INTERNALORINTERNATIONAL, LMDT_D, LMUR, OFTM_D, PARK, RENO, RWAY, STDI_D, SDEC, TERMINAL, TSAT_D); END IF; ELSE FFID_A := FFID; --截取航空公司代码 AIRLINE := SUBSTR(FFID_A,0,2); --截取航班号 FLIGHTNUMBER := SUBSTR(FFID_A,INSTR(FFID_A,'-',1)+1,INSTR(FFID_A,'-',INSTR(FFID_A,'-',1)+1)-INSTR(FFID_A,'-',1)-1); --截取出港标志 FLIGHTMARK := SUBSTR(FFID_A,INSTR(FFID_A,'-',2,2)+1,INSTR(FFID_A,'-',2,3)-1-INSTR(FFID_A,'-',2,2)); --截取进离港标志 INTERNALORINTERNATIONAL := SUBSTR(FFID_A,-1); --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_A where FFID = FFID_A; IF COUNTS > 0 THEN IF ABNS_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET ABNS = ABNS_A WHERE FFID = FFID_A; END IF; IF ACFT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET ACFT = ACFT_A WHERE FFID = FFID_A; END IF; IF ALAP_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET ALAP = ALAP_A WHERE FFID = FFID_A; END IF; IF BSTM_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET BSTM = BSTM_A WHERE FFID = FFID_A; END IF; IF CHDT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET CHDT = CHDT_A WHERE FFID = FFID_A; END IF; IF RENO_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET RENO = RENO_A WHERE FFID = FFID_A; END IF; IF EIBT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET EIBT = EIBT_A WHERE FFID = FFID_A; END IF; IF FATA_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET FATA = FATA_A WHERE FFID = FFID_A; END IF; IF FETA_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET FETA = FETA_A WHERE FFID = FFID_A; END IF; IF FSTA_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET FSTA = FSTA_A WHERE FFID = FFID_A; END IF; IF LMDT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET LMDT = LMDT_A WHERE FFID = FFID_A; END IF; IF LMUR_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET LMUR = LMUR_A WHERE FFID = FFID_A; END IF; IF PSTM_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET PSTM = PSTM_A WHERE FFID = FFID_A; END IF; IF RWAY_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET RWAY = RWAY_A WHERE FFID = FFID_A; END IF; IF SPOT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET SPOT = SPOT_A WHERE FFID = FFID_A; END IF; IF STND_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET STND = STND_A WHERE FFID = FFID_A; END IF; IF APRT_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET APRT = APRT_A WHERE FFID = FFID_A; END IF; IF PARK_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET PARK = PARK_A WHERE FFID = FFID_A; END IF; IF TERMINAL_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET TERMINAL = TERMINAL_A WHERE FFID = FFID_A; END IF; IF GROUNDDISTRIBUTION_A != ' ' THEN UPDATE TB_CMS_FLGTINFO_A SET GROUNDDISTRIBUTION = GROUNDDISTRIBUTION_A WHERE FFID = FFID_A; END IF; ELSE INSERT INTO TB_CMS_FLGTINFO_A (ID,ABNS,ACFT,AIRLINE,ALAP,BSTM,CHDT,APRT,FFID,FLIGHTNUMBER,FLIGHTMARK,GROUNDDISTRIBUTION,INTERNALORINTERNATIONAL,RENO,EIBT,FATA,FETA,FSTA,LMDT,LMUR,PARK,PSTM,RWAY,SPOT,STND,TERMINAL) VALUES (FLGTINFO_A_SEQ.NEXTVAL, ABNS, ACFT, AIRLINE, ALAP, BSTM_A, CHDT, APRT, FFID_A, FLIGHTNUMBER, FLIGHTMARK, GROUNDDISTRIBUTION, INTERNALORINTERNATIONAL, RENO, EIBT_A, FATA_A, FETA_A, FSTA_A, LMDT_A, LMUR, PARK, PSTM_A, RWAY, SPOT_A, STND, TERMINAL); END IF; END IF; END IF; COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM);END MIP_PARSE;/
五：存储过程里面用到的Function.SQL:
CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2) RETURN VARCHAR2IS --定义几个变量，出来解析过来的时间字符串 --日月年时分(11OCT141024) Str VARCHAR2(32); AA VARCHAR2(32); DAY VARCHAR2(32); MOUNTH VARCHAR2(32); YEAR VARCHAR2(32); HOUR VARCHAR2(32); MINUTE VARCHAR2(32); ValueReturn VARCHAR2 (64);BEGIN IF key != ' ' THEN 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; Str := 0; --日月年时分(11OCT141017) IF length(MOUNTH) < 2 THEN MOUNTH := Str||MOUNTH; ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE; ELSE ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE; END IF; --ValueReturn := HOUR || ':' || MINUTE; RETURN ValueReturn; ELSE ValueReturn := ' '; RETURN ValueReturn; END IF;END FormatDateValue;/
CREATE OR REPLACE FUNCTION MIP.GetXmlNodeValue (xmlStr CLOB, nodeName VARCHAR2) RETURN VARCHAR2IS --创建xml解析器实例xmlparser.Parser xmlPar xmlparser.Parser := xmlparser.newParser; --定义DOM文档 xDoc xmldom.DOMDocument; --定义item子节点数目变量 lenItme INTEGER; --定义节点列表，存放item节点们 itemNodes xmldom.DOMNodeList; --定义节点，存放单个item节点 itemNode xmldom.DOMNode; ValueReturn VARCHAR2 (100); BEGIN --解析xmlStr中xml字符串，并存放到xmlPar中 xmlparser.parseClob (xmlPar, xmlStr); --将xmlPar中的数据转存到dom文档中 xDoc := xmlparser.getDocument (xmlPar); --释放解析器实例 xmlparser.freeParser (xmlPar); --获取所有item节点 itemNodes := xmldom.getElementsByTagName (xDoc, nodeName); --获取item节点的个数 lenItme := xmldom.getLength (itemNodes); --如果无该标签，则返回EMPTY IF lenItme = 0 THEN ValueReturn := ' '; ELSE --获取节点列表中的第1个item节点 itemNode := xmldom.item (itemNodes, 0); --获取所有子节点的值 ValueReturn := xmldom.getNodeValue (xmldom.getFirstChild (itemNode)); END IF; --释放dom xmldom.freeDocument(xDoc); RETURN ValueReturn; END GetXmlNodeValue;/
六：最后是调用存储过程执行解析Clob字段里面的xml字符串的游标SQL：
/* Formatted on 2015/1/15 14:20:27 (QP5 v5.115.810.9015) */DECLARE --定义游标 CURSOR c_cursor IS --这里查询指定时间内的数据，根据时间判断一下id>那个编号开始 SELECT MBINMSGS_CLOB_MSG FROM MBINMSGS_TEMP; v_MBINMSGS_CLOB_MSG MBINMSGS_TEMP.MBINMSGS_CLOB_MSG%TYPE;BEGIN --打开游标 OPEN c_cursor; --提取游标数据 FETCH c_cursor INTO v_MBINMSGS_CLOB_MSG; WHILE c_cursor%FOUND LOOP DBMS_OUTPUT.put_line (v_MBINMSGS_CLOB_MSG); FETCH c_cursor INTO v_MBINMSGS_CLOB_MSG; MIP_PARSE(v_MBINMSGS_CLOB_MSG); END LOOP;END;
总结：以上所以的SQL操作都是在PL/SQL中完成的，这样执行完成后的结果就是把零时表里面的所有的CLOB字段里面的xml解析并更新到对应的数据表中。
0 0
- Oracle中通过游标执行带参数的存储过程实现解析CLOB字段内的xml字符串:
- Oracle存储过程中使用游标来批量解析CLOB字段里面的xml字符串:
- Oracle中通过：触发器，存储过程，Function调用实现解析Clob字段类型中存储的xml字符串
- Oracle中通过Function，存储过程，触发器，调用实现解析Clob字段中存在的xml字符串
- 在java的hibernate3框架中调用oracle数据库中带游标参数的存储过程
- Oracle中通过存储过程，Function，触发器实现解析时间类型的字段并插入的对应的数据表中
- oracle中带游标的存储过程示例
- oracle带游标的存储过程
- oracle带参数的存储过程的执行
- 使用存储过程读取Oracle中的clob字段的数据
- 存储过程中游标带参数
- Oracle关于创建存储过程权限问题以及带参数的游标的范例
- 执行带输出参数的存储过程
- 执行带参数的存储过程
- oracle pl/sql中 执行带 OUT 的存储过程
- hibernate里使用jdbc方式调用oracle带游标返回参数的简单分页存储过程
- JAVA调用ORACLE带数组输入参数和返回游标结果集的存储过程
- oracle存储过程中游标的使用（包括带参数的游标）
- UVa 132 Another Chocolate Maniac（状压DP）
- 如何查看加密过的SQL Server procedure
- Tomcat启动时类加载顺序
- [story]Linux下查看内存使用情况方法总结
- XP 删除与添加服务！
- Oracle中通过游标执行带参数的存储过程实现解析CLOB字段内的xml字符串:
- USB枚举过程
- 进程与线程的一个简单解释
- SQL面试题
- 自我介绍以及目标
- 使用scrapy爬取京东产品详细信息
- 关于 android 中 postDelayed方法的讲解
- Java Socket编程
- 在一台服务器上面，JVM可以分配到的最大内存是多少？
