定期运行 Oracle 查询的最佳方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12212147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to run Oracle queries periodically
提问by jabclab
I need to know what the best practice is regarding running a query periodically on Oracle (I'm using 11g).
我需要知道在 Oracle 上定期运行查询的最佳实践是什么（我使用的是11g）。
In my particular use case I have a DUE_DATEspecified in table x. What I want to do is to run a query at 00:01 every day to calculate the status (OK, Warn, Critical or Overdue) of some records. The status of a particular record is calculated from today's date (where 'today' is the day the query is being run) relative to x.DUE_DATEand some user-specified values for what signifies 'warn' and 'critical' (contained within table y).
在我的特定用例中，我DUE_DATE在 table 中指定了一个x。我想要做的是每天00:01运行一个查询来计算一些记录的状态（OK、Warn、Critical或Overdue）。特定记录的状态是从今天的日期（其中 'today' 是查询运行的日期）开始计算的，相对于x.DUE_DATE某些用户指定的表示“警告”和“关键”的值（包含在 table 中y）。
- OK -->
today < x.DUE_DATE - y.WARN - Warn -->
today >= x.DUE_DATE - y.WARN and today < x.DUE_DATE - y.CRITICAL - Critical -->
today >= x.DUE_DATE - y.CRITICAL and today <= x.DUE_DATE - Overdue -->
today > x.DUE_DATE
- 好的 -->
today < x.DUE_DATE - y.WARN - 警告 -->
today >= x.DUE_DATE - y.WARN and today < x.DUE_DATE - y.CRITICAL - 关键-->
today >= x.DUE_DATE - y.CRITICAL and today <= x.DUE_DATE - 逾期 -->
today > x.DUE_DATE
What is the best way of running this query periodically? I have found the following options but am not sure which is best for my use case:
定期运行此查询的最佳方法是什么？我找到了以下选项，但不确定哪个最适合我的用例：
I know that I could just calculate the status dynamically upon every user request but as stauses only change once a day I thought it would be more efficient to do the calculation and cache the subsequent result once a day too.
我知道我可以根据每个用户请求动态计算状态，但由于状态每天只更改一次，我认为进行计算并每天缓存一次后续结果会更有效。
Many thanks in advance.
提前谢谢了。
回答by GWu
- For running jobs (and queries) DBMS_SCHEDULERis the tool to choose. So if you want to update the status in a tablebased on the results of your query, use DBMS_SCHEDULER.
- 对于运行作业（和查询），DBMS_SCHEDULER是可供选择的工具。因此，如果您想根据查询结果更新表中的状态，请使用DBMS_SCHEDULER。
For example you could schedule a job doing the following update:
例如，您可以安排一个作业执行以下更新：
update x
set status = (CASE
WHEN sysdate < x.DUE_DATE - y.WARN THEN
'Ok'
WHEN sysdate >= x.DUE_DATE - y.WARN and today < x.DUE_DATE - y.CRITICAL THEN
'Warn'
WHEN sysdate >= x.DUE_DATE - y.CRITICAL and sysdate <= x.DUE_DATE THEN
'Critical'
WHEN sysdate > x.DUE_DATE THEN
'Overdue'
END)
;
To create the job scheduled daily at 00:00:
要创建计划在每天 00:00 的作业：
BEGIN
dbms_scheduler.create_job(job_name => 'Status Updater',
job_type => 'PLSQL_BLOCK',
job_action => '
BEGIN
update x
set status = (CASE
WHEN sysdate < x.DUE_DATE - y.WARN THEN
''Ok''
WHEN sysdate >= x.DUE_DATE - y.WARN and today < x.DUE_DATE - y.CRITICAL THEN
''Warn''
WHEN sysdate >= x.DUE_DATE - y.CRITICAL and sysdate <= x.DUE_DATE THEN
''Critical''
WHEN sysdate > x.DUE_DATE THEN
''Overdue''
END)
;
END;',
start_date => systimestamp,
repeat_interval => 'FREQ=DAILY;INTERVAL=1;BYHOUR=0;BYMINUTE=0;',
enabled => TRUE);
END;
/
- If you need to prepare a report, either schedule the report in the reporting tool or use a Materialized Viewto store the result set.
- 如果您需要准备报告，请在报告工具中安排报告或使用实体化视图来存储结果集。
回答by ik_zelf
the answer to your question: what is the best method to scheduler in Oracle is absolutely: dbms_scheduler.
您的问题的答案：Oracle 中调度程序的最佳方法绝对是：dbms_scheduler。
In you specific use case: why do this at all? Now you plan to do this every day for your data, also when nobody is interested. In the end, your status is a proces variable, computed from sysdate and an other data item.
在您的特定用例中：为什么要这样做？现在您计划每天为您的数据执行此操作，即使没有人感兴趣。最后，您的状态是一个过程变量，由 sysdate 和其他数据项计算得出。
