Oracle PL/SQL Cursors - For Loop and Using a Record

Here are two variations of looping through rows in Oracle PL/SQL.  The FOR structure reduces the amount of code, in either case is it more perference than anything else.

---------------------------------- 
--Cursor - For Loop
----------------------------------    
for i in (select constraint_name, table_name
          from all_constraints
          where constraint_type = 'R'
          and owner = lv_owner
          and status = 'ENABLED')  
  LOOP
  execute immediate 'alter table ' || i.table_name ||
                    ' disable constraint ' || i.constraint_name || '';
end loop;

----------------------------------
--Cursor - For each record
----------------------------------     
declare cursor csr is    
          select constraint_name, table_name
                    from all_constraints
                    where constraint_type = 'R'
                    and owner = lv_owner
                    and status = 'ENABLED';
                    
 begin
   for rcd in csr
    loop          
     execute immediate 'alter table ' || rcd.table_name ||
                       ' disable constraint ' || rcd.constraint_name || '';
    
    end loop;
  end;
Comments are closed