昨天写的一个东东，分享下给大家。 drop PROCEDURE if exists sp_cleanUserData; CREATE PROCEDURE `sp_cleanUserData`() BEGIN /*定义游标*/ declare v_dt bigint(20) default 0 ; declare v_num INT DEFAULT 0;
/*游标循环到末尾时给定义的常量赋值*/ declare cur_userId CURSOR FOR select userId from user_level_info where DATE_SUB(CURDATE(), INTERVAL 60 DAY) >= firstLoginDate and lv<=10 and vip=0 ; declare CONTINUE HANDLER FOR SQLSTATE '02000' SET v_dt = -1; /*开游标*/ OPEN cur_userId; /*游标赋值*/ FETCH cur_userId INTO v_dt; set v_num=1; /* 循环体 */ WHILE ( v_dt !=-1 ) DO /* 用户任务表 */ delete from task_user where user_id =v_dt; /* 玩家公告 */ delete from user_action_info where userId =v_dt; /*每循环100次commit下*/ set v_num= v_num+1; if v_num>100 then commit; set v_num=1; end if; /*读取下一行的数据*/ FETCH cur_userId INTO v_dt; /*循环结束*/ END WHILE; /*关闭游标*/ CLOSE cur_userId; END; call sp_cleanUserData;
查看更多关于mysql中游标在存储过程中的详细用法 - mysql数据库的详细内容...