用Forall与bulk collect快速复制表数据

    type t_cur is REF cursor;

    c_table t_cur;

    type t_employee is table of employees%rowtype;

    v_employees t_employee;

    rows number := 50;

    v_sql varchar2(300);

    v_table varchar(50);

    begin

    v_table := 'employee_cp';

    open c_table for

    select * from employees; --sour

    v_sql := 'insert /*+ APPEND*/ into ' || v_table ||

    ' (EMPLOYEE_ID,

    FIRST_NAME,

    LAST_NAME,

    EMAIL,

    PHONE_NUMBER,

    HIRE_DATE,

    JOB_ID,

    SALARY,

    COMMISSION_PCT,

    MANAGER_ID,

    DEPARTMENT_ID,

    BIRTHDAY) values (:1, :2,:3,:4,:5,:6, :7, :8,:9,:10, :11,:12)';

    loop

    fetch c_table bulk collect

    into v_employees limit rows; --分批

    dbms_output.put_line(v_employees.count);

    for i in 1 .. v_employees.count loop

    execute immediate v_sql

    using V_EMPLOYEES(i).EMPLOYEE_ID, V_EMPLOYEES(i).FIRST_NAME, V_EMPLOYEES(i).LAST_NAME, V_EMPLOYEES(i).EMAIL, V_EMPLOYEES(i).PHONE_NUMBER, V_EMPLOYEES(i).HIRE_DATE, V_EMPLOYEES(i).JOB_ID, V_EMPLOYEES(i).SALARY, V_EMPLOYEES(i).COMMISSION_PCT, V_EMPLOYEES(i).MANAGER_ID, V_EMPLOYEES(i).DEPARTMENT_ID, V_EMPLOYEES(i).BIRTHDAY;

    end loop;

    commit;

    exit when c_table%notfound;

    end loop;

    close c_table;

    end;