资金表实现过程方案一:资金表与冻结表合并(四)

4. delete 存储过程

 

CREATE OR REPLACE PROCEDURE PAYT.SP_BALANCE_FOR_DELETING (
   P_MONEY         IN FLOAT,                                        --结算金额
   P_CATEGORY_ID   IN NUMBER,                           --类别ID:1.投入,0.支出
   P_MEMBER_ID     IN NUMBER,                                         --会员ID
   P_MERCHANT_ID   IN NUMBER,                                         --商户ID
   P_VALID         IN NUMBER                        --是否有效:1.不解凍 0.解凍
)
IS
   V_MONEY         FLOAT (126);                                     --结算金额
   V_CATEGORY_ID   NUMBER (19);                         --类别ID:1.投入,0.支出
   V_MEMBER_ID     NUMBER (19);                                       --会员ID
   V_MERCHANT_ID   NUMBER (19);                                       --商户ID
   V_VALID         NUMBER (1);                      --是否有效:1.不解凍 0.解凍
BEGIN
   V_MONEY := P_MONEY;                                              --结算金额
   V_CATEGORY_ID := P_CATEGORY_ID;                      --类别ID:1.投入,0.支出
   V_MEMBER_ID := P_MEMBER_ID;                                        --会员ID
   V_MERCHANT_ID := P_MERCHANT_ID;                                    --商户ID
   V_VALID := P_VALID;                              --是否有效:1.不解凍 0.解凍

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

 

   CASE
      /*刪除商戶投入記錄*/
      WHEN V_MEMBER_ID IS NULL AND V_CATEGORY_ID IN (504, 509)      --商户投入
      THEN
         IF V_VALID = 1                                         --投入尚未成交
         THEN
            UPDATE   tab_merchantfund
               SET   balance = balance - v_money,
                     totalearning = totalearning - v_money,
                     usebalance = usebalance - v_money
             WHERE   merchant_id = v_merchant_id;
         ELSIF V_VALID = 0                                      --投入已经成交
         THEN
            raise_application_error (-20001, 'Have deal, refused to delete!');
         END IF;
      /*刪除商戶支出記錄*/

      WHEN V_MEMBER_ID IS NULL AND V_CATEGORY_ID IN (505, 506, 507, 508) --商户支出
      THEN
         IF V_VALID = 1                                         --支出尚未成交
         THEN
            UPDATE   tab_merchantfund
               SET   usebalance = usebalance + v_money,
                     frozenprice = frozenprice - v_money
             WHERE   merchant_id = v_merchant_id;
         ELSIF V_VALID = 0                                      --支出已经成交
         THEN
            raise_application_error (-20001, 'Have deal, refused to delete!');
         END IF;
      /*刪除会员投入記錄*/

      WHEN V_MERCHANT_ID IS NULL AND V_CATEGORY_ID IN (504, 509)    --会员投入
      THEN
         IF V_VALID = 1                                         --投入尚未成交
         THEN
            UPDATE   tab_memberfund
               SET   balance = balance - v_money,
                     totalearning = totalearning - v_money,
                     usebalance = usebalance - v_money
             WHERE   member_id = v_member_id;
         ELSIF V_VALID = 0                                      --投入已经成交
         THEN
            raise_application_error (-20001, 'Have deal, refused to delete!');
         END IF;
      /*刪除会员支出記錄*/

      WHEN V_MERCHANT_ID IS NULL AND V_CATEGORY_ID IN (505, 506, 507, 508) --会员支出
      THEN
         IF V_VALID = 1                                         --支出尚未成交
         THEN
            UPDATE   tab_memberfund
               SET   usebalance = usebalance + v_money,
                     frozenprice = frozenprice - v_money
             WHERE   member_id = v_member_id;
         ELSIF V_VALID = 0                                      --支出已经成交
         THEN
            raise_application_error (-20001, 'Have deal, refused to delete!');
         END IF;
   END CASE;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-745866/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-745866/