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

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;

3.使用BULK COLLECT和FORALL ，分批插入,多次提交,比较适用于大表; 前期数据字段定义比较烦锁(表各个字段必须分开定义)

-------------------

create or replace procedure cp_data as

type type_EMPLOYEE_ID is table of EMPLOYEES.EMPLOYEE_ID%type;

type type_FIRST_NAME is table of EMPLOYEES.FIRST_NAME%type;

type type_LAST_NAME is table of EMPLOYEES.LAST_NAME%type;

type type_EMAIL is table of EMPLOYEES.EMAIL%type;

type type_PHONE_NUMBER is table of EMPLOYEES.PHONE_NUMBER%type;

type type_HIRE_DATE is table of EMPLOYEES.HIRE_DATE%type;

type type_JOB_ID is table of EMPLOYEES.JOB_ID%type;

type type_SALARY is table of EMPLOYEES.SALARY%type;

type type_COMMISSION_PCT is table of EMPLOYEES.COMMISSION_PCT%type;

type type_MANAGER_ID is table of EMPLOYEES.MANAGER_ID%type;

type type_DEPARTMENT_ID is table of EMPLOYEES.DEPARTMENT_ID%type;

type type_BIRTHDAY is table of EMPLOYEES.BIRTHDAY%type;

V_EMPLOYEE_ID TYPE_EMPLOYEE_ID;

V_FIRST_NAME TYPE_FIRST_NAME;

V_LAST_NAME TYPE_LAST_NAME;

V_EMAIL TYPE_EMAIL;

V_PHONE_NUMBER TYPE_PHONE_NUMBER;

V_HIRE_DATE TYPE_HIRE_DATE;

V_JOB_ID TYPE_JOB_ID;

V_SALARY TYPE_SALARY;

V_COMMISSION_PCT TYPE_COMMISSION_PCT;

V_MANAGER_ID TYPE_MANAGER_ID;

V_DEPARTMENT_ID TYPE_DEPARTMENT_ID;

V_BIRTHDAY TYPE_BIRTHDAY;

type t_cur is ref cursor;

上一页  [1] [2] [3] 下一页

【责编:Ken】