oracle 让过程等待5分钟,怎么可能？同一个程序，pro*c要将近5分钟，而存储过程只要3－4秒？...

怎么可能？同一个程序，pro*c要将近5分钟，而存储过程只要3－4秒？

本来是用pro*c写的，反复调试，都很慢，不知何故，改用存储过程，竟然只要几秒，太打击人了，我可是刚从存储过程阵容转投pro*c 的啊，哪位高手能给我解释一下？死不瞑目啊

pro*C源码：

。。。。。

EXEC SQL DECLARE cur_cz CURSOR for

select /*+ use_nl(a b) */ b.subscrbid,acctitmid,sum(fee)

from tab_fee a,tab_subscrb b

where telno=svcnum and part=:lvPart1

group by  b.subscrbid,acctitmid;

EXEC SQL OPEN cur_cz;

sprintf(cvLogMsg,"游标已打开.记录数%ld",lvRecNum);

log4svc(env,cvLogMsg);

while(1) {

EXEC SQL FETCH cur_cz INTO :lvSubscrbID,:lvAcctItmID,:lvFee;

if (sqlca.sqlcode>0) break;

if((sqlca.sqlerrd[2]%500)==0) {

sprintf(cvLogMsg,"正在处理游标=0...,已有%ld条游标处理完毕.进度：%ld%%",sqlca.sqlerrd[2],sqlca.sqlerrd[2]*100/lvRecNum);

log4svc(env,cvLogMsg);

env->setProgress(sqlca.sqlerrd[2]*100/lvRecNum,cvLogMsg);

}

/* 有匹配的帐目就取用否则取缺省(帐目标识=0)的 */

EXEC SQL EXECUTE

BEGIN

select acctid into :lvAcctID from (select /*+ index(a) */ acctid

from tab_acctrelation a

where subscrbid=:lvSubscrbID and acctitmid in (:lvAcctItmID,0)

order by acctitmid desc

)

where rownum<2;

END;

END-EXEC;

EXEC SQL update tab_billdetail

set fee=fee+:lvFee

where mon=:lvPart2 and subscrbid=:lvSubscrbID and acctid=:lvAcctID and acctitmid=:lvAcctItmID and billingcyclid=:cvBillCycle and recstat='01';

if (sqlca.sqlcode>0)

EXEC SQL insert into tab_billdetail(AcctItmSN,acctitmid,subscrbid,acctid,fee,billingcyclid,mon,recstat)

values(seq_bill.nextval,:lvAcctItmID,:lvSubscrbID,:lvAcctID,:lvFee,:cvBillCycle,:lvPart2,'01');

lvSumFee+=lvFee;

}

EXEC SQL CLOSE cur_cz;

EXEC SQL update tab_cz_status set status='1' where stepid=3;

EXEC SQL INSERT INTO tab_acct_log(svcid,accttype,actiontime,recnum,sumfee,info)

VALUES('0','3',sysdate,0,:lvSumFee,'合帐成功结束!');

EXEC SQL COMMIT WORK RELEASE;

log4svc(env,"合帐完成.&quot

;       

pro*C 执行日志：

[2002.12.09 17:10:36] > 游标已打开.记录数2786

[2002.12.09 17:11:26] > 正在处理游标=0...,已有500条游标处理完毕.进度：17

[2002.12.09 17:12:16] > 正在处理游标=0...,已有1000条游标处理完毕.进度：35

[2002.12.09 17:13:07] > 正在处理游标=0...,已有1500条游标处理完毕.进度：53

[2002.12.09 17:13:57] > 正在处理游标=0...,已有2000条游标处理完毕.进度：71

[2002.12.09 17:14:47] > 正在处理游标=0...,已有2500条游标处理完毕.进度：89

[2002.12.09 17:15:16] > 合帐完成.

存储过程源码：

create or replace procedure proc_cz(err_num out number,err_msg out varchar2)

as

lvRecNum number(10);

lvTimeLen number(10);

lvCnt number(10);

lvFee number(10);

lvSumFee number(10);

lvAcctItmID number(10);

lvSubscrbID number(10);

lvAcctID number(10);

lvPart1 number(10);

lvPart2 number(10);

CURSOR cur_cz  is

select /*+ use_nl(a b) */ b.subscrbid,acctitmid,sum(fee)

from tab_fee a,tab_subscrb b

where telno=svcnum and part=2

group by  b.subscrbid,acctitmid;

begin

OPEN cur_cz;

loop

fetch cur_cz into lvSubscrbID,lvAcctItmID,lvFee;

exit when cur_cz%notfound;

begin

select acctid into lvAcctID from (select /*+ index(a) */ acctid

from tab_acctrelation a

where subscrbid=lvSubscrbID and acctitmid in (lvAcctItmID,0)

order by acctitmid desc

)

where rownum<2;

exception

when others then

lvAcctID:=0;

end;

update tab_billdetail

set fee=fee+lvFee

where mon=lvPart2 and subscrbid=lvSubscrbID and acctid=lvAcctID and acctitmid=lvAcctItmID and billingcyclid='200211' and recstat='01';

if sql%rowcount=0 then

insert into tab_billdetail(AcctItmSN,acctitmid,subscrbid,acctid,fee,billingcyclid,mon,recstat)

values(seq_bill.nextval,lvAcctItmID,lvSubscrbID,lvAcctID,lvFee,'200211',2,'01');

end if;

end loop;

close cur_cz;

err_msg:='over ok!';

exception

when others then

rollback;

err_num:=sqlcode;

err_msg:=SUBSTR(sqlerrm,1,80);

end;

/

执行日志：

SQL> declare

2    errnum number(10);

3    errmsg varchar2(100);

4  begin

5    proc_cz(errnum,errmsg);

6    dbms_output.put_line(errmsg);

7  end;

8  /

over ok!

PL/SQL procedure successfully completed

Executed in 3.395 seconds