资金表实现过程方案一:资金表与冻结表合并(二)
2. insert 操作存储过程
CREATE OR REPLACE PROCEDURE PAYT.SP_BALANCE_FOR_INSERTING (
P_ID IN NUMBER, --主键ID
P_COLUMNSIGN IN VARCHAR, --签名列
P_CREATEDATE IN TIMESTAMP, --创建时间
P_LASTDATE IN TIMESTAMP, --变更时间
P_MONEY IN FLOAT, --结算金额
P_VALID IN NUMBER, --是否有效
P_CATEGORY_ID IN NUMBER, --类别ID:1.投入,0.支出
P_MEMBER_ID IN NUMBER, --会员ID
P_MERCHANT_ID IN NUMBER, --商户ID
P_ORDER_ID IN NUMBER --订单ID
)
IS
V_ID NUMBER (19); --主键ID
V_COLUMNSIGN VARCHAR2 (50); --签名列
V_CREATEDATE TIMESTAMP (6); --创建时间
V_LASTDATE TIMESTAMP (6); --变更时间
V_MONEY FLOAT (126); --结算金额
V_VALID NUMBER (1); --是否有效
V_CATEGORY_ID NUMBER (19); --类别ID:1.投入,0.支出
V_MEMBER_ID NUMBER (19); --会员ID
V_MERCHANT_ID NUMBER (19); --商户ID
V_ORDER_ID NUMBER (19); --订单ID
V_ID_MERCHANTFUND NUMBER (19); --MERCHANTFUND 主键ID
V_ID_MEMBERFUND NUMBER (19); --MEMBERFUND 主键ID
V_BALANCE FLOAT (126); --商戶或會員結算余額
V_EXIST NUMBER (19); --商戶或會員是否存在
BEGIN
V_ID := P_ID; --主键ID
V_COLUMNSIGN := P_COLUMNSIGN; --签名列
V_CREATEDATE := P_CREATEDATE; --创建时间
V_LASTDATE := P_LASTDATE; --变更时间
V_MONEY := P_MONEY; --结算金额
V_VALID := P_VALID; --是否有效
V_CATEGORY_ID := P_CATEGORY_ID; --类别ID:1.投入,0.支出
V_MEMBER_ID := P_MEMBER_ID; --会员ID
V_MERCHANT_ID := P_MERCHANT_ID; --商户ID
V_ORDER_ID := P_ORDER_ID; --订单ID
V_ID_MERCHANTFUND := 0; --MERCHANTFUND 主键ID
V_ID_MEMBERFUND := 0; --MEMBERFUND 主键ID
V_BALANCE := 0; --商戶或會員結算余額
P_ID IN NUMBER, --主键ID
P_COLUMNSIGN IN VARCHAR, --签名列
P_CREATEDATE IN TIMESTAMP, --创建时间
P_LASTDATE IN TIMESTAMP, --变更时间
P_MONEY IN FLOAT, --结算金额
P_VALID IN NUMBER, --是否有效
P_CATEGORY_ID IN NUMBER, --类别ID:1.投入,0.支出
P_MEMBER_ID IN NUMBER, --会员ID
P_MERCHANT_ID IN NUMBER, --商户ID
P_ORDER_ID IN NUMBER --订单ID
)
IS
V_ID NUMBER (19); --主键ID
V_COLUMNSIGN VARCHAR2 (50); --签名列
V_CREATEDATE TIMESTAMP (6); --创建时间
V_LASTDATE TIMESTAMP (6); --变更时间
V_MONEY FLOAT (126); --结算金额
V_VALID NUMBER (1); --是否有效
V_CATEGORY_ID NUMBER (19); --类别ID:1.投入,0.支出
V_MEMBER_ID NUMBER (19); --会员ID
V_MERCHANT_ID NUMBER (19); --商户ID
V_ORDER_ID NUMBER (19); --订单ID
V_ID_MERCHANTFUND NUMBER (19); --MERCHANTFUND 主键ID
V_ID_MEMBERFUND NUMBER (19); --MEMBERFUND 主键ID
V_BALANCE FLOAT (126); --商戶或會員結算余額
V_EXIST NUMBER (19); --商戶或會員是否存在
BEGIN
V_ID := P_ID; --主键ID
V_COLUMNSIGN := P_COLUMNSIGN; --签名列
V_CREATEDATE := P_CREATEDATE; --创建时间
V_LASTDATE := P_LASTDATE; --变更时间
V_MONEY := P_MONEY; --结算金额
V_VALID := P_VALID; --是否有效
V_CATEGORY_ID := P_CATEGORY_ID; --类别ID:1.投入,0.支出
V_MEMBER_ID := P_MEMBER_ID; --会员ID
V_MERCHANT_ID := P_MERCHANT_ID; --商户ID
V_ORDER_ID := P_ORDER_ID; --订单ID
V_ID_MERCHANTFUND := 0; --MERCHANTFUND 主键ID
V_ID_MEMBERFUND := 0; --MEMBERFUND 主键ID
V_BALANCE := 0; --商戶或會員結算余額
----------------------------------------------------------------------------
-- CATEGORY ID DNAME --
-- TradeSettlementCategory 506 转帐支出 --
-- TradeSettlementCategory 505 退款支出 --
-- TradeSettlementCategory 507 提现支出 --
-- TradeSettlementCategory 508 手续费支出 --
-- TradeSettlementCategory 509 返点收入 --
-- TradeSettlementCategory 504 支付收入 --
----------------------------------------------------------------------------
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 AND V_CATEGORY_ID IN (504, 509) --存在并投入
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_EXIST > 0 AND V_CATEGORY_ID IN (505, 506, 507, 508) --存在并支出
THEN
SELECT balance
INTO v_balance
FROM TAB_MERCHANTFUND
WHERE MERCHANT_ID = V_MERCHANT_ID;
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_EXIST > 0 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_BALANCE >= 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;
ELSIF V_EXIST = 0 AND V_CATEGORY_ID IN (504, 509) --不存在并投入
THEN
SELECT SEQ_MERCHANTFUND.NEXTVAL INTO V_ID_MERCHANTFUND FROM DUAL;
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;
ELSIF V_EXIST = 0 AND V_CATEGORY_ID IN (504, 509) --不存在并投入
THEN
SELECT SEQ_MERCHANTFUND.NEXTVAL INTO V_ID_MERCHANTFUND FROM DUAL;
INSERT INTO TAB_MERCHANTFUND (ID,
BALANCE,
COLUMNSIGN,
CREATEDATE,
FROZENPRICE,
LASTLOGINTIME,
LASTDATE,
TOTALEARNING,
TOTALPAYOUT,
USEBALANCE,
OPERATOR_ID,
MERCHANT_ID)
VALUES (V_ID_MERCHANTFUND,
V_MONEY,
NULL,
V_CREATEDATE,
0,
NULL,
V_LASTDATE,
V_MONEY,
0,
V_MONEY,
NULL,
V_MERCHANT_ID);
ELSIF V_EXIST = 0 AND V_CATEGORY_ID IN (505, 506, 507, 508) --不存在并支出
THEN
raise_application_error (-20001,
'Balance is zero, 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;
BALANCE,
COLUMNSIGN,
CREATEDATE,
FROZENPRICE,
LASTLOGINTIME,
LASTDATE,
TOTALEARNING,
TOTALPAYOUT,
USEBALANCE,
OPERATOR_ID,
MERCHANT_ID)
VALUES (V_ID_MERCHANTFUND,
V_MONEY,
NULL,
V_CREATEDATE,
0,
NULL,
V_LASTDATE,
V_MONEY,
0,
V_MONEY,
NULL,
V_MERCHANT_ID);
ELSIF V_EXIST = 0 AND V_CATEGORY_ID IN (505, 506, 507, 508) --不存在并支出
THEN
raise_application_error (-20001,
'Balance is zero, 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 AND V_CATEGORY_ID IN (504, 509) --存在并投入
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_EXIST > 0 AND V_CATEGORY_ID IN (505, 506, 507, 508) --存在并支出
THEN
SELECT balance
INTO v_balance
FROM TAB_MEMBERFUND
WHERE MEMBER_ID = V_MEMBER_ID;
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_EXIST > 0 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_BALANCE >= 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;
ELSIF V_EXIST = 0 AND V_CATEGORY_ID IN (504, 509) --不存在并投入
THEN
SELECT SEQ_MEMBERFUND.NEXTVAL INTO V_ID_MEMBERFUND FROM DUAL;
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;
ELSIF V_EXIST = 0 AND V_CATEGORY_ID IN (504, 509) --不存在并投入
THEN
SELECT SEQ_MEMBERFUND.NEXTVAL INTO V_ID_MEMBERFUND FROM DUAL;
INSERT INTO tab_memberfund (ID,
BALANCE,
CREATEDATE,
FROZENPRICE,
LASTLOGINTIME,
LASTDATE,
TOTALEARNING,
TOTALPAYOUT,
USEBALANCE,
MEMBER_ID)
VALUES (V_ID_MEMBERFUND,
V_MONEY,
V_CREATEDATE,
0,
NULL,
V_LASTDATE,
V_MONEY,
0,
V_MONEY,
V_MEMBER_ID);
ELSIF V_EXIST = 0 AND V_CATEGORY_ID IN (505, 506, 507, 508) --不存在并支出
THEN
raise_application_error (-20001,
'Balance is zero, please phone!');
END IF;
END CASE;
END;
/
BALANCE,
CREATEDATE,
FROZENPRICE,
LASTLOGINTIME,
LASTDATE,
TOTALEARNING,
TOTALPAYOUT,
USEBALANCE,
MEMBER_ID)
VALUES (V_ID_MEMBERFUND,
V_MONEY,
V_CREATEDATE,
0,
NULL,
V_LASTDATE,
V_MONEY,
0,
V_MONEY,
V_MEMBER_ID);
ELSIF V_EXIST = 0 AND V_CATEGORY_ID IN (505, 506, 507, 508) --不存在并支出
THEN
raise_application_error (-20001,
'Balance is zero, please phone!');
END IF;
END CASE;
END;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25198367/viewspace-745863/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
