Description:
Cursor move to end & 'SELECT INTO variables' have no result are the same error code
How to repeat:
root@localhost : hotdb 03:32:44> SHOW CREATE TABLE h1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| h1 | CREATE TABLE `h1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 |
root@localhost : hotdb 03:34:51> SELECT * FROM h1;
+----+--------+
| id | name |
+----+--------+
| 1 | MySQL |
| 2 | Oracle |
| 3 | JAVA |
| 4 | PHP |
| 5 | Python |
| 20 | HTML5 |
| 21 | SWIFT |
+----+--------+
7 rows in set (0.00 sec)
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_select_no_data $$
CREATE PROCEDURE proc_select_no_data()
BEGIN
DECLARE i_num INT DEFAULT 0;
DECLARE i_cur_num INT DEFAULT 0;
DECLARE i_error TINYINT DEFAULT 0;
DECLARE i_while TINYINT DEFAULT 0;
DECLARE cur_test CURSOR FOR SELECT ID FROM h1 WHERE ID<10;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET i_error=2;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET i_error=1;
SET i_while=2;
OPEN cur_test;
WHILE i_while>0
DO
FETCH cur_test INTO i_cur_num;
WHILE i_error<>2
DO
SELECT id INTO i_num FROM h1 WHERE name='asdfdsfadfdf' limit 1;
SELECT CONCAT('while: ',i_while,' sqlexception: ',i_error,' i_num: ',i_num);
END WHILE;
SELECT CONCAT(REPEAT('*',i_while*i_cur_num*10),':',i_while,':',i_cur_num);
SET i_while=i_while-1;
END WHILE;
CLOSE cur_test;
END $$
DELIMITER ;
when I first execute the SQL like 'SELECT id INTO i_num' in the second WHILE statement,but the table h1 have no result to meet the condition of name='asdfdsfadfdf' and the SQL statement trigger the warning like :
Warning (Code 1329): No data - zero rows fetched, selected, or processed
this warning code the same as the error code of Cursor move to end
root@localhost : hotdb 04:03:59> call proc_select_no_data();
+---------------------------------------------------------------------------+
| CONCAT('while: ',i_while,' sqlexception: ',i_error,' i_num: ',i_num) |
+---------------------------------------------------------------------------+
| while: 2 sqlexception: 2 i_num: 0 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
+--------------------------------------------------------------------+
| CONCAT(REPEAT('*',i_while*i_cur_num*10),':',i_while,':',i_cur_num) |
+--------------------------------------------------------------------+
| ********************:2:1 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
+--------------------------------------------------------------------+
| CONCAT(REPEAT('*',i_while*i_cur_num*10),':',i_while,':',i_cur_num) |
+--------------------------------------------------------------------+
| ********************:1:2 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Suggested fix:
Suggest to separate the error code of Cursor move to end and 'SELECT INTO variables' have no result.