|
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`; (责任编辑：IT) |