Search code examples
sqloracle-databaseloopsplsqloracle12c

Setup value for half of records (Oracle, PL/SQL)


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


Solution

  • 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;