我是一个小白,刚开始学习。写了一个存储过程总是进入死循环。求帮忙
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:loopselect 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
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:loopselect 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
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
declare leftId varchar(32);/* 声明变量:左边安置的id*/
declare rightId varchar(32);/* 声明变量:右边边安置的id*/
declare nextId varchar(32);/* 声明变量:父级id*/set nextId=shop_in_id;
/* addTO 循环 开始*/
addTO:loopif 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