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;
/