一段存储过程，请大家帮忙优化
本帖最后由 zhangenming20080324 于 2010-05-07 16:21:23 编辑
CREATE PROCEDURE "loan".getpayschedule(dat date)
returning varchar(255);
define returnstr varchar(20);
define v_duebillno varchar(20);--借据号
define v_maxcurrentno INTEGER;--最大期次
define v_duebno varchar(20);
define v_sq INTEGER;
define v_paydate varchar(20);
define v_maturedate varchar(10);
define count1 int;
define v_gitid int;--自动id
define v_currentnosc int;--提前还款上一次期次
define v_gitid1 int;
define sql_err,isam_err int;--异常处理
SET DEBUG FILE TO "gittestcom.txt";
let sql_err = 0;
TRACE ON;
begin
on exception set sql_err
delete from Acc_PayScheduleTmp;
delete from acc_payschedule;
trace "sql_err--->"||sql_err;
return "0";
end exception with resume ;
delete from Acc_PayScheduleTmp;
delete from acc_payschedule;
--从核心中间表导入所需数据岛临时表
-- insert into Acc_PayScheduleTmp select ap.*,TO_CHAR(today,'%Y%m%d')||seqhkjh.NEXTVAL from Acc_PaySchedule1 ap,loanduebill ld where ap.duebillno=ld.duebillno;
insert into Acc_PayScheduleTmp select contractno,duebillno,currentno,maturedate,currentsum,currentinterest,currentnormalinterest,currentoverdueinterest,currentoffsheetinterest,payoption,paydate,paysum,payinterest,currency,hexinappid,TO_CHAR(today,'%m%d')||seqhkjh.NEXTVAL from Acc_PaySchedule1 where duebillno in('000001000096011001','000001000167011001');
foreach select duebillno into v_duebillno from Acc_PayScheduleTmp
--开始轮循单条借据
let count1=0;
foreach select duebillno,currentno,paydate,maturedate,gitid into v_duebno,v_sq,v_paydate,v_maturedate,v_gitid from Acc_PayScheduleTmp where duebillno=v_duebillno
if(v_sq==2) then--提前还款从2开始
let count1=count1+1;
if(count1==2) then --如果第二次提前还款跟新状态
select max(currentno) into v_maxcurrentno from Acc_PayScheduleTmp where duebillno=v_duebno and currentno>=2 and gitid<v_gitid;
foreach select currentno,gitid into v_currentnosc,v_gitid1 from Acc_PayScheduletmp where duebillno=v_duebno and gitid >v_gitid-1
update Acc_PayScheduleTmp set currentno=v_maxcurrentno+v_currentnosc-1 where duebillno=v_duebillno and gitid=v_gitid1;
end foreach
end if
end if
end foreach
end foreach
insert into acc_payschedule select contractno,duebillno,currentno,maturedate,currentsum,currentinterest,currentnormalinterest,currentoverdueinterest,currentoffsheetinterest,payoption,paydate,paysum,payinterest,currency,hexinappid from acc_payscheduleTmp;
let returnstr="1";
end
TRACE OFF;
return returnstr;
end procedure;
