按天删除数据
declare
HOWMANY_10MINS NUMBER;
SPECIFY_TIME DATE := TO_DATE('2020/09/15 23:59:59','yyyy/MM/dd hh24:mi:ss');--截止日期
BEGIN_TIME DATE;
END_TIME DATE;
begin
SELECT (SPECIFY_TIME - TRUNC(MIN(T.sampletime), 'DD')) HOWMANY_10MINS
INTO HOWMANY_10MINS
FROM datastore T;--获取间隔天数
FOR X IN 0 .. HOWMANY_10MINS LOOP
BEGIN_TIME := SPECIFY_TIME - (X + 1) ;--增量1
END_TIME := SPECIFY_TIME - X;
-- delete 操作,请批改删除表信息,及 where 过滤条件。
DELETE FROM datastore T
WHERE T.sampletime BETWEEN BEGIN_TIME AND END_TIME;--时间条件
COMMIT;
END LOOP;
end;
按小时删除,如果本身显示只有天的话,可能有问题
declare
HOWMANY_10MINS NUMBER;
SPECIFY_TIME DATE := TO_DATE('2020/09/19 23:59:59','yyyy/MM/dd hh24:mi:ss');
BEGIN_TIME DATE;
END_TIME DATE;
begin
SELECT (SPECIFY_TIME - TRUNC(MIN(T.sampletime), 'DD')) * 24 HOWMANY_10MINS
INTO HOWMANY_10MINS
FROM datastore T;
FOR X IN 0 .. HOWMANY_10MINS LOOP
BEGIN_TIME := SPECIFY_TIME - (X + 1)/24 ;
END_TIME := SPECIFY_TIME - X/24;
-- delete 操作,请批改删除表信息,及 where 过滤条件。
DELETE FROM datastore T
WHERE T.sampletime BETWEEN BEGIN_TIME AND END_TIME;
COMMIT;
END LOOP;
end;