multi row update using cursors PL SQL anonymous block

Question

How to we write a PL SQL block for updating multiple rows at a time. The scenarios is, there are two tables TABLE1,TABLE2 both have a common column COLUMN1. In the anonymous block the cursor is defined on the TABLE1.

On running the block for the first time i.e with v_run:=1 and v_offset:=2 ,the first two rows will be in cursor. On running the block for the second time v_run:=2 and next two rows from TABLE1 will be in the cursor and so on.

What should be v_SPECIAL_VARIABLE in the sample code be so that on the first run i.e v_run:=1, first two rows(row no 1,2) are fetched in the cursor and their corresponding COLUMN2 values in TABLE2 are updated to 'N' and the rest of the rows in TABLE2 are updated to 'n'.

On the second run i.e v_run:=2 next two rows (row no 3,4) are fetched from the TABLE1 in the cursor and their corresponding COLUMN2 values in TABLE2 are updated to 'N' and the rest of the rows in TABLE2 are updated to 'n'.

On the third run i.e v_run:=3 next two rows (row no 5,6) are fetched from the TABLE1 in the cursor and their corresponding COLUMN2 values in TABLE2 are updated to 'N' and the rest of the rows in TABLE2 are updated to 'n'.

Table1 contains

id | COLUMN1     | total
-------------------------
 1 | 1256480     | 5
 2 | 1256481     | 3
 3 | 1256482     | 2
 4 | 1256483     | 1
 5 | 1256484     | 3
 6 | 1256485     | 2
 7 | 1256486     | 1
 8 | 1256487     | 3

Table2 contains

   | COLUMN1     | column2
-------------------------
  | 1256480     | N
  | 1256481     | N
  | 1256482     | N
  | 1256483     | N
  | 1256484     | N
  | 1256485     | n
  | 1256486     | n
  | 1256487     | n

Sample code

declare
v_offset number:=2;
v_run    number:=1;
cursor curv1 is
select column1 from 
(select rownum rowcol,column1 from table1)
where rowcol between (v_run-1)*v_offset+1 and v_offset*v_run;
begin

open curv1 ;
fetch curv1 into v_SPECIAL_VARIABLE
----- first update
update table2 set column2='N' where
column1=(' ALL the values in v_SPECIAL_VARIABLE');
----- second update
update table2 set column2='n' where
column1 not in (' ALL the values in v_SPECIAL_VARIABLE');
close curv1 ;
end;

Answer 1

Iterating a cursor and updating on a row-by-row basis is a recipe for poor performance. Get rid of cursors whenever possible, as in:

declare
  v_offset number:=2;
  v_run    number:=1;
begin
  -- First update

  UPDATE TABLE2
    SET COLUMN2 = 'N'
    WHERE COLUMN1 IN (SELECT DISTINCT COLUMN1
                        FROM TABLE1
                        WHERE ROWCOL BETWEEN (v_run-1)*v_offset+1
                                         AND v_offset*v_run);

  -- Second update

  UPDATE TABLE2
    SET COLUMN2 = 'n'
    WHERE COLUMN1 NOT IN (SELECT DISTINCT COLUMN1
                            FROM TABLE1
                            WHERE ROWCOL BETWEEN (v_run-1)*v_offset+1
                                             AND v_offset*v_run);
end;

Note that in this case the updates could be simplified to:

declare
  v_offset number:=2;
  v_run    number:=1;
begin
  -- Second update

  UPDATE TABLE2
    SET COLUMN2 = 'n'
    WHERE COLUMN1 IS NOT NULL;

  UPDATE TABLE2
    SET COLUMN2 = 'N'
    WHERE COLUMN1 IN (SELECT DISTINCT COLUMN1
                        FROM TABLE1
                        WHERE ROWCOL BETWEEN (v_run-1)*v_offset+1
                                         AND v_offset*v_run);
end;

Best of luck.