用 DBMS_SCHEDULER 代替 DBMS_JOB

最新推荐文章于 2024-01-06 20:24:32 发布

jgmydsai

最新推荐文章于 2024-01-06 20:24:32 发布

阅读量1.2k

点赞数

分类专栏： DBMS_SCHEDULER

本文链接：https://blog.csdn.net/jgmydsai/article/details/38582631

版权

DBMS_SCHEDULER 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

前面讲了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

job_type

This attribute specifies the type of job that you are creating. If it is not specified, an error is generated. The supported values are:

'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 external to the database. External jobs are anything that can be executed from the command line of the operating system. Anydata arguments are not supported with a job or program type ofEXECUTABLE. 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.

二、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