Oracle添加多行说明,【讨论】为什么插入6000多行数据要3个小时 求大神 ！

CREATE OR REPLACE PROCEDURE WX_SENDCOUPON_SUBMIT(p_sendcoupon_id IN NUMBER,

r_code             OUT NUMBER,

r_message          OUT VARCHAR2) AS

-------------------------------------------------------------------------

--add by paco 20140901

-------------------------------------------------------------------------

p_sql varchar2(1000);

p_vips  clob;

type p_vip_type is record(id number(10));

type p_v_type is table of p_vip_type index by binary_integer;

p_vtype p_v_type;

p_status      number(10);

p_count number(10);

p_couponuseno varchar2(100);

BEGIN

select sc.vips,sc.status

into p_vips,p_status

from wx_sendcoupon sc

where sc.id=p_sendcoupon_id;

if nvl(p_status,0)=2 then

raise_application_error(-20201,'不能重复提交');

end if;

p_sql:=GET_FITLER_SQL(p_vips);

p_sql:='select v.id from wx_vip v where v.id '||p_sql;

begin

execute immediate p_sql bulk collect into p_vtype;

exception when others then

r_code:=-1;

r_message:='提交失败';

return;

end;

for vip in 1.. p_vtype.count loop

select count(1)

into p_count

from wx_couponemploy ce

where ce.wx_vip_id=p_vtype(vip).id;

p_couponuseno:=p_vtype(vip).id||DBMS_RANDOM.STRING('a', 1)||floor(dbms_random.VALUE(10000, 100000))||p_count;

INSERT INTO WX_COUPONEMPLOY(ID,AD_CLIENT_ID,AD_ORG_ID,SNCODE,STATE,WX_VIP_ID,ISSUETYPE,WX_COUPON_ID,OWNERID,MODIFIERID,CREATIONDATE,MODIFIEDDATE,USENUM)

select get_Sequences('WX_COUPONEMPLOY'),sc.ad_client_id,sc.ad_org_id,p_couponuseno,'N',p_vtype(vip).id,1,sc.wx_coupon_id,sc.ownerid,sc.modifierid,sysdate,sysdate,1

from wx_sendcoupon sc,wx_coupon c

where sc.wx_coupon_id=c.id

and sc.id=p_sendcoupon_id;

end loop;

update wx_sendcoupon s set s.status=2 where s.id=p_sendcoupon_id;

r_code := 0;

r_message:='提交成功';

END;