Commit in a loop

declare
type array is table of src%rowtype index by binary_integer;
l_data array ;

begin

loop

  begin
      select * bulk collect into l_data
        from src
       where processed = 'N'
         and rownum < 10
         for update of processed;
      exit when sql%rowcount = 0;
  exception
       when no_data_found then exit;
  end;

for i in 1 .. l_data.count
    loop
        update tgt set x = l_data(i).x , y = l_data(i).y where rowid = l_data(i).tgt_row_id  ;
        update src set processed = 'Y' where tgt_row_id = l_data(i).tgt_row_id;
    end loop;

commit; 



end loop;

end;
/

I editied the code to use bulk co开发者_如何学编程llect but it simply hangs in 11.2.

SQL> select * from src;

         X Y          TGT_ROW_ID         P
---------- ---------- ------------------ -
         1 ABC        AAAWZDAAEAAAA1EAAA Y
         1 DEF        AAAWZDAAEAAAA1EAAA Y
         2 ABC        AAAWZDAAEAAAA1EAAC Y

SQL> select * from tgt;

         X Y
---------- ----------
         1 ABC
         1
         2 ABC

There are really several questions here.

1) The error on line 10. That is because you need to use BULK COLLECT to select into an array:

select x,y,tgt_row_id 
bulk collect into l_data
from src

However, since l_data is defined using src%rowtype the above only works if the table has just the 3 columns x,y,tgt_row_id. When using%rowtype it is actually better to use select * as it is sure to match the record structure.

2) Your loop never exits. You need to add something like this:

loop
  select * bulk collect into l_data
    from src
   where processed = 'N'
     and rownum < 10
     for update of processed;

   exit when sql%rowcount = 0;
   ...
end loop;