分享
 
 
 

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

王朝other·作者佚名  2008-06-01
窄屏简体版  字體: |||超大  

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

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'

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有