mysql prepare 游标_一个mysql存储过程例子，包括游标，prepare等

drop PROCEDURE if EXISTS updateIntentionPosition;

create procedure updateIntentionPosition(n int)

BEGIN

declare intenpositon varchar(255);

declare _positionId int default 0;

declare _resumeId int default 0;

declare stop int default 0;

declare v_sql varchar(500);

declare aaaa varchar(255);

declare cur cursor for

select positionId,resumeId from per_personresume where intentionPositions!='0' and LENGTH(intentionPositions)>0 and LENGTH(positionId)>0 and positionId<>'0' limit 1000;

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

OPEN cur;

FETCH cur INTO _positionId,_resumeId;

WHILE stop <> 1 DO

#select _positionId;

if _positionId!='0' and length(_positionId)>0

THEN

set v_sql= concat('select GROUP_CONCAT(nodeName) as nname into @gg from bas_positionfunction where nodeValue IN(',_positionId,")");

set @v_sql=v_sql;

prepare stmt from @v_sql;

execute stmt;

if length(@gg)>0 THEN

update per_personresume set intentionPositions=@gg where resumeId=_resumeId;

select concat('update',_resumeId,",",@gg);

end if;

end if;

FETCH cur INTO _positionId,_resumeId;

END WHILE;

CLOSE cur;

END