1建立實現任務的過程
在schema manager或SQL PLUS里建立如下過程
CREATE OR REPLACE PROCEDURE "CUSTOMER"."T_JOBTEST" as
begin
update emp set active =0
where active =1
and date_published < sysdate - active_days;
end ;
2 向任務隊列中加入任務
在SQL PLUS中執行下列script
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno, 't_jobtest();', SYSDATE, 'SYSDATE + 1');
commit;
end;
該任務立即執行（SYSDATE），并且每隔一天執行一次（'SYSDATE + 1'）。
3 查詢此任務是否加入任務隊列
在SQL PLUS中執行下列script
SELECT job, next_date, next_sec, failures, broken
FROM user_jobs;
------------------
DBMS_JOB 包介紹
調度任務隊列里的任務要使用DBMS_JOB包中的過程。使用任務隊列不需要非凡的數據庫特權。任何可以使用這些過程的用戶都可以使用任務隊列。
Table 8-2 DBMS_JOB包中的過程
Procedure Description Described
SUBMIT
Submits a job to the job queue. 向任務隊列提交一個任務
REMOVE
Removes a specified job from the job queue. 從任務隊列中刪除指定的任務
CHANGE
Alters a specified job. You can alter the job description, the time at which the job will be run, or the interval between executions of the job. 改變任務
WHAT
Alters the job description for a specified job. 改變指定任務的任務內容
NEXT_DATE
Alters the next execution time for a specified job. 改變指定任務的下一次執行時間
INTERVAL
Alters the interval between executions for a specified job. 改變指定任務的執行時間間隔。
BROKEN
Disables job execution. If a job is marked as broken, Oracle does not attempt to execute it. 禁止指定任務的執行
RUN
Forces a specified job to run. 強制執行指定的任務
Submitting a Job to the Job Queue 向任務隊列提交一個任務
To submit a new job to the job queue, use the SUBMIT procedure in the DBMS_JOB package:
DBMS_JOB.SUBMIT( job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT SYSDATE,
interval IN VARCHAR2 DEFAULT 'null',
no_parse IN BOOLEAN DEFAULT FALSE)
The SUBMIT procedure returns the number of the job you submitted. describes the procedure's parameters.
Table 8-3 DBMS_JOB.SUBMIT 的參數
Parameter Description
job
This is the identifier assigned to the job you created. You must use the job number whenever you want to alter or remove the job. For more information about job numbers, see "Job Numbers".
what
This is the PL/SQL code you want to have executed. 這里是你想執行的PL/SQL代碼
For more information about defining a job, see "Job Definitions".
next_date
This is the next date when the job will be run. The default value is SYSDATE.
interval
This is the date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL.
For more information on how to specify an execution interval, see "Job Execution Interval".
no_parse
This is a flag. The default value is FALSE.
If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.
As an example, let's submit a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The job is run every 24 hours:
VARIABLE jobno number;
begin
2> DBMS_JOB.SUBMIT(:jobno,
3> 'dbms_ddl.analyze_object(''TABLE'',
4> ''DQUON'', ''ACCOUNTS'',
5> ''ESTIMATE'', NULL, 50);'
6> SYSDATE, 'SYSDATE + 1');
7> commit;
8> end;
9> /
Statement processed.
print jobno
JOBNO
----------
14144
Job Definition 任務定義任務定義就是SUBMIT過程中WHAT參數中指定的PL/SQL代碼。
通常任務定義（內容）是一個過程的一個調用。這個過程能有任意數量的參數。
Note: In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition. 在任務定義中，用2個單引號包圍字符串。任務定義的末尾總是帶一個分號。
Jobs and Import/EXPort Jobs can be exported and imported.
Thus, if you define a job in one database, you can transfer it to another
database. When exporting and importing jobs, the job's number, environment,
and definition remain unchanged.
任務是可以被卸出（exported ）卸入（imported）的。
Job Execution Interval 任務的執行間隔The INTERVAL date function is evaluated immediately before a job is executed. If the job completes sUCcessfully, the date calculated from INTERVAL becomes the new NEXT_DATE. If the INTERVAL date function evaluates to NULL and the job completes successfully, the job is
deleted from the queue. If a job should be executed periodically at a set interval, use a date expression similar to 'SYSDATE + 7' in the INTERVAL
parameter. For example, if you set the execution interval to 'SYSDATE + 7' on Monday, but for some reason (such as a network failure) the job is not
executed until Thursday, 'SYSDATE + 7' then executes every Thursday, not Monday.
If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), the INTERVAL and NEXT_DATE parameters should specify a date expression similar to 'NEXT_DAY(TRUNC(SYSDATE), "MONDAY")'.
Table 8-5 lists some common date expressions used for job execution intervals.
Table 8-5 Common Job Execution Intervals Date Expression Evaluation 'SYSDATE + 7'
exactly seven days from the last execution 最后一次執行的7天之后執行 'SYSDATE + 1/48'
every half hour 每半個小時執行一次 'NEXT_DAY(TRUNC(SYSDATE),
''MONDAY'') + 15/24'
every Monday at 3PM 每個禮拜一的下午3點執行 'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3),
''THURSDAY'')'
first Thursday of each quarter 每個季度的第一個星期四 --------------------------------------------------------------------------------Note: When specifying NEXT_DATE or INTERVAL, remember that date literals and strings must be enclosed in single quotation marks. Also, the value of INTERVAL must be enclosed in single quotation marks. --------------------------------------------------------------------------------
Removing a Job from the Job Queue 刪除任務隊列中的任務
To remove a job from the job queue, use the REMOVE procedure in the DBMS_JOB package:
DBMS_JOB.REMOVE(job IN BINARY_INTEGER)
The following statement removes job number 14144 from the job queue:
DBMS_JOB.REMOVE(14144);
Syntax for WHAT
You can alter the definition of a job by calling the DBMS_JOB.WHAT procedure. Table 8-3 describes the procedure's parameters.
DBMS_JOB.WHAT( job IN BINARY_INTEGER,
what IN VARCHAR2)
--------------------------------------------------------------------------------
Note:
When you execute procedure WHAT, Oracle records your current environment. This becomes
