用Forall与bulk collect快速复制表数据-入门基础-Oracle频道-中国IT实验室

 create or replace procedure cp_data2 as

    type TYPE_EMPLOYEES is table of EMPLOYEES%rowtype;

    V_EMPLOYEES TYPE_EMPLOYEES;

    v_table varchar2(30);

    v_sql varchar2(300);

    　v_rows number:=5000;

    begin

    execute immediate 'alter session set nls_date_format=''yyyy/mm/dd''';

    v_table := 'employee_cp';

    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)';

    select * bulk collect into V_EMPLOYEES from employees; --dest table

    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;

    if mod(i, v_rows) = 0 then

    commit;

    end if;

    end loop;

    commit;

    end;

--------------------next---------------------