DBMS_JOB

最新推荐文章于 2022-09-13 10:23:57 发布

原创最新推荐文章于 2022-09-13 10:23:57 发布 · 661 阅读

0 ·

CC 4.0 BY-SA版权

DBMS_SCHEDULER 专栏收录该内容

10 篇文章

订阅专栏

DBMS_SCHEDULER 出来很多年了，还有人在用DBMS_JOB

先来看下dbms_job的实现方法

DROP TABLE t_job_log PURGE;
DROP TABLE t_job_test PURGE;
CREATE TABLE t_job_log(proc VARCHAR2(30),created DATE,CODE NUMBER, message VARCHAR2(500));
CREATE TABLE t_job_test(created DATE,des VARCHAR2(50));

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;
/
SET serveroutput ON
DECLARE
  v_job BINARY_INTEGER;
BEGIN
  dbms_job.submit(job       => v_job,
                  what      => 'begin p_job_test; end;',
                  next_date => SYSDATE,
                  INTERVAL  => 'sysdate + 5/24/60/60');
  dbms_output.put_line(v_job);
END;
/
commit;

执行结果：

SQL> select * from t_job_test where rownum <=5;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-15 11:26:48  dbms_job
2014-08-15 11:27:23  dbms_job
2014-08-15 11:27:38  dbms_job
2014-08-15 11:27:53  dbms_job
2014-08-15 11:27:58  dbms_job
5 rows selected

相关监控信息比较少

SQL> select JOB,WHAT,LAST_DATE,THIS_DATE from user_jobs;
       JOB WHAT                           LAST_DATE   THIS_DATE
---------- ------------------------------ ----------- -----------
         5 begin p_job_test; end;         2014-08-15  
1 row selected

删掉job

BEGIN
  dbms_job.broken(job => 5,broken => TRUE);
  dbms_job.remove(job => 5);
  COMMIT;
END;
/

SQL> select JOB,WHAT,LAST_DATE,THIS_DATE from user_jobs;
       JOB WHAT                           LAST_DATE   THIS_DATE
---------- ------------------------------ ----------- -----------

SQL>