过程插入更新的3种方法:
a、逐条检查插入或更新,同时执行插入或更新
b、逐条merge into(逐条是为了记录过程日志与错误信息)
c、基于关联数组的检查插入、更新,通过forall批量sql执行
以下为模拟步骤:
1、创建模拟大表,数据量1亿不分区
create table big_table as
SELECT ROWNUM RN,'A' A,'B' B,'C' C,'D' D FROM(SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 1000) T1,
(SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 1000) T2,
(SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 100) T3;
说明:用connect by方法创建测试大表相对较快
2、给大表加上一个主键,因为插入更新基于主键
alter tableBIG_TABLE2add constraint PK_RN2 primary key (RN)
3、创建模拟的更新表,数据量20万
CREATE TABLE UP_TABLE ( RN NUMBER,A VARCHAR2(20),B VARCHAR2(20),C VARCHAR2(20),D VARCHAR2(20))
4、给更新表加数据,10万更新,10万插入,写的相对烦人些。。。代码不贴出来了
5、日志表结构
--Create table
create tableINS_UP_LOG
(
typeVARCHAR2(50),
timeNUMBER,
numNUMBER,
upNUMBER,
insNUMBER)
6、第一种方法的过程如下:
create or replace procedure sp_ins_up isv_bzchar(1);--插入更新标志
v_stime pls_integer;--过程起始时间
i pls_integer := 0; --执行数量
v_up pls_integer := 0;--更新数量
v_ins pls_integer := 0;--插入数量
v_time pls_integer;--循环当前循环起始时间
beginv_stime := dbms_utility.get_time();--过程起始时间
v_time := dbms_utility.get_time();--当前循环起始时间
for rec in (select * from up_table) loop--开始循环
i := i + 1;begin--当前数据执行插入更行判断
select '1' into v_bz from big_table1 a where a.rn =rec.rn;
exceptionwhen no_data_found thenv_bz := 0;end;if v_bz = 1 then--更新
v_up := v_up + 1;updatebig_table1 aset a.a = rec.a, a.b = rec.b, a.c = rec.c, a.d =rec.dwhere a.rn =rec.rn;else--插入
v_ins := v_ins + 1;insert into big_table1 values(rec.rn, rec.a, rec.b, rec.c, rec.d);end if;if mod(i, 5000) = 0 then--一定数量提交
insert intoINS_UP_LOGvalues('sp_ins_up',round((dbms_utility.get_time - v_time) / 100, 2),
v_up+v_ins,
v_up,
v_ins);commit;
v_up := 0;
v_ins := 0;
v_time :=dbms_utility.get_time;end if;endloop;commit;insert into ins_up_log--过程总时间记录
values('sp_ins_up',round((dbms_utility.get_time - v_stime) / 100, 2),'','','');commit;end sp_ins_up;
7、merge into方法过程如下:
create or replace procedure sp_merge isv_stime pls_integer;--过程起始时间
i pls_integer := 0; --执行数量
v_time pls_integer; --循环当前循环起始时间
v_rn up_table.rn%type; --缓存值
v_errmsg varchar2(500); --当前错误信息
beginv_stime :=dbms_utility.get_time();
v_time :=dbms_utility.get_time();for rec in (select * fromup_table) loop
i := i + 1;
v_rn := rec.rn; --缓存当前主键
merge into big_table3 t --merge into
using (select * from up_table where rn =rec.rn) aon (t.rn =a.rn)when matched then
update set t.a = a.a, t.b = a.b, t.c = a.c, t.d =a.dwhen not matched then
insert values(a.rn, a.a, a.b, a.c, a.d);
v_errmsg :=sqlerrm;if mod(i, 5000) = 0 then
insert intoins_up_logvalues('sp_merge',round((dbms_utility.get_time - v_time) / 100, 2),
i,
i/ 2, --此处插入数量不必在意
i / 2);commit;
v_time :=dbms_utility.get_time();end if;endloop;insert into ins_up_log --过程总时间记录
values('sp_merge',round((dbms_utility.get_time - v_stime) / 100, 2),
i,'','');commit;
exceptionwhen others then
insert into ins_up_err values ('sp_merge', v_rn, v_errmsg);end sp_merge;
8、forall方法,结合第一种方法的判断方式:
create or replace procedure sp_forall_new istype table_typeis table of up_table%rowtype index by pls_integer; --增量表缓存
tab_all table_type; --增量表全
tab_up table_type; --增量表-更新
tab_ins table_type; --增量表-插入
v_bz char(1); --存在标志
cursor cur_up is
select * from up_table; --更新表游标
v_stime pls_integer; --过程开始时间
v_time pls_integer; --每次循环开始时间
v_num_ins pls_integer := 0; --每次循环插入数量
v_num_up pls_integer := 0; --每次循环更新数量
v_num number; --数量缓存值
v_err_msg number; --forall中错误信息
beginv_stime := dbms_utility.get_time(); --过程开始
open cur_up; --打开游标
loop--开始循环游标
v_time := dbms_utility.get_time(); --当前循环开始时间
v_num_ins := 0; --每次循环置零
v_num_up := 0; --每次循环置零
fetch cur_up bulkcollectinto tab_all limit 5000; --批量插入缓存表,每次限定5000条
exit when tab_all.count = 0; --当缓存表中无数据,退出循环
--
for i in 1 .. tab_all.countloop--此循环功能:判断是否存在目标表中
begin
select '1' into v_bz from big_table2 t where t.rn = tab_all(i).rn; --存在
exceptionwhen no_data_found thenv_bz := '0'; --不存在
end;--
if v_bz = '1' then
--存在
v_num_up := v_num_up + 1; --更新数值+1
tab_up(v_num_up) := tab_all(i); --复制到更新缓存表
else
--不存在
v_num_ins := v_num_ins + 1; --插入数值+1
tab_ins(v_num_ins) := tab_all(i); --复制到插入缓存表
end if;--
endloop;--
--批量SQL,且在批量执行中错误的信息记录在sql%bulk_exceptions缓存表中
if tab_up.count <> 0 thenforall iin tab_up.first .. tab_up.last saveexceptionsupdatebig_table2 tset t.a =tab_up(i).a,
t.b=tab_up(i).b,
t.c=tab_up(i).c,
t.d=tab_up(i).dwhere t.rn =tab_up(i).rn;for i in 1 .. sql%bulk_exceptions.countloop
v_num := sql%bulk_exceptions(i).error_index;
v_err_msg := sqlerrm(-sql%bulk_exceptions(i).error_code);insert intoins_up_errvalues('sp_forall', tab_up(v_num).rn, v_err_msg);endloop;commit;end if; --更新批量结束
--批量SQL,且在批量执行中错误的信息记录在sql%bulk_exceptions缓存表中
if tab_ins.count <> 0 thenforall iin tab_ins.first .. tab_ins.last saveexceptionsinsert intobig_table2values(tab_ins(i).rn,
tab_ins(i).a,
tab_ins(i).b,
tab_ins(i).c,
tab_ins(i).d);for i in 1 .. sql%bulk_exceptions.countloop
v_num := sql%bulk_exceptions(i).error_index;
v_err_msg := sqlerrm(-sql%bulk_exceptions(i).error_code);insert intoins_up_errvalues('sp_forall', tab_ins(v_num).rn, v_err_msg);endloop;commit;end if; --插入批量结束
insert into ins_up_log --记录日志
values('sp_forall',round((dbms_utility.get_time - v_time) / 100, 2),5000,
v_num_up,
v_num_ins);commit;--清空当前循环插入、更新缓存表数据(不清空,下次循环重复执行)
tab_up.delete;
tab_ins.delete;endloop;close cur_up; --关闭游标
insert into ins_up_log --过程总时间记录
values('sp_forall',round((dbms_utility.get_time - v_stime) / 100, 2),'','','');commit;end sp_forall_new;
最后结果:同样对一亿的表插入更新20万条数据,多次执行平均时间
sp_merge 14.48秒sp_forall 6.63秒sp_ins_up 44.33秒
从每5000条提交一次的时间可以得出来,forall最稳定,其次merge稍有起伏,手动执行插入更新浮动最大从0.3秒到6.9秒不等。
效率方面:forall优势明显,其次merge也不差,手动插入更新最慢且不稳定
代码方面:merge into与手动插入更新 相对简洁,forall就比较复杂
扩展性:手动插入更新可以加上许多业务性功能,forall方式目前oracle也提供了相当多的函数用于数据处理,所以次之,merge into就个人而言,相对笨重一些了。