Oracle存储过程--案例
限额控制
CREATE OR REPLACE PACKAGE BODY NP_PCKG_MERCHANT_LIMIT AS PROCEDURE CHECK_LIMIT ( in_iplCode IN VARCHAR2, --行业编号 in_iplState IN VARCHAR2, --卡类型 in_posNo IN VARCHAR2, --商户号 in_tranAmt IN VARCHAR2, --交易金额 out_retcode OUT VARCHAR2 --返回码 ) IS v_date VARCHAR2(8); --系统日期 --v_merchantType VARCHAR2(30); --行业类型 v_debitSingleMax NUMBER(18,2); --借记卡单笔限额 v_debitDayMax NUMBER(18,2); --借记卡日累计限额 v_debitMonthMax NUMBER(18,2); --借记卡月累计限额 v_debitYearMax NUMBER(18,2); --借记卡年累计限额 v_debitDaySum NUMBER(18,2); --借记卡日累计限额当日发生额 v_debitMonthSum NUMBER(18,2); --借记卡月累计限额当月发生额 v_debitYearSum NUMBER(18,2); --借记卡年累计限额当年发生额 v_debitLastDate NUMBER(18,2); --借记卡限额上次交易日 v_creditSingleMax NUMBER(18,2); --贷记卡单笔限额 v_creditDayMax NUMBER(18,2); --贷记卡日累计限额 v_creditMonthMax NUMBER(18,2); --贷记卡月累计限额 v_creditYearMax NUMBER(18,2); --贷记卡年累计限额 v_creditDaySum NUMBER(18,2); --贷记卡日累计限额当日发生额 v_creditMonthSum NUMBER(18,2); --贷记卡月累计限额当月发生额 v_creditYearSum NUMBER(18,2); --贷记卡年累计限额当年发生额 v_creditLastDate NUMBER(18,2); --贷记卡限额上次交易日 --v_stt VARCHAR2(1); --账户状态 BEGIN out_retcode := '0'; --取当前日期 v_date := to_char(SYSDATE, 'yyyymmdd'); ---------------------------------------------------------------- -- 判断商户收单限额(设置的客商户日累计限额) ---------------------------------------------------------------- --取收单限额 也要区分借记卡 和 贷记卡 IF in_iplState = 1 THEN --是借记卡 BEGIN SELECT IPL_DEBIT_SINGLE,IPL_DEBIT_DAYMAX,IPL_DEBIT_MONTHMAX,IPL_DEBIT_YEARMAX INTO v_debitSingleMax,v_debitDayMax,v_debitMonthMax,v_debitYearMax FROM IM_PAY_LIMIT WHERE IPL_CODE = in_iplCode AND --此处需要一个参数，行业的code IPL_STATE in('00','01') ; --先判断限额状态 00标示 如果是借记卡和贷记卡都可以用，或者借记卡和贷记卡只有一个能用，或者都不能用 EXCEPTION WHEN NO_DATA_FOUND THEN NULL; dbms_output.put_line(v_debitSingleMax); END; ELSIF in_iplState = 2 THEN --是贷记卡 BEGIN SELECT IPL_CREDIT_SINGLE,IPL_CREDIT_DAYMAX,IPL_CREDIT_MONTHMAX,IPL_CREDIT_YEARMAX INTO v_creditSingleMax,v_creditDayMax,v_creditMonthMax,v_creditYearMax FROM IM_PAY_LIMIT WHERE IPL_CODE = in_iplCode AND --此处需要一个参数，行业的code IPL_STATE in('00','10'); --先判断限额状态 00标示 如果是借记卡和贷记卡都可以用，或者借记卡和贷记卡只有一个能用，或者都不能用 EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; --取商户年月日累计限额 --- BEGIN SELECT IMD_DEBIT_DAYAMT, IMD_DEBIT_MONTHAMT, IMD_DEBIT_YEARAMT, IMD_DEBIT_TRANSDAY, IMD_CREDIT_DAYAMT, IMD_CREDIT_MONTHAMT, IMD_CREDIT_YEARAMT, IMD_CREDIT_TRANSDAY INTO v_debitDaySum, v_debitMonthSum, v_debitYearSum, v_debitLastDate, v_creditDaySum, v_creditMonthSum, v_creditYearSum, v_creditLastDate FROM IM_MERCHANT_DAYSUM WHERE IMD_POSNO = in_posNo; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO IM_MERCHANT_DAYSUM (IMD_POSNO,IMD_DEBIT_DAYAMT,IMD_DEBIT_MONTHAMT,IMD_DEBIT_YEARAMT,IMD_CREDIT_DAYAMT,IMD_CREDIT_MONTHAMT,IMD_CREDIT_YEARAMT,IMD_DEBIT_TRANSDAY,IMD_CREDIT_TRANSDAY) VALUES (in_posNo,'0.00','0.00','0.00','0.00','0.00','0.00',v_date,v_date); END; --判断是商户借记卡 =1 还是贷记卡 =2 START IF in_iplState = 1 THEN --如果是借记卡，则判断借记卡的单笔，日累计，月累计，年累计限额 v_debitSingleMax IF TO_NUMBER(in_tranAmt) > v_debitSingleMax THEN out_retcode := 'NPML1001'; --错误码NPML1001:超过借记卡单笔限额 --ROLLBACK; RETURN; END IF; -- 判断借记卡日累计交易限额否需要清零 ，如果是昨天的交易，今天需要清零 开始 IF v_date <> v_debitLastDate THEN --here v_debitDaySum := 0; END IF; -- 判断借记卡日累计交易限额否需要清零 ，如果是昨天的交易，今天需要清零 结束 IF v_debitDaySum + TO_NUMBER(in_tranAmt) > v_debitDayMax THEN --借记卡日累计限额 out_retcode := 'NPML1002'; --错误码NPML1002:超过借记卡日累计限额 --ROLLBACK; RETURN; END IF; --判断借记卡月累计交易限额是否需要清零，如果是上个月的交易，则临时置空 开始 IF substr(v_date,1,6) <> substr(v_debitLastDate,1,6) THEN v_debitMonthSum := 0; END IF; --判断借记卡月累计交易限额是否需要清零，如果是上个月的交易，则临时置空 结束 IF v_debitMonthSum + TO_NUMBER(in_tranAmt) > v_debitMonthMax THEN --借记卡月累计限额 out_retcode := 'NPML1003'; --错误码NPML1003:超过借记卡月累计限额 --ROLLBACK; RETURN; END IF; --判断借记卡年累计交易限额是否需要清零，如果是去年的交易，则临时置空 开始 IF substr(v_date,1,4) <> substr(v_debitLastDate,1,4) THEN v_debitYearSum := 0; END IF; --判断借记卡年累计交易限额是否需要清零，如果是去年的交易，则临时置空 结束 IF v_debitYearSum + TO_NUMBER(in_tranAmt) > v_debitYearMax THEN --借记卡年累计限额 out_retcode := 'NPML1004'; --错误码NPML1004:超过借记卡年累计限额 --ROLLBACK; RETURN; END IF; ELSIF in_iplState = 2 THEN --如果是贷记卡 IF TO_NUMBER(in_tranAmt) > v_creditSingleMax THEN out_retcode := 'NPML1005'; --错误码NPML1005:超过贷记卡单笔限额 --ROLLBACK; RETURN; END IF; -- 判断贷记卡日累计交易限额是否需要清零 开始 IF v_date <> v_creditLastDate THEN --here v_creditDaySum := 0; END IF; -- 判断贷记卡日累计交易限额是否需要清零 结束 IF v_creditDaySum + TO_NUMBER(in_tranAmt) > v_creditDayMax THEN --贷记卡日累计限额 out_retcode := 'NPML1006'; --错误码NPML1006:超过贷记卡日累计限额 --ROLLBACK; RETURN; END IF; --判断贷记卡月累计交易限额是否需要清零，如果是上个月的交易，则临时置空 开始 IF substr(v_date,1,6) <> substr(v_creditLastDate,1,6) THEN v_creditMonthSum := 0; END IF; --判断贷记卡月累计交易限额是否需要清零，如果是上个月的交易，则临时置空 结束 IF v_creditMonthSum + TO_NUMBER(in_tranAmt) > v_creditMonthMax THEN --贷记卡月累计限额 out_retcode := 'NPML1007'; --错误码NPML1007:超过贷记卡月累计限额 --ROLLBACK; RETURN; END IF; --判断贷记卡年累计交易限额是否需要清零，如果是去年的交易，则临时置空 开始 IF substr(v_date,1,4) <> substr(v_creditLastDate,1,4) THEN v_creditYearSum := 0; END IF; --判断贷记卡年累计交易限额是否需要清零，如果是去年的交易，则临时置空 结束 IF v_creditYearSum + TO_NUMBER(in_tranAmt) > v_creditYearMax THEN --贷记卡月累计限额 out_retcode := 'NPML1008'; --错误码NPML1008:超过贷记卡年累计限额 --ROLLBACK; RETURN; END IF; END IF; --判断是商户借记卡 =1 还是贷记卡 =2 END COMMIT; END; PROCEDURE UPDATE_LIMIT ( in_posNo IN VARCHAR2, --商户号 in_iplState IN VARCHAR2, --卡类型 in_tranAmt IN VARCHAR2, --交易金额 out_retcode OUT VARCHAR2 --返回码 ) IS v_date VARCHAR2(8); --系统日期 v_debitDaySum NUMBER(18,2); --借记卡日累计限额当日发生额 v_debitMonthSum NUMBER(18,2); --借记卡月累计限额当月发生额 v_debitYearSum NUMBER(18,2); --借记卡年累计限额当年发生额 v_debitLastDate NUMBER(18,2); --借记卡限额上次交易日 v_creditDaySum NUMBER(18,2); --贷记卡日累计限额当日发生额 v_creditMonthSum NUMBER(18,2); --贷记卡月累计限额当月发生额 v_creditYearSum NUMBER(18,2); --贷记卡年累计限额当年发生额 v_creditLastDate NUMBER(18,2); --贷记卡限额上次交易日 BEGIN out_retcode := '0'; --取当前日期 v_date := to_char(SYSDATE, 'yyyymmdd'); --取客户日累计限额 BEGIN SELECT IMD_DEBIT_DAYAMT, IMD_DEBIT_MONTHAMT, IMD_DEBIT_YEARAMT, IMD_CREDIT_DAYAMT, IMD_CREDIT_MONTHAMT, IMD_CREDIT_YEARAMT INTO v_debitDaySum, v_debitMonthSum, v_debitYearSum, v_creditDaySum, v_creditMonthSum, v_creditYearSum FROM IM_MERCHANT_DAYSUM WHERE IMD_POSNO = in_posNo FOR UPDATE; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; --判断借记卡日累计交易限额否需要清零 IF v_date <> v_debitLastDate THEN v_debitDaySum := 0; END IF; -- 判断贷记卡日累计交易限额是否需要清零 IF v_date <> v_creditLastDate THEN --here v_creditDaySum := 0; END IF; --判断借记卡月累计交易限额否需要清零 IF substr(v_date,1,6) <> substr(v_debitLastDate,1,6) THEN v_debitMonthSum := 0; END IF; --判断贷记卡月累计交易限额否需要清零 IF substr(v_date,1,6) <> substr(v_creditLastDate,1,6) THEN v_creditMonthSum := 0; END IF; --判断借记卡年累计交易限额否需要清零 IF substr(v_date,1,4) <> substr(v_debitLastDate,1,4) THEN v_debitYearSum := 0; END IF; --判断贷记卡年累计交易限额否需要清零 IF substr(v_date,1,4) <> substr(v_creditLastDate,1,4) THEN v_creditYearSum := 0; END IF; --更新商户收单限额累计表 需要区分借记卡和贷记卡 IF in_iplState = 1 THEN --借记卡 UPDATE IM_MERCHANT_DAYSUM --更新借记卡收单累计限额 SET IMD_DEBIT_DAYAMT = v_debitDaySum + TO_NUMBER(in_tranAmt), IMD_DEBIT_MONTHAMT = v_debitMonthSum + TO_NUMBER(in_tranAmt), IMD_DEBIT_YEARAMT = v_debitYearSum + TO_NUMBER(in_tranAmt), IMD_DEBIT_TRANSDAY = v_date WHERE IMD_POSNO = in_posNo; COMMIT; ELSIF in_iplState = 2 THEN --贷记卡 UPDATE IM_MERCHANT_DAYSUM --更新贷记卡收单累计限额 SET IMD_CREDIT_DAYAMT = v_creditDaySum + TO_NUMBER(in_tranAmt), IMD_CREDIT_MONTHAMT = v_creditMonthSum + TO_NUMBER(in_tranAmt), IMD_CREDIT_YEARAMT = v_creditYearSum + TO_NUMBER(in_tranAmt), IMD_CREDIT_TRANSDAY = v_date WHERE IMD_POSNO = in_posNo; COMMIT; END IF; END; PROCEDURE ROLL_LIMIT ( in_posNo IN VARCHAR2, --商户号 in_iplState IN VARCHAR2, --卡类型 in_orderNo IN VARCHAR2, --交易流水号 out_retcode OUT VARCHAR2 --存储过程返回码 ) IS v_amt VARCHAR2(20); v_transDate VARCHAR2(8); v_nowDate VARCHAR2(8); BEGIN out_retcode:='0'; v_nowDate:=to_char(SYSDATE, 'yyyymmdd'); --取客户日累计限额 BEGIN SELECT substr(NPF_TRAN_TIME,1,8), NPF_ORDER_AMT INTO v_transDate, v_amt FROM NP_PAY_FLOW WHERE NPF_FLOWNO = in_orderNo FOR UPDATE; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; IF v_transDate ='' THEN out_retCode:='0'; return; END IF; --IF v_transDate<> v_nowDate then--如果不是今天的指令，不处理当日的限额 -- out_retCode:='0'; -- return; --END IF; --查找到该订单，并且回滚的是今天的订单 --如果是借记卡交易 -- IF in_iplState = 1 then --回滚借记卡 IF v_transDate = v_nowDate then--如果交易日期小于今天当天日期，则判断是否为本月的交易， 回滚当月和当年的 UPDATE IM_MERCHANT_DAYSUM--更新当天、本月、本年的累计额度 SET IMD_DEBIT_DAYAMT = IMD_DEBIT_DAYAMT - v_amt,--更新当日的限额 IMD_DEBIT_MONTHAMT = IMD_DEBIT_MONTHAMT - v_amt,--更新本月的限额 IMD_DEBIT_YEARAMT = IMD_DEBIT_YEARAMT - v_amt--更新本年的限额 WHERE IMD_POSNO = in_posNo;--商户号 COMMIT; ELSIF v_transDate < v_nowDate then --如果交易时间不是今天，则判断是否为本月的交易 IF substr(v_transDate,1,6) = substr(v_nowDate,1,6) THEN--不是当天的交易，则判断是否为本月的交易START UPDATE IM_MERCHANT_DAYSUM--更新本月、本年的交易 SET IMD_DEBIT_MONTHAMT = IMD_DEBIT_MONTHAMT - v_amt,--更新本月的限额 IMD_DEBIT_YEARAMT = IMD_DEBIT_YEARAMT - v_amt --更新本年的限额 WHERE IMD_POSNO = in_posNo;--商户号 COMMIT; ELSIF substr(v_transDate,1,6) < substr(v_nowDate,1,6) THEN--不是当天的交易，也不是本月的交易，则判断是否为本年的交易 IF substr(v_transDate,1,4) = substr(v_nowDate,1,4) THEN--不是当天的交易，也不是本月的交易，是本年的交易 UPDATE IM_MERCHANT_DAYSUM--更新本年的交易 SET IMD_DEBIT_YEARAMT = IMD_DEBIT_YEARAMT - v_amt--更新本年的限额 WHERE IMD_POSNO = in_posNo;--商户号 COMMIT; END IF; END IF;--不是当天的交易，则判断是否为本月的交易END END IF;--回滚借记卡结束 ELSIF in_iplState = 2 then --回滚贷记卡 IF v_transDate = v_nowDate then--如果交易日期小于今天当天日期，则判断是否为本月的交易， 回滚当月和当年的 UPDATE IM_MERCHANT_DAYSUM--更新当天、本月、本年的累计额度 SET IMD_CREDIT_DAYAMT = IMD_CREDIT_DAYAMT - v_amt,--更新当日的限额 IMD_CREDIT_MONTHAMT = IMD_CREDIT_DAYAMT - v_amt,--更新本月的限额 IMD_CREDIT_YEARAMT = IMD_CREDIT_YEARAMT - v_amt--更新本年的限额 WHERE IMD_POSNO = in_posNo;--商户号 COMMIT; ELSIF v_transDate < v_nowDate then --如果交易时间不是今天，则判断是否为本月的交易 IF substr(v_transDate,1,6) = substr(v_nowDate,1,6) THEN--不是当天的交易，则判断是否为本月的交易START UPDATE IM_MERCHANT_DAYSUM--更新本月、本年的交易 SET IMD_CREDIT_MONTHAMT = IMD_CREDIT_MONTHAMT - v_amt,--更新本月的限额 IMD_CREDIT_YEARAMT = IMD_CREDIT_YEARAMT - v_amt--更新本年的限额 WHERE IMD_POSNO = in_posNo;--商户号 COMMIT; ELSIF substr(v_transDate,1,6) < substr(v_nowDate,1,6) THEN--不是当天的交易，也不是本月的交易，则判断是否为本年的交易 IF substr(v_transDate,1,4) = substr(v_nowDate,1,4) THEN--不是当天的交易，也不是本月的交易，是本年的交易 UPDATE IM_MERCHANT_DAYSUM--更新本年的交易 SET IMD_CREDIT_YEARAMT = IMD_CREDIT_YEARAMT - v_amt --更新本年的限额 WHERE IMD_POSNO = in_posNo;--商户号 COMMIT; END IF; END IF;--不是当天的交易，则判断是否为本月的交易END END IF;--回滚借记卡结束 END IF;--回滚借记卡、贷记卡结束 -- END; END NP_PCKG_MERCHANT_LIMIT;
表结构:
