oracle大量插入数据很慢,大量数据插入数据库老慢老慢 高手指点一下不 先谢了...

create or replace procedure impjfport(machineroom in varchar2)  is

v_jfid  temp_k_jfinfo.jfid%type;

v_jfcol temp_k_jfinfo.jfcol%type;

v_jfmk  temp_k_jfinfo.jfmk%type;

v_jfdz  temp_k_jfinfo.jfdz%type;

v_jfdztype  temp_k_jfinfo.jfdztype%type;

v_jftype   temp_k_jfinfo.jftype%type;

v_jfsysnum  tcs_s_box.ssysserialnum%type;

v_sysid   tcs_s_machineroom.sysid%type;

v_citycode tcs_s_machineroom.citycode%type;

v_state    temp_k_jfinfo.state%type;

v_id       temp_k_jfinfo.id%type;

singport    varchar2(50);

V_STR1       varchar2(16):='0000000000000000';

V_STR2       varchar2(3):='000';

V_STR3       varchar2(3):='000';

V_STR4       varchar2(5):='00000';

jfidlgh  int:=0;

jfcollgh int:=0;

jfmklgh  int:=0;

jfdzlgh  int:=0;

v_count  int:=0;

v_coutjf integer:=0;

cursor c_jf is

select  id,jfid, jfcol, jfmk, jfdz, jfdztype, jftype, state  from temp_k_jfinfo ;

begin

open c_jf;

loop

fetch c_jf into v_id,v_jfid,v_jfcol,v_jfmk,v_jfdz,v_jfdztype,v_jftype,v_state;

exit when c_jf%notfound;

select count(*) into v_coutjf from tcs_s_machineroom t where trim(t.roomcode)=trim(v_id);

if v_coutjf =1 then

--sysid citycode

select t.sysid,t.citycode into v_sysid,v_citycode from tcs_s_machineroom t where trim(t.roomcode)=trim(v_id);

--length()

jfidlgh:=length(trim(v_jfid));

jfcollgh:=length(trim(v_jfcol));

jfmklgh:=length(trim(v_jfmk));

jfdzlgh:=length(trim(v_jfdz));

--create prot

singport:=substr(V_STR1,1,16-jfidlgh)||trim(v_jfid)||'*'||substr(V_STR2,1,3-jfcollgh)||trim(v_jfcol)

||'*'||substr(V_STR3,1,3-jfmklgh)||trim(v_jfmk)

||'*'||substr(V_STR4,1,5-jfdzlgh)||trim(v_jfdz);

--jfsysnum

select count(t.ssysserialnum) into v_count  from tcs_s_box t where trim(t.shelfnum)=trim(v_jfid) and trim(t.roomcode)=trim(v_sysid);

if v_count=0 then

insert into err_id values('这个设备不存在','temp_jfinfo',v_jfid,v_jfid,'交接箱分线盒的外连接表');

else

select t.ssysserialnum into v_jfsysnum from tcs_s_box t where trim(t.shelfnum)=trim(v_jfid) and trim(t.roomcode)=trim(v_sysid);

--equ

if (v_jfdztype='A' or v_jfdztype='a') then

insert into tcs_s_connectboxequport

values(S_HC_RS_EQ_CONNECTBOXEQU.Nextval,v_jfsysnum,v_jfdz,'',v_state,'未预留','未连接','未连接','未使用',v_jfcol,v_jfmk,singport,'','','','','');

end if;

--user

if (v_jfdztype='B' or v_jfdztype='b') then

insert into tcs_s_connectboxuser

values(S_HC_RS_EQ_CONNECTBOXS1.Nextval,v_jfsysnum,v_jfdz,'',v_state,'未连接','未预留','未连接','未使用',v_jfcol,v_jfmk,singport,'','','','','');

end if;

end if;

else

insert into err_id values('机房编号不正确','temp_jfinfo',v_jfid,v_jfid,'交接箱分线盒的端子表');

end if;

end loop;

commit;

close c_jf;

exception

when others then

dbms_output.put_line('交接箱不存在:'||v_jfid);

end impjfport;