MYSQL存储过程,while循环和游标的嵌套使用
时间:2019-02-18 12:59 来源:linux.it.net.cn 作者:IT
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)
| 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) |