MySQL存储过程:包含输入和输出
DELIMITER $$
DROP PROCEDURE IF EXISTS `p_integral_freeze_product_type_modify` $$
CREATE PROCEDURE p_integral_freeze_product_type_modify (IN `in_contractId` BIGINT ,IN `in_contractApplyId` BIGINT ,in in_staffId bigint , OUT out_ret INT, OUT out_desc VARCHAR(100))
_return:BEGIN
DECLARE _userId bigint(20);
DECLARE _integralSum int;#用户总积分
DECLARE _totalIntegral INT;#变更产品类型合同得到积分
DECLARE _remainIntegral INT;#变更产品类型合同剩余积分
declare _freezeIntegral int;#冻结积分
declare _version int;#版本
declare _balanceScore int ;#剩余总积分
DECLARE _error INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1;
SET out_ret = -1;
SET out_desc = '执行失败';
#老合同无积分
IF NOT EXISTS (SELECT id FROM t_jt_contract_integral_info WHERE contractId=in_contractId) THEN
SET out_ret = 2;
SET out_desc = '老合同';
LEAVE _return;
END IF;
SELECT DISTINCT userId into _userId FROM t_jt_contract_front_map WHERE contractId=in_contractId;
select integralSum into _integralSum from t_jt_contract_integral where userId=_userId;
SELECT totalIntegral,remainIntegral INTO _totalIntegral,_remainIntegral FROM t_jt_contract_integral_info WHERE contractId=in_contractId;
SELECT `version` INTO _version FROM t_jt_contract_integral WHERE userId = _userId;
START TRANSACTION;
#计算冻结积分额度 冻结积分 = 变更产品类型合同剩余积分
set _freezeIntegral = _remainIntegral;
set _balanceScore = _integralSum - _remainIntegral;
#更新用户总积分和冻结积分
UPDATE t_jt_contract_integral SET integralSum=integralSum-_freezeIntegral,freezeIntegral=IFNULL(freezeIntegral,0)+_freezeIntegral WHERE userId=_userId AND VERSION = _version;
SET @ret_update = ROW_COUNT();
IF @ret_update = 0 THEN
ROLLBACK;
SET out_ret = -10;
SET out_desc = '系统错误';
LEAVE _return;
END IF;
#记录详情
INSERT INTO t_jt_contract_integral_detail ( contractId, userId, contractGiftId, addScore, cutScore, balanceScore, createTime, TYPE, staffId, operationMemo, staffType, giId )
VALUES ( in_contractId, _userId, 0, 0, _freezeIntegral, _balanceScore, NOW(), 10, in_staffId, '变更产品类型冻结积分', 2, 0 );
#更新合同剩余积分和冻结积分
update t_jt_contract_integral_info set remainIntegral=0,freezeIntegral=IFNULL(freezeIntegral,0)+_freezeIntegral
where userId=_userId and contractId=in_contractId;
#插入提前赎回积分冻结详情
INSERT INTO t_jt_contract_integral_product_type_modify (contractId,contractApplyId,freezeIntegral,createTime,`integralStatus`)
VALUES(in_contractId,in_contractApplyId,_freezeIntegral,now(),1);
IF _error <> 0 THEN
ROLLBACK;
SET out_ret = -100;
SET out_desc = '执行异常';
LEAVE _return;
ELSE
COMMIT;
SET out_ret = 1;
SET out_desc = '';
END IF;
END $$
DELIMITER ;