I have the following scenario:
A table containing more than a million records. I need to create a loop that deletes 10'000 rows per execution (loop) based on the column number of the rows. So if in the end of the execution the column has 500 rows for example, then i need to delete all these rows.
So I imagined that this might work:
declare COUNTER int; while (select count(*) from Table) >= 10000) loop DELETE FROM (SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000); endloop;
It's something like this that I need, but I don't know how to increase the
Counter variable in the loop. And I suppose that I need to put an
IF THEN ... ELSE... statement inside the
WHILE to check the
SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000, and in the
ELSE, delete the rest of the rows that I have: i.e the last 500 rows.
I'm trying to make this work:
DECLARE COUNTER INTEGER :=0; BEGIN DBMS_OUTPUT.PUT_LINE('START'); loop -- keep looping COUNTER := COUNTER + 1; --do the delete 1000in each iteration Delete TEST where rownum < 1000; -- exit the loop when there where no more 1000 reccods to delete. exit when SQL%rowcount <= 1000; commit; end loop; DBMS_OUTPUT.PUT_LINE(COUNTER); commit; END;
I put 10'000 rows in my TEST table. In the end the counter should be 9 and at the end of the
loop the table should contain 1000 rows, but in my case the counter is 11 and the table is empty.
Why doesn't it work?