存储过程中使用游标遍历数据问题。

疯飞 2010-04-23 01:59:01

数据库版本：MYSQL５.１
代码功能：游标嵌套，外层游标从A表中取出uid，赋给内层游标从B表中检索出fuid对应记录的条件，然后对数据进行处理，最后将所有fuid连接起来并存入C表中。代码如下。
BEGIN
DECLARE p_uid bigint(20) DEFAULT '0';
DECLARE p_friendUid bigint(20) DEFAULT '0';
DECLARE doneUid int(10) DEFAULT '0';
DECLARE doneFuid int(10) DEFAULT '0';
DECLARE p_fuidStr text ;

DECLARE userId CURSOR FOR SELECT uid FROM sns_member;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneUid=1;
OPEN userId;
REPEAT
FETCH userId INTO p_uid;
IF(NOT doneUid) THEN
BEGIN
DECLARE addStr varchar(255) DEFAULT '';
DECLARE tempStr text;
DECLARE searchFuid CURSOR FOR SELECT DISTINCT(fuid) FROM sns_friend WHERE uid=p_uid AND status=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneFuid=1;

SET p_fuidStr='';
OPEN searchFuid;
REPEAT
FETCH searchFuid INTO p_friendUid;
IF (NOT doneFuid) THEN
IF((SELECT COUNT(uid) FROM sns_friend WHERE uid=p_friendUid AND fuid=p_uid AND status='1')<1) THEN
INSERT INTO sns_friend(uid,fuid ,fusername,status,gid,note,num,new,dateline) VALUES(p_friendUid,p_uid,(SELECT name FROM sns_space WHERE uid=p_uid) ,1,0,' ',0,0, UNIX_TIMESTAMP(now()));
ELSEIF( (SELECT COUNT(uid) FROM sns_friend WHERE uid=p_friendUid AND fuid=p_uid AND status='1') >1)THEN
DELETE FROM sns_friend WHERE uid=p_friendUid AND fuid=p_uid AND status='1' AND id NOT IN(SELECT * FROM ( SELECT id FROM sns_friend WHERE uid=p_friendUid AND fuid=p_uid AND status='1' GROUP BY fuid) AS friend_b WHERE sns_friend.id=friend_b.id );
END IF;
END IF;
SET addStr=CONCAT(p_friendUid,',');
SET p_fuidStr= CONCAT(p_fuidStr,addStr);
UNTIL doneFuid=1
END REPEAT;
CLOSE searchFuid;
IF(LENGTH (p_fuidStr)>1) THEN
SET tempStr=SUBSTRING(p_fuidStr, '1' , LENGTH (p_fuidStr)-1) ;
UPDATE sns_spacefield SET friend=tempStr, feedfriend =tempStr WHERE uid=p_uid;
ELSE
UPDATE sns_spacefield SET friend='0', feedfriend =' 0 ' WHERE uid=p_uid;
END IF;
END;
END IF;
UNTIL doneUid=1
END REPEAT;
CLOSE userId;
END
问题：当将外层游标修改成　DECLARE userId CURSOR FOR SELECT uid FROM sns_member　 WHERE uid=inputUid;在运行时输入参数，能够走常运行。想让它循环遍历就不执行，如外层游标现在这样　DECLARE userId CURSOR FOR SELECT uid FROM sns_member;

...全文