查询-》保存 存储过程

DELIMITER $$

USE `activity_v2`$$

DROP PROCEDURE IF EXISTS `user_currency_limit_merge`$$

CREATE DEFINER=`root`@`%` PROCEDURE `user_currency_limit_merge`()
BEGIN
	DECLARE no_more_departments INT(3);
	DECLARE _userName VARCHAR(20);
	DECLARE _cashLimit DOUBLE DEFAULT 0;
	DECLARE _tangCoinLimit DOUBLE DEFAULT 0;
	DECLARE	_limitAmount DOUBLE DEFAULT 0;
	DECLARE _limitCount INT(3);
	#钻石限制游标
	DECLARE cashLimitList CURSOR FOR SELECT userName,moneyLimit FROM `user_credits`;
	#金币限制游标
	DECLARE tangCoinLimitList CURSOR FOR SELECT userName,diamondLimit FROM `user_diamond_coin_limit`;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; 
	#开钻石限制游标
	SET no_more_departments=0; 
	OPEN cashLimitList;
	FETCH cashLimitList INTO _userName,_cashLimit;
	WHILE no_more_departments !=1 DO  
		INSERT INTO `user_currency_limit`(userName,limitAmount,limitStatment)VALUE(_userName,_cashLimit,_cashLimit*5);
		FETCH cashLimitList INTO _userName,_cashLimit;
	END WHILE;
	CLOSE cashLimitList;
	#开金币限制游标
	SET no_more_departments=0; 
	OPEN tangCoinLimitList;
	FETCH tangCoinLimitList INTO _userName,_tangCoinLimit;
	read_loop:LOOP
		IF no_more_departments =1 THEN
			LEAVE read_loop; 
		END IF ;
		SELECT limitAmount ,COUNT(id) INTO _limitAmount,_limitCount  FROM `user_currency_limit` WHERE userName=_userName;
		IF _limitCount>0 THEN
			UPDATE `user_currency_limit`SET limitAmount=_limitAmount+_tangCoinLimit/10000,limitStatment=(_limitAmount+_tangCoinLimit/10000)*5 WHERE userName=_userName;
		ELSE
			INSERT INTO `user_currency_limit`(userName,limitAmount,limitStatment)VALUE(_userName,_tangCoinLimit/10000,(_tangCoinLimit/10000)*5);
		END IF;
		FETCH tangCoinLimitList INTO _userName,_tangCoinLimit;	
	END LOOP; 
	CLOSE tangCoinLimitList;
END$$

DELIMITER ;