【赛迪网－it技术报道】本文中介绍的几种写法分别是从代码的简易性,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;

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;

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;

c_table t_cur;

v_table varchar2(30); --dest table

v_sql varchar2(300);

v_rows number := 50;

begin

v_table := 'employee_cp';

open c_table for

select * from employees; --sour table

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 --.employee_id, c_table.first_name, c_table.last_name, c_table.email, c_table.phone_number, c_table.hire_date, c_table.job_id, c_table.salary, c_table.commission_pct, c_table.manager_id, c_table.department_id, c_table.birthday

bulk collect

into v_employee_id, v_first_name, v_last_name, v_email, v_phone_number, v_hire_date, v_job_id, v_salary, v_commission_pct, v_manager_id, v_department_id, v_birthday limit v_rows; --分批

forall i in 1 .. v_employee_id.count execute immediate v_sql using

v_employee_id(i), v_first_name(i), v_last_name(i),

v_email(i), v_phone_number(i), v_hire_date(i),

v_job_id(i), v_salary(i), v_commission_pct(i),

v_manager_id(i), v_department_id(i), v_birthday(i)

;

commit;

exit when c_table%notfound;

end loop;

end;

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

4相关附助sql:

select 'type type_' || column_name || ' is table of ' || table_name || '.' ||

column_name || '%type'

from dba_tab_columns

where table_name = 'employees'

and owner = 'hyf'

select 'v_' || column_name || ' type_' || column_name ||';'

from dba_tab_columns

where table_name = 'employees'

and owner = 'hyf'

select 'v_' || column_name || ','

from dba_tab_columns

where table_name = 'employees'

and owner = 'hyf'

select 'v_' || column_name || '(i),'

from dba_tab_columns

where table_name = 'employees'

and owner = 'hyf'