Oracle ROWNUM with DELETE and Looping

Question

想知道是否有人可以为我确认以下删除语句以及下面列出的循环。我正在尝试做的是删除CREATED_DATE超过 90 天的记录，但一次只删除 1k 条记录。如果正确，我计划将其放入下面列出的循环中。我在使用 ROWNUM 时遇到了不同的结果，只想确认一下。

DELETE from
(select * from ESPADMIN.ESP_STATUS_MESSAGE where CREATED_DATE <SYSDATE-90)
WHERE ROWNUM <1001 ;
commit;

LOOP
DELETE from
(select * from ESPADMIN.ESP_STATUS_MESSAGE where CREATED_DATE <SYSDATE-90)
WHERE ROWNUM <1001 ;
commit;
END LOOP;

Answer 1

你可能想要这样的东西：

DECLARE 
  cnt INT;
BEGIN
  SELECT COUNT(*)    -- 0/1
  INTO cnt
  FROM dual
  WHERE EXISTS (SELECT 1 
               FROM ESPADMIN.ESP_STATUS_MESSAGE 
               where CREATED_DATE <SYSDATE-90);
  WHILE (cnt > 0) LOOP
    DELETE ESPADMIN.ESP_STATUS_MESSAGE 
    where CREATED_DATE <SYSDATE-90
      AND rownum < 1001;
    COMMIT;
    SELECT COUNT(*)
    INTO cnt
    FROM dual
    WHERE EXISTS (SELECT 1 
                  FROM ESPADMIN.ESP_STATUS_MESSAGE 
                  where CREATED_DATE <SYSDATE-90);
  END LOOP;
END;

编辑：

我会把你的例子重写为：

BEGIN
LOOP 
  DELETE from (select * 
               from ESPADMIN.ESP_STATUS_MESSAGE where CREATED_DATE <SYSDATE-90) 
  WHERE ROWNUM <1001 ;
  EXIT WHEN sql%ROWCOUNT = 0;
  commit;
END LOOP;
COMMIT;
END;