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 |
解决方案 »
- 奇怪的循环
- mysql 分布式数据库代理 的解决方案
- MYSQL有没有象sqlserver2000的sql语句 如top 返回前1行记录
- 我想在一句查询语句中得到不同条件的几种统计结果,SQL这样写不对?
- MySQL有没有可视化管理的工具?就像MS SQLServer的Enterprise Manager一样的工具?
- WINDOWS下的MYSQL的哪个版本支持LOAD DATA LOCAL INFILE...
- 请问使用LONGBLOB字段需要做什么设置
- 添加外键爆错:Duplicate key name 求大神
- MYSQL 内存占用率居高不下
- MySQL找不到列名,列名真的存在
- 初学mysql,买了个虚拟主机空间,用什么客户端管理最好呢?
- 初学POSTGRESSQL 的问题
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;
$$