Oracle存儲過程--案例

限額控制

CREATE OR REPLACE PACKAGE BODY NP_PCKG_MERCHANT_LIMIT
ASPROCEDURE CHECK_LIMIT (in_iplCode         IN   VARCHAR2,       --行業編號in_iplState        IN   VARCHAR2,       --卡類型in_posNo           IN   VARCHAR2,      --商戶號in_tranAmt         IN   VARCHAR2,      --交易金額out_retcode        OUT  VARCHAR2      --返回碼
  )ISv_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);      --賬戶狀態BEGINout_retcode := '0';--取當前日期v_date := to_char(SYSDATE, 'yyyymmdd');------------------------------------------------------------------ 判斷商戶收單限額(設置的客商戶日累計限額)------------------------------------------------------------------取收單限額 也要區分借記卡 和 貸記卡IF in_iplState = 1 THEN --是借記卡BEGINSELECTIPL_DEBIT_SINGLE,IPL_DEBIT_DAYMAX,IPL_DEBIT_MONTHMAX,IPL_DEBIT_YEARMAXINTOv_debitSingleMax,v_debitDayMax,v_debitMonthMax,v_debitYearMaxFROMIM_PAY_LIMITWHEREIPL_CODE = in_iplCode AND --此處需要一個參數，行業的codeIPL_STATE in('00','01') ;  --先判斷限額狀態 00標示  如果是借記卡和貸記卡都可以用，或者借記卡和貸記卡只有一個能用，或者都不能用
EXCEPTIONWHEN NO_DATA_FOUND THENNULL;dbms_output.put_line(v_debitSingleMax);END;ELSIF in_iplState = 2 THEN --是貸記卡BEGINSELECTIPL_CREDIT_SINGLE,IPL_CREDIT_DAYMAX,IPL_CREDIT_MONTHMAX,IPL_CREDIT_YEARMAXINTOv_creditSingleMax,v_creditDayMax,v_creditMonthMax,v_creditYearMaxFROMIM_PAY_LIMITWHEREIPL_CODE = in_iplCode AND --此處需要一個參數，行業的codeIPL_STATE in('00','10');  --先判斷限額狀態 00標示  如果是借記卡和貸記卡都可以用，或者借記卡和貸記卡只有一個能用，或者都不能用
EXCEPTIONWHEN NO_DATA_FOUND THENNULL;END;END IF;--取商戶年月日累計限額 ---BEGINSELECTIMD_DEBIT_DAYAMT,IMD_DEBIT_MONTHAMT,IMD_DEBIT_YEARAMT,IMD_DEBIT_TRANSDAY,IMD_CREDIT_DAYAMT,IMD_CREDIT_MONTHAMT,IMD_CREDIT_YEARAMT,IMD_CREDIT_TRANSDAYINTOv_debitDaySum,v_debitMonthSum,v_debitYearSum,v_debitLastDate,v_creditDaySum,v_creditMonthSum,v_creditYearSum,v_creditLastDateFROMIM_MERCHANT_DAYSUMWHEREIMD_POSNO = in_posNo;EXCEPTIONWHEN NO_DATA_FOUND THENINSERT 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  STARTIF in_iplState = 1 THEN--如果是借記卡，則判斷借記卡的單筆，日累計，月累計，年累計限額  v_debitSingleMaxIF TO_NUMBER(in_tranAmt) > v_debitSingleMax THENout_retcode := 'NPML1001';                    --錯誤碼NPML1001:超過借記卡單筆限額--ROLLBACK;RETURN;END IF;-- 判斷借記卡日累計交易限額否需要清零 ，如果是昨天的交易，今天需要清零 開始IF v_date <> v_debitLastDate THEN --herev_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) THENv_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) THENv_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 THENout_retcode := 'NPML1005';                    --錯誤碼NPML1005:超過貸記卡單筆限額--ROLLBACK;RETURN;END IF;-- 判斷貸記卡日累計交易限額是否需要清零 開始IF v_date <> v_creditLastDate THEN --herev_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) THENv_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) THENv_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  ENDCOMMIT;END;PROCEDURE UPDATE_LIMIT (in_posNo               IN    VARCHAR2,            --商戶號in_iplState            IN    VARCHAR2,            --卡類型in_tranAmt             IN    VARCHAR2,            --交易金額out_retcode            OUT   VARCHAR2            --返回碼
    )ISv_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);   --貸記卡限額上次交易日BEGINout_retcode := '0';--取當前日期v_date := to_char(SYSDATE, 'yyyymmdd');--取客戶日累計限額BEGINSELECTIMD_DEBIT_DAYAMT,IMD_DEBIT_MONTHAMT,IMD_DEBIT_YEARAMT,IMD_CREDIT_DAYAMT,IMD_CREDIT_MONTHAMT,IMD_CREDIT_YEARAMTINTOv_debitDaySum,v_debitMonthSum,v_debitYearSum,v_creditDaySum,v_creditMonthSum,v_creditYearSumFROMIM_MERCHANT_DAYSUMWHEREIMD_POSNO = in_posNoFOR UPDATE;EXCEPTIONWHEN NO_DATA_FOUND THENNULL;END;--判斷借記卡日累計交易限額否需要清零IF v_date <> v_debitLastDate THENv_debitDaySum := 0;END IF;-- 判斷貸記卡日累計交易限額是否需要清零IF v_date <> v_creditLastDate THEN --herev_creditDaySum := 0;END IF;--判斷借記卡月累計交易限額否需要清零IF substr(v_date,1,6) <> substr(v_debitLastDate,1,6) THENv_debitMonthSum := 0;END IF;--判斷貸記卡月累計交易限額否需要清零IF substr(v_date,1,6) <> substr(v_creditLastDate,1,6) THENv_creditMonthSum := 0;END IF;--判斷借記卡年累計交易限額否需要清零IF substr(v_date,1,4) <> substr(v_debitLastDate,1,4) THENv_debitYearSum := 0;END IF;--判斷貸記卡年累計交易限額否需要清零IF substr(v_date,1,4) <> substr(v_creditLastDate,1,4) THENv_creditYearSum := 0;END IF;--更新商戶收單限額累計表    需要區分借記卡和貸記卡IF in_iplState = 1 THEN --借記卡UPDATE IM_MERCHANT_DAYSUM --更新借記卡收單累計限額SETIMD_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_dateWHEREIMD_POSNO = in_posNo;COMMIT;ELSIF in_iplState = 2 THEN --貸記卡UPDATE IM_MERCHANT_DAYSUM --更新貸記卡收單累計限額SETIMD_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_dateWHEREIMD_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      --存儲過程返回碼
  )ISv_amt     VARCHAR2(20);v_transDate VARCHAR2(8);v_nowDate   VARCHAR2(8);BEGINout_retcode:='0';v_nowDate:=to_char(SYSDATE, 'yyyymmdd');--取客戶日累計限額BEGINSELECTsubstr(NPF_TRAN_TIME,1,8),NPF_ORDER_AMTINTOv_transDate,v_amtFROMNP_PAY_FLOWWHERENPF_FLOWNO = in_orderNoFOR UPDATE;EXCEPTIONWHEN NO_DATA_FOUND THENNULL;END;IF v_transDate ='' THENout_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--更新當天、本月、本年的累計額度SETIMD_DEBIT_DAYAMT = IMD_DEBIT_DAYAMT - v_amt,--更新當日的限額IMD_DEBIT_MONTHAMT = IMD_DEBIT_MONTHAMT - v_amt,--更新本月的限額IMD_DEBIT_YEARAMT = IMD_DEBIT_YEARAMT - v_amt--更新本年的限額WHEREIMD_POSNO = in_posNo;--商戶號COMMIT;ELSIF v_transDate < v_nowDate then --如果交易時間不是今天，則判斷是否為本月的交易IF substr(v_transDate,1,6) = substr(v_nowDate,1,6) THEN--不是當天的交易，則判斷是否為本月的交易STARTUPDATE IM_MERCHANT_DAYSUM--更新本月、本年的交易SETIMD_DEBIT_MONTHAMT = IMD_DEBIT_MONTHAMT - v_amt,--更新本月的限額IMD_DEBIT_YEARAMT = IMD_DEBIT_YEARAMT - v_amt --更新本年的限額WHEREIMD_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--更新本年的交易SETIMD_DEBIT_YEARAMT = IMD_DEBIT_YEARAMT - v_amt--更新本年的限額WHEREIMD_POSNO = in_posNo;--商戶號COMMIT;END IF;END IF;--不是當天的交易，則判斷是否為本月的交易ENDEND IF;--回滾借記卡結束ELSIF in_iplState = 2 then  --回滾貸記卡IF v_transDate = v_nowDate then--如果交易日期小于今天當天日期，則判斷是否為本月的交易，    回滾當月和當年的UPDATE IM_MERCHANT_DAYSUM--更新當天、本月、本年的累計額度SETIMD_CREDIT_DAYAMT = IMD_CREDIT_DAYAMT - v_amt,--更新當日的限額IMD_CREDIT_MONTHAMT = IMD_CREDIT_DAYAMT - v_amt,--更新本月的限額IMD_CREDIT_YEARAMT = IMD_CREDIT_YEARAMT - v_amt--更新本年的限額WHEREIMD_POSNO = in_posNo;--商戶號COMMIT;ELSIF v_transDate < v_nowDate then --如果交易時間不是今天，則判斷是否為本月的交易IF substr(v_transDate,1,6) = substr(v_nowDate,1,6) THEN--不是當天的交易，則判斷是否為本月的交易STARTUPDATE IM_MERCHANT_DAYSUM--更新本月、本年的交易SETIMD_CREDIT_MONTHAMT = IMD_CREDIT_MONTHAMT - v_amt,--更新本月的限額IMD_CREDIT_YEARAMT = IMD_CREDIT_YEARAMT - v_amt--更新本年的限額WHEREIMD_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--更新本年的交易SETIMD_CREDIT_YEARAMT = IMD_CREDIT_YEARAMT - v_amt --更新本年的限額WHEREIMD_POSNO = in_posNo;--商戶號COMMIT;END IF;END IF;--不是當天的交易，則判斷是否為本月的交易ENDEND IF;--回滾借記卡結束END IF;--回滾借記卡、貸記卡結束--
  END;END NP_PCKG_MERCHANT_LIMIT;

?表結構:

?