Oracle updating records within same cursor

16-Nov-2017 20:47

FORALL update using SET ROW. We have avoided both the primary key updates and the redundant updates against columns that do not change.

===================== PARSING IN CURSOR #7 len=89 dep=1 uid=57 oct=6 lid=57 tim=81572960151 hv=2362111366 ad='21cee5a8' UPDATE ( SELECT NAME, TYPE FROM UPDATE_TABLE WHERE ID = : B1 ) SET NAME = : B2 , TYPE = : B3 END OF STMT ===================== Note that this 10g solution targets the updates via the primary key values.

DECLARE 2 3 CURSOR c_source IS 4 SELECT * 5 FROM update_table; 6 7 TYPE aat_whole_row IS TABLE OF c_source%ROWTYPE 8 INDEX BY PLS_INTEGER; 9 10 TYPE aat_id IS TABLE OF 11 INDEX BY PLS_INTEGER; 12 13 aa_rows aat_whole_row; 14 aa_ids aat_id; 15 16 BEGIN 17 18 /* Fetch in one go as we are using small demo volumes... COUNT LOOP 25 26 --[complex processing goes here]-- 27 aa_rows(i):= LOWER(aa_rows(i).name); 28 aa_rows(i):= LOWER(aa_rows(i).type); 29 30 /* Assign IDs to PK array... PUT_LINE( SQL%ROWCOUNT

For further validation that this technique avoids all primary key updates, readers can try the following tests.This means, therefore, that the source data doesn't necessarily have to come from the table that is to be updated, even though this is how the example is structured.