我写了两个存储过程,通过A调用B,平常B只要处理一次,月末时重新从月初处理到月末。
碰到一个奇怪问题,A总是没有达到我的预想目的,每次最多执行12天就结束了,可是却没有异常抛出,请高手救命啊。
create or replace procedure a(procdate date) is p_date date;
begin if trunc(procdate,'dd')=trunc(last_day(procdate),'dd') then p_date:=add_months(last_day(procdate)+1,-1); while trunc(p_date,'dd')<=trunc(procdate,'dd') loop
begin B(p_date);
p_date:=p_date+1;
end;
end loop;
else
B(procdate); end if;
exception when others then
dbms_output.put_line('ERROR'));
end ;
/
碰到一个奇怪问题,A总是没有达到我的预想目的,每次最多执行12天就结束了,可是却没有异常抛出,请高手救命啊。
create or replace procedure a(procdate date) is p_date date;
begin if trunc(procdate,'dd')=trunc(last_day(procdate),'dd') then p_date:=add_months(last_day(procdate)+1,-1); while trunc(p_date,'dd')<=trunc(procdate,'dd') loop
begin B(p_date);
p_date:=p_date+1;
end;
end loop;
else
B(procdate); end if;
exception when others then
dbms_output.put_line('ERROR'));
end ;
/
v_portion number(5,2);
v_recdate char(8) := to_char(procdate,'yyyymmdd');
v_branchcode char(16);
v_cust_no varchar2(32) ;
v_acct_no CHAR(32) ;
v_subacct CHAR(32) ;
v_openbrc CHAR(9);
v_profitbrc CHAR(9);
v_cust_type char(1);
v_prd_type CHAR(7);
v_ccy CHAR(3);
v_subcode CHAR(10);
v_accstat char(1);
v_marginflg char(1);
v_xdflg char(1);
v_dhflg char(1);
v_intplanno char(3);
v_floatflg char(1);
v_floatrate number(10,6);
v_khrq DATE;
v_dqrq DATE;
v_xhrq DATE;
v_bal number(17,2);
v_rmb_bal number(17,2);
v_usd_bal number(17,2);
v_CB_type VARCHAR2(10);
v_count number;
cursor t_cur is
select a.cust_no,a.acct_no,a.subacct,a.openbrc,a.profitbrc,a.cust_type,a.prd_type,a.ccy,a.subcode,a.accstat,a.marginflg,a.xdflg,a.dhflg,
a.intplanno,a.floatflg,a.floatrate,a.khrq,a.dqrq,a.xhrq,a.bal,a.rmb_bal,a.usd_bal,a.cb_type,upper(b.username),b.portion
from cb_deposit_acct a,cb_deposit_portion b where recdate=v_recdate and a.subacct=b.subacct and b.end_time>procdate;
begin delete from cb_deposit_Dist where recdate=v_recdate;
commit;
select count(*) into v_count from cb_deposit_acct a,cb_deposit_portion b where recdate=v_recdate and a.subacct=b.subacct and b.end_time>procdate;
if v_count>0 then
open t_cur;
loop
begin
fetch t_cur into
v_cust_no,v_acct_no,v_subacct,v_openbrc,v_profitbrc,v_cust_type,v_prd_type,v_ccy,v_subcode,v_accstat,v_marginflg,v_xdflg,v_dhflg,
v_intplanno,v_floatflg,v_floatrate,v_khrq,v_dqrq,v_xhrq,v_bal,v_rmb_bal,v_usd_bal,v_cb_type,v_username,v_portion;
exit when t_cur%notfound; p_get_deptid(v_username,v_branchcode);
p_status:=40;
insert into CB_Deposit_Dist values(v_recdate,v_cust_no,v_acct_no,v_subacct,v_openbrc,v_profitbrc,v_branchcode,v_username,v_cust_type,
v_prd_type,v_ccy,v_subcode,v_accstat,v_marginflg,v_xdflg,v_dhflg,v_intplanno,v_floatflg,v_floatrate,v_khrq,v_dqrq,v_xhrq,
v_bal*v_portion/100,v_rmb_bal*v_portion/100,v_usd_bal*v_portion/100,v_cb_type); end;
end loop;
commit;
close t_cur;
end if;
exception when others then
dbms_output.put_line('ERROR');end ;
/
而且这两个过程我都加了调试语句,每次都是执行了10天,或者11天,A就退出了
我试过,如果将A中的调用B(p_date)注释掉,过称执行正常。
所以我定位在B,可是我怎么也找不到问题,而且B也没抛出异常啊
while trunc(p_date,'dd')<=trunc(procdate,'dd') loop
begin
dbms_output.put_line('pdate is:'||to_char(p_date,'yyyy-mm-dd'));
B(p_date);
p_date:=p_date+1;
end;
end loop;
在A中加一调试信息,每次到11号或12号就结束了,异常退出。
dbms_output.put_line(sqlcode||' '||sqlerrm));
while trunc(p_date,'dd')<=trunc(procdate,'dd') loop
begin
--B(p_date);
dbms_output.put_line(to_char(p_date));
p_date:=p_date+1;
end;
end loop;
可能造成循环逻辑错误。