Tuesday, October 6, 2009

Update table and commit every n rows


Declare

i integer;
x NUMBER ;
v_min NUMBER ;
v_max NUMBER ;

begin

select max(EMPID) into x from EMPLOYEE ;

v_min :=0 ;
v_max :=25000 ;

loop
update EMPLOYEE set CIO_NAME = 'JOHN' where EMPID >= v_min and EMPID < v_max ;
commit ;
v_min := v_min+25000 ;
v_max := v_min+25000 ;
if v_max > (x+30000) then
commit ;
dbms_output.put_line('All rows updated successfully ....') ;
exit ;
end if ;
end loop ;

Exception When others then
dbms_output.put_line('Error Occured ...') ;

end ;
/

No comments: