创建job样例

CREATE OR REPLACE PROCEDURE job_procedure AS
i_count INTEGER;
v_job VARCHAR2(128);
v_schedule VARCHAR2(128);
BEGIN
v_job := upper('job_name');
SELECT COUNT(*)
INTO i_count
FROM user_objects t
WHERE t.OBJECT_TYPE = 'JOB'
AND t.OBJECT_NAME = upper(v_job);
IF i_count > 0
THEN
DBMS_SCHEDULER.DROP_JOB(v_job);
END IF;
v_schedule := 'S_' || v_job;
SELECT COUNT(*)
INTO i_count
FROM user_objects t
WHERE t.OBJECT_TYPE = 'SCHEDULE'
AND t.OBJECT_NAME = upper(v_schedule);
IF i_count > 0
THEN
DBMS_SCHEDULER.DROP_SCHEDULE(v_schedule);
END IF;
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => v_schedule,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=2;',
comments => 'Run at 2 every 24 hours');
DBMS_SCHEDULER.CREATE_JOB(job_name => v_job,
job_type => 'STORED_PROCEDURE',
job_action => 'DELETEHISTORYDATA.P_DELETE_HISTROY_DATA',
schedule_name => v_schedule,
enabled => TRUE,
auto_drop => FALSE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
const.log_error_to_table(SQLCODE, SQLERRM, 'fail to create job ' || v_job);
END init_job_procedure;
/
call job_procedure();
drop procedure job_procedure;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值