创建JOB

CREATE OR REPLACE PROCEDURE METERIAL.RUNDAILYBYJOB
AS
VAR_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