MySQL Forums
Forum List  »  Stored Procedures

procedure can not fetch data with cursor
Posted by: jim xie
Date: April 27, 2017 10:48PM

mysql version is
Server version: 5.5.54-0ubuntu0.14.04.1 (Ubuntu)

table's sql as follows:

drop table if exists testinfo;
create table testinfo
(
name varchar(10) not null,
age int
);
truncate table testinfo;
insert into testinfo() values('you',15);
insert into testinfo() values('me',20);


mysql> select * from testinfo;
+------+------+
| name | age |
+------+------+
| you | 15 |
| me | 20 |
+------+------+
2 rows in set (0.00 sec)

procedure sql as folows:

drop procedure if exists test_proc;
delimiter //
create procedure test_proc()
begin
DECLARE done int default false;
DECLARE a char(10);
DECLARE b int;
DECLARE cur1 cursor for select name,age from testinfo ;
DECLARE continue handler for SQLSTATE '02000' set done = true;

open cur1;

read_loop: loop
FETCh cur1 into a,b;
if done then
leave read_loop;
end if;
select @a,@b;
insert into testinfo values(@a,@b);
end loop;

close cur1;
end
//
delimiter ;
call test_proc();

result as follows:
mysql> \. /opt/mysql/test_proc.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------+------+
| @a | @b |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

ERROR 1048 (23000): Column 'name' cannot be null

my question is why fetched data is null。 thank you!

Options: ReplyQuote


Subject
Views
Written By
Posted
procedure can not fetch data with cursor
1157
April 27, 2017 10:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.