mysql使用游标循环结果集

存储过程：

CREATE DEFINER=`orion`@`%` PROCEDURE `task`()
BEGIN
		#定义需要操作结果集中的变量
		declare id int(11);
		declare name varchar(50);
		#设置开始标志，默认是true，如果为false循环结束
		declare flag int default true;
		#将结果集扔进定义的游标中
		

    declare yb cursor for select chapter_id,if(chapter_name REGEXP '^[0-9]',REPLACE(REPLACE(SUBSTRING_INDEX(chapter_name,' ',-1), CHAR(10),''), CHAR(13),''),chapter_name) as tmp from sc_title_chapter where material_id=123398 and grade_id in(158737,158738,158739,158740,158741,158742) and subject_id='s101'and chapter_name not  REGEXP '单元|综合|习作|语文|口语|读书|诗|文言文';
    		#声明如果flag为false时循环结束
    		declare continue handler for not found set flag = false;
    		#开是循环游标
    		open yb;
    		#具体操作
    		repeat
    					fetch yb into id,name;
    					set @chapter_id=(select chapter_id from sc_title_chapter where chapter_name REGEXP name and total!=0 order by total desc limit 1);
    					#if(@chapter_id!="")
    					insert into tmp values(@chapter_id,id,name);
    					#end if;
    		#循环结束
    		until flag = false
    		end repeat;
    		close yb;
    END

执行语句

delete from tmp;
call task;

记录结束！