存储过程求高人！~~~~总是进入死循环

[qqwwa1]

我是一个小白，刚开始学习。写了一个存储过程总是进入死循环。求帮忙


BEGIN
declare leftId varchar(32);/* 声明变量：左边安置的id*/
declare rightId varchar(32);/* 声明变量：右边边安置的id*/
declare nextId varchar(32);/* 声明变量：父级id*/

set nextId=shop_in_id;
set leftId = '0';
set rightId='0';


/* addTO 循环 开始*/
lop1:loop

select id into leftId from member where leftPartition_id =nextId;/* 获得 父级 左边 id 分区*/
if leftId is not null then
update member set leftPartitionNumber = leftPartitionNumber+1 where id=leftId;/* 获得 父级 左边 分区数+1*/
else
set leftId = '0';
select id into rightId from member where rightPartition_id = nextId;/* 获得 父级 右边 id 分区*/
if rightId is not null then
update member set rightPartitionNumber =rightPartitionNumber+1 where id=rightId;/* 获得 父级 右边 分区数+1*/

else
set rightId='0';
end if;
end if;


if leftId !='0' then
set nextId=leftId;
end if;

if rightId !='0' then
set nextId=rightId;
end if;

if rightId = '0' and leftId ='0' then
leave lop1;/* addTO 循环 跳出结束*/
end if;

end loop;
END

[qqwwa1]

……等人啊啊啊。啊啊啊啊啊啊啊啊 啊啊啊啊啊 啊啊啊 啊啊啊啊啊啊啊 啊 啊啊啊啊 啊啊 啊啊啊

[qqwwa1]

各种方法都使了，不知道怎么解决啊。求高人指点下就行。

[qqwwa1]

help^~~~~~~~~~~~~~~~~~~

[ACMAIN_CHM]

建议参考一下手册中的写法，使用handler CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; OPEN cur2; REPEAT FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END IF; UNTIL done END REPEAT; CLOSE cur1; CLOSE cur2; END

[qqwwa1]

我这个 是怎么错了啊？

[qqwwa1]

表示问题解决了。分送给版主吧，个人感觉可能是变了传递的时候和自己想的不一样,本初想弄成递归，希望高人指点。我继续研究下。本人纯自学希望高手帮忙 BEGIN declare leftId varchar(32);/* 声明变量：左边安置的id*/ declare rightId varchar(32);/* 声明变量：右边边安置的id*/ declare nextId varchar(32);/* 声明变量：父级id*/ set nextId=shop_in_id; /* addTO 循环 开始*/ addTO:loop if exists (select id from member where leftPartition_id =nextId) then select id into leftId from member where leftPartition_id =nextId;/* 获得 父级 左边 id 分区*/ set nextId=leftId; update member set leftPartitionNumber = leftPartitionNumber+1 where id=leftId;/* 获得 父级 左边 分区数+1*/ else if (select id from member where rightPartition_id =nextId) then select id into rightId from member where rightPartition_id = nextId;/* 获得 父级 右边 id 分区*/ set nextId=rightId; update member set rightPartitionNumber =rightPartitionNumber+1 where id=rightId;/* 获得 父级 右边 分区数+1*/ else leave addTO;/* addTO 循环 跳出结束*/ end if; end if; end loop; END