Oracle 作业计划程序在每天一个特定时间执行，对该作业任务进行两次查询

Question

我需要 Oracle 作业调度程序每天下午 1：00 的帮助。我想在单个作业调度程序上执行两个查询。像这样的查询

第一个是简单的删除查询。

delete from NEWS_NO_OF_VIEWS where TO_CHAR(MONTH_YEAR,'MM-YYYY') = 
TO_CHAR(SYSDATE,'MM-YYYY')

第二个是插入查询

INSERT INTO NEWS_NO_OF_VIEWS(NEWS_TYPE, NO_OF_VIEWS, MONTH_YEAR)    
((SELECT 'Latest News' AS NEWS_TYPE,NVL(SUM(NO_OF_VIEWED),0) NO_OF_VIEWS,MONTH_YEAR FROM(
SELECT NO_OF_VIEWED,TO_CHAR(CREATED_DATE,'MM-YYYY') AS MONTH_YEAR FROM NEWS_LATEST_UR
UNION ALL
SELECT NO_OF_VIEWED,TO_CHAR(CREATED_DATE,'MM-YYYY')  AS MONTH_YEAR FROM NEWS_LATEST_EN
) E WHERE MONTH_YEAR = TO_CHAR(SYSDATE,'MM-YYYY') GROUP BY MONTH_YEAR) 

Answer 1

您需要做的就是创建一个过程"give_some_proc_name"并将"DELETE"和"INSERT"语句放入该过程并按如下所示提交，以便将来如果要添加或修改语句，则

可以轻松。

create or replace procedure give_some_proc_name
as
begin
delete from NEWS_NO_OF_VIEWS where TO_CHAR(MONTH_YEAR,'MM-YYYY') = TO_CHAR(SYSDATE,'MM-YYYY');
INSERT INTO NEWS_NO_OF_VIEWS(NEWS_TYPE, NO_OF_VIEWS, MONTH_YEAR)    
((SELECT 'Latest News' AS NEWS_TYPE,NVL(SUM(NO_OF_VIEWED),0) NO_OF_VIEWS,MONTH_YEAR FROM(
SELECT NO_OF_VIEWED,TO_CHAR(CREATED_DATE,'MM-YYYY') AS MONTH_YEAR FROM NEWS_LATEST_UR
UNION ALL
SELECT NO_OF_VIEWED,TO_CHAR(CREATED_DATE,'MM-YYYY')  AS MONTH_YEAR FROM NEWS_LATEST_EN
) E WHERE MONTH_YEAR = TO_CHAR(SYSDATE,'MM-YYYY') GROUP BY MONTH_YEAR);
commit;
end;

然后按如下方式创建作业，该作业从今天下午 01：00 开始，每天下午 01：00 运行。 在作业内部，您的job_type将为"STORE_PROCEDURE"，作业操作将为"NAME_OF_PROCEDURE"，即前缀架构名称带有"."分隔符的"give_some_proc_name"，如下所示。

BEGIN
DBMS_SCHEDULER.create_job (
job_name        => 'give_some_name_to_job',
job_type        => 'STORED_PROCEDURE',
job_action      => 'schema_name.give_some_proc_name',
start_date      => to_date(to_char(sysdate,'DD-MON-YYYY')||' 13:00:00','DD-MON-YYYY HH24:MI:SS'),
repeat_interval => 'freq=daily; byhour=13; byminute=0; bysecond=0;',
enabled         => TRUE);
END;