|
【赛迪网－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'
|
