oracle 存储过程太慢 3个小数才插入9万条数据 问题出在哪儿？

[heitaoc]

create or replace procedure 老系统个人补缴转换 as
d_aac001 varchar2(100);
d_sfbzh varchar2(100);
d_lxname varchar2(100);
d_count varchar2(10);
d_date varchar2(10);
d_state varchar2(10);
d_pzh varchar2(100);
d_kssj varchar2(100);
d_jssj varchar2(100);
begin
for x in (select * from grbjpz_h_old) loop

--判断是否是17位，是就转换为18
d_sfbzh := x.shbzh;
if length(d_sfbzh) = 17 then
select sfz_jy(d_sfbzh) into d_sfbzh from dual;
end if;

--查找人员编号，查不到就跳出当次循环
select count(*) into d_count from simis.ac01 where acd015 = d_sfbzh;
if d_count = 0 then
GOTO END_LOOP;
end if;

--区别于新系统，凭证号前面加100000
d_pzh := '100000'||x.pzh;


d_kssj := x.bj_qs_ny;
if length(x.bj_qs_ny || '-') != 7 then
d_kssj := '000000';
end if;

d_jssj := x.bj_js_ny;
if length(x.bj_js_ny || '-') != 7 then
d_jssj := '000000';
end if;

--插入不用转换字段
insert into ac10_zhuanhuan
(bab221,
bab222,
aac121,
aac122,
aae140,
aac123,
aae108,
aae011,
aae036,
aab001)
values
(d_pzh,
d_pzh,
d_kssj,
d_jssj,
'11',
x.dw_bj_lj + x.gr_bj_lj,
'0',
x.zdy,
x.zdsj,
x.aab001);

--个人编号
select aac001 into d_aac001 from simis.ac01 where acd015 = d_sfbzh;
update ac10_zhuanhuan set aac001 = d_aac001 where bab221 = d_pzh;

--时间转换
if x.lrsj is not null then
select to_char(x.lrsj, 'yyyymm') into d_date from dual;
update ac10_zhuanhuan set aae002 = d_date where bab221 = d_pzh;
end if;

--人员状态
select aac008 into d_state from simis.ac01 where acd015 = d_sfbzh;
update ac10_zhuanhuan set aac008 = d_state where bab221 = d_pzh;

--AAE143+AAE013
select name into d_lxname from sb_dicttype where id = x.pz_lx;
update ac10_zhuanhuan set aae143 = x.pz_lx where bab221 = d_pzh;
update ac10_zhuanhuan set aae013 = d_lxname where bab221 = d_pzh;

commit;

<<END_LOOP>>
NULL;
end loop;
end;

[nayi_224]

不只是不该用for，存储过程都多余。9w次全表count，9w次insert，27w次select，45w次update。所完成的功能只与一条insert一致。看起来也就30秒的事。

[heitaoc]

引用 3 楼 nayi_224 的回复:

不只是不该用for，存储过程都多余。9w次全表count，9w次insert，27w次select，45w次update。所完成的功能只与一条insert一致。看起来也就30秒的事。

对的，所有的操作都是跟一条数据有关，但咋个写呢

[nayi_224]

insert into ac10_zhuanhuan_1 /*+ append*/ select decode(length(d_sfbzh), 17, sfz_jy(d_sfbzh)), '100000'||x.pzh, decode(length(x.bj_qs_ny || '-'), 7, x.bj_qs_ny, '000000'), decode(length(x.bj_js_ny || '-'), 7, x.bj_js_ny, '000000'), t2.aac001, decode(x.lrsj, null, null, to_char(x.lrsj, 'yyyymm')), t2.aac008, decode(t3.id, null, x.pz_lx) aae143, decode(t3.id, null, d_lxname) aae013 from grbjpz_h_old x, simis.ac01 t2, sb_dicttype t3 where 1 = 1 and x.d_sfbzh = t2.acd015 and t3.id = x.pz_lx(+) and exists( select 1 into d_count from simis.ac01 where acd015 = d_sfbzh ) ; 不知道具体数据特征，没搞外关联或者去重什么的。实现的功能应该跟上面的过程一样。

[heitaoc]

引用 5 楼 nayi_224 的回复:

insert into ac10_zhuanhuan_1 /*+ append*/ select decode(length(d_sfbzh), 17, sfz_jy(d_sfbzh)), '100000'||x.pzh, decode(length(x.bj_qs_ny || '-'), 7, x.bj_qs_ny, '000000'), decode(length(x.bj_js_ny || '-'), 7, x.bj_js_ny, '000000'), t2.aac001, decode(x.lrsj, null, null, to_char(x.lrsj, 'yyyymm')), t2.aac008, decode(t3.id, null, x.pz_lx) aae143, decode(t3.id, null, d_lxname) aae013 from grbjpz_h_old x, simis.ac01 t2, sb_dicttype t3 where 1 = 1 and x.d_sfbzh = t2.acd015 and t3.id = x.pz_lx(+) and exists( select 1 into d_count from simis.ac01 where acd015 = d_sfbzh ) ; 不知道具体数据特征，没搞外关联或者去重什么的。实现的功能应该跟上面的过程一样。

谢谢了通过你的办法8秒就可以了。但是最后一句是干啥的

[sxq129601]

应该是查询或更新的条件没有索引

[heitaoc]

现在插入数据更慢了，差不多1秒一条，怎么优化啊？不应该用for吗？

[minsic78]

先把过程里哪些步骤跑的慢给弄出来吧

[nayi_224]

引用 6 楼 G1994 的回复:

[quote=引用 5 楼 nayi_224 的回复:] insert into ac10_zhuanhuan_1 /*+ append*/ select decode(length(d_sfbzh), 17, sfz_jy(d_sfbzh)), '100000'||x.pzh, decode(length(x.bj_qs_ny || '-'), 7, x.bj_qs_ny, '000000'), decode(length(x.bj_js_ny || '-'), 7, x.bj_js_ny, '000000'), t2.aac001, decode(x.lrsj, null, null, to_char(x.lrsj, 'yyyymm')), t2.aac008, decode(t3.id, null, x.pz_lx) aae143, decode(t3.id, null, d_lxname) aae013 from grbjpz_h_old x, simis.ac01 t2, sb_dicttype t3 where 1 = 1 and x.d_sfbzh = t2.acd015 and t3.id = x.pz_lx(+) and exists( select 1 into d_count from simis.ac01 where acd015 = d_sfbzh ) ; 不知道具体数据特征，没搞外关联或者去重什么的。实现的功能应该跟上面的过程一样。

谢谢了通过你的办法8秒就可以了。但是最后一句是干啥的[/quote] 你是指 and exists( select 1 into d_count from simis.ac01 where acd015 = d_sfbzh )这句？ 这句的逻辑对应于 --查找人员编号，查不到就跳出当次循环 select count(*) into d_count from simis.ac01 where acd015 = d_sfbzh; if d_count = 0 then GOTO END_LOOP;