熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

用Forall與bulkcollect快速復制表數據

2013-11-13 15:52:01  來源: Oracle 

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

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.