关于mysql创建游标

CREATE DEFINER=root@% PROCEDURE pr_club_reports(IN uuid bigint)
COMMENT ‘俱乐部报表’
BEGIN
#发展玩家下级个数()
declare playerNo int DEFAULT 0; #声明变量
#发展玩家下级个数()
declare dlUsercode VARCHAR(255) DEFAULT 0;
#下级玩家usercode
declare allPLayer VARCHAR(255) DEFAULT 0;
#下级玩家贡献总额
declare playerTotalGX bigint default 0;

#用于游标循环遍历存储值
declare uuids bigint default 0;
– 游标结束的标志
declare done int default 0;

 declare ret int default 0;

#select userCode INTO dlUsercode from tb_user_account_info where uid=uuid;
#代理usercode
select userCode into dlUsercode from tb_user_account_info where uid=uuid;

BEGIN

#游标 下级玩家汇总 #创建游标名字             #查询一,二，三级代理
declare playerSum cursor for  select uid from tb_user_account_info where userCode like CONCAT(dlUsercode, '%') 
   and LENGTH(userCode)-LENGTH(REPLACE(usercode,'|',''))<=3 and uid<>uuid;
#select uid from tb_user_account_info where userCode like CONCAT(uuid, '%') 
  # and LENGTH(userCode)<LENGTH(uid)+20 and uid<>uuid;

    #设定游标跳出循环的初始值
  DECLARE CONTINUE HANDLER for  not FOUND set done =1;


-- 打开游标
open playerSum;
		-- loop 循环
		player:loop
				-- 根据游标当前指向的一条数据  
				fetch playerSum into uuids;
				-- 当 游标的返回值为 1 时 退出 loop循环 
				if done = 1 then
						leave player;
				end IF; 
           
             #获取贡献总数 and差时间
						#select sum(ABS(change_value)) into playerTotalGX from tb_account_gold_change_record where uid=uuids;  
           #上周的贡献总数
          select IFNULL(sum(ABS(IFNULL(change_value,0))),0) into playerTotalGX from tb_account_gold_change_record where uid=uuids AND YEARWEEK(change_dt,1) = YEARWEEK(NOW(),1)-1; 
                       
           #注意：插入tb_agency_reports表前，需要想删除改UID的所有记录。
          DELETE  FROM tb_agency_reports WHERE uid = uuids;
         
          #todo 循环，通过当前时间now(),来判断今天星期几，如果今天星期三，这需要向表(tb_agency_reports)插入4条数据reports。
					  #select  WEEKDAY (endDate) as week ;
        #  SELECT date_format(NOW(),'%w') AS weeks INTO mnber

            #得到周几加2
         # SET nmbers = mnber+1 ;

           #SET bers = 0

           #WHILE bers <= nmbers DOSERT 
                IF playerTotalGX>0 THEN
                 INSERT INTO tb_agency_reports (uid,week,updatetime,contribution_value,develop_sum,touid)VALUES(uuids,-1,NOW(),playerTotalGX,2,uuid);
                 END IF;
								#添加上周的所有贡献值

            #添加这周的贡献值
          INSERT INTO tb_agency_reports (uid,week,updatetime,contribution_value,develop_sum,touid)
        
         select uid,date_format(change_dt,'%w') as weeks,NOW() as cdate,sum(ABS(IFNULL(change_value,0))) as num,2,uuid from tb_account_gold_change_record
         where uid=uuids AND YEARWEEK(change_dt,1) = YEARWEEK(NOW(),1) 
        GROUP BY uid,date_format(change_dt,'%w'),DATE_FORMAT(change_dt,'%Y-%m-%d');
           #SET bers = bers + 1;
             #添加语句
          # IN

          #END WHILE;
		 end  loop player;
		-- 关闭游标
		close playerSum;
	 
END;		


#代理usercode
#select userCode into dlUsercode  from tb_user_account_info where uid=uuid;

#发展下级玩家个数
#IF(startDate!=''&&endDate!='') THEN
#			select count(*) into playerNo from tb_user_account_info where userCode like CONCAT(dlUsercode, '%') and 
#		(LENGTH(usercode)<LENGTH(dlUsercode)+10) and register_dt>=startDate and register_dt<=endDate;

ELSEIF(startDate!=’’) THEN

select count(*) into playerNo from tb_user_account_info where userCode like CONCAT(dlUsercode, ‘%’) and

#		(LENGTH(usercode)<LENGTH(dlUsercode)+10)and register_dt>=startDate and register_dt<=NOW();

ELSE

#			select count(*) into playerNo from tb_user_account_info where userCode like CONCAT(dlUsercode, '%') and 
#		(LENGTH(usercode)<LENGTH(dlUsercode)+10);

END IF;

#发展下级玩家贡献总额
#  select sum(ABS(change_value)) into playerTotalGX from tb_account_gold_change_record where uid in(select SUBSTRING_INDEX(usercode,'|',-1) from tb_user_account_info where userCode like CONCAT(dlUsercode, '%') and 
	#	LENGTH(usercode)<LENGTH(dlUsercode)+10);
#发展一级代理
#发展二级代理
#发展三级代理

select * FROM tb_agency_reports WHERE touid = uuid ;
END