Environment: 11g

create table emp_copy
as
select empno, ename, job, hiredate from emp where 1 = 2;  

1.Whole
--Abbreviation
declare
     cursor c_emps is
        select empno, ename, job, hiredate from emp;
     type emp_aat is table of c_emps%rowtype
        index by pls_integer;
     aa_emps emp_aat;

  begin
     open c_emps;
     fetch c_emps bulk collect into aa_emps;
     close c_emps;

     forall i in  1 .. aa_emps.count
        insert /*+ append_values*/ into emp_copy
        values  aa_emps(i);
     dbms_output.put_line(
        to_char(sql%rowcount) || ' rows inserted.'
        );
  end;

//Equivalent to: (specify field)
declare
     cursor c_emps is
        select empno, ename, job, hiredate from emp;
     type emp_aat is table of c_emps%rowtype  ;
       
     aa_emps emp_aat;

  begin
     open c_emps;
     fetch c_emps bulk collect into aa_emps;
     close c_emps;

     forall i in  1 .. aa_emps.count
       insert /*+ append_values*/ into emp_copy
          (empno, ename, job, hiredate)
        values
          (aa_emps(i).empno,
           aa_emps(i).ename,
           aa_emps(i).job,
           aa_emps(i).hiredate);
  end;
  //Support append ﹣ values


2.Specified field,indices of You can skip values that don't exist
create table emp_copy
  as
  select empno, ename, job, hiredate from emp where 1 = 2;  
  
  select * from emp_copy;
  
  declare
     cursor c_emps is
        select empno, ename, job, hiredate from emp;
     type emp_aat is table of c_emps%rowtype
        index by pls_integer;
     aa_emps emp_aat;

  begin
     /* bulk fetch sample data... */
     open c_emps;
     fetch c_emps bulk collect into aa_emps;
     close c_emps;
     
     forall i in indices of aa_emps
        insert into emp_copy
          (empno, ename, job, hiredate)
        values
          (aa_emps(i).empno,
           aa_emps(i).ename,
           aa_emps(i).job,
           aa_emps(i).hiredate);

     dbms_output.put_line(
        to_char(sql%rowcount) || ' rows inserted.'
        );
  end;
//In the same way, support / * + append [values * / / / actual measurement


3.Specified field,Usage dynamics sql
declare
     cursor c_emps is
     select empno, ename, job, hiredate from emp;
     --Not used here index by pls_integer Same effect;
     type emp_aat is table of c_emps%rowtype   index by pls_integer;
     aa_emps emp_aat;
     --dynamic sql Same support append_values
     v_sql varchar2(20000):='insert /*+ append_values*/ into emp_copy (empno, ename, job, hiredate) values(:1,:2,:3,:4)';

  begin
     open c_emps;
     fetch c_emps bulk collect into aa_emps;
     close c_emps;
     forall i in  1 .. aa_emps.count
        execute immediate v_sql using  aa_emps(i).empno,
                                       aa_emps(i).ename,
                                       aa_emps(i).job,
                                       aa_emps(i).hiredate;
  end;

//Support append ﹣ values;


4.forall Also support insert into..select mode,Field insertion can be specified
//But the append method is not supported
//The effect of append and append [values] is the same;
insert /*+append_values*/ into emp_copy
select empno, ename, job, hiredate  from emp where rowid='AAAUVnAAEAAAiOHAAB';


//Method 1: one time submission
declare
  cursor c_emps is
    select rowid rid from emp;
  v_tab dbms_sql.Urowid_Table;

begin
  open c_emps;
  fetch c_emps bulk collect
    into v_tab;
  close c_emps;

  forall i in 1 .. v_tab.count
    insert 
    into emp_copy
      select empno, ename, job, hiredate from emp where rowid = v_tab(i);          
  dbms_output.put_line(sql%rowcount || ' rows inserted.');  
  commit; --Put on sql%rowcount behind,Otherwise, you will not get the number of lines
end;


//Mode 2: batch submission, relatively complete writing
declare
  cursor c_emps is
    select rowid rid from emp;
  v_tab   dbms_sql.Urowid_Table;
  v_limit pls_integer := 5;
  v_count pls_integer:=0;
begin
  open c_emps;

  loop
    v_count:=v_count+1;
    fetch c_emps bulk collect
      into v_tab limit v_limit;   
      
    forall i in 1 .. v_tab.count
      insert 
      into emp_copy
        select empno, ename, job, hiredate from emp where rowid = v_tab(i);     
    dbms_output.put_line('The first'||v_count||'Secondary cycle:'||(sql%rowcount) || ' rows inserted.'); 
    commit;
    exit when c_emps%notfound;
  end loop;   
  close c_emps;   
  
exception when
  others then
  dbms_output.put_line(sqlerrm||dbms_utility.format_error_backtrace);
end;

//Output:
//The first cycle: 5 rows inserted
//The second cycle: 5 rows inserted
//The third cycle: 2 rows inserted


//Update and delete can be done in the same way as above

Summary:
For the efficiency of insert operation, the priority is cats, followed by merge into + append [values
If the memory is insufficient, batch submit + append ﹣ values is used;
For batch submissions:
If the values in the set are not continuous, use indexes of;
If you want to Insert into values continuously, you can,
The fourth method does not support append, and it is checked twice. The efficiency is not good, and it is not recommended;
If the sql to be queried is dynamic, use dynamic sql;

For update operation efficiency:
rowid is the most efficient, but the operation is a little bit cumbersome
Then manually query rowid and batch update or merge into
Then automatic task DBMS? Parallel? Execute

If anything is wrong, please correct it. Thank you
Q:279907389