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

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'

上一页  [1] [2] [3] 

【责编:Ken】