前面讲了DBMS_JOB的用法,我们来用DBMS_SCHEDULER 对比下
TRUNCATE TABLE t_job_test;
/
TRUNCATE TABLE t_job_log;
/
BEGIN
dbms_scheduler.purge_log(job_name => 'scheduler_job_test');
END;
/
CREATE OR REPLACE PROCEDURE p_job_test AS
v_code NUMBER;
v_err VARCHAR2(500);
BEGIN
INSERT INTO t_job_test VALUES (SYSDATE, 'dbms_job');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_err := substrb(SQLERRM, 1, 500);
INSERT INTO t_job_log VALUES ('p_scheduler_job_test', SYSDATE, v_code, v_err);
NULL;
END;
/
BEGIN
dbms_scheduler.create_job(job_name => 'scheduler_job_test',
job_type => 'STORED_PROCEDURE',
job_action => 'p_job_test',
start_date => SYSDATE,
repeat_interval => 'FREQ=SECONDLY; INTERVAL=5',
enabled => TRUE);
END;
/
执行结果:
select * from t_job_test where rownum <=5;
CREATED DES
-------------------- --------------------------------------------------
2014-08-15 13:51:07 dbms_job
2014-08-15 13:51:17 dbms_job
2014-08-15 13:50:57 dbms_job
2014-08-15 13:51:02 dbms_job
2014-08-15 13:51:12 dbms_job
5 rows selected
这时查job日志就方便(可以用名称查询,而不是难记的job号)和详细的多
SQL> SELECT log_id,
2 to_char(log_date, 'yyyy-mm-dd hh24:mi:ss.ff') AS log_date,
3 status
4 FROM user_scheduler_job_run_details
5 WHERE job_name = 'SCHEDULER_JOB_TEST'
6 AND rownum <= 5;
LOG_ID LOG_DATE STATUS
---------- ----------------------------- ------------------------------
1789 2014-08-15 13:50:57.968000 SUCCEEDED
1790 2014-08-15 13:51:02.021000 SUCCEEDED
1791 2014-08-15 13:51:07.015000 SUCCEEDED
1792 2014-08-15 13:51:12.023000 SUCCEEDED
1793 2014-08-15 13:51:17.015000 SUCCEEDED
5 rows selected
删除方法
BEGIN
dbms_scheduler.drop_job(job_name => 'scheduler_job_test');
END;
/
这个案例中create_job里有两点需要说明:
一、job_type 这儿用的是procedure
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72301
| This attribute specifies the type of job that you are creating. If it is not specified, an error is generated. The supported values are:
|
二、repeat_interval
repeat_interval 比较复杂,这个案例中用的其中的一个方式:以秒为单位,间隔5秒
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#BABFBCEF
frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency ) predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" user_defined_frequency = named_schedule interval_clause = "INTERVAL" "=" intervalnum intervalnum = 1 through 99
也可以设定执行的时间,比如间隔为分钟,第5,10,15...秒
TRUNCATE TABLE t_job_test;
/
TRUNCATE TABLE t_job_log;
/
CREATE OR REPLACE PROCEDURE p_job_test AS
v_code NUMBER;
v_err VARCHAR2(500);
BEGIN
INSERT INTO t_job_test VALUES (SYSDATE, 'dbms_job');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_err := substrb(SQLERRM, 1, 500);
INSERT INTO t_job_log VALUES ('p_job_test', SYSDATE, v_code, v_err);
NULL;
END;
/
BEGIN
dbms_scheduler.create_job(job_name => 'scheduler_job_test',
job_type => 'STORED_PROCEDURE',
job_action => 'p_job_test',
start_date => SYSDATE,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1; BYSECOND=0,5,10,15,20,25,30,35,40,45,50,55',
enabled => TRUE);
END;
/
此时执行结果为:
SQL> SELECT log_id,
2 to_char(log_date, 'yyyy-mm-dd hh24:mi:ss.ff') AS log_date,
3 status
4 FROM user_scheduler_job_run_details
5 WHERE job_name = 'SCHEDULER_JOB_TEST'
6 AND rownum <= 5;
LOG_ID LOG_DATE STATUS
---------- ----------------------------- ------------------------------
1840 2014-08-15 14:06:35.037000 SUCCEEDED
1841 2014-08-15 14:06:40.007000 SUCCEEDED
1842 2014-08-15 14:06:45.023000 SUCCEEDED
1843 2014-08-15 14:06:50.017000 SUCCEEDED
4 rows selected
SQL> select * from t_job_test where rownum <=5;
CREATED DES
-------------------- --------------------------------------------------
2014-08-15 14:07:50 dbms_job
2014-08-15 14:06:35 dbms_job
2014-08-15 14:06:40 dbms_job
2014-08-15 14:06:45 dbms_job
2014-08-15 14:06:50 dbms_job
5 rows selected
这种方式可以很好的控制执行时间。如果要每天12点执行,则参数可以改为:'FREQ=DAILY;BYHOUR=12',
详见: http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN12502