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

转载

mb646da62f0d7e4 2023-05-24 14:11:41

文章标签 table manager date email sql 文章分类 jQuery 前端开发

　　三种不同的写法:

　　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'