数据库版本:MYSQL5.1
代码功能:游标嵌套,外层游标从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;
代码功能:游标嵌套,外层游标从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;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货