mysql 短信验证存储过程

自己写的mysql 短信验证的存储过程

大家可以看看

不懂的留言

CREATE PROCEDURE `ver_SMS_SEND`
(
in yzm VARCHAR(6),
in sjhm VARCHAR(11)
)
BEGIN
	declare flag VARCHAR(255); -- 验证状态
	declare done int; -- 辅助游标变量
	declare _yzm varchar(6); -- 验证码
	declare _sjhm varchar(11); -- 手机号码
	declare _time datetime; -- 生成时间
	declare _state varchar(2); -- 状态
	declare _context varchar(255);
	declare count int ;
	DECLARE cur_sms CURSOR FOR SELECT yzm,sjhm,create_time,state,state_text  FROM sms as s WHERE s.yzm = yzm and s.sjhm=sjhm;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	set count = 0;
	OPEN cur_sms;
	cursor_loop:loop
		FETCH cur_sms into _yzm, _sjhm, _time,_state,_context; -- 取数据
		if (done=1) then -- 游标为空离开loop
			if(count = 0) then -- 如果count==0 且 qie==1说明游标一开始就为空标记flag=1
				SET flag = "验证码错误";
			end if;
			leave cursor_loop;
		else
			if(_state=1) then
				SET flag = _context;
			else 
				if(TIMESTAMPDIFF(MINUTE,_time,now())>5) then -- 大于5分钟验证码过期
					SET flag ='验证码已过期';
					UPDATE sms s SET s.update_time= now(),s.state='1',s.state_text='验证码已过期' WHERE s.yzm = yzm and s.sjhm=sjhm; 
				else 
						SET flag = '0';
						UPDATE sms s SET s.update_time= now(),s.state='1',s.state_text='验证码已使用' WHERE s.yzm = yzm and s.sjhm=sjhm; 
				end if;
			end if;
		end if;
		set count=count+1;
	end loop cursor_loop;
	CLOSE cur_sms;
	SELECT flag;
END

CALL test('','') ;