MySQL储存过程实战小案例

近期项目上需要做一个小功能，每一个付费会员，每周的收益红包，必须有推荐的新注册才能领取，之前 的推荐人也算，但是之前没有计算过这个东西，需要写一个存储过程更新 一下，之前的哪个会员推荐了多少人，将数字更新的到这个字段里。

CREATE PROCEDURE init_tj()
begin
DECLARE s int DEFAULT 0;
DECLARE tj_num int unsigned default 0;
DECLARE tj_id varchar(255) DEFAULT '';
DECLARE report CURSOR FOR select reference_id, count(1) from app_user

group by reference_id having count(1)>0 and

reference_id !=1 and reference_id is not

null;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
open report;
fetch report into tj_id,tj_num;
while s<>1 do
update app_user set reference_num=tj_num where id=tj_id;
fetch report into tj_id,tj_num;
end while;
close report;
end;