DELIMITER $$
Use ' qrsoft_dyj_db ' $$
DROP PROCEDURE IF EXISTS ' Proc_withdraw_approve ' $$
CREATE PROCEDURE ' Proc_withdraw_approve ' (
In p_apply_id INT,
In P_handleuserid INT,
In P_handlestate INT,
In P_handletype INT,
In P_bankreceipt VARCHAR (200),
In P_apply_mark VARCHAR (2000),
In P_cash_serial VARCHAR (200))
Label_pro:begin
DECLARE Apply_userid INT;
DECLARE apply_balance DECIMAL;
DECLARE Apply_code VARCHAR (200);
DECLARE before_avabalance DECIMAL;
START TRANSACTION;
--Validate Audit record ID
IF p_apply_id = 0 Then
SELECT-1002 as Result_code;
ROLLBACK;
LEAVE Label_pro;
END IF;
--Verifying the status of audits
IF p_handlestate <> 2 && p_handlestate <> 3 Then
SELECT-1001 as Result_code;
ROLLBACK;
LEAVE Label_pro;
END IF;
--Query User ID withdrawal order number withdrawal amount
SELECT Applyrecord_ref_userid,
Applyrecord_balance,
Applyrecord_code into Apply_userid, Apply_balance, Apply_code
From Qr_apply_record
WHERE applyrecord_id = p_apply_id;
IF p_handlestate = 2 Then-audit success
--Update the withdrawal information Form
UPDATE Qr_apply_record SET
Applyrecord_handlestate = P_handlestate,
Applyrecord_handletime = Now (),
Applyrecord_handleuserid = P_handleuserid,
Applyrecord_handletype = P_handletype,
Applyrecord_bankreceipt = P_bankreceipt,
Applyrecord_mark = P_apply_mark
WHERE applyrecord_id = p_apply_id;
--Modify the accumulated withdrawal amount value in the User account information table
UPDATE qr_useraccount SET account_drawalsbalance = account_drawalsbalance + apply_balance
WHERE Account_ref_userid = Apply_userid;
ELSE--Audit failure
--Update the withdrawal information Form
UPDATE Qr_apply_record SET
Applyrecord_handlestate = P_handlestate,
Applyrecord_handletime = Now (),
Applyrecord_handleuserid = P_handleuserid,
Applyrecord_mark = P_apply_mark
WHERE applyrecord_id = p_apply_id;
--return withdrawal amount to account balance and add refund record
--1. Check the available Balance before user changes
SELECT account_avabalance to Before_avabalance from Qr_useraccount
WHERE Account_ref_userid = Apply_userid;
--2. Update Account Available Balance
UPDATE qr_useraccount SET account_avabalance = before_avabalance + apply_balance
WHERE Account_ref_userid = Apply_userid;
--3. Add a refund record
--Type of transaction (4: Refund (refund of travel Order request) 5: Refund (Withdrawal request denied refund))
--Trading Mode 1: Balance payment
INSERT into ' Qr_cash_record ' (' cashrecord_serial ',
' Cashrecord_ref_userid ',
' Cashrecord_beforebalance ',
' Cashrecord_balance ',
' Cashrecord_afterbalance ',
' Cashrecord_type ',
' Cashrecord_time ',
CASHRECORD_REF_ID,
Cashrecord_model)
VALUES (
P_cash_serial,
Apply_userid,
Before_avabalance,
Apply_balance,
(Before_avabalance + apply_balance), 5, Now (), p_apply_id, 1);
END IF;
COMMIT;
SELECT 1 as Result_code;
end$$
DELIMITER;
MySQL stored procedure uses leave to implement the return syntax in MSSQL stored procedures