循环处理数据提交

DECLARE  
V_COUNTER NUMBER;  
begin
  V_COUNTER := 0;
  for i in 1 .. 1000022
  loop 
  insert into p1 values(i, 'a' || i);
  V_COUNTER := V_COUNTER + 1;
  IF (V_COUNTER >= 10000) THEN
    COMMIT;  
    V_COUNTER := 0;
  END IF;
end loop;
end;

这种情况下,余下的22条记录就不会被提交。

SQL> select max(id) from p1;

   MAX(ID)
----------
   1000000



---------------------------------------------
DECLARE
  V_COUNTER NUMBER;
begin
  V_COUNTER := 0;
  for i in 1 .. 1000022 loop
    insert into p1 values (i, 'a' || i);
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 10000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
    commit;
  end loop;

end;

SQL> select max(id) from p1;

   MAX(ID)
----------
   1000022