3. update 存储过程

 

CREATE OR REPLACE PROCEDURE PAYT.SP_BALANCE_FOR_UPDATING (
   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_OLDMONEY      IN FLOAT,                                      --原结算金额
   P_VALID         IN NUMBER,                   --新值是否有效:1.不解凍 0.解凍
   P_OLDVALID      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_OLDMONEY      FLOAT (126);                                   --原结算金额
   V_VALID         NUMBER (1);                  --新值是否有效:1.不解凍 0.解凍
   V_OLDVALID      NUMBER (1);                  --原值是否有效:1.不解凍 0.解凍
   V_DIFF          FLOAT (126);                               --新旧值差异金额
   V_BALANCE       FLOAT (126);                           --商戶或會員結算余額
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_OLDMONEY := P_OLDMONEY;                                      --原结算金额
   V_VALID := P_VALID;                          --新值是否有效:1.不解凍 0.解凍
   V_OLDVALID := P_OLDVALID;                    --原值是否有效:1.不解凍 0.解凍
   V_DIFF := 0;                                               --新旧值差异金额
   V_BALANCE := 0;                                        --商戶或會員結算余額

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

   SELECT   v_money - v_oldmoney INTO v_diff FROM DUAL;

   CASE
      /*变更商户投入金额*/
      WHEN V_MEMBER_ID IS NULL AND V_CATEGORY_ID IN (504, 509)      --商户投入
      THEN
         IF V_OLDVALID = 1 AND V_VALID = 1 AND v_diff > 0
         --投入尚未成交并追加投入
         THEN
            UPDATE   tab_merchantfund
               SET   balance = balance + v_diff,
                     totalearning = totalearning + v_diff,
                     usebalance = usebalance + v_diff
             WHERE   merchant_id = v_merchant_id;
         ELSIF V_OLDVALID = 1 AND V_VALID = 1 AND v_diff < 0
         --投入尚未成交并减少投入
         THEN
            UPDATE   tab_merchantfund
               SET   balance = balance + v_diff,
                     totalearning = totalearning + v_diff,
                     usebalance = usebalance + v_diff
             WHERE   merchant_id = v_merchant_id;
         ELSIF V_OLDVALID = 1 AND V_VALID = 0                   --投入正常使用
         THEN
            DBMS_OUTPUT.PUT_LINE ('OK');
         ELSIF V_OLDVALID = 0 --投入已经成交则不能修改,只能新增投入,加一笔记录
         THEN
            raise_application_error (-20001, 'Have deal, refused to update!');
         END IF;
      /*变更商户支出金额*/

      WHEN V_MEMBER_ID IS NULL AND V_CATEGORY_ID IN (505, 506, 507, 508)
      --商户支出
      THEN
         SELECT   balance
           INTO   v_balance
           FROM   tab_merchantfund
          WHERE   merchant_id = v_merchant_id;

         IF V_OLDVALID = 1 AND V_VALID = 1 AND v_diff > 0
         --支出尚未成交并追加支出
         THEN
            IF V_BALANCE >= V_DIFF                    --目前没考虑手续费的问题
            THEN
               UPDATE   tab_merchantfund
                  SET   frozenprice = frozenprice + v_diff,
                        usebalance = usebalance - v_diff
                WHERE   merchant_id = v_merchant_id;
            ELSE
               raise_application_error (
                  -20001,
                  'Balance is insufficient, please phone!'
               );
            END IF;
         ELSIF V_OLDVALID = 1 AND V_VALID = 1 AND v_diff < 0
         --支出尚未成交并减少支出
         THEN
            UPDATE   tab_merchantfund
               SET   frozenprice = frozenprice + v_diff,
                     usebalance = usebalance - v_diff
             WHERE   merchant_id = v_merchant_id;
         ELSIF V_OLDVALID = 1 AND V_VALID = 0      --支出正常成交,支出金額解凍
         THEN
            UPDATE   tab_merchantfund
               SET   balance = balance - v_oldmoney,
                     frozenprice = frozenprice - v_oldmoney,
                     totalpayout = totalpayout + v_oldmoney
             WHERE   merchant_id = v_merchant_id;
         ELSIF V_OLDVALID = 0 --支出已经成交则不能修改,只能新增支出,加一笔记录
         THEN
            raise_application_error (-20001, 'Have deal, refused to update!');
         END IF;
      /*变更會員投入金额*/

      WHEN V_MERCHANT_ID IS NULL AND V_CATEGORY_ID IN (504, 509)    --会员投入
      THEN
         IF V_OLDVALID = 1 AND V_VALID = 1 AND v_diff > 0
         --投入尚未成交并追加投入
         THEN
            UPDATE   tab_memberfund
               SET   balance = balance + v_diff,
                     totalearning = totalearning + v_diff,
                     usebalance = usebalance + v_diff
             WHERE   member_id = v_member_id;
         ELSIF V_OLDVALID = 1 AND V_VALID = 1 AND v_diff < 0
         --投入尚未成交并减少投入
         THEN
            UPDATE   tab_memberfund
               SET   balance = balance + v_diff,
                     totalearning = totalearning + v_diff,
                     usebalance = usebalance + v_diff
             WHERE   member_id = v_member_id;
         ELSIF V_OLDVALID = 1 AND V_VALID = 0                   --投入正常使用
         THEN
            DBMS_OUTPUT.PUT_LINE ('OK');
         ELSIF V_OLDVALID = 0 --投入已经成交则不能修改,只能新增投入,加一笔记录
         THEN
            raise_application_error (-20001, 'Have deal, refused to update!');
         END IF;
      /*变更会员支出金额*/

      WHEN V_MERCHANT_ID IS NULL AND V_CATEGORY_ID IN (505, 506, 507, 508)
      --会员支出
      THEN
         SELECT   balance
           INTO   v_balance
           FROM   tab_memberfund
          WHERE   member_id = v_member_id;

         IF V_OLDVALID = 1 AND V_VALID = 1 AND v_diff > 0
         --支出尚未成交并追加支出
         THEN
            IF V_BALANCE >= V_DIFF                      --目前没考虑手续费问题
            THEN
               UPDATE   tab_memberfund
                  SET   frozenprice = frozenprice + v_diff,
                        usebalance = usebalance - v_diff
                WHERE   member_id = v_member_id;
            ELSE
               raise_application_error (
                  -20001,
                  'Balance is insufficient, please phone!'
               );
            END IF;
         ELSIF V_OLDVALID = 1 AND V_VALID = 1 AND v_diff < 0
         --支出尚未成交并减少支出
         THEN
            UPDATE   tab_memberfund
               SET   frozenprice = frozenprice + v_diff,
                     usebalance = usebalance - v_diff
             WHERE   member_id = v_member_id;
         ELSIF V_OLDVALID = 1 AND V_VALID = 0      --支出正常成交,支出金額解凍
         THEN
            UPDATE   tab_memberfund
               SET   balance = balance - v_oldmoney,
                     frozenprice = frozenprice - v_oldmoney,
                     totalpayout = totalpayout + v_oldmoney
             WHERE   member_id = v_member_id;
         ELSIF V_OLDVALID = 0 --支出已经成交则不能修改,只能新增支出,加一笔记录
         THEN
            raise_application_error (-20001, 'Have deal, refused to update!');
         END IF;
   END CASE;
END;
/