INSERT INTO ON DUPLICATE KEY UPDATE 用法

DELIMITER $$

DROP PROCEDURE IF EXISTS `SP_BACK_MANAGE_EAT`$$

CREATE PROCEDURE `SP_BACK_MANAGE_EAT`(
	IN p_drawdateId BIGINT, 
	IN p_gameId INT
)
proc:
    BEGIN
	DECLARE _number VARCHAR(4);
	DECLARE _big DECIMAL(13,6);
	DECLARE _small  DECIMAL(13,6);
	
	DECLARE _stockMaxPrize DECIMAL(20,6);
	DECLARE _weekDay INT;
	
	DECLARE _number_game_draw VARCHAR(20);
	
	DECLARE _firstBigPrize DECIMAL(13,6);
	DECLARE _firstSmlPrize DECIMAL(13,6);
	
	DECLARE _eatBig DECIMAL(13,6);
	DECLARE _eatSml DECIMAL(13,6)
	
	DECLARE _maxBig DECIMAL(13,6);
	DECLARE _maxSmall DECIMAL(13,6);
	
	DECLARE _putBig DECIMAL(13,6);
	DECLARE _putSml DECIMAL(13,6);
	DECLARE done, error BOOLEAN DEFAULT FALSE;
	DECLARE i INT;
	DECLARE _values MEDIUMTEXT;
	
	DECLARE c CURSOR FOR   
		 SELECT number,SUM(big) big,SUM(small) small FROM TB_BACK_EAT 
		 WHERE game_id=p_gameId AND drawDate_id=p_drawdateId AND STATUS=0 
		 GROUP BY number;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE,done=TRUE;
	DECLARE CONTINUE HANDLER FOR 1329 SET done = TRUE;
		 
	SELECT stock_max_prize,week_day INTO _stockMaxPrize,_weekDay FROM TB_BACK_DRAW_DATE WHERE id = p_drawdateId;
	SELECT first_big,first_sml INTO _firstBigPrize,_firstSmlPrize FROM TB_BACK_GAME WHERE id=p_gameId;
	
	SET _maxBig = _stockMaxPrize/_firstBigPrize;
	SET _maxSmall = _stockMaxPrize/_firstSmlPrize;

	OPEN c;
	   IF error THEN  
	      SELECT error;
	      LEAVE proc; 
	   END IF;  
	SET autocommit=0; 
	SET i=0;
	UPDATE TB_BACK_EAT_REPORT SET fromBig=0,fromSml=0,big=0,small=0,putBig=0,putSml=0 WHERE gameId=p_gameId AND drawDateId=p_drawDateId;
	REPEAT  FETCH c INTO _number,_big,_small;
	  IF NOT done THEN	

		   IF _big>=_maxBig THEN 
			SET _eatBig = _maxBig;
			SET _eatSml = 0;
		   ELSE
			SET _eatBig = _big;
			SET _maxSmall = FLOOR((_stockMaxPrize - _big*_firstBigPrize)/_firstSmlPrize);
			IF _small>=_maxSmall THEN
			   SET _eatSml = _maxSmall;
			ELSE
			   SET _eatSml = _small;
			END IF;			
		   END IF;
		   SET _putBig = _big - _eatBig;
		   SET _putSml = _small-_eatSml;
		   SET _number_game_draw = CONCAT(_number,'_',p_gameId,'_',p_drawDateId);
		 
		   IF i=0 THEN	   
			SET _values = CONCAT('(',p_gameId,',',p_drawDateId,',',_weekDay,',\'',_number,'\',',_big,',',_small,',',_eatBig,',',_eatSml,',',_putBig,',',_putSml,',',0,',',0,',\'',_number_game_draw,'\')');
		   ELSE
			SET _values = CONCAT(_values,',','(',p_gameId,',',p_drawDateId,',',1,',\'',_number,'\',',_big,',',_small,',',_eatBig,',',_eatSml,',',_putBig,',',_putSml,',',0,',',0,',\'',_number_game_draw,'\')');
		   END IF;
		 
		 SET i=i+1;

	 END IF;
		UNTIL done END REPEAT;	
	CLOSE c;
	SET @sqltext=CONCAT('INSERT INTO TB_BACK_EAT_REPORT(gameId, drawDateId,WEEKDAY,number,fromBig,fromSml,big,small,putBig,putSml,outBig,outSml,num_game_draw) VALUES',_values,'ON DUPLICATE KEY 
			     UPDATE fromBig=VALUES(fromBig),fromSml=VALUES(fromSml),big=VALUES(big),small=VALUES(small),putBig=VALUES(putBig),putSml=VALUES(putSml);');
	
	PREPARE stmt FROM @sqltext;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
	COMMIT;
	SELECT error;
    END$$

DELIMITER ;