用Forall與bulkcollect快速復制表數據

　　本文中介紹的幾種寫法分別是從代碼的簡易性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

From:http://tw.wingwit.com/Article/program/Oracle/201311/17422.html