DROP PROCEDURE IF EXISTS ruleipfltr_map_cardtype_proc;-- create the proc to map the cardtype
delimiter $$
create procedure ruleipfltr_map_cardtype_proc()
begin
DECLARE done INT DEFAULT 0;
DECLARE var_SP CHAR(17);
DECLARE var_RULENUM SMALLINT;
DECLARE var_SRC TINYINT UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT sp,ruleNum,src FROM RuleIpFltr;
OPEN cur1;
REPEAT
FETCH cur1 INTO var_SP,var_RULENUM,var_SRC; IF NOT done THEN
UPDATE RuleIpFltr SET cardType=3 WHERE sp=var_SP and ruleNum=var_RULENUM;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;
$$delimiter ;call ruleipfltr_map_cardtype_proc;这个存储过程执行会报这个错误
mysql> call ruleipfltr_map_cardtype_proc();
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed试试上数据时存在的。mysql> SELECT sp,ruleNum,src FROM RuleIpFltr;
+------------+---------+------+
| sp | ruleNum | src |
+------------+---------+------+
| MGT | 1025 | 1 |
| MGT | 1026 | 1 |
| MGT | 1027 | 1 |
| MGT | 1028 | 1 |
| MGT | 1029 | 1 |
| MGT | 1030 | 1 |
| MGT | 1031 | 1 |
| MGT | 1032 | 1 |
| MGT | 1033 | 1 |
| MGT | 1034 | 1 |
| MGT | 1035 | 1 |
| MGT | 1036 | 1 |
| MGT | 1037 | 1 |
delimiter $$
create procedure ruleipfltr_map_cardtype_proc()
begin
DECLARE done INT DEFAULT 0;
DECLARE var_SP CHAR(17);
DECLARE var_RULENUM SMALLINT;
DECLARE var_SRC TINYINT UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT sp,ruleNum,src FROM RuleIpFltr;
OPEN cur1;
REPEAT
FETCH cur1 INTO var_SP,var_RULENUM,var_SRC; IF NOT done THEN
UPDATE RuleIpFltr SET cardType=3 WHERE sp=var_SP and ruleNum=var_RULENUM;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;
$$delimiter ;call ruleipfltr_map_cardtype_proc;这个存储过程执行会报这个错误
mysql> call ruleipfltr_map_cardtype_proc();
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed试试上数据时存在的。mysql> SELECT sp,ruleNum,src FROM RuleIpFltr;
+------------+---------+------+
| sp | ruleNum | src |
+------------+---------+------+
| MGT | 1025 | 1 |
| MGT | 1026 | 1 |
| MGT | 1027 | 1 |
| MGT | 1028 | 1 |
| MGT | 1029 | 1 |
| MGT | 1030 | 1 |
| MGT | 1031 | 1 |
| MGT | 1032 | 1 |
| MGT | 1033 | 1 |
| MGT | 1034 | 1 |
| MGT | 1035 | 1 |
| MGT | 1036 | 1 |
| MGT | 1037 | 1 |
WHILE done=0 DO
UPDATE RuleIpFltr SET cardType=3 WHERE sp=var_SP and ruleNum=var_RULENUM;
set done=0;
FETCH cur1 INTO var_SP,var_RULENUM,var_SRC;
END WHILE;
DECLARE cur1 CURSOR FOR SELECT sp,ruleNum,src FROM RuleIpFltr;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
FETCH cur1 INTO var_SP,var_RULENUM,var_SRC;
WHILE done=0 DO
UPDATE RuleIpFltr SET cardType=3 WHERE sp=var_SP and ruleNum=var_RULENUM;
set done=0;
FETCH cur1 INTO var_SP,var_RULENUM,var_SRC;
END WHILE;
UPDATE RuleIpFltr SET cardType=3 WHERE sp=var_SP and ruleNum=var_RULENUM;
这句,估计是没有满足条件的记录
create procedure ruleipfltr_map_cardtype_proc()
begin
DECLARE done INT DEFAULT 0;
DECLARE var_SP CHAR(17);
DECLARE var_RULENUM SMALLINT;
DECLARE var_SRC TINYINT UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT sp,ruleNum,src FROM RuleIpFltr;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO var_SP,var_RULENUM,var_SRC; IF NOT done THEN
UPDATE RuleIpFltr SET cardType=3 WHERE sp=var_SP and ruleNum=var_RULENUM;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;
$$