存储过程
由于我们后台代码编写除了一些问题,需要在线上进行批量的数据处理。有两种选择 ,一是利用sql执行,二是存储过程。如果使用sql进行执行,速度比较慢。所以选择存储过程进行执行是一个很好的选择。
存储过程变量的定义与赋值
DECLARE param_1 INT ;(注意一定要有分号)
DECLARE(参数名)(参数类型) ;
变量的赋值:
SELECT COUNT(*) into param_1 from zyzs_smokecollector_tasks_record where taskName=’品吸任务’
将一个值赋值给我们定义的变量 into 变量 .
创建一个简单的存储过程:
create PROCEDURE example1()
BEGIN
DECLARE param_1 INT;
SELECT COUNT(*) into param_1 from zyzs_smokecollector_tasks_record where taskName='品吸任务' and createDate>'2018-02-03 09:00:00' ;
UPDATE zyzs_smokecollector_order SET count=@param_1 WHERE id='8a9a234c5f715c48015f71c6b53f381b'
END
存储过程中的条件语句
IF 条件 Then
Else
End IF存储过程中的游标
创建一个游标,来迭代SQL语句所返回的结果集。
DECLARE _cur CURSOR FOR SELECT openid,COUNT(openid) as c from zyzs_mysupports t WHERE t.count=0 GROUP BY openid HAVING c=1;
将一个集合放入游标中
一个游标的应用示例:
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE _openid varchar(32) DEFAULT NULL;
DECLARE _c int DEFAULT 0;
DECLARE num INT;
DECLARE _cur CURSOR FOR SELECT openid,COUNT(openid) as c from zyzs_mysupports t WHERE t.count=0 GROUP BY openid HAVING c=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN _cur;
this_loop: LOOP
FETCH _cur INTO _openid ,_c;
IF done = 1 THEN /*End */
LEAVE this_loop;
END IF;
SELECT COUNT(*) into num from zyzs_smokecollector_tasks_record where openid=_openId and taskName='品吸任务' and createDate>'2018-02-01 09:00:00' ;
IF num=0 THEN
UPDATE zyzs_smoke_collector set totalSupports=totalSupports+10 WHERE openid=_openid;
END IF;
END LOOP this_loop;
CLOSE _cur;
END