SP for performing commit after deleting n records
96866Jun 12 2006 — edited Jun 12 2006I want to create a stored procedure in which i have to perform a commit after n number of records are deleted.So i wrote a procedure like this
create procedure test_del (n in number) is
x number:=0;
begin
Loop
for i in 1 .. n
loop
delete emp1 where hiredate < sysdate;
end loop;
commit;
x:=x+1;
exit when sql%notfound;
end loop;
dbms_output.put_line(x);
end;
I gave n a value 2 and executed the sp. EMP1 table had 14 rows and all rows are deleted and committed but x is giving value 1. Is there something wrong with the SP?or the inner loop in the sp is executed 7 times? I am expecting a value 7 for x!!