一下是我写的存储过程
delimiter $$
create procedure xx009()
begin
declare i_Playerid bigint(20) unsigned;
declare i_mynum bigint(20) unsigned;
declare i_othernum bigint(20) unsigned;
declare i_id bigint(20) unsigned;
declare i_my bigint(20) unsigned;
declare i_other bigint(20) unsigned;
DECLARE done1,done2 INT DEFAULT 0;
declare cur_landarea cursor for select playerid from uw_playrer_dynamic_info;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = 1; open cur_landarea;
cur1_loop:LOOP
fetch cur_landarea into i_Playerid;
IF done1=1 THEN
LEAVE cur1_loop;
END IF;
BEGIN
DECLARE cur CURSOR FOR select count(dynamic_type = 0 or NULL)as a,count(dynamic_type = 1 or NULL) as b from uw_playrer_dynamic_info WHERE playerid=i_Playerid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
open cur;
cur2_loop:LOOP
fetch cur into i_mynum,i_othernum,i_id;
IF done2=1 THEN
LEAVE cur2_loop;
END IF;
if i_mynum > 100 then
set i_my = i_mynum - 50;
SET @sqlcmd = CONCAT('delete FROM uw_playrer_dynamic_info WHERE playerid =', i_id, 'AND dynamic_type=1 order by id asc limit', i_my );
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
if i_othernum > 50 then
set i_other = i_othernum - 50;
SET @sqlcmd = CONCAT('delete FROM uw_playrer_dynamic_info WHERE playerid=',i_id,' AND dynamic_type=1 order by id asc limit' ,i_other);
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
END LOOP cur2_loop;
close cur;
end;
END LOOP cur1_loop;end;当我在mysql上 call 00xx9时就是报 can't return a result set in given context
是怎么回事? 怎么解决呢?
delimiter $$
create procedure xx009()
begin
declare i_Playerid bigint(20) unsigned;
declare i_mynum bigint(20) unsigned;
declare i_othernum bigint(20) unsigned;
declare i_id bigint(20) unsigned;
declare i_my bigint(20) unsigned;
declare i_other bigint(20) unsigned;
DECLARE done1,done2 INT DEFAULT 0;
declare cur_landarea cursor for select playerid from uw_playrer_dynamic_info;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = 1; open cur_landarea;
cur1_loop:LOOP
fetch cur_landarea into i_Playerid;
IF done1=1 THEN
LEAVE cur1_loop;
END IF;
BEGIN
DECLARE cur CURSOR FOR select count(dynamic_type = 0 or NULL)as a,count(dynamic_type = 1 or NULL) as b from uw_playrer_dynamic_info WHERE playerid=i_Playerid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
open cur;
cur2_loop:LOOP
fetch cur into i_mynum,i_othernum,i_id;
IF done2=1 THEN
LEAVE cur2_loop;
END IF;
if i_mynum > 100 then
set i_my = i_mynum - 50;
SET @sqlcmd = CONCAT('delete FROM uw_playrer_dynamic_info WHERE playerid =', i_id, 'AND dynamic_type=1 order by id asc limit', i_my );
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
if i_othernum > 50 then
set i_other = i_othernum - 50;
SET @sqlcmd = CONCAT('delete FROM uw_playrer_dynamic_info WHERE playerid=',i_id,' AND dynamic_type=1 order by id asc limit' ,i_other);
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
END LOOP cur2_loop;
close cur;
end;
END LOOP cur1_loop;end;当我在mysql上 call 00xx9时就是报 can't return a result set in given context
是怎么回事? 怎么解决呢?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货