oracle job

1. 定义

Job是一个可以被调度以在特定时间或按一定频率自动执行的数据库对象。Job通常用于执行预定的后台任务，如数据清理、统计信息更新、备份操作、数据导入导出、报告生成等。这些任务可以是任何可以在数据库环境中执行的操作，最常见的形式是调用存储过程。DBMS_JOB包已经被DBMS_SCHEDULER包所取代。

Job的创建和管理主要通过Oracle的DBMS_JOB包来进行，该包提供了用于提交、修改、删除、启用或禁用Job的PL/SQL过程。Job可以被配置为只运行一次，也可以按照特定的时间间隔重复执行

2.查看job

select * from dba_jobs;
select * from dba_jobs_running;
select * from user_jobs;
select * from all_jobs;

3.job

1.队列job
DBMS_JOB.SUBMIT( 
   job       OUT    BINARY_INTEGER,
   what      IN     VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE, 
   interval  IN     VARCHAR2 DEFAULT 'NULL',
   no_parse  IN     BOOLEAN DEFAULT FALSE,
   instance  IN     BINARY_INTEGER DEFAULT ANY_INSTANCE,
   force     IN     BOOLEAN DEFAULT FALSE);

2.更改job
 DBMS_JOB.CHANGE(  JOB IN BINARY_INTEGER,
   what                  IN VARCHAR2 DEFAULT NULL,
   next_date             IN DATE DEFAULT NULL,
   interval              IN VARCHAR2 DEFAULT NULL,
   instance              IN BINARY_INTEGER DEFAULT NULL,
   force                 IN BOOLEAN DEFAULT FALSE );

3.运行job
DBMS_JOB.RUN( 
      job    IN BINARY_INTEGER,
      force  IN BOOLEAN DEFAULT FALSE);

4.停止job
DBMS_JOB.BROKEN ( 
   job       IN  BINARY_INTEGER,
   broken    IN  BOOLEAN,
   next_date IN  DATE DEFAULT SYSDATE);

5.移除job
DBMS_JOB.REMOVE ( 
   job       IN  BINARY_INTEGER );

4.案例

1.建表
create table TEST(a date);

2.自定义过程
create or replace procedure myjob as
begin
insert into TEST values(sysdate);
end;
/

3.创建JOB
variable job1 number;

begin
dbms_job.submit(:job1,'myjob;',sysdate,'sysdate+1/1440');　　--每天1440分钟，即一分钟运行test过程一次
end;
/

4.运行JOB
begin
dbms_job.run(:job1);
end;
/

5.验证
select to_char(a,'yyyy/mm/dd hh24:mi:ss') time from TEST;

TIME
-------------------
2024/06/16 13:43:53
2024/06/16 13:44:53

6.删除job
begin
dbms_job.remove(:job1);
end;
/ 

or

begin
  dbms_job.remove(23);
  commit;
end;
/

7.验证
select * from dba_jobs';
select * from dba_jobs_running;
select * from user_jobs;
select * from all_jobs;