如何用oracle存储过程实现输入金额,期限和年利率给出等额本息的结果呢?
下面这个存储过程帮我们实现了,如下:
CREATE OR REPLACE PROCEDURE BIZ_GJL_DEBX(
iouCode in varchar2,
loanAmount in NUMBER,
yearRate in NUMBER,
periondMonth in NUMBER
)
IS
period_unit number;
monthLixi number;
monthRepayAmountFinal number;
monthLixiReprical number;
aaa number;
bbb number;
monthRepayAmount number;
instenstSum number;
ipSum number;
monthRate number;
BEGIN
period_unit:=0;--遍历的期数
--月利率
monthRate:=yearRate/12;
aaa:=loanAmount*monthRate;
bbb:=(1-1/(power(1+monthRate,periondMonth)));
---月还金额
monthRepayAmount:=aaa/bbb;
---利息总额
instenstSum:= ROUND(monthRepayAmount*periondMonth-loanAmount,2);
---本息和
ipSum:=ROUND(monthRepayAmount*periondMonth,2);
---月还金额保留两位小数
monthRepayAmountFinal:=ROUND(monthRepayAmount,2);
---每月利息
--monthLixi:= ROUND(instenstSum/(periondMonth),2);
---每月本金
--monthBenjin:= monthRepayAmountFinal-monthLixi;
For period_unit in 1..periondMonth LOOP
----每月应还利息
monthLixi:=Round(((loanAmount*monthRate-monthRepayAmountFinal)*power(1+monthRate,period_unit-1)+monthRepayAmountFinal),2);
----每月应还本息之和monthRepayAmountFinal
-----每月应还本金monthRepayAmountFinal-monthLixi
monthLixiReprical:=monthRepayAmountFinal-monthLixi;
insert into debx values(iouCode,monthRepayAmountFinal,monthLixiReprical,monthLixi);
end loop;
END BIZ_GJL_DEBX;
表debx的表结构如下:
create table debx(
iouCode varchar2(200),---借据号
amount number(19,2), ---每月还的钱
bj number(19,2),---每月应还本金
lx number(19,2)---每月应还利息
)
调用的时候如下:(注意第二个参数30000代表的是借款金额,年利率的单位是 除以%以后的值,比如年利率4.75% 这里第三个参数要输入0.0475。 第四个参数期限是月这里是17个月)
call BIZ_GJL_DEBX('11111',30000,0.0475,17);
结果如下图:
和网上用计算器算出来的效果一样:
下面这个是工作中的掺杂业务参数的逻辑,可以不看:
在实际工作中,我们要根据本金,期数,年利率来计算出等额本息的还款计划,下面给出存储过程的实现,如下:
CREATE OR REPLACE PROCEDURE BIZ_DEBX_REPAY_PLAN
IS
CURSOR C_MAIN IS --声明显式游标
SELECT t.*,iou.year_rate,iou.apply_period from v_fdbs t left join t_ba_iou iou on t.iou_code=iou.iou_code;
C_MAIN_ROW C_MAIN%ROWTYPE;
periondYear number;
monthRepayAmount number;
monthRepayAmountFinal number;
monthRate number;
instenstSum number;
ipSum number;
monthLixi number;
monthBenjin number;
iouCode varchar2(50);
fdAmount number;
periondMonth1 number;
yearRate1 number;
aaa number;
bbb number;
period_unit number;
BEGIN
FOR
C_MAIN_ROW IN C_MAIN LOOP
iouCode:=C_MAIN_ROW.IOU_CODE;---借据号
fdAmount:=C_MAIN_ROW.FD_AMOUNT;---放款金额
periondMonth1:=C_MAIN_ROW.apply_period;---期数月
yearRate1:=C_MAIN_ROW.Year_Rate;---年利率 比如0.26
period_unit:=0;--遍历的期数
--月利率
monthRate:=yearRate1/12;
aaa:=fdAmount*monthRate;
bbb:=(1-1/(power(1+monthRate,periondMonth1)));
---月还金额
monthRepayAmount:=aaa/bbb;
---利息总额
instenstSum:= ROUND(monthRepayAmount*periondMonth1-fdAmount,2);
---本息和
ipSum:=ROUND(monthRepayAmount*periondMonth1,2);
---月还金额保留两位小数
monthRepayAmountFinal:=ROUND(monthRepayAmount,2);
For period_unit in 1..periondMonth1 LOOP
----每个月还的利息
monthLixi:=Round(((fdAmount*monthRate-monthRepayAmountFinal)*power(1+monthRate,period_unit-1)+monthRepayAmountFinal),2);
----每个月还的本金
monthRepayAmountFinal-monthLixi;
end LOOP;
end loop;
END BIZ_DEBX_REPAY_PLAN;