关于oracle:循环删除大表数据delete-指定日期指定条件前数据

54次阅读

共计 628 个字符，预计需要花费 2 分钟才能阅读完成。

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;
/

正文完