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

最新推荐文章于 2024-10-08 21:21:45 发布

原创最新推荐文章于 2024-10-08 21:21:45 发布 · 5.3k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

MySql 专栏收录该内容

25 篇文章

订阅专栏

本文介绍了一个具体的MySQL存储过程实现案例，该过程用于处理积分冻结业务逻辑，包括输入参数的使用、变量声明、错误处理及事务控制等内容。通过此例，读者可以了解如何在MySQL中编写复杂的业务逻辑。

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 ;