三种不同的写法:
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;
if mod(i, v_rows) = 0 then
commit;
end if;
end loop;
commit;
end;
2.使用BULK COLLECT，不使用FORALL, 分批插入,多次提交,比较适用于大表;
create or replace procedure cp_data5 as
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;