一个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

转载于:https://www.cnblogs.com/zhouzhou163/p/3548130.html