create or replace PROCEDURE move_column AS type num_table IS TABLE OF NUMBER INDEX BY pls_integer; round_trips number; recid_table num_table; avid_table num_table; scale NUMBER; starting_point NUMBER; ending_point NUMBER; BEGIN SELECT recid, attrib_value_id bulk collect INTO recid_table, avid_table FROM very_big_table; scale := SQL % rowcount; round_trips := TRUNC((scale + 9999) / 10000); FOR bite IN 1 .. round_trips LOOP starting_point :=(bite -1) *10000 + 1; ending_point := least(scale, bite *10000); forall this IN starting_point .. ending_point UPDATE another_very_big_table SET rec_id = recid_table(this) WHERE id = avid_table(this); COMMIT; --DBMS_OUTPUT.PUT_LINE('Move: ' || ending_point); END LOOP; END move_column;
Friday, November 6, 2009
Update massive number of records
Simple Pl/SQL procedure which help to do massive update.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment