MySQL 存储过程踩坑

雨点打透心脏的1/2处 2021-10-09 04:34 408阅读 0赞

之前很少写存储过程，自从开始写存储过程之后，就爱不释手了，最近写存储过程比较多。之前写的存储过程比较简单，也很顺利都没有遇到过任何问题。直到今天，遇到一个让我觉得很神奇的问题。

DELIMITER //
    CREATE PROCEDURE `luckdraw_proc`()
    BEGIN
    	DECLARE done INT default 0;
    	DECLARE rNum INT default 0;
    	
    	DECLARE i_luckId INT;
    	DECLARE i_userId INT;
    	DECLARE i_attdTime datetime;
    	DECLARE i_minAmt decimal(12,2);
    	DECLARE p_investTime datetime;
    	DECLARE p_invorderId varchar(50);
    	DECLARE p_transAmt decimal(12,2);
    	DECLARE p_detailId INT;
    	DECLARE p_detailName varchar(50);
    	DECLARE p_investCycle INT;
    	DECLARE p_yearAmt decimal(12,2);
    	DECLARE p_jsonVal text;
    	
    	-- 游标
    	DECLARE investList CURSOR FOR (
            -- 查询有效中奖用户信息
    		select 
    			t.id, 
    			t.fk_userId, 
    			t.attendTime,
    			t.minAmt
    		from t_luckdraw as t
    		where t.awardType =1 
    			and t.`status` = 0
    			and t.yearAmt is null
    			and now() <= t.expiryTime
    		order by awardAmt desc
    	);
    	
    	DECLARE CONTINUE HANDLER FOR not found SET done = 1;
    	--打开游标
    	OPEN investList;
    	--遍历游标
    	FETCH investList INTO i_luckId,i_userId,i_attdTime,i_minAmt;
    
        -- 开始循环
    	WHILE !done DO
    	
    	--事务开始
    	start transaction;
                -- 查询用户购买信息
    			select 
    				ic.orderId,
    				ic.transAmt,
    				ic.tradeTime,
    				ic.pro_id,
    				ic.pro_name,
    				round(ifnull(ic.transAmt,0)*ifnull(fd.investmentCycle,0)/365,2) as yearAmount,
    				fd.investmentCycle
    				into p_invorderId,p_transAmt,p_investTime,p_detailId,p_detailName,p_yearAmt,p_investCycle
    			from c_invest as ic 
    				left join p_detail as fd on ic.pro_id = fd.detailId
    				left join t_luckdraw as al on ic.orderId = al.investOrderId
    			where ic.fk_userId = i_userId 
    				and ic.status = 1
    				and al.id is null
    				and ic.tradeTime >= i_attdTime
    				group by ic.id having yearAmount >= i_minAmt
    				order by ic.tradeTime asc
    				limit 1;
    		
    			if p_invorderId is not null then	
    				-- 修改中奖记录
    				update t_luckdraw set  `investTime`=p_investTime, `investOrderId`=p_invorderId, `yearAmt`=p_yearAmt,`status`=3,upd_date = now() 
    				WHERE `id`=i_luckId and `status`=0;
    			end if;
    
    	commit;
    	
    	FETCH investList INTO i_luckId,i_userId,i_attdTime,i_minAmt;
    	END WHILE;
    
    	CLOSE investList;
    
    END
    //
    DELIMITER ;

以上是我写的最初版的存储过程，因为涉及线上的数据库的结构，SQL有做一些修改，也不再把表结构帖出来了，大家可以看一看写的内容。

问题一描述：

现有两条符合有效的中奖客户信息，第一个用户未购买相关产品，不符合发放奖励条件，第二个用户则符合发放奖励的条件。正常情况下，第一条数据不做修改，而第二条数据则应该按照要求进行修改。

结果：可实际情况是两条都未进行修改

原因：

写法一：
    DECLARE CONTINUE HANDLER FOR not found SET done = 1;
    
    写法二：
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

是我对这一句的SQL理解的不够，以上两句的意思都是 **当未找到数据的时候，将done参数设置为1**（在MySQL中，数值不为0，对应的boolean为true，done是用来做循环的条件判断的）。我就想当然的以为，这个是监测游标的数据列表的，而没有去细想。

其实只要是select XX into XXX from tablename 这种情况，未找到数据的情况下，就会进行修改。所以上述的存储过程，在循环体循环第一条的时候，因为没有满足要求的数据，因此没有数据查询出来，直接将done置为1，循环就此结束了。

**解决办法**：

select count(*) into rNum from (
    	select 
    		ic.fk_userId,
    		round(ifnull(ic.transAmt,0)*ifnull(fd.investmentCycle,0)/365,2) as yearAmount
    	from c_invest as ic 
    		left join p_detail as fd on ic.pro_id = fd.detailId
    		left join t_luckdraw as al on ic.orderId = al.investOrderId
    	where ic.fk_userId = i_userId 
    		and ic.status = 1
    		and al.id is null
    		and ic.tradeTime >= i_attdTime
    		group by ic.id having yearAmount >= i_minAmt
    		order by ic.tradeTime asc
    ) as t; 
    
    if rNum>0 then
        -- 查询用户购买信息
    	select 
    		ic.orderId,
    		ic.transAmt,
    		ic.tradeTime,
    		ic.pro_id,
    		ic.pro_name,
    		round(ifnull(ic.transAmt,0)*ifnull(fd.investmentCycle,0)/365,2) as yearAmount,
    		fd.investmentCycle
    		into p_invorderId,p_transAmt,p_investTime,p_detailId,p_detailName,p_yearAmt,p_investCycle
    	from c_invest as ic 
    		left join p_detail as fd on ic.pro_id = fd.detailId
    		left join t_luckdraw as al on ic.orderId = al.investOrderId
    	where ic.fk_userId = i_userId 
    		and ic.status = 1
    		and al.id is null
    		and ic.tradeTime >= i_attdTime
    		group by ic.id having yearAmount >= i_minAmt
    		order by ic.tradeTime asc
    		limit 1;
    		
    	if p_invorderId is not null then	
    		-- 修改中奖记录
    		update t_luckdraw set  `investTime`=p_investTime, `investOrderId`=p_invorderId, `yearAmt`=p_yearAmt,`status`=3,upd_date = now() 
    		WHERE `id`=i_luckId and `status`=0;
    	end if;
    end if;

这是我一个简单的解决办法，当然还有好多其他办法，大家可以自己尝试一下。

问题二描述：

上面初版的存储过程还有一个问题，当第一条数据符合要求，第二条不符合要求的情况下，会同时修改两条数据。

原因：

这个就是参数定义的问题了，在循环体里面，循环第一条数据的时候，设置了参数的值，当循环第二条数据的时候，因为没有查询出来数据，因此不会重置掉参数的值，参数的值依然是上一条循环时设置的内容。

解决办法：

上面先查询数量就可以解决这个问题，也可以每次循环重置一下参数。