oracle job

定义oracle job

 

DECLARE

  V_JOB      NUMBER;
  V_PRO_NAME VARCHAR2(40) := 'PROCEDURE_NAME;';
BEGIN

  FOR JOB IN (SELECT JOB
                FROM USER_JOBS A
               WHERE UPPER(A.WHAT) = UPPER(V_PRO_NAME)) LOOP
 
    BEGIN
      DBMS_JOB.REMOVE(JOB.JOB);
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
         ROLLBACK;
    END;
  END LOOP;

  FOR JOBINFO IN (SELECT MAX(JOB) JOB
                    FROM USER_JOBS A) LOOP
 
    V_JOB := NVL(JOBINFO.JOB, 0);
 
  END LOOP;

  V_JOB := CASE WHEN V_JOB = 0 THEN V_JOB + 2 ELSE V_JOB + 1 END;

  BEGIN
    SYS.DBMS_JOB.ISUBMIT(V_JOB, V_PRO_NAME, TO_DATE('19-01-2011 02:00:00', 'dd-mm-yyyy hh24:mi:ss'), ' TRUNC(SYSDATE) +1+1/24');
   COMMIT; 
  END;

END;