Oracle SQL實(shí)現(xiàn)日、月、周循環(huán)
許多業(yè)務(wù)報(bào)表系統(tǒng)都是按日、月、周時(shí)間區(qū)間統(tǒng)計(jì)數(shù)據(jù)，通常都是通過job調(diào)用存儲(chǔ)過程實(shí)現(xiàn)。
涉及到歷史數(shù)據(jù)跑批時(shí)需要實(shí)現(xiàn)區(qū)間時(shí)間段按日、周、月循環(huán)。

-- 時(shí)間區(qū)間按日循環(huán) 
declare 
  -- Local variables here
  i integer;
  START_TIME DATE:=TO_DATE( '2020-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
  END_TIME DATE:=TO_DATE( '2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
  START_TIME_STR VARCHAR2(20);
  END_TIME_STR VARCHAR2(20);
begin
  -- Test statements here
   while START_TIME < END_TIME  loop
     START_TIME_STR :=to_char(START_TIME,'yyyy-mm-dd')||' 00:00:00';
     END_TIME_STR := to_char(START_TIME,'yyyy-mm-dd')||' 23:59:59';
     DBMS_OUTPUT.PUT_LINE(START_TIME_STR||'    '||END_TIME_STR);
     --DBMS_OUTPUT.PUT_LINE(to_date(START_TIME_STR,'yyyy-mm-dd hh24:mi:ss')||'    '||to_date(END_TIME_STR,'yyyy-mm-dd hh24:mi:ss'));
     START_TIME:= START_TIME + 1;
  end loop;
end;


-- 時(shí)間區(qū)間按月循環(huán) 
declare 
  -- Local variables here
  i integer;
  START_TIME DATE:=TO_DATE( '2019-08-01', 'yyyy-mm-dd');
  END_TIME DATE:=TO_DATE( '2020-10-01', 'yyyy-mm-dd');
  START_TIME_STR VARCHAR2(20);
  END_TIME_STR VARCHAR2(20);
begin
  -- Test statements here
   while START_TIME < END_TIME  loop
     START_TIME_STR :=to_char(trunc(add_months(last_day(START_TIME), -1) + 1), 'yyyy-mm-dd')||' 00:00:00';
     END_TIME_STR := to_char(last_day(START_TIME), 'yyyy-mm-dd')||' 23:59:59';
     DBMS_OUTPUT.PUT_LINE(START_TIME_STR||'    '||END_TIME_STR);
     --DBMS_OUTPUT.PUT_LINE(to_date(START_TIME_STR,'yyyy-mm-dd hh24:mi:ss')||'    '||to_date(END_TIME_STR,'yyyy-mm-dd hh24:mi:ss'));
     START_TIME:= add_months(START_TIME,1);
  end loop;
end;


-- 時(shí)間區(qū)間按周循環(huán) 
declare 
  -- Local variables here
  i integer;
  START_TIME DATE:=TO_DATE( '2019-08-01', 'yyyy-mm-dd');
  END_TIME DATE:=TO_DATE( '2020-10-01', 'yyyy-mm-dd');
  START_TIME_STR VARCHAR2(20);
  END_TIME_STR VARCHAR2(20);
begin
  -- Test statements here
   while START_TIME < END_TIME  loop
     START_TIME_STR :=to_char( trunc(START_TIME,'iw') , 'yyyy-mm-dd')||' 00:00:00';
     END_TIME_STR := to_char( trunc(START_TIME,'iw')+6, 'yyyy-mm-dd')||' 23:59:59';
     DBMS_OUTPUT.PUT_LINE(START_TIME_STR||'    '||END_TIME_STR);
     --DBMS_OUTPUT.PUT_LINE(to_date(START_TIME_STR,'yyyy-mm-dd hh24:mi:ss')||'    '||to_date(END_TIME_STR,'yyyy-mm-dd hh24:mi:ss'));
     START_TIME:= trunc(START_TIME,'iw')+7;
  end loop;
end;

D552281B-56B8-4f2d-B690-845B884A14FD.png