oracle job定时任务

imjtofvqop

　　DECLARE JobNum NUMBER(5) := 0;
        JobID  NUMBER(5) := 0;
BEGIN
 -- Sp_test：存储过程名；SUBSTRB(WHAT,1,7)：7是存储过程名称字符个数
  SELECT COUNT(*) INTO JobNum FROM DBA_JOBS WHERE UPPER(SUBSTRB(WHAT,1,7)) = UPPER('Sp_test');
  IF JobNum > 0 THEN
    SELECT NVL(JOB,0) INTO JobID FROM DBA_JOBS WHERE UPPER(SUBSTRB(WHAT,1,7)) = UPPER('Sp_test');
    IF JobID > 0 THEN
      DBMS_JOB.REMOVE(JobID);
      COMMIT;
    END IF;
  END IF;
END;
/
　　DECLARE
  v_Job NUMBER;
BEGIN
  -- TRUNC(SYSDATE+1) + 1/288：明天的00:05分
  DBMS_JOB.SUBMIT(v_Job, 'Sp_test;', TRUNC(SYSDATE+1) + 1/288, 'SYSDATE + 1');
  COMMIT;
END;
/