Oracle 为每条记录增加一天

我随机生成3个表的行。对于tour_detail表，我如何找到MAX(tour_time) MMDDYYYY仅添加一天 (保持HH24:MI:SS相同) 并将这些值插入tour_detail表中。

create table locations as
select level as location_id,
       'Door ' || level as location_name,
       case round(dbms_random.value(1,3)) 
            when 1 then 'A' 
            when 2 then 'T' 
            when 3 then 'G' 
       end as location_type
from   dual
connect by level <= 25;

alter table locations 
  add ( 
    constraint location_id_pk
    primary key (location_id)
  );

create table tour_hdr(
   tour_id NUMBER(4),
   tour_name VARCHAR2(20),
   active_days VARCHAR2(7)
);

insert into tour_hdr (tour_id, tour_name, active_days)
with loop1(tour_id) as (
  select level 
  from   dual connect by level <= 10
), loop2(b) as (select level from dual connect by level <= 7)
  SELECT tour_id,
         'Tour ' || tour_id AS tour_name,
         replace(listagg(CASE round(dbms_random.value(1,2))
                           WHEN 1 THEN 'Y'
                           WHEN 2 THEN 'N'
                          END,',')               within group (order by b)
                 ,',') AS active_days
  FROM  loop1
  cross join loop2
  group by tour_id;

alter table tour_hdr
  add ( 
    constraint tour_hdr_id_pk
    primary key (tour_id)
  );

create table tour_detail 
(
      tour_id NUMBER(4),
      tour_time DATE,
      location_id NUMBER(4)
);
  
alter table tour_detail 
  add ( 
    constraint tour_detail_fk
    foreign key (tour_id)
    references tour_hdr (tour_id)
  );

declare
    v_loc number;      
    v_prev_loc number := 0;
    v_dt date := trunc(sysdate);
    v_dt_save date := trunc(sysdate);
begin

  for trs in ( select tour_id from tour_hdr )
  loop
     -- for each  tour generate 15 to 25 rows
     for i in 1..dbms_random.value(15, 25) loop
     
       -- If date >23:45:00 reset.
       if v_dt > (v_dt_save + (1/1440*1425))
       then 
          -- reset time 
          v_dt :=  v_dt_save;
       else
          -- increase last used date by random 6 to 12 minutes
          v_dt := v_dt + dbms_random.value(6,12)/(24*60);
       end if;

       -- get random location
       select location_id 
       into   v_loc 
       from (select location_id from locations where location_type = 'G' order by dbms_random.value) 
       where rownum = 1;

       -- ensure same location_id isn't consecutive 
       if v_prev_loc != v_loc
       then
         insert into tour_detail (tour_id, tour_time, location_id)
         values (trs.tour_id, v_dt, v_loc);
         v_prev_loc := v_loc;
       end if;

    end loop;
  end loop;
end;
/
复制

我不确定您要计算此最大值的确切方式/位置。

要找到列中的最高值，您只需要MAX函数即可。

并记住: Oracle数据库中的算术单位是日。所以添加一个日期，你会得到完全相同的时间第二天:

select max ( tour_time ) mx, 
       max ( tour_time ) + 1 mx_next_day
from   tour_detail;

MX                     MX_NEXT_DAY            
24-JUN-2020 23:45:59   25-JUN-2020 23:45:59 
复制