广东手机网站建设,wordpress备份插件中文,iapp网站怎么做软件,广东专业网站定制摘要#xff1a;之前在项目中使用到了Oracle数据库中通过触发器去调用存储过程执行数据解析并Update到对应的数据表中#xff0c;但是#xff0c;经过一段时间的测试使用发现#xff0c;如果job那天停掉了#xff0c;然后你再重新新建job的话#xff0c;这时候可能会有很…摘要之前在项目中使用到了Oracle数据库中通过触发器去调用存储过程执行数据解析并Update到对应的数据表中但是经过一段时间的测试使用发现如果job那天停掉了然后你再重新新建job的话这时候可能会有很多遗留的数据没有出来因为之前是通过触发器的方式来一条数据解析一条并Update到对应的数据表中现在一下要执行很多数据就会报错内存溢出的错误所以后来经过分析讨论把之前通过触发器解析的方式改成了通过job来定时调用存储过程解析并且在存储过程中增加了游标的使用这样就不会有丢失的数据同时也能保证在那天job出问题了再新建job后数据解析出现问题了具体的SQL如下 这里我把我用到的所有的存储过程函数job的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);BEGINIF key ! THENDAY : SUBSTR(key,0,2);MOUNTH : SUBSTR(key,3,3);IF INSTR (MOUNTH,JAN) 0 THENMOUNTH : 01;END IF;IF INSTR (MOUNTH,FEB) 0 THENMOUNTH : 02;END IF;IF INSTR (MOUNTH,MAR) 0 THENMOUNTH : 03;END IF;IF INSTR (MOUNTH,APR) 0 THENMOUNTH : 04;END IF;IF INSTR (MOUNTH,MAY) 0 THENMOUNTH : 05;END IF;IF INSTR (MOUNTH,JUN) 0 THENMOUNTH : 06;END IF;IF INSTR (MOUNTH,JUL) 0 THENMOUNTH : 07;END IF;IF INSTR (MOUNTH,AUG) 0 THENMOUNTH : 08;END IF;IF INSTR (MOUNTH,SEP) 0 THENMOUNTH : 09;END IF;IF INSTR (MOUNTH,OCT) 0 THENMOUNTH : 10;END IF;IF INSTR (MOUNTH,NOV) 0 THENMOUNTH : 11;END IF;IF INSTR (MOUNTH,DEC) 0 THENMOUNTH : 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 THENMOUNTH : Str||MOUNTH;ValueReturn : AA || YEAR || - || MOUNTH || - || DAY || || HOUR || : || MINUTE;ELSEValueReturn : AA || YEAR || - || MOUNTH || - || DAY || || HOUR || : || MINUTE;END IF;--ValueReturn : HOUR || : || MINUTE;RETURN ValueReturn;ELSEValueReturn : ;RETURN ValueReturn;END IF;END FormatDateValue;
/CREATE OR REPLACE FUNCTION MIP.GetXmlNodeValue (xmlStr CLOB, nodeName VARCHAR2)RETURN VARCHAR2
IS--创建xml解析器实例xmlparser.ParserxmlPar 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);--如果无该标签则返回EMPTYIF lenItme 0 THENValueReturn : ;ELSE--获取节点列表中的第1个item节点itemNode : xmldom.item (itemNodes, 0);--获取所有子节点的值ValueReturn : xmldom.getNodeValue (xmldom.getFirstChild (itemNode));END IF; --释放domxmldom.freeDocument(xDoc);RETURN ValueReturn;END GetXmlNodeValue;
/二存储过程 CREATE OR REPLACE PROCEDURE MIP.JOB_PRO_TEMP
ASTEMP_ID NUMBER;
BEGINSELECT NVL (MAX (ID), 0) INTO TEMP_ID FROM MBINMSGS_TEMP;INSERT INTO MBINMSGS_TEMPSELECT *FROM MBINMSGSWHERE MBINMSGS.ID TEMP_ID;--WHERE MBINMSGS.mbinmsgs_date_processed to_date(2015-1-12 12:21:23,yyyy-mm-dd hh24:mi:ss);COMMIT;
EXCEPTIONWHEN OTHERSTHENDBMS_OUTPUT.PUT_LINE (Exception happened,data was rollback);ROLLBACK;
END;
/CREATE OR REPLACE PROCEDURE MIP.LOOP_COPY_PRO_TEMPAS BEGINDECLARE--定义游标CURSOR c_cursorIS--这里查询指定时间内的数据根据时间判断一下id那个编号开始SELECT ID,MBINMSGS_CLOB_MSG FROM MBINMSGS_TEMP WHERE MBINMSGS_DATE_PROCESSED IS NULL ORDER BY ID DESC;v_ID MBINMSGS_TEMP.ID%TYPE;v_MBINMSGS_CLOB_MSG MBINMSGS_TEMP.MBINMSGS_CLOB_MSG%TYPE;BEGIN--打开游标OPEN c_cursor;--提取游标数据FETCH c_cursor INTO v_ID,v_MBINMSGS_CLOB_MSG;WHILE c_cursor%FOUNDLOOPDBMS_OUTPUT.put_line (v_ID ||:||v_MBINMSGS_CLOB_MSG);MIP_PARSE(v_MBINMSGS_CLOB_MSG);UPDATE MBINMSGS_TEMP SET MBINMSGS_DATE_PROCESSED SYSDATE WHERE IDv_ID;FETCH c_cursor INTO v_ID,v_MBINMSGS_CLOB_MSG;END LOOP;--关闭游标CLOSE c_cursor;COMMIT;EXCEPTIONWHEN OTHERSTHENDBMS_OUTPUT.PUT_LINE (Exception happened,data was rollback);ROLLBACK;END;END;
/CREATE OR REPLACE PROCEDURE MIP.MIP_PARSE (xmlStr IN CLOB)
ISSTYP 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);BEGINSTYP : 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 THENFFID_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,如果不存在就SAVESELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_D where FFID FFID_D;IF COUNTS 0 THENIF A_TOBT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET A_TOBT A_TOBT_D WHERE FFID FFID_D;END IF;IF A_WEATHER_D ! THENUPDATE TB_CMS_FLGTINFO_D SET A_WEATHER A_WEATHER_D WHERE FFID FFID_D;END IF;IF ABNS_D ! THENUPDATE TB_CMS_FLGTINFO_D SET ABNS ABNS_D WHERE FFID FFID_D;END IF;IF ACFT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET ACFT ACFT_D WHERE FFID FFID_D;END IF;IF ASAT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET ASAT ASAT_D WHERE FFID FFID_D;END IF;IF BCTM_D ! THENUPDATE TB_CMS_FLGTINFO_D SET BCTM BCTM_D WHERE FFID FFID_D;END IF;IF BOTM_D ! THENUPDATE TB_CMS_FLGTINFO_D SET BOTM BOTM_D WHERE FFID FFID_D;END IF;IF BETM_D ! THENUPDATE TB_CMS_FLGTINFO_D SET BETM BETM_D WHERE FFID FFID_D;END IF;IF C_TOBT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET C_TOBT C_TOBT_D WHERE FFID FFID_D;END IF;IF COBT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET COBT COBT_D WHERE FFID FFID_D;END IF;IF CTOT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET CTOT CTOT_D WHERE FFID FFID_D;END IF;IF DINT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DINT DINT_D WHERE FFID FFID_D;END IF;IF DLAB_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DLAB DLAB_D WHERE FFID FFID_D;END IF;IF DNAP_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DNAP DNAP_D WHERE FFID FFID_D;END IF;IF DOUT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DOUT DOUT_D WHERE FFID FFID_D;END IF;IF EDDI_D ! THENUPDATE TB_CMS_FLGTINFO_D SET EDDI EDDI_D WHERE FFID FFID_D;END IF;IF EOBT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET EOBT EOBT_D WHERE FFID FFID_D;END IF;IF EPGT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET EPGT EPGT_D WHERE FFID FFID_D;END IF;IF EPOT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET EPOT EPOT_D WHERE FFID FFID_D;END IF;IF FATD_D ! THENUPDATE TB_CMS_FLGTINFO_D SET FATD FATD_D WHERE FFID FFID_D;END IF;IF FSTD_D ! THENUPDATE TB_CMS_FLGTINFO_D SET FSTD FSTD_D WHERE FFID FFID_D;END IF;IF LMDT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET LMDT LMDT_D WHERE FFID FFID_D;END IF;IF LMUR_D ! THENUPDATE TB_CMS_FLGTINFO_D SET LMUR LMUR_D WHERE FFID FFID_D;END IF;IF OFTM_D ! THENUPDATE TB_CMS_FLGTINFO_D SET OFTM OFTM_D WHERE FFID FFID_D;END IF;IF RENO_D ! THENUPDATE TB_CMS_FLGTINFO_D SET RENO RENO_D WHERE FFID FFID_D;END IF;IF RWAY_D ! THENUPDATE TB_CMS_FLGTINFO_D SET RWAY RWAY_D WHERE FFID FFID_D;END IF;IF STDI_D ! THENUPDATE TB_CMS_FLGTINFO_D SET STDI STDI_D WHERE FFID FFID_D;END IF;IF STND_D ! THENUPDATE TB_CMS_FLGTINFO_D SET STND STND_D WHERE FFID FFID_D;END IF;IF TSAT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET TSAT TSAT_D WHERE FFID FFID_D;END IF; IF DPRT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DPRT DPRT_D WHERE FFID FFID_D;END IF; IF PARK_D ! THENUPDATE TB_CMS_FLGTINFO_D SET PARK PARK_D WHERE FFID FFID_D;END IF; IF TERMINAL_D ! THENUPDATE TB_CMS_FLGTINFO_D SET TERMINAL TERMINAL_D WHERE FFID FFID_D;END IF; IF GROUNDDISTRIBUTION_D ! THENUPDATE TB_CMS_FLGTINFO_D SET GROUNDDISTRIBUTION GROUNDDISTRIBUTION_D WHERE FFID FFID_D;END IF; ELSEINSERT 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;ELSEFFID_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,如果不存在就SAVESELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_A where FFID FFID_A;IF COUNTS 0 THENIF ABNS_A ! THENUPDATE TB_CMS_FLGTINFO_A SET ABNS ABNS_A WHERE FFID FFID_A;END IF;IF ACFT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET ACFT ACFT_A WHERE FFID FFID_A;END IF;IF ALAP_A ! THENUPDATE TB_CMS_FLGTINFO_A SET ALAP ALAP_A WHERE FFID FFID_A;END IF;IF BSTM_A ! THENUPDATE TB_CMS_FLGTINFO_A SET BSTM BSTM_A WHERE FFID FFID_A;END IF;IF CHDT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET CHDT CHDT_A WHERE FFID FFID_A;END IF;IF RENO_A ! THENUPDATE TB_CMS_FLGTINFO_A SET RENO RENO_A WHERE FFID FFID_A;END IF;IF EIBT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET EIBT EIBT_A WHERE FFID FFID_A;END IF;IF FATA_A ! THENUPDATE TB_CMS_FLGTINFO_A SET FATA FATA_A WHERE FFID FFID_A;END IF;IF FETA_A ! THENUPDATE TB_CMS_FLGTINFO_A SET FETA FETA_A WHERE FFID FFID_A;END IF;IF FSTA_A ! THENUPDATE TB_CMS_FLGTINFO_A SET FSTA FSTA_A WHERE FFID FFID_A;END IF;IF LMDT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET LMDT LMDT_A WHERE FFID FFID_A;END IF;IF LMUR_A ! THENUPDATE TB_CMS_FLGTINFO_A SET LMUR LMUR_A WHERE FFID FFID_A;END IF;IF PSTM_A ! THENUPDATE TB_CMS_FLGTINFO_A SET PSTM PSTM_A WHERE FFID FFID_A;END IF;IF RWAY_A ! THENUPDATE TB_CMS_FLGTINFO_A SET RWAY RWAY_A WHERE FFID FFID_A;END IF;IF SPOT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET SPOT SPOT_A WHERE FFID FFID_A;END IF;IF STND_A ! THENUPDATE TB_CMS_FLGTINFO_A SET STND STND_A WHERE FFID FFID_A;END IF;IF APRT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET APRT APRT_A WHERE FFID FFID_A;END IF; IF PARK_A ! THENUPDATE TB_CMS_FLGTINFO_A SET PARK PARK_A WHERE FFID FFID_A;END IF; IF TERMINAL_A ! THENUPDATE TB_CMS_FLGTINFO_A SET TERMINAL TERMINAL_A WHERE FFID FFID_A;END IF; IF GROUNDDISTRIBUTION_A ! THENUPDATE TB_CMS_FLGTINFO_A SET GROUNDDISTRIBUTION GROUNDDISTRIBUTION_A WHERE FFID FFID_A;END IF; ELSEINSERT 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;ELSEIF INSTR(FFID,-D-) 0 THENFFID_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,如果不存在就SAVESELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_D where FFID FFID_D;IF COUNTS 0 THENIF A_TOBT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET A_TOBT A_TOBT_D WHERE FFID FFID_D;END IF;IF A_WEATHER_D ! THENUPDATE TB_CMS_FLGTINFO_D SET A_WEATHER A_WEATHER_D WHERE FFID FFID_D;END IF;IF ABNS_D ! THENUPDATE TB_CMS_FLGTINFO_D SET ABNS ABNS_D WHERE FFID FFID_D;END IF;IF ACFT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET ACFT ACFT_D WHERE FFID FFID_D;END IF;IF ASAT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET ASAT ASAT_D WHERE FFID FFID_D;END IF;IF BCTM_D ! THENUPDATE TB_CMS_FLGTINFO_D SET BCTM BCTM_D WHERE FFID FFID_D;END IF;IF BOTM_D ! THENUPDATE TB_CMS_FLGTINFO_D SET BOTM BOTM_D WHERE FFID FFID_D;END IF;IF BETM_D ! THENUPDATE TB_CMS_FLGTINFO_D SET BETM BETM_D WHERE FFID FFID_D;END IF;IF C_TOBT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET C_TOBT C_TOBT_D WHERE FFID FFID_D;END IF;IF COBT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET COBT COBT_D WHERE FFID FFID_D;END IF;IF CTOT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET CTOT CTOT_D WHERE FFID FFID_D;END IF;IF DINT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DINT DINT_D WHERE FFID FFID_D;END IF;IF DLAB_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DLAB DLAB_D WHERE FFID FFID_D;END IF;IF DNAP_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DNAP DNAP_D WHERE FFID FFID_D;END IF;IF DOUT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DOUT DOUT_D WHERE FFID FFID_D;END IF;IF EDDI_D ! THENUPDATE TB_CMS_FLGTINFO_D SET EDDI EDDI_D WHERE FFID FFID_D;END IF;IF EOBT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET EOBT EOBT_D WHERE FFID FFID_D;END IF;IF EPGT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET EPGT EPGT_D WHERE FFID FFID_D;END IF;IF EPOT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET EPOT EPOT_D WHERE FFID FFID_D;END IF;IF FATD_D ! THENUPDATE TB_CMS_FLGTINFO_D SET FATD FATD_D WHERE FFID FFID_D;END IF;IF FSTD_D ! THENUPDATE TB_CMS_FLGTINFO_D SET FSTD FSTD_D WHERE FFID FFID_D;END IF;IF LMDT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET LMDT LMDT_D WHERE FFID FFID_D;END IF;IF LMUR_D ! THENUPDATE TB_CMS_FLGTINFO_D SET LMUR LMUR_D WHERE FFID FFID_D;END IF;IF OFTM_D ! THENUPDATE TB_CMS_FLGTINFO_D SET OFTM OFTM_D WHERE FFID FFID_D;END IF;IF RENO_D ! THENUPDATE TB_CMS_FLGTINFO_D SET RENO RENO_D WHERE FFID FFID_D;END IF;IF RWAY_D ! THENUPDATE TB_CMS_FLGTINFO_D SET RWAY RWAY_D WHERE FFID FFID_D;END IF;IF STDI_D ! THENUPDATE TB_CMS_FLGTINFO_D SET STDI STDI_D WHERE FFID FFID_D;END IF;IF SDEC ! THENUPDATE TB_CMS_FLGTINFO_D SET SDEC SDEC WHERE FFID FFID_D;END IF;IF TSAT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET TSAT TSAT_D WHERE FFID FFID_D;END IF; IF DPRT_D ! THENUPDATE TB_CMS_FLGTINFO_D SET DPRT DPRT_D WHERE FFID FFID_D;END IF; IF PARK_D ! THENUPDATE TB_CMS_FLGTINFO_D SET PARK PARK_D WHERE FFID FFID_D;END IF; IF TERMINAL_D ! THENUPDATE TB_CMS_FLGTINFO_D SET TERMINAL TERMINAL_D WHERE FFID FFID_D;END IF; IF GROUNDDISTRIBUTION_D ! THENUPDATE TB_CMS_FLGTINFO_D SET GROUNDDISTRIBUTION GROUNDDISTRIBUTION_D WHERE FFID FFID_D;END IF; ELSEINSERT 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;ELSEFFID_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,如果不存在就SAVESELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_A where FFID FFID_A;IF COUNTS 0 THENIF ABNS_A ! THENUPDATE TB_CMS_FLGTINFO_A SET ABNS ABNS_A WHERE FFID FFID_A;END IF;IF ACFT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET ACFT ACFT_A WHERE FFID FFID_A;END IF;IF ALAP_A ! THENUPDATE TB_CMS_FLGTINFO_A SET ALAP ALAP_A WHERE FFID FFID_A;END IF;IF BSTM_A ! THENUPDATE TB_CMS_FLGTINFO_A SET BSTM BSTM_A WHERE FFID FFID_A;END IF;IF CHDT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET CHDT CHDT_A WHERE FFID FFID_A;END IF;IF RENO_A ! THENUPDATE TB_CMS_FLGTINFO_A SET RENO RENO_A WHERE FFID FFID_A;END IF;IF EIBT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET EIBT EIBT_A WHERE FFID FFID_A;END IF;IF FATA_A ! THENUPDATE TB_CMS_FLGTINFO_A SET FATA FATA_A WHERE FFID FFID_A;END IF;IF FETA_A ! THENUPDATE TB_CMS_FLGTINFO_A SET FETA FETA_A WHERE FFID FFID_A;END IF;IF FSTA_A ! THENUPDATE TB_CMS_FLGTINFO_A SET FSTA FSTA_A WHERE FFID FFID_A;END IF;IF LMDT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET LMDT LMDT_A WHERE FFID FFID_A;END IF;IF LMUR_A ! THENUPDATE TB_CMS_FLGTINFO_A SET LMUR LMUR_A WHERE FFID FFID_A;END IF;IF PSTM_A ! THENUPDATE TB_CMS_FLGTINFO_A SET PSTM PSTM_A WHERE FFID FFID_A;END IF;IF RWAY_A ! THENUPDATE TB_CMS_FLGTINFO_A SET RWAY RWAY_A WHERE FFID FFID_A;END IF;IF SPOT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET SPOT SPOT_A WHERE FFID FFID_A;END IF;IF STND_A ! THENUPDATE TB_CMS_FLGTINFO_A SET STND STND_A WHERE FFID FFID_A;END IF;IF APRT_A ! THENUPDATE TB_CMS_FLGTINFO_A SET APRT APRT_A WHERE FFID FFID_A;END IF; IF PARK_A ! THENUPDATE TB_CMS_FLGTINFO_A SET PARK PARK_A WHERE FFID FFID_A;END IF; IF TERMINAL_A ! THENUPDATE TB_CMS_FLGTINFO_A SET TERMINAL TERMINAL_A WHERE FFID FFID_A;END IF; IF GROUNDDISTRIBUTION_A ! THENUPDATE TB_CMS_FLGTINFO_A SET GROUNDDISTRIBUTION GROUNDDISTRIBUTION_A WHERE FFID FFID_A;END IF; ELSEINSERT 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;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE (SQLERRM);END MIP_PARSE;
/三job var job_num number;
begindbms_job.submit(:job_num,JOB_PRO_TEMP;,sysdate,sysdate1/24/60);
end;
commit;var job_num number;
begindbms_job.submit(:job_num,LOOP_COPY_PRO_TEMP;,sysdate,sysdate1/24/60);
end;
commit; 以上就是所有在Oracle中用到的相关的PL-SQL的语句
