创建JOB
CREATE OR REPLACE PROCEDURE METERIAL.RUNDAILYBYJOBASVAR_COUNT INTEGER;VAR_DATE VARCHAR2(20); CURSOR cur_location is select * from mate_location_info; BEGIN VAR_DATE:=TO_CHAR(SYSDATE,'YYYY-MM-DD'); SELECT COUNT(*) INTO VAR_COUNT FROM MATE_LOCATION_INFO;IF VAR_COUNT>0 THEN FOR TEMPCUR IN cur_location LOOP SELECT COUNT(*) INTO VAR_COUNT FROM MATE_LOCATION_INFO_DAILY WHERE DAYTIME=VAR_DATE AND FEEDNUM=TEMPCUR.feednum AND ITEMNAME=TEMPCUR.itemname AND FOLDADDRESS=TEMPCUR.foldaddress AND TOLOCATION=TEMPCUR.tolocation; IF VAR_COUNT<=0 THEN INSERT INTO MATE_LOCATION_INFO_DAILY SELECT MATE_LOCATION_INFO_DAILY_S.nextval,VAR_DATE, TEMPCUR.feednum,TEMPCUR.itemname,TEMPCUR.foldaddress,'',TEMPCUR.tolocation, TEMPCUR.movecount,TEMPCUR.unit,TEMPCUR.itemmoment,TEMPCUR.depict, TEMPCUR.feedtype,TEMPCUR.remark FROM DUAL; ELSE UPDATE MATE_LOCATION_INFO_DAILY SET MOVECOUNT=TEMPCUR.movecount WHERE DAYTIME=VAR_DATE AND FEEDNUM=TEMPCUR.feednum AND ITEMNAME=TEMPCUR.itemname AND FOLDADDRESS=TEMPCUR.foldaddress AND TOLOCATION=TEMPCUR.tolocation; END IF; END LOOP; commit;END IF;exception
?
