本文中介绍的几种写法分别是从代码的简易性,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)';
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; |
