存储过程 和 job

--------------------------------------job-------------------------
DECLARE
CURSOR GET_JOB IS SELECT * FROM USER_JOBS where what like '存储过程名%';
BEGIN
FOR REC IN GET_JOB LOOP
DBMS_JOB.REMOVE(REC.JOB);
END LOOP;
COMMIT;
END;
/

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '存储过程名(sysdate);'
,next_date => to_date('30-06-2021 05:00:00', 'dd-mm-yyyy hh24:mi:ss')
,interval => 'TRUNC(sysdate+1)+5/(24)'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

----------------------------------存储过程--------------------

CREATE OR REPLACE PROCEDURE 存储过程名(vdate IN DATE) is
v_day VARCHAR2(8);
verrinfo VARCHAR2(200);
begin
--写入日志begin
INSERT INTO JOB_LOG_TAB
VALUES (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'), '存储过程名', 'begin');
COMMIT;
--获取前一天时间
v_day := TO_CHAR(vdate- 1,'yyyymmdd');
--删除前一天的数据,进行全量更新
delete 存储过程名 where substr(CREATE_TIME,1,8) = v_day and state = '0' ;
commit;
--写入日志end
INSERT INTO JOB_LOG_TAB
VALUES (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'), '存储过程名', 'end');
COMMIT;
exception
WHEN OTHERS THEN
BEGIN
--抛出异常时回滚数据
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE));
--写入日志异常情况
verrinfo := SUBSTR(SQLERRM, 1, 200);
INSERT INTO JOB_LOG_TAB
VALUES
(TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
'存储过程名',
verrinfo);
COMMIT;
END;
end 存储过程名;

/