MYSQL存储过程,while循环和游标的嵌套使用

DELIMITER $$
DROP PROCEDURE IF EXISTS `pro_zlh_test`$$

CREATE PROCEDURE pro_zlh_test ()
BEGIN
DECLARE done INT DEFAULT 0 ;
DECLARE _contractId BIGINT (20) ; #合同id
DECLARE _contractIntegral INT ; #合同积分
DECLARE _investAmount DECIMAL (18, 2) ; #合同投资金额
DECLARE _userId BIGINT (20) ; #用户id
DECLARE _userIntegral INT ; #用户积分
DECLARE _firstInvestTime VARCHAR (30) ; #首投时间
DECLARE _remainIntegral VARCHAR (30) ; #合同剩余积分
#声明游标
DECLARE cur_1 CURSOR FOR
SELECT d.id,
IFNULL(d.firstInvestTime, '2018-01-01'),
i.totalIntegral, i.remainIntegral,
d.investAmount,
u.userId,
t.integralSum
FROM t_jt_contract_data d
LEFT JOIN t_jt_contract_integral_info i ON d.id = i.contractId
LEFT JOIN t_jt_contract_user u ON d.userIDCard = u.userIDCard
LEFT JOIN t_jt_contract_integral t ON t.userId = u.userId
WHERE '2017-12-01' <= d.interestPeriod AND d.interestPeriod <= '2017-12-31'
AND d.branchId IN (1, 3, 4, 6) AND d.productType IN (1, 2) AND d.contractFlag IN (2, 3) ;
#游标取完后的标志变量设置为1
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
#打开游标
OPEN cur_1 ;
#将游标中的值赋给声明好的变量
FETCH cur_1 INTO _contractId, _firstInvestTime, _contractIntegral, _remainIntegral, _investAmount, _userId, _userIntegral ;
#判断是否该结束循环,游标的值取完后结束循环
WHILE done <> 1 DO
IF _investAmount * 0.01 * 1.5 = _contractIntegral THEN     
    IF DATE_FORMAT(_firstInvestTime, '%Y-%m-%d') < '2017-12-01' THEN
    #insert语句的另外一种写法,直接通过select语句查询出来的值来插入 数据
  INSERT INTO t_jt_contract_integral_detail (contractId, userId, contractGiftId, addScore, cutScore, balanceScore, createTime, TYPE, staffId, operationMemo, staffType, giId) SELECT _contractId, _userId, NULL, NULL, _investAmount * 0.01 * 0.2, integralSum - _investAmount * 0.01 * 0.2, NOW(), 4, 1, '不参与年终答谢会,减积分', 1, NULL FROM t_jt_contract_integral WHERE userId = _userId;
    UPDATE t_jt_contract_integral SET  integralSum = integralSum - _investAmount * 0.01 * 0.2 WHERE userId = _userId  ;
    UPDATE t_jt_contract_integral_info SET remainIntegral = _remainIntegral - _investAmount * 0.01 * 0.2, totalIntegral = _investAmount * 0.01 * 1.3 WHERE contractId = _contractId ;
    ELSE
  INSERT INTO t_jt_contract_integral_detail (contractId, userId, contractGiftId, addScore, cutScore, balanceScore, createTime, TYPE, staffId, operationMemo, staffType, giId) SELECT _contractId, _userId, NULL, NULL, _investAmount * 0.01 * 0.5, integralSum - _investAmount * 0.01 * 0.5, NOW(), 4, 1, '不参与年终答谢会,减积分', 1, NULL FROM t_jt_contract_integral WHERE userId = _userId;
    UPDATE t_jt_contract_integral SET VERSION = VERSION + 1, integralSum = integralSum - _investAmount * 0.01 * 0.5 WHERE userId = _userId ;
    UPDATE t_jt_contract_integral_info SET remainIntegral = _remainIntegral - _investAmount * 0.01 * 0.5, totalIntegral = _investAmount * 0.01 WHERE contractId = _contractId ;
    END IF ;
END IF ;
#固定格式
FETCH cur_1 INTO _contractId, _firstInvestTime, _contractIntegral, _remainIntegral, _investAmount, _userId, _userIntegral;
END WHILE ;
#关闭游标
CLOSE cur_1 ;
END$$

DELIMITER ;
#调用存储过程
CALL pro_zlh_test ();
#删除存储过程
DROP PROCEDURE IF EXISTS `pro_zlh_test`;