资金表实现过程方案二:资金表与冻结表分离(三)

3. 冻结表插入操作存储过程

 

CREATE OR REPLACE PROCEDURE PAYT.SP_FROZEN_FOR_INSERTING (
   P_MONEY         IN FLOAT,                                        --冻结金额
   P_VALID         IN NUMBER,                        --是否冻结::1.冻结,0.解冻
   P_MEMBER_ID     IN NUMBER,                                         --会员ID
   P_MERCHANT_ID   IN NUMBER                                          --商户ID
)
IS
   V_MONEY         FLOAT (126);                                     --冻结金额
   V_VALID         NUMBER (1);                       --是否冻结::1.冻结,0.解冻
   V_MEMBER_ID     NUMBER (19);                                       --会员ID
   V_MERCHANT_ID   NUMBER (19);                                       --商户ID
   V_USEBALANCE    FLOAT (126);                           --商戶或會員可用余額
   V_EXIST         NUMBER (19);                           --商戶或會員是否存在
BEGIN
   V_MONEY := P_MONEY;                                              --冻结金额
   V_VALID := P_VALID;                               --是否冻结::1.冻结,0.解冻
   V_MEMBER_ID := P_MEMBER_ID;                                        --会员ID
   V_MERCHANT_ID := P_MERCHANT_ID;                                    --商户ID
   V_USEBALANCE := 0;                                     --商戶或會員可用余額

 

   -----------------------------------------------------------------------------------
   --       CATEGORY                         ID             DNAME            FROZEN --
   --       TradeSettlementCategory         506            转帐支出          YES    --
   --       TradeSettlementCategory         505            退款支出          YES    --
   --       TradeSettlementCategory         507            提现支出          YES    --
   --       TradeSettlementCategory         508            手续费支出        NO     --
   --       TradeSettlementCategory         509            返点收入          NO     --
   --       TradeSettlementCategory         504            支付收入          NO     --
   -----------------------------------------------------------------------------------

   CASE
      WHEN V_MEMBER_ID IS NULL
      --商户处理:转帐支出,退款支出,提现支出
      /*    由于上述三种支出需长时间处理且状态不确定,为保证资金安全,需先冻结*/
      THEN
         SELECT   COUNT (MERCHANT_ID)
           INTO   V_EXIST
           FROM   TAB_MERCHANTFUND
          WHERE   MERCHANT_ID = V_MERCHANT_ID;

         IF V_EXIST > 0
         --存在并冻结
         /*    余额=总收入-总支出=可用余额+冻结金额  */
         /*    余额不变,可用余额减少,冻结金额增加  */
         THEN
                SELECT   usebalance
                  INTO   v_usebalance
                  FROM   TAB_MERCHANTFUND
                 WHERE   MERCHANT_ID = V_MERCHANT_ID
            FOR UPDATE   ;

            IF V_USEBALANCE >= V_MONEY
            --余额大于冻结金额
            THEN
               UPDATE   tab_merchantfund
                  SET   frozenprice = frozenprice + v_money,
                        usebalance = usebalance - v_money
                WHERE   merchant_id = v_merchant_id;
            ELSE
               --可用余额,不足以支付该笔交易则报错
               raise_application_error (
                  -20001,
                  'Balance is insufficient, please phone!'
               );
            END IF;
         ELSE
            --不存在则不能冻结并报错
            raise_application_error (
               -20001,
               'Balance is insufficient, please phone!'
            );
         END IF;
      WHEN V_MERCHANT_ID IS NULL
      --会员处理:转帐支出,退款支出,提现支出
      /*    由于上述三种支出需长时间处理且状态不确定,为保证资金安全,需先冻结*/
      THEN
         SELECT   COUNT (MEMBER_ID)
           INTO   V_EXIST
           FROM   TAB_MEMBERFUND
          WHERE   MEMBER_ID = V_MEMBER_ID;

         IF V_EXIST > 0
         --存在并冻结
         /*    余额=总收入-总支出=可用余额+冻结金额  */
         /*    余额不变,可用余额减少,冻结金额增加  */
         THEN
                SELECT   usebalance
                  INTO   v_usebalance
                  FROM   TAB_MEMBERFUND
                 WHERE   MEMBER_ID = V_MEMBER_ID
            FOR UPDATE   ;

            IF V_USEBALANCE >= V_MONEY
            --余额大于冻结金额
            THEN
               UPDATE   TAB_MEMBERFUND
                  SET   frozenprice = frozenprice + v_money,
                        usebalance = usebalance - v_money
                WHERE   member_id = v_member_id;
            ELSE
               --可用余额,不足以支付该笔交易则报错
               raise_application_error (
                  -20001,
                  'Balance is insufficient, please phone!'
               );
            END IF;
         ELSE
            --不存在则不能冻结并报错
            raise_application_error (
               -20001,
               'Balance is insufficient, please phone!'
            );
         END IF;
   END CASE;
END;
/