mysql游标使用实例讲解

游标（cursor）相当于一条sql对应的结果集

声明一个游标

declare 游标名 cursor for select_statement

打开游标

open 游标名

取值

fetch 游标名 into var1，var2，[，var3...]

关闭一个游标

close 游标名

例1：

mysql> delimiter //
mysql> create procedure demo1()
    -> begin
    -> declare c int;
    -> declare n varchar(20);
    -> declare total int default 0;
    -> declare done int default false;
    -> declare cur cursor for select username from users;
    -> declare continue handler for not found set done=true;
    -> set total=0;
    -> open cur;
    -> fetch cur into n;
    -> while(not done) do
    -> set total = total + 1;
    -> fetch cur into n;
    -> end while;
    -> close cur;
    -> select total;
    -> end;
    -> //

例2：

mysql> delimiter //
mysql> create procedure demo1()
    -> begin
    -> declare username varchar(20) default '';
    -> declare cur1 cursor for select username from users;
    -> declare continue handler for sqlstate '02000' set username=null;
    -> open cur1;
    -> fetch cur1 into username;
    -> while (username is not null) do
    -> set username = concat("hello",username);
    -> fetch cur1 into username;
    -> end while;
    -> close cur1;
    -> select username;
    -> end;
    -> //