还是要严谨些,害人害己
begin
DECLARE myid int;
DECLARE notfound INT DEFAULT 0; #定义一个辅助变量用于判断
DECLARE mycursor CURSOR FOR SELECT DISTINCT c.userid FROM hde.chio c LEFT JOIN DB_USER.User_TAB u on c.userid = u.userID where DATE_FORMAT(FROM_UNIXTIME(c.addtimestamp/1000),'%Y-%m-%d') = DATE_FORMAT(_date,'%Y-%m-%d') and c.userid > 0 ; #把select出来的数据全部保存到游标mycursor中
DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfound = 1; #定义declare continue handler, 必须在游标定义后定义
OPEN mycursor; #打开游标
REPEAT
fetch mycursor into myid; #读取游标中的数据一一复给变量myid。
CALL admin_user_staticstic_procedure(myid,DATE_FORMAT(_date,'%Y-%m-%d'));
UNTIL notfound END REPEAT;#判断是不是到了最后一条数据
close mycursor;#最后关闭游标.游标里面存放了那么多数据总是要清理掉的吧。
end
begin
DECLARE myid int;
DECLARE notfound INT DEFAULT 0; #定义一个辅助变量用于判断
DECLARE mycursor CURSOR FOR SELECT DISTINCT c.userid FROM hde.chio c LEFT JOIN DB_USER.User_TAB u on c.userid = u.userID where DATE_FORMAT(FROM_UNIXTIME(c.addtimestamp/1000),'%Y-%m-%d') = DATE_FORMAT(_date,'%Y-%m-%d') and c.userid > 0 ; #把select出来的数据全部保存到游标mycursor中
DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfound = 1; #定义declare continue handler, 必须在游标定义后定义
OPEN mycursor; #打开游标
REPEAT
fetch mycursor into myid; #读取游标中的数据一一复给变量myid。
CALL admin_user_staticstic_procedure(myid,DATE_FORMAT(_date,'%Y-%m-%d'));
UNTIL notfound END REPEAT;#判断是不是到了最后一条数据
close mycursor;#最后关闭游标.游标里面存放了那么多数据总是要清理掉的吧。
end
其实是一个意思;注意循环退出条件和游标结果集非空处理,不然会抛异常:Procedure execution failed 1329 - No data - zero rows fetched, selected, or processed.....
切记切记
修改存储过程定义者:
update mysql.proc set DEFINER='usename' WHERE NAME='proc_name' AND db='mydb';