IN `dbid` varchar(20)BEGIN
#Routine body goes here...
DECLARE done INT DEFAULT 0;
DECLARE dbgid INT DEFAULT 0;
DECLARE gid INT DEFAULT 0; # the first cate
DECLARE mycursor1 CURSOR FOR SELECT dbcenter_goods_id,goods_id FROM qg_goods WHERE database_id=dbid; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN mycursor1;
REPEAT
FETCH mycursor1 INTO dbgid,gid;
if NOT done then
SET @var = 0;
set @sql=CONCAT('select goods_id into @var from `',dbid,'`.qg_goods ', ' where goods_id = ', gid);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
if(@var <> gid) THEN
DELETE FROM qg_goods WHERE dbcenter_goods_id=dbgid;
END if; DEALLOCATE PREPARE stmt1;
END if;
UNTIL done END REPEAT;
CLOSE mycursor1; 红字的地方只执行了一次,请问为啥 ?要怎样才执行多次。
#Routine body goes here...
DECLARE done INT DEFAULT 0;
DECLARE dbgid INT DEFAULT 0;
DECLARE gid INT DEFAULT 0; # the first cate
DECLARE mycursor1 CURSOR FOR SELECT dbcenter_goods_id,goods_id FROM qg_goods WHERE database_id=dbid; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN mycursor1;
REPEAT
FETCH mycursor1 INTO dbgid,gid;
if NOT done then
SET @var = 0;
set @sql=CONCAT('select goods_id into @var from `',dbid,'`.qg_goods ', ' where goods_id = ', gid);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
if(@var <> gid) THEN
DELETE FROM qg_goods WHERE dbcenter_goods_id=dbgid;
END if; DEALLOCATE PREPARE stmt1;
END if;
UNTIL done END REPEAT;
CLOSE mycursor1; 红字的地方只执行了一次,请问为啥 ?要怎样才执行多次。
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
set @sql=CONCAT('select goods_id from `',dbid,'`.qg_goods ', ' where goods_id = ', gid);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
if FOUND_ROWS() <=0 then
DELETE FROM dbcenter.qg_goods WHERE dbcenter_goods_id=dbgid;
end if; 我用行数来判断是不是有, 其他信息不用了。
set @sql=CONCAT('select goods_id into @var from `',dbid,'`.qg_goods ', ' where goods_id = ', gid);
if NOT done then注意一下这个 done, 是系统自动控制的,在执行其它语句的时候也会导致这个 done 变量值变化。
set @gname
set @sql=CONCAT('select goods_id,goods_name into @gname from `',dbid,'`.qg_goods ', ' where goods_id = ', gid);这个里面的 @gname 要怎么写?? 谢谢。
END if;
SET done = 0;
UNTIL done END REPEAT;
CLOSE mycursor1;
if NOT done then
SET @var = 0;
set @sql=CONCAT('select goods_id into @var from `',dbid,'`.qg_goods ', ' where goods_id = ', gid);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
if(@var <> gid) THEN
DELETE FROM qg_goods WHERE dbcenter_goods_id=dbgid;
END if;DEALLOCATE PREPARE stmt1;
END if;
set done=oldDone; UNTIL done END REPEAT;