mysql游标的应用

create procedure test(out d float)
begin
declare a,b int;
declare cur_1 cursor for select count(*) from sur_question;
DECLARE CONTINUE HANDLER for not found
set b = 1;
open cur_1;
  repeat
    fetch cur_1 into a;
    until b = 1
  end repeat;
close cur_1;
set d = a;
end



CREATE PROCEDURE p (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT count(*) from sur_question;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;


 /*初始化*/ 
 drop procedure if exists  useCursor //    
 
 /*建立 存储过程 create */ 
 CREATE PROCEDURE useCursor()
    BEGIN
    /*局部变量的定义 declare*/ 
         declare tmpName varchar(20) default '' ;
         declare allName varchar(255) default '' ;
         
         declare cur1 CURSOR FOR SELECT name FROM test.level ;
         
         /*    mysql 不知道为什么用异常加入判断 ？
          *    此请参考官方文档 20.2.11. 光标 光标 
          *        这把 游标 异常后 捕捉 
          *        并设置 循环使用 变量 tmpname 为 null 跳出循环。
          */
         declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
    
    
    /*开游标*/ 
     OPEN cur1;
         /*游标向下走一步*/ 
         FETCH cur1 INTO tmpName;
         
         /* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
      WHILE ( tmpname is not null) DO
          set tmpName = CONCAT(tmpName ,";") ;
          set allName = CONCAT(allName ,tmpName) ;
        /*游标向下走一步*/ 
        FETCH cur1 INTO tmpName;
      END WHILE;
  
    CLOSE cur1;
    
    select allName ;
END;//
call useCursor()//
 

运行结果：
mysql> call useCursor()//
+--------------------------------------+
| allName                              |
+--------------------------------------+
| f1;c3;c6;c5;c2;c4;c1;f1;f3;f4;f2;f5; |
+--------------------------------------+
1 row in set (0.00 sec)