mysql存储过程建临时表和游标

CREATE PROCEDURE two(IN `mount` integer)
BEGIN
	DECLARE aa INT(11);
	DECLARE bb INT(11);
	DECLARE cc VARCHAR(222);
	#select yin into bb from cihai where yin like '%/%' ;
	set aa = 1;
	drop TEMPORARY table if EXISTS temp;
	create TEMPORARY table temp(`lr` int(11) auto_increment,`id` int(11),`yin` varchar(255),PRIMARY key(`lr`));
	insert into temp(id,yin) select id,yin from cihai where yin like '%/%';
	mylp:loop

		select id,yin into bb,cc from temp where lr=aa;
		update cihai set yin = (select REGEXP_replace(cc, '/.*?( |$)', ' ') as yin) where id = bb;
		set aa = aa+1;
		IF aa>mount THEN
			LEAVE mylp;
		END IF;
  end loop mylp;
drop TEMPORARY table if EXISTS temp;
END

由于在一条语句里又查询又更新会报错，只能建临时表做个中转，lr是自增列，再把要修改的数据insert进临时表，再用正则修改数据再update，循环跑完删掉临时表。

更新游标处理方式，效率更高

BEGIN
	DECLARE aa INT(11);
	DECLARE bb INT(11);
	DECLARE cc VARCHAR(222);
	DECLARE done INT;
	DECLARE rs_cursor CURSOR FOR select id,yin from cihai where yin like '%/%';
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	OPEN rs_cursor; 
	cursor_loop:LOOP
	FETCH rs_cursor INTO bb,cc; -- 取数据
	IF done=1 THEN
		leave cursor_loop;
	END IF;
	-- 更新表
	UPDATE cihai SET yin=(select REGEXP_replace(cc, '/.*?( |$)', ' ') as yin) WHERE id=bb;
	END LOOP cursor_loop;
	CLOSE rs_cursor;

END