循环删除大表数据(delete 指定日期、指定条件前数据)

循环删除大表数据(delete 指定日期、指定条件前数据)

DECLARE
  HOWMANY_10MINS NUMBER;
  -- 指定时间节点,删除该时间之前过滤数据。
  -- 请一定注意 LOOP 中 DELETE 条件过滤,否则将全部删除!!!
  SPECIFY_TIME   DATE := TO_DATE('2021/07/30 23:59:59','yyyy/MM/dd hh24:mi:ss');
  BEGIN_TIME     DATE;
  END_TIME       DATE;
BEGIN
  SELECT (SPECIFY_TIME - TRUNC(MIN(T.COLLECTTIME), 'DD')) * 24 * 6 HOWMANY_10MINS
    INTO HOWMANY_10MINS
    FROM IMES_SAC1_MD.DEVICE_COLLECT_DATA T;
  FOR X IN 0 .. HOWMANY_10MINS LOOP
    BEGIN_TIME := SPECIFY_TIME - (X + 1) / 24 / 6;
    END_TIME   := SPECIFY_TIME - X / 24 / 6;
    -- delete 操作,请修改删除表信息,及 where 过滤条件。
    DELETE FROM IMES_SAC1_MD.DEVICE_COLLECT_DATA T
     WHERE T.COLLECTTIME BETWEEN BEGIN_TIME AND END_TIME;
    /*    INSERT INTO TT VALUES (X, BEGIN_TIME, END_TIME, SYSDATE);*/
    COMMIT;
  END LOOP;
END;
/

你可能感兴趣的