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

本文中介绍的几种写法分别是从代码的简易性,FORALL和bulk collect的使用,以及分批插入这三方面考虑得出的，大家可以根据自己的需要灵活选择。

三种不同的写法:

1.使用了BULK COLLECT,没有使用FORALL, 一次性插入,分批COMMIT,这种方法比较适用于10万以下条数据的表;

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;

i 1000 f mod(i, v_rows) = 0 then

commit;

end if;

end loop;

commit;

end;