Oracle DB - Fully automatically update the data of a table at the specified time（全自动定时更新表中的数据）

Version

• Oracle Database 11g

Goals

1. Delete expired data from the table seckill and insert new data into it at the specified time;
2. Fully automatic without any need of conscious interference.

Steps

1. sequence seckillseq，table product，table seckill

create sequence seckillseq
increment by 1
start with 0
minvalue 0
nomaxvalue
nocycle
cache 16;
--
create table product
(
id number(30) primary key,
name varchar2(100) unique,
descr varchar2(4000),
normalPrice number(10,2),
memberPrice number(10,2),
pdate date
);
--
create table seckill
(
id number(30),
productid number(30),
starttime date,
endtime date
);

2. job : execute seckill_procedure at the specified time

declare
  job number;
begin
  dbms_job.submit(
    JOB => job, -- generate JOB_ID automatically
    WHAT => 'seckill_procedure;', -- procedure or sql sentence
    NEXT_DATE => sysdate+10/(24*60*60), -- the first execution time: 10 seconds later
    INTERVAL => 'fn_get_myjob_interval(sysdate)' -- create by function
  );
end;

3. function : Calculate the next execution time of the job

create or replace function fn_get_myjob_interval(now date)
  return date
is
  nextdate date;
  
  v_n_hour number;
begin
  v_n_hour := to_number(to_char(now, 'hh24'));
  if (mod(v_n_hour, 2) = 0) then
    nextdate := trunc(sysdate, 'hh') + 2 / 24 + 10 / (24 * 60); -- 8:xx:xx -> 10:10:00
  else
    nextdate := trunc(sysdate, 'hh') + 1 / 24 + 10 / (24 * 60); -- 9:xx:xx -> 10:10:00
  end if;
  
  return(nextdate);
end;

4. procedure : First delete expired data from table seckill, then insert new data

create or replace procedure seckill_procedure
is
  cursor c is
    select temp.id from 
    	(select id from product where id not in 
    		(select productid from seckill) 
    	order by dbms_random.value) temp 
    where rownum <= 16; -- 16 records
  countnum number;
begin
  -- When we restart the Oracle services, all of data may be deleted from table seckill
  delete from seckill where endtime < sysdate;
  commit; -- Attention the commit
  
  select count(*) into countnum from seckill;
  
  for v_tem in c loop
  	-- trigger `seckill_trig`
    insert into seckill (productid) values (v_tem.id);
  end loop;
  commit; -- Attention the commit
  
  -- if 0, we should insert another 16 records into table seckill to make sure 
  -- there are always 32 records in table seckill
  if (countnum = 0) then
    seckill_procedure;
  end if;
  commit; -- Attention the commit
end;

5. trigger : Before insert new data into table seckill, we need to set the id , starttime and endtime of each record

create or replace trigger seckill_trig
  before insert on seckill
  for each row
declare
  id number;
  seckilltime date;
  seckillhour number;
  countnum number;
  PRAGMA AUTONOMOUS_TRANSACTION; -- Attention: when the trigger is being executed, the table seckill will be locked.
begin
  select count(*) into countnum from seckill;
	-- check if there are records in table seckill.
	-- if no, "seckilltime" will be assigned with current time.
	-- if yes, "seckilltime" will be assigned with the time which is 2 hours later.
  if (countnum = 0) then
    seckilltime := sysdate;
  else
    seckilltime := sysdate + 2 / 24;
  end if;
  
  :new.id := seckillseq.nextval;
  
  seckillhour := to_number(to_char(seckilltime, 'hh24'));
	-- check whether "seckillhour " is odd or even
  if (mod(seckillhour, 2) = 0) then
    :new.starttime := trunc(seckilltime, 'hh');
    :new.endtime := trunc(seckilltime + 2 / 24, 'hh');
  else
    :new.starttime := trunc(seckilltime - 1 / 24, 'hh');
    :new.endtime := trunc(seckilltime + 1 / 24, 'hh');
  end if;
end;

Example

Others

• 下面是中文过程简述，毕竟学了这么年英语，不能荒废掉。
• job：定时执行存储过程 seckill_procedure；
• function：计算 job 下一次的执行时间，比如现在是 08:40 或 09:11，那么下一次的执行时间是 10:10，也就是偶数小时的第十分钟；
• procedure：先删除 seckill 表中的过期记录，再插入16条新的记录（随机从 product 表中抽选且 seckill 中没有的）；另外保证 seckill 表中始终有32条记录，即当前时间段和下一时间段的记录（一个时间段16条记录）；特殊情况如服务器宕机一天后，存储过程需执行两次；
• trigger：向 seckill 表中插入记录时，设置 id，starttime 和 endtime；如果插入前表中没有记录（空），则 starttime 和 endtime 为当前时间段；如果有，则为下一时间段。