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