oracle job

 

 Oracle 10g的schedule job操作概要版 

 

參考:http://www.blogjava.net/javabloger/archive/2008/03/14/186206.html

-- job 权限
grant create job to somebody;


-- job 创建
begin
 dbms_scheduler.create_job (
     job_name => 'AGENT_LIQUIDATION_JOB',
     job_type => 'STORED_PROCEDURE',
     job_action => 'AGENT_LIQUIDATION.LIQUIDATION', --存储过程名
     start_date => sysdate,
     repeat_interval => 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',  -- 按月，间隔为1个(月),每月1号,凌晨1点
     comments => '执行代理商清分程序'
 );
end;
/

-- job 执行时间测试
DECLARE
    start_date date;
    return_date_after date;
    next_run_date date;
BEGIN
    start_date := sysdate;--to_timestamp_tz('10-OCT-2004 10:00:00','DD-MM-YYYY HH24:MI:SS');
    return_date_after := start_date;
FOR i IN 1..10 LOOP
     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',start_date, return_date_after, next_run_date);
     DBMS_OUTPUT.PUT_LINE('next_run_date: ' || to_char(next_run_date,'yyyy-mm-dd HH24:MI:SS'));
     return_date_after := next_run_date;
    END LOOP;
END;
/

-- job 查询
select owner, job_name, state from dba_scheduler_jobs;
select job_name, state from user_scheduler_jobs;

-- job 启用
begin
    dbms_scheduler.enable('BACKUP_JOB');
end;
/

-- job 运行
begin
    dbms_scheduler.run_job('COLA_JOB',TRUE); -- true代表同步执行
end;
/

-- job 停止(不太好用)
begin
    dbms_scheduler.stop_job(job_name => 'COLA_JOB',force => TRUE);
end;
/

-- job 删除(对停job来说好用)
begin
    dbms_scheduler.drop_job(job_name => 'COLA_JOB',force => TRUE);)
end;
/

 

 

 

本节内容由罗勇根据参考手册Oracle 9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2) 翻译并加入自己的理解，实践。oracle 10g有另外一种推荐的方法。

 

为了更好的演示，创建一个表及存储过程

 

create table job_log_test

(job number,

what varchar2(500),

begin_time date,

next_date date,

myinterval date,

last_date date,

isbroken CHAR(1)

)

 

 

 

CREATE OR REPLACE PROCEDURE test_job(job        NUMBER,

                                     what       VARCHAR2,

                                     next_date  DATE,

                                     myinterval DATE,

                                     isbroken   BOOLEAN) AS

  str_isbroken  CHAR(1) := 'Y';

  ld_begin_time DATE := SYSDATE;

BEGIN

  IF NOT isbroken THEN

    str_isbroken := 'N';

  END IF;

  INSERT INTO job_log_test

    (job, what, begin_time, next_date, myinterval, last_date, isbroken)

  VALUES

    (job, what, ld_begin_time, next_date, myinterval, SYSDATE, str_isbroken);

END;

 

45.1 新建一个作业

 

新产生的作业序号来自sys.jobseq。

 

语法如下：

 

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,

    instance IN BINARY_INTEGER DEFAULT any_instance,

    force IN BOOLEAN DEFAULT FALSE);

 

 

 

参数解释：

 

参数

 

说明

 

job

 

新提交成功的作业的序列号

 

what

 

要运行的PL/SQL

 

next_date

 

作业下次运行的时间，默认为当前日期

 

interval

 

一个日期函数，用来计算下次运行的时间，只能为NULL或者某个将来时间，默认值为NULL

 

no_parse

 

一个标志，默认为FALSE。当值为false时，oracle将会解析同该作业相关联的PL/SQL块，当值为true时，那么oracle将会在该作业第一次运行时解析同该作业相关联的PL/SQL块。例如，你想在你创建和作业相关的表之前提交作业的话，将该参数设置为true。

 

instance

 

当作业提交时，指定某个示例来运行该作业，默认是任何实例都可以运行该作业。

 

force

 

如果设置为true，那么任何正数值的instace参数都将认为是指定运行作业的实例。如果设置为false(默认值也是false)，那么你创建作业的这个实例要在运行中。其他值将会抛出异常。

 

使用说明：

 

参数instance和force主要用于作业队列相关性(job queue affinity)，作业队列相关性给与了用户指定某个实例运行提交的作业或者任何实例都可以运行作业的能力。

 

 

 

示例如下：该作业运行erp.sp_sync_data_from_erp;提交成功后立即运行该作业，然后每24个小时运行一次。

 

VARIABLE jobno number;

BEGIN

    DBMS_JOB.SUBMIT(:jobno,'erp.sp_sync_data_from_erp;',SYSDATE,'SYSDATE + 1');

commit;

END;

/

上面这个语法是书本上的，下面是我写的。

 

 

DECLARE

    jobno number;

BEGIN

    DBMS_JOB.SUBMIT(jobno,'erp.sp_sync_data_from_erp;',SYSDATE,'SYSDATE + 1');

    commit;

    DBMS_OUTPUT.PUT_LINE(jobno);

END;

/

 

45.2 删除一个作业

 

该语法不会停止正在运行的作业。

 

DBMS_JOB.REMOVE (job IN BINARY_INTEGER );

 

参数说明，job是要删除的作业的序列号，该序列号也可以从表sys.dba_jobs的job列查询到，当然啦，提交作业的时候最好记下作业的序列号。

 

示例：

 

execute dbms_job.remove(25);

 

45.3 更改一个作业

 

语法如下：

 

DBMS_JOB.CHANGE (

    job IN BINARY_INTEGER,

    what IN VARCHAR2,

    next_date IN DATE,

    interval IN VARCHAR2,

    instance IN BINARY_INTEGER DEFAULT NULL,

    force IN BOOLEAN DEFAULT FALSE);

 

参数含义同新建作业语法，恕不重复啦。

 

使用说明：如果参数what, next_date, 或者 interval输入NULL，那么就是不更改原作业的值。

 

示例如下：

 

EXEC DBMS_JOB.change(25,NULL,NULL,'SYSDATE + 2/24');

COMMIT;

 

 

 

EXECUTE DBMS_JOB.change(25,'erp.sp_sync_data_from_erp;',SYSDATE,'SYSDATE + 2/24');

COMMIT;

 

 

 

45.4 更改一个作业执行的PL/SQL

 

语法：

 

DBMS_JOB.WHAT(job IN BINARY_INTEGER,what IN VARCHAR2);

 

罗勇补充：

 

值得说明的是，job, next_date, broken 是可以作为里面过程的参数直接传递的。但是broken是BOOLEAN数据类型哦。这对于建立一个作业log表或许有用吧。

 

比如

 

EXEC DBMS_JOB.what(25,'test_job(job,''test_job'',next_date,SYSDATE + 1/24/10, broken);');

COMMIT;

 

45.5 更改作业下次执行的时间

 

语法:

 

DBMS_JOB.NEXT_DATE (

job IN BINARY_INTEGER,

next_date IN DATE);

 

 

 

罗勇补充的示例：通过实践认为这会更改原来计算好的下次运行时间，而改为刚才更改的时间，当然了，运行周期等其他与作业有关的不会变化。

 

EXEC DBMS_JOB.NEXT_DATE (25,TO_DATE('2010-04-12 11:26:00','YYYY-MM-DD HH24:MI:SS'));

COMMIT;

 

 

 

45.6 更改运行作业的实例

 

语法如下：

 

DBMS_JOB.INSTANCE (

job IN BINARY_INTEGER,

instance IN BINARY_INTEGER,

force IN BOOLEAN DEFAULT FALSE);

 

估计一般用不着，不翻译啦

 

45.7 更改运行作业频率

 

语法如下：

 

 DBMS_JOB.INTERVAL (

job IN BINARY_INTEGER,

interval IN VARCHAR2);

 

 

 

使用注意：更改成功后，下次执行时间将会是select interval from dual这个时间。如果传入’NULL’那么这个作业将会只运行一次。

 

下面这个例子将会说明这一切：

 

SET SERVEROUTPUT ON;

BEGIN

DBMS_JOB.interval(25,'SYSDATE + 1/24/12');

 

--将作业置为每周一运行一次可以这样

 

--DBMS_JOB.interval (25,'next_day(sysdate,''星期一'')');

COMMIT;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE + 1/24/12,'YYYY-MM-DD HH24:MI:SS'));

END;

 

45.8 将作业置为可用或者不可用状态

 

语法如下：

 

DBMS_JOB.BROKEN (

    job IN BINARY_INTEGER,

    broken IN BOOLEAN,

    next_date IN DATE DEFAULT SYSDATE);

 

示例如下：

 

exec DBMS_JOB.BROKEN(25,true);

commit;

 

当设置为true执行成功后，改作业将不会运行，下次执行时间将会变成4000-01-01。特别注意的是不要在作业执行期间运行改过程，因为作业运行成功后将会重设作业的状态。