iLeichun

//建立表结构
create table person(name varchar(10));

insert into person values(¹a¹);
insert into person values(¹b¹);
insert into person values(¹c¹);
insert into person values(¹d¹);
insert into person values(¹e¹);

//初始化
drop procedure if exists  cursorTest   

//建立
CREATE PROCEDURE cursorTest()
    BEGIN

 //定义变量
         declare name1 varchar(10) default ¹¹ ;
         declare name2 varchar(100) default ¹¹ ;

         declare mycursor CURSOR FOR select name from person;

         declare CONTINUE HANDLER FOR SQLSTATE ¹02000¹ SET tmpname = null;

 //打开游标
 OPEN mycursor;

 //遍历游标
 FETCH mycursor INTO name1;

 //把游标查询出的 name 都加起并用 ; 号隔开
 WHILE( name1 is not null) DO
  set name1 = CONCAT(name1, ";") ;
  set name2 = CONCAT(name2, name1) ;
  FETCH mycursor INTO name1;
 END WHILE;
 CLOSE mycursor;

 select mycursor;
    END;

//调用游标
call mycursor()

运行结果：

mysql> call mycursor()
+--------------------------------------+

| name2                                |

+--------------------------------------+

| a;b;c;d;e;                           |

+--------------------------------------+

1 row in set (0.01 sec)