MySQL存储过程:包含输入和输出

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 ;