ANBOB™

有时会有这样的需求，自己写个procedure，想下班后自动执行，但又只想运行一次，然后只关心job的结果，如果用dbms_job 可能就要建个job,然后频率间隔很长时间，执行完后在把那个job drop,有没有一种job执行一次后自动删除呢？有

利用dbms_scheduler package，早就用它来替换dbms_job,下面我用它来实现上面的需求

语法

DBMS_SCHEDULER.CREATE_JOB (
   job_name             IN VARCHAR2,
   job_type             IN VARCHAR2,
   job_action           IN VARCHAR2,
   number_of_arguments  IN PLS_INTEGER              DEFAULT 0,
   start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   repeat_interval      IN VARCHAR2                 DEFAULT NULL,
   end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
   enabled              IN BOOLEAN                  DEFAULT FALSE,
   auto_drop            IN BOOLEAN                  DEFAULT TRUE,
   comments             IN VARCHAR2                 DEFAULT NULL);

这个函数有几种重载，
【job_name】
This attribute specifies the name of the job and uniquely identifies the job. The name has to be unique in the SQL namespace. For example, a job cannot have the same name as a table in a schema. If the job being created will reside in another schema, it must be qualified with the schema name.

【job_type】
•’PLSQL_BLOCK’
This specifies that the job is an anonymous PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0.

•’STORED_PROCEDURE’
This specifies that the job is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported.

•’EXECUTABLE’
This specifies that the job is a job external to the database. External jobs are anything that can be executed from the operating system’s command line. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.

•’CHAIN’
This specifies that the job is a chain. Arguments are not supported for a chain, so number_of_arguments must be 0.
【job_action】
This attribute specifies the action of the job.
For a stored procedure, the action is the name of the stored procedure. You have to specify the schema if the procedure resides in another schema than the job.
…

【repeat_interval】
This attribute specifies how often the job should repeat. You can specify the repeat interval by using calendaring or PL/SQL expressions.
The expression specified is evaluated to determine the next time the job should run. If repeat_interval is not specified, the job will run only once at the specified start date

【end_date】
This attribute specifies the date after which the job will expire and will no longer be executed. When end_date is reached, the job is disabled. The STATE of the job will be set to COMPLETED, and the enabled flag will be set to FALSE.

If no value for end_date is specified, the job will repeat forever unless max_runs or max_failures is set, in which case the job stops when either value is reached.
The value for end_date must be after the value for start_date. If it is not, an error is generated when the job is enabled.

【enabled】
This attribute specifies whether the job is created enabled or not. The possible settings are TRUE or FALSE. By default, this attribute is set to FALSE and, therefore, the job is created as disabled. A disabled job means that the metadata about the job has been captured and the job exists as a database object but the Scheduler will ignore it and the job coordinator will not pick the job for processing. In order for the job coordinator to process the job, the job has to be enabled. You can enable a job by setting this argument to TRUE or by using the ENABLE procedure.

【auto_drop】
This flag, if TRUE, causes a job to be automatically dropped after it has completed or has been disabled. A job is considered completed if:
•Its end date (or its schedule’s end date) has passed
•It has run max_runs number of times. max_runs must be set with SET_ATTRIBUTE.
•It is not a repeating job and has run once
A job is disabled when it has failed max_failures times. max_failures is also set with SET_ATTRIBUTE.
If this flag is set to FALSE, the jobs are not dropped and their metadata is kept until the job is explicitly dropped with the DROP_JOB procedure.
By default, jobs are created with auto_drop set to TRUE.

上面官方文档说的已经很细了，再强调一遍，默认创建的如果未指定enabled=true是disable的,repeat_interval 参数如果未指定是只运行一次的，auto_drop 自动删除是未指定执行频率并且已执行一次的。

我建了三个procedure
qa_bj_02,qa_bj_03,qa_bj_04

 BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  'qa_03',
    job_type           =>  'STORED_PROCEDURE',
    job_action         =>  ' qa_bj_03 ',
    enable              =>true,
    start_date         =>  trunc(sysdate)+20/24;
    );
 END;
 
 BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  'qa_04',
    job_type           =>  'STORED_PROCEDURE',
    job_action         =>  ' qa_bj_04 ',
     enable              =>true,
    start_date         =>  trunc(sysdate+1)+5/24;
    );
 END;

 BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  'qa_02',
    job_type           =>  'STORED_PROCEDURE',
    job_action         =>  ' qa_bj_02 ',
    enable              =>true,
    start_date         =>  trunc(sysdate+1)+20/24;
    );
 END;
 

创建三个job，10号创建的， 下面是11号的查询的执行情况

SQL> l                                                                                                                                                                          
  1* select JOB_NAME,LOG_DATE,STATUS,ERROR#,CPU_USED,ACTUAL_START_DATE from dba_scheduler_job_run_details where job_name like 'QA%'
SQL> /                                                                                                                                                                          

JOB_NAME   LOG_DATE                       STATUS              ERROR# CPU_USED             ACTUAL_START_DATE
---------- ------------------------------ --------------- ---------- -------------------- ------------------------------
QA_03      10-JAN-12 08.08.43.005480 PM + SUCCEEDED                0 +000 00:04:49.20     10-JAN-12 08.00.00.096616 PM +
           08:00                                                                          08:00

QA_04      11-JAN-12 05.35.14.281065 AM + SUCCEEDED                0 +000 00:31:11.61     11-JAN-12 05.00.00.093203 AM +
           08:00                                                                          08:00

SQL> l                                                                                                                                                                          
  1* SELECT JOB_NAME,JOB_TYPE ,job_action,START_DATE,REPEAT_INTERVAL,state,enabled FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'QA%'
SQL> /                                                                                                                                                                          

JOB_NAME   JOB_TYPE         JOB_ACTION           START_DATE                             REPEAT_INTERVAL                STATE           ENABL
---------- ---------------- -------------------- -------------------------------------- ------------------------------ --------------- -----
QA_02      STORED_PROCEDURE icme.qa_bj_02        11-JAN-12 08.00.00.000000 PM +08:00                                   SCHEDULED       TRUE

从dba_scheduler_job_run_details视图已可以看到qa_03,qa_04已执行成功错误编号无，且从DBA_SCHEDULER_JOBS可以看到已执行的job已经自动删除。

打赏

微信扫一扫，打赏作者吧～