小试Mysql存储过程
今天,需要对sohu邮箱用户的过期积分进行清理
过期的积分早已经计算好了,存放在mysql的一个表中
因此,清理的过程逻辑相对比较简单,就是update用户的可用积分,并保留此刻的可用积分作为历史记录
由于都是针对Mysql的操作,所以,就想用Mysql的存储过程来实现
N年之前,我在第一家公司里,曾写过无数的SQL SERVER的存储过程和触发器
MySql的Procedure一直没写过,不过,查了下Mysql的参考手册,还是比较简单的
折叠复制代码
- drop procedure if exists pro_ysz;
- delimiter //
- CREATE PROCEDURE pro_ysz(out rtn int)
- BEGIN
- DECLARE v_userid varchar(50);
- DECLARE v_totalscore int default 0;
- DECLARE v_freescore int default 0;
- DECLARE v_expirescore int default 0;
- DECLARE v_done int default 0;
- DECLARE cur cursor for select userid,expirescore from ... where ... limit 0,10;
- -- cursor end
- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done = 1;
- -- exception occur
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION rollback;
- SET rtn = 0;
- open cur;
- fetch cur into v_userid,v_expirescore;
- while v_done = 0 do
- select ...,... into v_totalscore,v_freescore from ... where ... = v_userid;
- select v_userid,v_totalscore,v_freescore;
- if v_freescore > v_expirescore then
- set v_freescore = v_freescore - v_expirescore;
- else
- set v_freescore = 0;
- end if;
- insert into ... (...) values(v_userid,v_totalscore,v_freescore,now());
- &nb
sp; update ... set ... = v_freescore where ... = v_userid; - update ... set ... = 1 where ... = v_userid;
- commit;
- -- reset score
- set v_totalscore = 0;
- set v_freescore = 0;
- set rtn = rtn + 1;
- fetch cur into v_userid,v_expirescore;
- end while;
- close cur;
- END
- //
- delimiter ;
其中,里面有几个要点:
- delimiter //,是设置Mysql statement的分隔符,默认是分号;,所以需要先修改成其它的符号,最后修改回去
- out rtn int,设置存储过程的返回参数,使用call pro_ysz(@rtn);调用后,可通过select @rtn;来查看返回值
- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done = 1;当游标到末尾时,设置对应的变量,控制后续的循环
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION rollback;设置执行中有异常时,rollback,并继续后续的执行
- select v_userid,v_totalscore,v_freescore;在存储过程中模拟print语句来调试,可以打印出对应的变量值
最后,由一个Shell来执行
折叠复制代码
- cdate=`date +%Y%m%d%H%M%S`
- echo "$cdate Starting shell..."
- while [ 1 -lt 2 ]
- do
- mysql -e 'call pro_ysz(@rtn)' db
- sleep 5
- done