Oracle中通过游标执行带参数的存储过程实现解析CLOB字段内的xml
oracle
parameter
storage
accomplish
implement
cursor
parse
process
pass
摘要：近来之前的项目数据出现了问题，原因是由于之前在设计数据库的时候把时间字段设置成了字符串式，所以给后期的数据操作带来了很大的麻烦，这里提醒一下各位程序猿，以后在开发项目的时候时间字段一定要是时间字段，不要为了方便操作就设成字符串，这样
摘要：近来之前的项目数据出现了问题，原因是由于之前在设计数据库的时候把时间字段设置成了字符串格式，所以给后期的数据操作带来了很大的麻烦，这里提醒一下各位程序猿，以后在开发项目的时候时间字段一定要是时间字段，不要为了方便操作就设成字符串，这样后期改你带来的麻烦是你想象不到的，接下来就看看我解决这个问题的方法吧！
一：存储数据的零时表：
二：零时表里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;
/
Copy after login
五：存储过程里面用到的Function.SQL:
CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2) RETURN VARCHAR2 IS --定义几个变量，出来解析过来的时间字符串 --日月年时分(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) <br> <pre class="brush:php;toolbar:false">
Copy after login
六：最后是调用存储过程执行解析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;Copy after login
总结：以上所以的SQL操作都是在PL/SQL中完成的，这样执行完成后的结果就是把零时表里面的所有的CLOB字段里面的xml解析并更新到对应的数据表中。
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Assassin's Creed Shadows: Seashell Riddle Solution
1 weeks ago
By DDD
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Where to find the Crane Control Keycard in Atomfall
1 weeks ago
By DDD
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
CakePHP Tutorial
1359
52
1359
52
How long will Oracle database logs be kept?
May 10, 2024 am 03:27 AM
The retention period of Oracle database logs depends on the log type and configuration, including: Redo logs: determined by the maximum size configured with the "LOG_ARCHIVE_DEST" parameter. Archived redo logs: Determined by the maximum size configured by the "DB_RECOVERY_FILE_DEST_SIZE" parameter. Online redo logs: not archived, lost when the database is restarted, and the retention period is consistent with the instance running time. Audit log: Configured by the "AUDIT_TRAIL" parameter, retained for 30 days by default.
The order of the oracle database startup steps is
May 10, 2024 am 01:48 AM
The Oracle database startup sequence is: 1. Check the preconditions; 2. Start the listener; 3. Start the database instance; 4. Wait for the database to open; 5. Connect to the database; 6. Verify the database status; 7. Enable the service (if necessary ); 8. Test the connection.
How much memory does oracle require?
May 10, 2024 am 04:12 AM
The amount of memory required by Oracle depends on database size, activity level, and required performance level: for storing data buffers, index buffers, executing SQL statements, and managing the data dictionary cache. The exact amount is affected by database size, activity level, and required performance level. Best practices include setting the appropriate SGA size, sizing SGA components, using AMM, and monitoring memory usage.
How to see the number of occurrences of a certain character in Oracle
May 09, 2024 pm 09:33 PM
To find the number of occurrences of a character in Oracle, perform the following steps: Get the total length of a string; Get the length of the substring in which a character occurs; Count the number of occurrences of a character by subtracting the substring length from the total length.
Oracle database server hardware configuration requirements
May 10, 2024 am 04:00 AM
Oracle database server hardware configuration requirements: Processor: multi-core, with a main frequency of at least 2.5 GHz. For large databases, 32 cores or more are recommended. Memory: At least 8GB for small databases, 16-64GB for medium sizes, up to 512GB or more for large databases or heavy workloads. Storage: SSD or NVMe disks, RAID arrays for redundancy and performance. Network: High-speed network (10GbE or higher), dedicated network card, low-latency network. Others: Stable power supply, redundant components, compatible operating system and software, heat dissipation and cooling system.
How to read dbf file in oracle
May 10, 2024 am 01:27 AM
Oracle can read dbf files through the following steps: create an external table and reference the dbf file; query the external table to retrieve data; import the data into the Oracle table.
How much memory is needed to use oracle database
May 10, 2024 am 03:42 AM
The amount of memory required for an Oracle database depends on the database size, workload type, and number of concurrent users. General recommendations: Small databases: 16-32 GB, Medium databases: 32-64 GB, Large databases: 64 GB or more. Other factors to consider include database version, memory optimization options, virtualization, and best practices (monitor memory usage, adjust allocations).
Oracle scheduled tasks execute the creation step once a day
May 10, 2024 am 03:03 AM
To create a scheduled task in Oracle that executes once a day, you need to perform the following three steps: Create a job. Add a subjob to the job and set its schedule expression to "INTERVAL 1 DAY". Enable the job.
