用分页来实现动态分批插入数据

--通用动态分批插入数据
declare


  v_num      number := 0;
  v_col_name varchar2(50);
  v_sql      varchar2(30000);
  i          number := 0;
  v_col      varchar2(50);  --列名
  v_table1    varchar2(50);  --'pepsdata.cit_journal_interface';  --源表
  v_table2    varchar2(50);  --'pepsdata.cit_journal_interface_2012';  --目标 transform_ych,transform_ych_bak
  
begin
   
  select count(*) into v_num from &v_table1;


  --动态获取表的列名
  for c_col in (select column_name from all_tab_columns c where c.table_name = upper('&v_table1') order by c.column_id) loop
    
    v_col_name := c_col.column_name;
    
    if v_col is null then
      
      v_col := v_col_name;
        
    else

      v_col := v_col || ',' || v_col_name;

    end if;
    
  end loop;
  
  --每2000条提交一次
  for i in 0..ceil(v_num/2000) loop  
    
    --每次插入2000条数据
    v_sql := '
      insert into &v_table2
      select '||v_col||'
        from (select a.*, rownum rn
from (select * from &v_table1) a
where rownum <= i * 2000 + 2000)  --结束页
where rn >= i * 2000 + 1;  --起始页
    ';

    execute immediate v_sql;
    
    commit;  
    
  end loop;
  
end;




--分批插入数据(指定表)  每99条数据插入一次
declare

  v_count number := 0;

begin

  select count(*) into v_count from public_account_base;

for i in 0..ceil(v_count/99) loop

 insert into public_account_base_bak
 select account_id,sob_code,account_code,account_name,account_type,hierarchy_num,valid,
      description,created_by,created_date,updated_by,updated_date,module_type_id,account_order 
 from (select t.*, rownum rn
                from (select * from public_account_base) t
               where rownum <= i * 99 + 99)
       where rn >= i * 99 + 1;

end loop;

end;




--------分批删除数据
declare


  v_count       number := 0;


begin

  --根据日期删除旧数据
  loop


    delete amlm_ars_company_result_inf where trunc(updated_date) = sysdate - 1 and partition_flag = to_char(sysdate - 1, 'dd') and rownum <= 5000;
      exit when sql%rowcount = 0;   --%取模，整除取余,这个参数必须要在一个修改语句和commit之间放置,就如你在sqlplus下执行delete from之后提示已删除xx行一样
     
    if sql%rowcount != 0 then
   
 commit;
 
 end if;
  
  end loop;
  loop
/*
 delete from it_high_report_manpower_detail where year = 2012 and rownum <= 2000;

v_count := sql%rowcount;       

commit;

dbms_output.put_line(v_count);

exit when v_count <= 0;
  
  end loop;
*/

end;