動態SQL完成大表資料的遷移

目前需求是根據源表資料如salary>10000的資料插入目標表中，目標表根據需要指定，且插入指定N條資料後提交。

對於此類問題可以用insert into select方法但需要按指定N條資料提交，說明表中資料量很大，插入時應批量提取，再按

指定條數插入新表，且批量提取資料時也應根據資料大小分批提取，於是考慮在動態sql中使用bulk collect into + limit的方法

具體例項如下

drop table emp_bak;
create table emp_bak
  as select  first_name
            ,salary
            ,rownum  rnid
  from employees
  where 1 != 1;

declare
  SOURCE_TABLE varchar(100);
  TAG_A VARCHAR(100);
  C_COUNT  VARCHAR(100);
  v_query_sql   varchar2(500);
  v_query_rn   varchar2(100);
  
  type delArray1 is table of hr.employees.first_name%type index by binary_integer;
  type delArray2 is table of hr.employees.salary%type index by binary_integer;
  type delArray3 is table of  hr.employees.employee_id%type index by binary_integer;
  first_name delArray1;
  salary delArray2;
  rnid delArray3;
  rnd number;
 
  TYPE i_cursor_type IS REF CURSOR;
  my_cursor i_cursor_type;
  begin 
       SOURCE_TABLE := 'employees';
       TAG_A := 'emp_bak';
       C_COUNT  := 10;
       v_query_rn :=
        'select   count(*)
       from '||SOURCE_TABLE||'
       where salary > 10000';
      
       v_query_sql :=
        'select    first_name
                  ,salary
                  ,rownum
       from '||SOURCE_TABLE||'
       where salary > 10000';
      
      open   my_cursor  for v_query_sql;
      execute immediate v_query_rn into rnd;
      for i in 1..rnd loop
        fetch my_cursor bulk collect into first_name,salary,rnid limit 10;
        for i in 1..first_name.count
        loop        
         execute immediate  'insert into hr.'|| TAG_A||' values (:1, :2, :3)'
                          using first_name(i),salary(i),rnid(i);
        end loop;
       end loop;
      close my_cursor;          
  end;
 
 select count(*) from emp_bak;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-2074785/，如需轉載，請註明出處，否則將追究法律責任。