创建JOB

最新推荐文章于 2022-06-07 15:27:42 发布

qq1988627

最新推荐文章于 2022-06-07 15:27:42 发布

阅读量95

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/qq1988627/article/details/84084775

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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