openGauss/MogDB实现定时任务调度

openGauss/MogDB支持数据库创建定时任务，定时任务管理，属性修改等操作，下面进行简单的测试。

创建测试表

create table public.t_test_o_job(
  id  number(10),
  flag  varchar2(1),
  name   varchar2(10)
);

创建存储过程为表插入数据

create procedure public.p_test_o_job 
IS
begin
   insert into public.t_test_o_job values(10,'N','测试');
end;
/

创建job，调用存储过程

DECLARE
   jobid INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'p_test_o_job;', SYSDATE, 'SYSDATE + 6/(24 * 60 * 60)');
   raise info 'jobid=%',jobid;
END;
/

暂停job

select dbms_job.broken(31611,true,sysdate);
查看表数据的变量和job的运行情况和状态
select job_id,job_status,failure_msg,this_run_date from pg_job;
select * from public.t_test_o_job;

启动job

select dbms_job.broken(31611,false,sysdate);
查看表数据的变量和job的运行情况和状态
select job_id,job_status,failure_msg,this_run_date from pg_job;
select * from public.t_test_o_job;

修改job的属性

select dbms_job.next_date(31611,to_date('2024-3-30 16:18:39','yyyy-mm-dd hh24:mi:ss'));
select * from public.t_test_o_job;
，查看表数据的变量和job的运行情况和状态
select job_id,job_status,failure_msg,this_run_date from pg_job;

删除job、存储过程、表

select dbms_job.remove(31611);
删除存储过程
drop procedure if exists public.p_test_o_job;
删除表 
drop procedure if exists public.p_test_o_job;