I'm trying to loop all my records from tableA and for half of records set-up for columnA = 1 and for second half of records columnA = 2.
declare
Type T2 Is Table Of TableA%Rowtype Index By Binary_Integer;
V2 T2;
Type T3 Is Table Of TableA%Rowtype Index By Binary_Integer;
V3 T3;
Maxrow Number(10);
mHalf Number(10);
begin
Select round(Max(Rownum)/2) Into Maxrow From TableA;
Select * Bulk Collect Into V2 From TableA Where Rownum < Mhalf;
Select * Bulk Collect Into V3 From TableA Where Rownum >= Mhalf;
For I In 1..2 Loop
If I=1 Then
For Z In V2.First..V2.Last Loop
update tableA set columnA = 1 where Rownum = V2(Z);
End Loop;
Elsif I=2 Then
For ZZ In V3.First..V3.Last Loop
update tableA set columnA = 2 where Rownum = V3(ZZ);
End Loop;
End if;
End Loop;
end;
But something is getting wrong. When I'm checking:
Select Count(*) From tableA Where Rownum > 300;
here I don't get any records
declare
mHalf Number;
l_rec TableA%rowtype;
cntr NUMBER := 1;
CURSOR cur IS Select * From TableA FOR UPDATE OF columnA;
begin
Select round(count(*)/2,0) Into mHalf From TableA;
OPEN cur;
LOOP
FETCH cur INTO l_rec;
EXIT WHEN cur%notfound;
UPDATE
tableA
SET
columnA=CASE WHEN cntr <= mhalf then 1 else 2 end
WHERE
CURRENT OF cur;
cntr := cntr +1;
END LOOP;
Commit;
end;