oracle 定时任务

---

建立储存定时任务数据表

比如现在我们有个books的表：

现在写个简单的定时任务：每天凌晨1点运行一次，每次查books表里一楼的书本记录
建立一个存储定时任务数据的表：

create table DS_books
(
  ISBN        VARCHAR2(255) not null,
  BOOKNAME    VARCHAR2(255) not null,
  TYPE        VARCHAR2(255) not null,
  AUTHOR      VARCHAR2(255) not null,
  PUBLISH     VARCHAR2(255) not null,
  PUBLISHTIME DATE not null,
  PRICE       NUMBER(20) not null,
  FLOOR       VARCHAR2(255),
  BOOKSHELF   VARCHAR2(255),
  INTRODUCE   CLOB,
  SJSCRQ      DATE
)

---

写查询语句

接下来先写sql语句：

SELECT ISBN,BOOKNAME,TYPE,AUTHOR,PUBLISH,PUBLISHTIME,PRICE,FLOOR,BOOKSHELF,INTRODUCE,
SYSDATE AS SJSCRQ FROM BOOKS WHERE REGEXP_LIKE(FLOOR,'一楼')

---

写存储过程

create or replace procedure P_DS_BOOK
as

begin 
  
DELETE FROM DS_BOOKS WHERE to_char(SJSCRQ,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
COMMIT;

INSERT INTO DS_BOOKS
(ISBN,BOOKNAME,type,AUTHOR,PUBLISH,PUBLISHTIME,PRICE,FLOOR,BOOKSHELF,INTRODUCE,SJSCRQ)

SELECT ISBN,BOOKNAME,type,AUTHOR,PUBLISH,PUBLISHTIME,PRICE,FLOOR,BOOKSHELF,INTRODUCE,
SYSDATE AS SJSCRQ FROM BOOKS WHERE REGEXP_LIKE(FLOOR,'一楼');

COMMIT;

END;

---

创建定时任务job

DECLARE
JOB NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
        JOB=>JOB,
        WHAT=>'ARRAY.P_DS_BOOK;',
        NEXT_DATE=>SYSDATE,
        INTERVAL=>'TRUNC(SYSDATE+1)+1/24'   ---每天凌晨1点运行
  );
COMMIT;
END;

---

查看定时任务

SELECT * FROM USER_JOBS;

---

三天后查看表内容

---

删除定时任务

begin
  DBMS_JOB.remove(3);
  COMMIT;
END;