本文中介绍的几种写法分别是从代码的简易性FORALL和bulk collect的使用以及分批插入这三方面考虑得出的大家可以根据自己的需要灵活选择
三种不同的写法:
使用了BULK COLLECT没有使用FORALL 一次性插入分批COMMIT这种方法比较适用于万以下条数据的表;create or replace procedure cp_data as
type TYPE_EMPLOYEES is table of EMPLOYEES%rowtype;
V_EMPLOYEES TYPE_EMPLOYEES;
v_table varchar();
v_sql varchar();
v_rows number:=;
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 (: ::::: : ::: ::);
select * bulk collect into V_EMPLOYEES from employees; dest table
for i in unt 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) = then
commit;
end if;
end loop;
commit;
end;
使用BULK COLLECT不使用FORALL 分批插入多次提交比较适用于大表;
create or replace procedure cp_data 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 := ;
v_sql varchar();
v_table varchar();
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 (: ::::: : ::: ::);
loop
fetch c_table bulk collect
into v_employees limit rows; 分批
dbms_outputput_line(unt);
for i in unt 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;
使用BULK COLLECT和FORALL 分批插入多次提交比较适用于大表; 前期数据字段定义比较烦锁(表各个字段必须分开定义)
create or replace procedure cp_data as
type type_EMPLOYEE_ID is table of EMPLOYEESEMPLOYEE_ID%type;
type type_FIRST_NAME is table of EMPLOYEESFIRST_NAME%type;
type type_LAST_NAME is table of EMPLOYEESLAST_NAME%type;
type type_EMAIL is table of EMPLOYEESEMAIL%type;
type type_PHONE_NUMBER is table of EMPLOYEESPHONE_NUMBER%type;
type type_HIRE_DATE is table of EMPLOYEESHIRE_DATE%type;
type type_JOB_ID is table of EMPLOYEESJOB_ID%type;
type type_SALARY is table of EMPLOYEESSALARY%type;
type type_COMMISSION_PCT is table of EMPLOYEESCOMMISSION_PCT%type;
type type_MANAGER_ID is table of EMPLOYEESMANAGER_ID%type;
type type_DEPARTMENT_ID is table of EMPLOYEESDEPARTMENT_ID%type;
type type_BIRTHDAY is table of EMPLOYEESBIRTHDAY%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 varchar(); dest table
v_sql varchar();
v_rows number := ;
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 (: ::::: : ::: ::);
loop
fetch c_table EMPLOYEE_ID c_tableFIRST_NAME c_tableLAST_NAME c_tableEMAIL c_tablePHONE_NUMBER c_tableHIRE_DATE c_tableJOB_ID c_tableSALARY c_tableCOMMISSION_PCT c_tableMANAGER_ID c_tableDEPARTMENT_ID c_tableBIRTHDAY
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 unt 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;
相关附助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
