存储过程·定时任务定时调用的某个存储过程

create or replace procedure P_CREATE_CONCLUSION is

  Cursor conclusionCur is SELECT * FROM T_CONCLUSION WHERE STATUS IN (0);

  concRow T_CONCLUSION%rowtype;


  currentDate date;--当前时间

  currentDayOfMonth number(2);--当月几号

  currentWeek number(2);--周几

  currentQuarter number(2); --季度第几天

  currentQuarterDate date; --季度时间


  Cursor planCur is SELECT * FROM T_INSPECTION_PLAN WHERE PLANTYPE = 1;--周期性巡检

  planRow T_INSPECTION_PLAN%rowtype;


  locationType number;


  deviceType number(2);--设备类型

  

  type deviceCurType is ref cursor return t_device%rowtype;

  deviceCur deviceCurType;

  deviceRow deviceCur%rowtype;


  insertFinallyDate date; --插入数据的最终时间


  stationRow T_STATION_POLICE%Rowtype;


BEGIN

    SELECT TO_DATE(TO_CHAR(SYSDATE,'yyyymmdd'),'yyyymmdd') INTO currentDate FROM DUAL; -- 当前时间

    SELECT TO_NUMBER(TO_CHAR(SYSDATE,'dd')) INTO currentDayOfMonth FROM DUAL; -- 判断是否为月的第一天

    SELECT TO_NUMBER(TO_CHAR(SYSDATE-1,'d')) INTO currentWeek FROM DUAL; -- 判断是否为周的第一天 减1 是由于默认周天为1 周六为7

    SELECT TO_NUMBER(TRUNC(SYSDATE)-TRUNC(SYSDATE,'q')-1) INTO currentQuarter FROM dual;--判断现在是季度的第几天

   

    --修改任务状态 是否超时

    BEGIN

       FOR concRow in conclusionCur

         LOOP

       --当前时间大于本次巡检结束时间 说明该任务已超时 更新任务状态

           IF currentDate > concRow.ENDDATE THEN

              UPDATE T_CONCLUSION SET STATUS = 2 WHERE ID = concRow.id;

           END IF;

         END LOOP;  

    END;

    COMMIT;


    --遍历计划表添加任务

    BEGIN

      --判断当前时间是周的第一天或月的第一天或季度的第一天

      IF currentWeek = 1 OR c